Find a record based on two criteria

[rilkyn]

Hi

I have a form which currently displays all records based on a value from a combo box.

I am looking to change the form so that there are two combo boxes. The data is as follows:

Column 1 = Datagroup
Column 2 = Datagroup Version
Column 3 = Data

The first combo box will filter based on datagroup.
The second combo box will filter based on the version of the datagroup selected in combo 1. This works fine. What I can't get is the correct record to display in the subform. It is displaying the first record in the table which matches the version number. The Master/Child link on the subform is currently set to datagroup and version.

I think the problem is in the find record event on the second combo box. As far as I can work out it will only filter on one value and is currently set to the following:

Me.RecordsetClone.FindFirst "[Version] = " & Me![Combo68]

I tried this:
Me.RecordsetClone.FindFirst ("[Version] = " & Me![Combo68]), ("[Datagroup Name] = " & Me![Combo52])

but received the following error:
'Wrong number of arguments or invalid property assignment'

Can you help with the syntax or advise if a better method for what I am trying to acheive is available.

I am currently using Access '97.

Thanks

 

[ssatech]

Have you tried using the Me.FilterOn = False in your combo?

 

[PHV]

Me.RecordsetClone.FindFirst ("[Version] = " & Me![Combo68] & " AND [Datagroup Name] = " & Me![Combo52])

You may have to use single quotes for strings values.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rilkyn]

I've now tried the following but am getting a runtime 13 error

Me.RecordsetClone.FindFirst (("[Datagroup Name] = '" & Me![Combo52] & "'") And ("[Version] = " & Me![Combo68]))

The combo values are populating correctly so I assume the problem is with using both values to find the record I am after. This code is being run as an event in the afterupdate of the second combo box (Combo68).

 

[rilkyn]

Haha! Have now figured it out. It was the syntax I was using. Got round the problem by assigning it to a string value as such:

Dim StrSrch As String

' Find the record that matches the control.
StrSrch = "[Datagroup Name] = '" & Me![Combo52] & "'" & "And [Version] = " & Me![Combo68]
Me.RecordsetClone.FindFirst StrSrch
Me.Bookmark = Me.RecordsetClone.Bookmark