Filter Combo Box based on a Form fields value

[CharlieT302]

Hi All,

Here is my question.

I have a drop-down box that displays vendors from the same county as the customer whose record is active. The customer record is on a main form and the vendor is on a subform. I use a criteria on the underlying query for the drop down box that looks at the county field on the primary field.

Example:
[forms]![frm Referral Primary].[County Code]

This works great. However, I need a second filter. The drop down box must display only those vendors from the customer's county that provide a specific service.

The Service field and the drop-down box are both on the subform. I can't get the criteria to read the Service field.

I tried using [Service] on the same line as the above criteria to create an "and" statement. Doesn't work.

I tried [Forms]![frm Referral Foreign].[Service] - it also didn't work. I am sure my syntax is wrong.

Any ideas? Thanks much.

 

[Remou]

Hi
You are missing a Form, I think:
[Forms]![frm Referral Foreign].Form.[Service]
You must also make sure that [frm Referral Foreign] is the name of the subform control, not the form it contains. Mostly these are the same, but not always.

 

[BSman]

Why don't you try using the Access query builder to build the query that will be used as the source for the combo box? That way you can get the field on the main form referenced correctly as well as the field on the subform. When you bring up the "build" wizard (right click on the criteria for each column), be sure to select the subform listed under the main form, not the subform separately listed.

I assume that you automatically requery the combo box when the combo box is in focus or when the customer changes on the main form.

 

[PHV]

And this ?
[forms]![frm Referral Primary]![frm Referral Foreign].Form.[Service]

Anyway, have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886