Solved: FindFirst method based on "two" fields matching, how?

[beginner]

hi all helpers....

i got a headache to display selected records in a subform based on selected comboBox's [BADGE_ID] and a Text's [DATE]

1. the mainForm sourced from a table while the subForm sourced from SQL of the same table.
2. have linked Master & Child the mainForm and subForm with [BADGE_NO]


i notice its quite common people use below coding for recordset FindFirst matching...(for single field matching), and it work fine.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
Set rs = Me.Recordset.Clone
rs.FindFirst "[BADGE_NO] = '" & Me![Combo_BADGE_NO] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''

but for the above "re.FindFirst...." what i need is something similar to below:
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
rs.FindFirst "[BADGE_NO] = '" & Me![Combo_BADGE_NO] & "'" And "[DATE] ='" & Me![Txt_date] & "'"

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''

however, the above "single line" code got error,

would anyone can help how to achieve recordset FindFirst method to base on two field matching ??

thanks in advanced. http://www.theofficeexperts.com/forum/images/smilies/crap.gif:help

[xCav8r]

Why not change the record source of the subform to a SQL statement instead?

[beginner]

i am not sure but i need to take a deeper look into it as i am still a bit confuse about the subform operation/programming. As i read some internet info. mentioned that if the Main and subform are linked by Master/Child field (that's in my case), then it can't be used VBA to control the recordSource etc. but i am not sure ?? need to study more and try on this....will be back..

Tks. for your tips again!

[xCav8r]

Using Master/Child fields make it easier, but you can always do mysubform.recordsource="Select Field1, Field2 FROM MyTable WHERE Condition1=Something And Condition2=SomethingElse"

[beginner]

Tks for your tips and i will try taking out those Master/Child fields link and use SQL instead and see what happen.....tks.

[beginner]

hi xCav8r.......need yr help.....

i have tried removing the Master/Child linked fields and also removed the Record Source property of the Subform (table1_Sub_form1) and put the below coding instead, but it can't run and error indicate can't locate the subform....any tips in my below coding error ? Tks in advanced.


Private Sub Combo_BADGE_NO_AfterUpdate()

' others declare....

Dim SQL As String

' [table1] is the table from which the subform generated the displayed fields data.

' below is a single line & BADGE_NO, DATE1 are the text fields in table1 & ID is the autoNumber in table1 too, while Combo_BADGE_NO and Txt_date are in the mainform for input.

SQL = "SELECT * FROM [table1] WHRERE BADGE_NO ='" & Me![Combo_BADGE_NO].Value & "' AND DATE1='" & Me![Txt_date].Value & "' ORDER BY ID DESC"

Forms![table1_Sub_form1].RecordSource = SQL

End Sub

[xCav8r]

What about referencing it with something like Me.SubFormName.RecordSource = SQL

[beginner]

hi xCav8r.......finally it works as below, tks for your help & tips!!

Me.[table1_Sub_form1].Form.RecordSource = SQL

:beerchug: