Having an <all> option in form for report criteria

[Guest]

I am new to creating dynamic reports. I have found much help to have a form
appear where the user can choose from a drop down box to filter what report
data is shown (i.e. a customer's name in the drop down box). Where I am
having problems, is that my combo box forces the user to choose one of the
options on the list. I have noticed that there is a way to put an <all>
selection in the combo box, but I'm not sure how to do it. Having the combo
default to a blank field instead of the first choice on the list would also
work. I want the user to have one of the 2 aforementioned choices so they
may print all the records instead of just one record. Can anyone give me
detailed suggestions?

Thank you for all your help.

 

[Joshua A. Booker]

You can add the <All> option by using an union query for the combo box
recordsource like this:

SELECT "<ALL>" UNION SELECT CustomerName FROM tblCustomer

Then you need to trap for that choice when you open report like this:

If Me!ComboBoxName = "<ALL>" then
'Open the report without criteria
Docmd.OpenReport "rptName", acViewPreview
Else
'Open the report with criteria
Docmd.OpenReport "rptName", acViewPreview,,"CustomerName='" &
Me!ComboBoxName & "'"
End if

HTH,
Josh

 

[Guest]

Thanks, I'll give it a try in the morning at work.

 

[John Spencer]

Pardon me,
I think you need a small change in the Union query.

SELECT "<ALL>" as Choices FROM tblCustomer UNION SELECT CustomerName FROM
tblCustomer

To be more efficient, you might use a table in the first part of the union
that has very few records - often a lookup table.

SELECT "<ALL>" as Choices FROM [SomeTable with just a few Records]
UNION SELECT CustomerName FROM tblCustomer

The reason this is more efficient is that Access won't need to work with as
many records when it elements the duplicates.