Add ALL to combo box

[Damon Grieves]

Hi
I've been tearing my hair out trying to get this to work. I set up a union
query on an unbound combo box in A97
SELECT StatusID, Assigned FROM tblStatus UNION SELECT 0 as AllChoice ,
"(All)" as Bogus From tblStatus ORDER by StatusID;

StatusId is an autonumber field and the bound field and Assigned is text and
the field displayed in the combo. These are the only fields in tblStatus
I have a main query that looks to several combo boxes for parameters....
everything works fine. But I'd like to put 'All' on the pull down
combos...this is the first one. (All) appears in the combo pull down but
when chosen the query returns no records.
Am I missing something? Should there be some code in AfterUpdate like if
Me!cmbStatus.Column(0)) = 0 Then ...
and should the query criteria start with Like?
Thanks in advance for your help

 

[John Spencer (MVP)]

For an autonumber field you can to set the criteria as

Where StatusID = IIF(Forms!YourFormName!ComboboxName=0,StatusID,Forms!YourFormName!ComboboxName)

Another option is

Where (StatusID = Forms!YourFormName!ComboboxName OR
Forms!YourFormName!ComboboxName = 0)

The problem with the 2nd option is that a query can get quite complex (too
complex) if you use that method for multiple fields.

 

[Damon Grieves]

Thanks John
That works beautifully!

For an autonumber field you can to set the criteria as

Where StatusID = IIF(Forms!YourFormName!ComboboxName=0,StatusID,Forms!YourFormName!ComboboxNa
me)

Another option is

Where (StatusID = Forms!YourFormName!ComboboxName OR
Forms!YourFormName!ComboboxName = 0)

The problem with the 2nd option is that a query can get quite complex (too
complex) if you use that method for multiple fields.