Search in vb6

[brendalyn.quiba]

hi i cant seem to make the search code for my program work . . how is this so . .? this is my code:

Private Sub cmdSearch_Click()
With Adodc1
    .RecordSource = "Select * from PATIENT where Last Name='" & txtSearch.Text & "'"

If .Recordset.RecordCount = 0 Then
    MsgBox "No record found!", vbOKOnly + vbCritical, "Medical Record System"

    txtSearch.Text = ""
    txtSearch.SetFocus
End If

    If txtSearch.Text = "" Then
        Set Me.DataGrid1.DataSource = Adodc1
    Else
    Set rs = New ADODB.Recordset

rs.Open "Select * from PATIENT where Last Name=" & txtSearch.Text & ""
Set rptRecord.DataSource = rs
rptRecord.Show vbModal
Set rs = Nothing

    End If

Set Me.DataGrid1.DataSource = Adodc1
End With
End Sub

[brendalyn.quiba]

i also have a code for searching using date picker but it also doesnt work . . .

Private Sub cmdGen_Click()
With Adodc1
    .RecordSource = "Select * from PATIENT WHERE ((PATIENT.Date) >= #" & dtpFrom.Value & "#) AND ((PATIENT.Date)<=#" & dtpTo.Value & "#)"
If .Recordset.RecordCount = 0 Then
    MsgBox "No record found!", vbOKOnly + vbCritical, "Medical Record System"
Else
    .Recordset.MoveFirst
Do Until Adodc1.Recordset.EOF
    Adodc1.Recordset.MoveNext
Loop
    Set DataGrid1.DataSource = Adodc1
End If
End With
End Sub

[tinstaafl]

You'll probably have to be more specific. This is like taking a car to a mechanic and saying,"Fix it it's broken". What part doesn't work right?

[AndreRet]

Which line of your code is showing an error. If no errors, what happens when you click on the search button?

[ImZick]

Please tell what seem to be the problem?
which line and what messagebox said?

[brendalyn.quiba]

the problem with the codes is that it doesnt work . . no errors show yet nothing happens when i try to run the codes

[AndreRet]

First change this -

''.RecordSource = "Select * from PATIENT where Last Name='" & txtSearch.Text & "'"

TO

.RecordSource = "Select * from PATIENT where [Last Name]='" & txtSearch.Text & "'" ''Try and stay away from adding spaces between names in your fields

Wait, quite a few errors here. Here is the revised code -

Private Sub cmdSearch_Click()
With Adodc1
    .RecordSource = "Select * from PATIENT where [Last Name]='" & txtSearch.Text & "'"
If .Recordset.RecordCount = 0 Then
    MsgBox "No record found!", vbOKOnly + vbCritical, "Medical Record System"
    txtSearch.Text = ""
    txtSearch.SetFocus
End If
    If txtSearch.Text = "" Then
        ''Set Me.DataGrid1.DataSource = Adodc1
        ''You cant let your grid show anything if their is no search criteria added.
        Msgbox "Please select some search criteria first"

        txtSearch.Text = ""
        txtSearch.SetFocus
    Else
    Set rs = New ADODB.Recordset
rs.Open "Select * from PATIENT where [Last Name]=" & txtSearch.Text & ""

Set Me.DataGrid1.DataSource = Adodc1
''Add data to grid here...

Set rptRecord.DataSource = rs
rptRecord.Show vbModal
Set rs = Nothing
    End If
''Set Me.DataGrid1.DataSource = Adodc1 rs is closed, can't show it here...
End With
End Sub