Link Multiple Tables to One Form

[kristyspdx]

Hello,

I have a form that requires user input. The user inputs a check number in an unbound text box and then the form pulls the data for that check number based off of Table 2. If the check number is not in Table 2, a message is displayed that the record can not be found. Here is the code. Keep in mind I know little about code and stole this from someone else, but it works great.

Private Sub Enter_Check_Number_AfterUpdate()
If (Enter_Check_Number & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Check Number]=""" & Enter_Check_Number & """"
If rs.NoMatch Then
MsgBox "Sorry, no such record was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark


End If
rs.Close
txtGoTo = Null
End Sub

What I need the form to do now is if the check number entered by the user can not be found in Table 2, to look at Table 3 and then Table 4 to find it. If it is not in Table 2, 3, or 4 it's okay to display the message that the record can't be found. There is no relationship between the three tables, but they all have exactly the same fields. Each record will be in one of the three tables only one time.

Thanks.

[pbaldy]

It is almost certainly a mistake to have 3 tables holding identical fields. If you can't change it, you can do what you're asking. You'd put your code to check table 3 in the "NoMatch" section of the first, and similarly for 4. The other problem you presumably face is that you can't set the bookmark to a record not in the recordset of the form, so you'd have to change the form's source or change forms. Having multiple tables for the same data is going to cause a lot of problems.

[June7]

I agree, why are there 3 tables that appear to be identitical in structure?