Default combo box value of "All" for a subform query criteria 1

[VBAjedi]

I have an unbound 1-column combo box (named "LimitToAnalyst") on a form named "Dashboard". LimitToAnalyst is populated by a SELECT query from a table containing analyst names. The value in this combo box is then used as a filter criteria value in a subform query:

Criteria: [Forms]![Dashboard]![LimitToAnalyst]

What I want is for the first/default value in the combo box to be "(All)", and for the subform query to then return all records. The catch is that I don't want to add an "All" row to my analysts Table because that table is used to populate other controls where "All" would be confusing. I tried setting the combobox to be blank by default, but then the query returns zero records instead of all records.

What's the best way for me to go about getting that default "all records" functionality for my subform query?

The combo box is currently set as Limit To List = Yes, but I might consider changing that if it makes this easier.

Thanks, friends!

VBAjedi

 

[dhookom]

Use a union query like:

SELECT Analysts
FROM tblAnalysts
UNION
SELECT " ALL"
FROM tblAnalysts;

Then, you need to figure out how to modify the subform query to ignore the combo box if " All" is selected.


Duane
Hook'D on Access
MS Access MVP

 

[VBAjedi]

A union query did the trick. Thanks, and have a star!

As far as how I modified the subform query to ignore the combo box if " All" is selected, I ended up adding a column to my query in Design view with a reference to the combobox:

Field: [Forms]![Dashboard]![LimitToAnalyst]

In Criteria on that query column I put:

Criteria: "All"

So when LimitToAnalyst contains "All", the Criteria is true and it returns all records.

Then I added a second set of criteria (i.e. on another Criteria row) that returns records where Analyst matches the LimitToAnalyst combo box on my form.




VBAjedi

 

[PHV]

No need to add a column to your query:
Criteria: =[Forms]![Dashboard]![LimitToAnalyst] OR [Forms]![Dashboard]![LimitToAnalyst]='All'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886