Full Version: Filter form with a field from linked table
UtterAccess Forums > Microsoft® Access > Access Forms
msaccessprogrammer
Hi.
I have a form which I want to filter with a combo box. The row source of the combo box is a field in a table which is linked to the table the form is based on.
The tables are:
Table1:
PK, Field1, Field2, Field3 ...
Table 2:
PK, FK , FilterField ...
Table1 is linked to Table2 in 1 to many link. The form is based Table1 and I want to filter it with FilterField.
How can I do this ?
Thanks.
Jack Cowley
Table 1 is the One side of a One-to-Many relationship so you do not filter Table1 from Table2 but the other way round...
You should have a subform, based on Table2 on a form based on Table1...
hth,
Jack
msaccessprogrammer
Hi Jack, thanks for the reply.
Oknow this is unusual, but I want the form based on Table1, which is a continuous form, to display all the records in Table1, which have a certain value of FilterField in Table2. I'll put the combo box on the form based on Table1, and the filtering will be performed after I choose a value from it.
xample:
Table1:
PK=1,Name1, ID1,...
PK=2, Name2, ID2
Table2:
FK = 1, FilterField = "a",...
By choosing "a" in the combo box the continuous form will display all the records in table 1, which have a child FilterField with the value "a", for example the first record.
Hope this is clear enough. Is this scenario possible ?
Thanks.
Jack Cowley
I would put the continuous form as a subform on another form. I would put a combo box on the form and a command button with code similar to this. Select a value from the combo box (based on Table2) and then click the command button:
CODE
Dim strSQL As String
strSQL = "SELECT Table1.Table1ID, Table1.Field1, Table1.Field2 " _
& " FROM Table1 " _
& " WHERE (((Table1.Table1ID) In (SELECT Table2.Table1ID " _
& " FROM Table2 " _
& " WHERE (((Table2.Field1)='" & [Forms]![FormName]![ComboboxName & "'"));)));"
Me.SubformName.Form.RecordSource = strSQL

PURE AIR CODE AND UNTESTED, but this should get you started..
hth,
Jack
msaccessprogrammer
Thank you Jack, I tried it and it works great. sad.gif
Jack Cowley
You are most welcome and I am glad the suggestion worked for you... Continued success with your project...
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.