If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Form\Sub Form SQL
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 |
#2
|
|||
|
|||
Form\Sub Form SQL
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" -- Daryl S "Little Penny" wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|