Use field in table to filter other table

[fhickler]

Let’s say I have 2 tables brought into a form as subforms. "Subform1" has an index field of unique numbers called "Id" and "Subform2" has a number field called "MatchNum". I want to program a button on the form that, when clicked, will filter out every record in "Subform1" where the value of "Id" matches some value in the "Subform2" field "MatchNum".



Like this:

Subform1

Id	Other data...
01
02
03
04
05

Subform2

Id	MatchNum	Other data...
01	02
02	02
03	03
04	02
05	03

Clicking the button would filter Subform1 as:

Subform1

Id	Other data...
01
04
05

I'm looking for suggestions as to what VBA code to use for the button. Thanks in advance.

[June7]

Usually subform Master/Child Links manage synchronization of related forms. Subform1 appears to be the 'master' (parent) data and Subform2 the 'child' (dependent data). Normally child does not dictate to master.

Might be able to do something like:

Subform1.Form.RecordSource = "SELECT * FROM table WHERE ID IN (" & subform2.Form.RecordSource & ")"

[fhickler]

Thank you. I managed to make some progress based on your response. I have this code attached to the button:

strSQL = "SELECT * FROM table1 WHERE table1.ID IN (" & subform2.Form.MatchNum & ")"
Me![subform1].Form.RecordSource = strSQL

What this does is filters subform1 to show the record whose ID matches the currently selected record's MatchNum value in subform2. What I want is for it to do is filter subform1 so that it shows records whose ID matches ANY MatchNum value in subform2 (or doesn't show them - either way - I imagine once I get one of them, getting the inverse will be trivial)

I also successfully tried:

strSQL = "SELECT * FROM table1 WHERE table1.ID IN (3,5)"
Me![subform1].Form.RecordSource = strSQL

So it seems to me that all I have to do is get the values of the field MatchNum for all records in subform2 as a list. How would I do that?

EDIT:
I just came across this:
https://stackoverflow.com/questions/...mn-in-an-array
which indicates that I'd have to do it by looping through all the records. I guess I was hoping there was simpler way to do it. Is there?
EDIT2:
Just realized the above link was referring to VBA for Excel. Not sure if it is the same for Access.

[June7]

That link is for Excel. Excel sheets don't have recordsets.

My example referenced RecordSource property, not a field name. But did not test and example was incomplete.

If subform RecordSource is a simple SQL statement SELECT * FROM table2; following code will build an SQL statement.

Subform1.Form.RecordSource = "SELECT * FROM table1 WHERE NOT ID IN (SELECT MatchNum FROM (" & Replace(Me.subform2.Form.RecordSource, ";", "") & "));"

If subform RecordSource is simply a table or query name:

"SELECT * FROM table1 WHERE NOT ID IN (SELECT MatchNum FROM (SELECT * FROM " & Me.subform2.Form.RecordSource & "));"

Nested subquery serves as list. Including subform filter criteria will get a little more complicated. How is subform filtered?

If you want to provide db for analysis, follow instructions at bottom of my post.

[fhickler]

Wow that worked. Thank you. Now all I have to do is study this code so I actually understand it!
EDIT:
I guess I pretty much understand it. One part that threw me was the Replace - it seemed you were removing semicolons from the list, but I wasn't sure why. When I removed the Replace, it still seemed to work. What is the purpose of the Replace?