I would step back - write a SQL Query that pulls the information you want.
Then you only need to get your form to use that query.
ξ
It might be:
Code:
SELECT Pedie1.Names From Pedie1;
'----------------------------------------------------------------------
I've created a very simple DB with two tables:
<a href="http://northernocean.net/etc/mrexcel/20110408_db1.zip">SAMPLE DATABASE</a>
(md5sum for zip file: 8710260583eda7c54f0564fb06ba4e84)
(sha256sum for zip file: 02a1ad6e8cf61ef7c5813eaaad393bbd3db92a1b9a9140ac2565acaff0bbc410)
In the database we have:
Table
Pedie1
Table
Pedie2
There is also a very simple form:
Form1
When you open the form the form's load event asks you to enter a 1 or a 2. When you enter a 1, the form's record source is set to
Pedie1 (the table Pedie2). If you enter a 2 the form's record source is set to
Pedie2 (the table Pedie2). If you enter anything else, the form's record source will not be set and no data will show (you will see error's in the fields instead). Scrolling through the records you will see all of the records from the table chosen (we could use a datasheet view or continuous form view to show them all at once instead of one at a time).
The code is not useful as you need to write code to get the user's name. But the principle is the same. The code in the form's load event is:
Code:
Private Sub Form_Load()
Dim s As String
Dim rsp As String
'//We will use table Pedie1 or Table Pedie2 based on user input
rsp = InputBox("Enter 1 or 2")
If IsNumeric(rsp) Then
If rsp = 1 Then
s = "Pedie1"
Me.Label1.Caption = "Using Table Pedie1 "
Else
s = "Pedie2"
Me.Label1.Caption = "Using Table Pedie2 "
End If
End If
Me.RecordSource = s
End Sub
