Getting the desired recordset form different tables

[jarnold]

I have a situation where I want to search different tables where all tables have th field "casenumber" in common. I want to do a search by casenumber and the different tables hold different case types. After pressing the command button I would like to display the various data for that particular recordset. The idea I have is to go from table to table until the record is found. I have tried the following code but it only works for records in the last table.( There are more tables but I coded for two just to get started) Any and all help is appreciated.

Private Sub Command1_Click()
data.RecordSource = "SELECT * FROM aa_case ORDER BY casenumber"
data.Refresh
data.Recordset.FindFirst _
"CASENUMBER = '" & UCase(txtsearch.Text) & "'"
If data.Recordset.NoMatch Then
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 "RECORD NOT FOUND"
End If
End Sub

[JHausmann]

Try moving the data to a location (format a string and fill a multi-line text box, for example) before refreshing the data control.

[jarnold]

I probably wasn't clear in my initial explanation of the problem. Each table holds case information but at no time is an individual casenumber in more that one table. Thats the reason I thought I could search one table for a casenumber and go to the next and so on until it found the case. Any code help here would be appreciated.

[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

[jarnold]

Thanks