Having real problems adding 'All' to a combo box

[choward]

Hi Guys,

this is my sql for the Row Source of a combo box:

SELECT [LOCATIONS].[LOCATION ID], [LOCATIONS].[LOCATION NAME] FROM LOCATIONS
UNION (SELECT '*' AS [LOCATION ID], 'All' AS [LOCATION NAME] FROM LOCATIONS)
ORDER BY [LOCATION NAME]

Any ideas why it doesn't work? Location ID is the field that filters the form and Location Name is what s displayed in the combo box.

 

[gemma-the-husky]

try

SELECT [LOCATIONS].[LOCATION ID], [LOCATIONS].[LOCATION NAME] FROM LOCATIONS 
UNION (SELECT [COLOR="Red"]-1[/COLOR] AS [LOCATION ID], '[COLOR="red"]<All>'[/COLOR] AS [LOCATION NAME] FROM LOCATIONS) 
ORDER BY [LOCATION NAME]

if location id is numeric, it needs to be numeric in the union bit (i think)
ive changed it to -1, so its not 0 by accident, but 0 would work ok, and would then be the default.

secondly, put <> round the text to get it sorted at the top of the list, otherwise its buried in the combo box, and you may not see it there.

 

[choward]

Thanks Gemma,

the combo box doesn't crash anymore - but selecting 'ALL' doesn't bring up the results as if it were unfiltered. In fact, it shows nothing.

i cant explain it - and i am 100% certain there is data there. Stupid access 2007.

 

[gemma-the-husky]

no because all youve done is change the combo box. The form wont change automatically

eg
if you pick item 12 from the combo box, say, you filter item 12

if you pick the <all> row, you are now filtering for item id 0 or item id -1 (depending on how you did it) which will return n orecords

so it depends on the recordsource for the form

if its a query then you need to change the query to allow for the combo box being value 0 or -1, and returnnig all the records

if its some other way, then you need to change the form filter or recordsource in the afteupdate event of the combo box to select all the records

 

[pegahen]

A related question:
I have a query that filters the field SiteID(text field) to generate a report
This is what I have in the query now: [Forms]![PathogenData]![SiteID]
I added the "All" to my combo box(SELECT DISTINCT tblPathogens_all.SiteID, tblPathogens_all.Agency FROM tblPathogens_all UNION (SELECT 'All', ' All' FROM tblPathogens_all) ORDER BY tblPathogens_all.SiteID, tblPathogens_all.Agency

how do I alter the query (as you mention above?) to include this All selection??

 

[gemma-the-husky]

look at it in the query design mode, then see what the sql looks like

in the sql design mode put the target column, and in the criteria row put "forms!myform!mycombobox". (modified as required) this will pickup up the value when you HAVE selected a value in the combo box

now in the design, in a new columnb put the expression "forms!myform!mycombobox" and in the criteria row put 0 or -1, or whatever corresponds to the all row

the sql will show you how to construct the query correctly

----------
logically the where clause should resemble this

if (targetfield = comboboxvalue) or (comboboxvalue = -1)

where the -1 is the value that corresponds to the <all> row

i hope this makes more sense - you may need to play around withquery design to get this working

 

[pegahen]

thanks

I got it to work! I didn't need to add a new column though-

just added a -1 in the Or Criteria