Getting the desired recordset form different tables

[JHausmann]

Try this:

Private Sub Command1_Click()

Findcase
'call sub to do something with data control that's been loaded, if at all

End Sub

Public Sub FindCase()

data.RecordSource = "SELECT * FROM aa_case ORDER BY casenumber"
data.Refresh
data.Recordset.FindFirst _
"CASENUMBER = '" & UCase(txtsearch.Text) & "'"
If data.Recordset.NoMatch Then
MsgBox "No record found for table aa_case"
else
Exit Sub
End If
data.RecordSource = "SELECT * FROM ad_case ORDER BY casenumber"
data.Refresh
data.Recordset.FindFirst _
"CASENUMBER = '" & UCase(txtsearch.Text) & "'"
If data.Recordset.NoMatch Then
MsgBox "No record found for table ad_case"
else
Exit Sub
End If

End Sub