Is It Possible To Disable Combo Box on Parameter Query Form

[Brian]

This site has always got me through a problem so here's another one.
To open a report the user has a form to enter required filters such as
dates, values etc. which are passed to a paramter query.
Some reports need to be filtered by Supplier and some do not. The user input
form has a combo box of suppliers, this works fine when needed but I'm
looking for a method to ignore the combo box when not required. I've tried a
blank line in the combo box (ERROR! Null values not allowed).

Is the best answer to have separate queries with/without Supplier Filter or
somehow disable the combo box or have different user input forms
including/excluding Supplier Combo.

 

[tina]

set the criteria in the query as

Forms!FormName!ComboBoxName Or Forms!FormName!ComboBoxName Is Null

replace FormName and ComboBoxName with the correct names, of course.

hth

 

[Brian]

Thanks for the suggestion but unfortunately that hasn't solved the problem.
I think your expression was trying to ignore Non-selected Suppliers.
-Currently the user input form displays the combo box
-The combo box must display a supplier name (cannot display blank value).
-That supplier name is passed to the query.

I am looking for a way to igonre the whole combo not just a value within it.

 

[tina]

hmm, you're right, different scenario. to avoid having two queries and
possibly two reports, you might try removing the supplier parameter from the
query entirely, leaving the other parameters in place. then, in the code
that opens the report, choose to open the report with a WHERE clause to
restrict the records by supplier, or without a WHERE clause, as

If someclue = usesupplier Then
DoCmd.OpenReport "ReportName", , , "SomeField = " _
& Me!ComboName
Else
DoCmd.OpenReport "ReportName"
End if

you'll have to decide what "clue" you want the code to check for, when
deciding whether to open the report "with" or "without".

hth

 

[Ken Sheridan]

Unless the combo box is a bound control there is no reason why it can’t be
Null, which simply means the user leaving it blank or deleting its current
value (no need for a Null row in the underlying RowSource), in which case
Tina's method will work. If it is an unbound control and you want to allow
users to disregard it another common approach is to include something like
<All> as the first item in its list, by means of a RowSource such as:

SELECT SupplierID, Supplier, 1 AS SortColumn
FROM Suppliers
UNION
SELECT 0, "<All>", 0
FROM Suppliers
ORDER BY SortColumn, Supplier;

And make the first row the default selection by putting:

Me.ComboBoxName = 0

in the form's Open event procedure.

In this case you'd test for OR Forms!FormName!ComboBoxName = 0 rather than
for IS NULL for the expression to evaluate to TRUE for every row in query if
<All> is selected as the value of the combo box's (hidden) bound column would
be zero and consequently the expression would evaluate to TRUE for every row.

If the parameter is on the supplier name column of course, you'd simply omit
the SupplierID and the 0 constant columns from the two parts of the UNION
operation and in the query test for:

OR Forms!FormName!ComboBoxName = "<All>"

If on the other hand the combo box is a bound control and cannot be Null add
an unbound check box, chkAllSuppliers say, to the form and in the form's
Current event procedure set its value to False. The user can then check it
to ignore the current supplier, in which case the criterion would be:

Forms!FormName!ComboBoxName Or Forms!FormName!chkAllSuppliers

Ken Sheridan
Stafford, England