Form\Sub Form SQL

[Little Penny]

I have four main tables in my database.

tbl_Table1 has a one to many relationship with table tbl_Table2,
tbl_Table3 and tbl_Table4. I also have a form who's record source is
loaded with

select * from tbl_Table1"

The form also has three sub forms for tbl_Table2, tbl_Table3 and
tbl_Table4. I created command buttons on two fields in table1 to
searches by criteria.

Form_frm_OperatorLogJobDataViewSQL.RecordSource = "select * from
tbl_Table1 where " & GCriteria

GCriteria = Forms![tbl_Table1ID]![cboSearchField] & " = " &
Forms![tbl_Table1 ID]![txtSearchString]


How can I do something similar but based on fields of one of the the
link tables. For example tbl_Table4

So my record source for the form would be Select From tbl_Table1
where field 1 in the linked table (tbl_Table4) = yellow

My goal is to use SQL

Hope this makes sense

Thanks



Little Penny

 

[Daryl S]

Little Penny -

You can change the record source of the main form to be:

Select tbl_Table1.* from tbl_Table1 INNER JOIN tbl_Table4 ON
tbl1_Table1.joinfieldname = tbl_Table4.joinfieldname
WHERE tbl_Table4.field1 = "yellow"