'Enter parameter value' bug

[mor]

Hi everyone,

I'm new to access and was wondering if anyone could help with the standard 'enter parameter value' prompt that catches all newbies out.

Basically, I have a form that has two combo boxes (combo1 & combo2) in a form "Client_database". The record sources for each of these combo boxes are primary keys derived from two different tables (Company_details and Product_details). These values are then referenced in separate fields in a query to filter another table (Client_information). This query has been inserted as a sub form into "Client_database" as a datasheet.

The SQL is coded such that when the combo boxes are empty, the query doesn't filter out any of the results. For some reason access has started prompting me for parameter values when I open either the form, query or subform. I have checked the references and they are correct. If I enter the values then the query works fine. Why is it prompting me?? The SQL where condition is below:*

WHERE
((( [Forms]![Client_database]![Combo1]) Is Null) AND ( ( [Forms]![Client_database]![Combo2]) Is Null))

OR

(((Company_details.company_id)=[Forms]![Client_database]![Combo1]) AND (([Forms]![Client_database]![Combo1]) Is Not Null) AND (([Forms]![Client_database]![Combo2]) Is Null))

OR

(((Product_details.product_id)=[Forms]![Client_database]![Combo2]) AND (([Forms]![Client_database]![Combo1]) Is Null) AND (([Forms]![Client_database]![Combo2]) Is Not Null))

OR

(((Company_details.company_id)=[Forms]![Client_database]![Combo1]) AND ((Product_details.product_id)=[Forms]![Client_database]![Combo2]) AND (([Forms]![Client_database]![Combo1]) Is Not Null) AND (([Forms]![Client_database]![Combo2]) Is Not Null));

 

[spikepl]

WHERE 
((Company_details.company_id=[Forms]![Client_database]![Combo1] OR
[Forms]![Client_database]![Combo1]) Is Null )

AND 

(Product_details.product_id=[Forms]![Client_database]![Combo2] OR [Forms]![Client_database]![Combo2] Is Null));

Try the above -that should be the equivalent of your logic.

This query has been inserted as a sub form into "Client_database" as a datasheet.

Controls on a subform are referred to by

Forms!MyMainForm!MySubform.Form!MyControlName

which is why you get prompted, since access cannot find, e.g., [Forms]![Client_database]![Combo1].

Give your controls meaningful names, instead of "Combo1" etc . When you revisit your code in 6 months, that will make you happy.

 

[mor]

Thats what I had originally and for some reason Access changed it?

Also I just changed the name of the control temporarily to try to understand why I was getting prompted. Usually I wouldn't use something as generic as that!

 

[Galaxiom]

Give your controls meaningful names, instead of "Combo1" etc . When you revisit your code in 6 months, that will make you happy.

I would also suggest you avoid calling a form "Client_database" because it does not describe the form in a meaningful way.

You can display whatever you want at the top of a form by editing the Caption property of the form.

 

[mor]

Ok thanks guys!

 

[mor]

Sorry spike pl, when you mentioned that the reference couldn't be found, the combo boxes are in the main form, so I don't understand why the prompt would come up?

The query references the controls in the main form through [Forms]![Client_database]![Combo1]. Surely this is right?

All the subform does is show the results of the query.

I just don't understand what has changed as it was working fine before.

Thanks

 

[Galaxiom]

You would be surprised how often spelling mistakes cause parameter prompts. It can sometimes be hard to see your own error.

Don't forget to check the Select and From clauses too as well as the table and form names themselves