How to search two tables in a combobox

[PharmaFun]

Hi there...I have two tables. What I want to do in a form is be able to search by SearchID in a combobox and to pull records with a matching number in two different tables. Here is the code that someone else graciously helped me to write, but it doesn't work. I'd be happy to send the file to anyone who might be able to help. I know nothing about Access, so please be patient.

Thanks,
Dino

Private Sub cboSearchID_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset, r2 As DAO.Recordset, SQL As String, i As Integer



Set rs = Me.Recordset.Clone


SQL = "SELECT * FROM [Initial Report] order by [Number];"

Set r2 = CurrentDb.OpenRecordset(SQL)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
If rs("Number&quot = Me.cboSearchID Then
Me.Bookmark = rs.Bookmark
Exit Do
End If

rs.MoveNext
Loop

r2.MoveLast
rs.MoveFirst
Do While Not r2.EOF
If r2("Number&quot = Me.cboSearchID Then
For i = 0 To 19
MsgBox Str(i) & " " & r2.Fields(i).Name & " " & r2.Fields(i)

Next i

Exit Do
End If

r2.MoveNext
Loop
Me.cboSearchID = Null
rs.Close
Set rs = Nothing

End Sub

 

[LonnieJohnson]

First let me be sure I understand what you want to do.

You have a combo box.
It has ID numbers in it.
You want to select a number from the combo box.
Then you want every record that has that same ID to be returned from either table and displayed on the form.

If that is the case, then I would have a subform with a recordset that is a "union query" of the two tables with a where clause for the ID pointing to your combobox. You can send me your dB and I will show you.

If I am wrong about what you want, please explain.





ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!