Selecting records on a subform using multiple criteria

[rilkyn]

Hi

I have a subform with the following fields:
Module, Datagroup, Version.

The main form has two combo boxes:
Combo Box 1 lists all datagroups
Combo Box 2 lists all versions based on the datagroup selected in combo box 1

The subform needs to display the modules based on the datagroup/version combination selected above.
The master/child link is set to datagroup/datagroup and version/version.

I tried the following in the second combo box after update but got a run-time 13 error:

Me.RecordsetClone.FindFirst "[VERSION] = " & Me![Combo1] And "[DATAGRP] = " & Me![Combo2]

I'm guessing it doesn't like the 'and' What is the correct syntax to find the records I am looking for?

 

[GingerR]

Me.RecordsetClone.FindFirst "[VERSION] = " & Me![Combo1] & " and [DATAGRP] = " & Me![Combo2]

Translation:

[Version] = 1 and [DataGrp] = 7

(Your AND needed to be inside the quotes).


However,

Me.SubformNameHere.Requery

should do the trick.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[rilkyn]

Thanks for the suggestion but I have run into the following problem:

I am now getting the following error:
Run-time error 3070
The Microsoft Jet Database engine does not recognise 'CD010' as a valid field name or expression.

CD010 is the first datagroup value on the table.

 

[GingerR]

Try the Requery statement instead. The reason you set up Master/Child links is so the subform will automatically go where you want it to without you having to write any code to make it go anywhere.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

Me.RecordsetClone.FindFirst "VERSION=" & Me![Combo1] & " AND DATAGRP='" & Me![Combo2] & "'"

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

 

[rilkyn]

Thanks for your help. I rewrote the subform query to pick up the values from the combo boxes and then set a requery (as suggested) on the second combo box. Works like a dream.

Thanks

R