MS access combo box "All" option not working

1
votes

I have used Union query to get all option in combo box in msaccess 2010.

The All option is now showing in my combobox successfully. But while I am selecting All option in combobox, it's not selecting all categories at once instead showing blank form.

Can anyone please what's wrong with the below syntax?

SELECT tbl_XXXXXX.ID, tbl_XXXXXX.YYYYYY FROM tbl_XXXXXX 
UNION select 0 as AllChoice, '(All)' as Bogus from tbl_XXXXXXX

My form property row source

SELECT * FROM tbl_AAA WHERE YYYYY = 'ZZZZZ';

Thanks in advance

1
ms-accesscomboboxms-access-2010
The combobox row source looks ok, you need to check the record source of the form - it must handle the id = 0 case. Please edit your question and add the form record source. – Andre
Hi thanks for your quick response. I am not a pro in using MS access. Just a beginner. I have added the form row source in my question. please provide your suggestion. What i need exactly is, i have a combo box which is used to filter the categories in the subform, i did that successfully. But I need "All" category need to be included in my combo box, if i select that "All" option" in combo box the subform need to display all categories. But unfortunately, it shows blank. – rozario_k
The form rowsource carries no reference at all to the combobox, so how should this be able to filter anything? – Gustav
Could you please help me out on how to fix this one? your views to rectify this one – rozario_k

1 Answers

1
votes

The form row source SQL needs to handle the 0 option in your combobox. Try:

SELECT * FROM tbl_AAA WHERE YYYYY = 'ZZZZZ' OR 0 = 'ZZZZZ';

Where 'ZZZZZ' represents your combobox value.

The key is that when your combobox value is zero, you want the WHERE condition in your SQL statement to evaluate as TRUE for everything. When the combobox is nonzero, the selected value will limit the results and the OR condition will be false. When the combobox returns zero, the first part of your WHERE clause will be false, but because 0 = 0, all rows will return from the underlying table.