Here is my situation:

I have 2 data controls - one is for a header table and the other is for a detail table. Let's say, to simplify things, the tables look like this

Header Table - fields
ID
X

Detail - fields
ID
NOTE

The recordsource for header is; SELECT * FROM HEADER WHERE X = 1 ORDER BY ID

The recordsource for detail is; SELECT * FROM DETAIL ORDER BY ID

Now, what I want to do, is open a third record set in my code that will use an inner join to get all the records in detail for all the records in header (where x=1). The sql without data control would look something like this (I think)

SELECT DETAIL.ID, DETAIL.NOTE, HEADER.ID FROM DETAIL INNER JOIN HEADER ON DETAIL.ID = HEADER.ID

I can't figure out the syntax (if even possible) on how to do this. (Because of the data controls) My closest gues is something like this.

"SELECT " & rstDetail & ".ID, " & rstDetail & " .NOTE"
...etc etc.

Does anyone have any ideas on this? if my problem is unclear please tell me.