DoCmd.SearchforRecord Syntax 2 Conditions Help

[kalmi]

I'm trying to add an unbound combobox to locate records on my form. I am trying to use the DoCmd.Searchforrecord but I can't seem to figure out the correct syntax to use for a search on two fields. I have two primary keys, CaseNo and FIPS, since a case number can have multiple FIPS assigned to it and a FIPS can be assigned to multiple cases I need the two working together to define a unique record.

I can get the following code to work to find the case number but can’t figure out how to add the 2nd condition to match the FIPS. (this is in the afterupdate of the combobox)

DoCmd.SearchForRecord acActiveDataObject, , acFirst, "[CaseNo] = " & "'" & [Screen].[ActiveControl] & "'"

Can anyone tell me what I need to add and where? Or if you have a better suggestion to accomplish this.
Thanks in advance.

 

[pbaldy]

Along the lines of:

DoCmd.SearchForRecord acActiveDataObject, , acFirst, "[CaseNo] = '" & [Screen].[ActiveControl] & "' AND OtherField = '" & OtherControl & "'"

Which assumes it's another text field.

 

[spikepl]

#2 Will that not get confused, depending on the sequence of selection, since the ActiveControl could become the Other Control? I would suggest

DoCmd.SearchForRecord acActiveDataObject, , acFirst, "[CaseNo] = '" & TheComboWithCaseNo & "' AND OtherField = '" & OtherControl & "'"
If your CaseNo is a number and not a string, then remove the single quotes

 

[kalmi]

Hi guys,
Thanks for the help. I finally managed to get it right using

DoCmd.SearchForRecord acActiveDataObject, , acFirst, "[CaseNo] = '" & Me.FindCase.Column(0) & "' AND [FIPS] = '" & Me.FindCase.Column(1) & "'"
I forgot to mention that there are two columns in the combobox, after I added the column numbers and got all the quotations correct it seems to work exactly the way I wanted. Thanks again!

 

[pbaldy]

Glad you got it sorted out.