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

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

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?

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

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

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

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
This article has been dead for over six months. Start a new discussion instead.