Parameter query not working for fields with combo box

Question

Hello, 

I have prepared a form to enter certain fields (which get stored in the form of a table, Table1 (Say)).

Some of these fields are normal (Field A, say) and some give user a list of choices (drop down) using combo box (Field B, say).

The choices in combo box are integrated in form fields from a separate table (Table 2, say)

Form (Form 1, say)is working fine but I am facing problem with parameter query which tries to select and display some records from Field B based on criteria defined (say a particular Paramater name). It displays nothing.

However, with similar parameter query in Field A, the records are displayed fine as per parameter criteria defined in query.

I have tried to run parameter query with the following syntax of criteria:

a. Like"*"&[Parameter name?]&"*"

b. [Forms]![Form1]![Field B]

But both have not produced desired results.

Please help me.

Regards.

Answer 1

First, this is one of the reasons why we recommend that you don't use lookup fields on the table level. They mask what is actually stored in the table. I would recommend that you remove all the lookup fields by changing them back to text boxes. Do all your lookups on your forms using list controls.

Second, This is one of the reasons I rarely use parameter queries. They are very inflexible. Query criteria should be entered on forms In your queries you can use:

=Forms!formname!controlname

to reference the value in the control that you want to use as criteria. In this way you can have the user select from a value rather than enter a value that might not match.

So for example (it would have been better if you used real examples rather than field A, etc.) lets say you have are tracking sales in stores. And you want to show the sales for a specific store. So your Sales table looks like this:

SalesID (PK)

StoreID (FK)

SaleDate

SaleAmont

So what is stored in this table is a StoreID taken from your Stores table which would look like:

StoreID (PK)

StoreName

StoreAddress

etc.

Because of the lookup field you see the StoreName not the StoreID which is what is actually stored. So if the user enters the Storename as the criteria its not going to match. But if you use a combobox with the same Rowsource, you will be comparing the StoreID.

Answer 2

Hi. A combobox value may be different than what is displayed on the form. Typically, it would be the ID  number from the source table.