combobox with a select ALL choice

[Keefay78]

On my form there is combobox which lists Staff names from a table but as well as picking one member of staff it would be usefull to be able to select all of them.



Whether it was a blank cbobox that resulted in all of them being selected or an ALL selection.

I have tried some different UNION query tutorials to no avail.

My SQL is originally:

SELECT tblSurgeon.[Staff ID], tblSurgeon.[Staff Name]
FROM tblSurgeon
ORDER BY tblSurgeon.[Staff Name];

then I tried a guide that said about adding in the UNION part and ended up with

SELECT tblSurgeon.[Staff ID], tblSurgeon.[Staff Name] FROM tblSurgeon UNION Select Null as AllChoice , "(All)" as Bogus From tblSurgeon
ORDER BY tblSurgeon.[Staff Name];


Although I am not sure where this leaves me, I mean it doesnt do anything but doesnt flag up errors :/

[Minty]

If you are using this to drive another query based of the combo box try adding an "or is null" similar to

SELECT * FROM MyTable
WHERE StaffID = Forms!MyForm!cmbStaff OR Forms!MyForm!cmbStaff Is Null

[Keefay78]

well the end game is i have staff and departments, I can pick either or to populate my form.

So far this works great, but to be able to pick ALL the staff is the goal.

Ill try that code and see if I can work it in

[Welshgasman]

Well I just used a listbox?

[Keefay78]

So, I have somehow ended up here:

SELECT DISTINCTROW tblSurgeon.[Staff ID], tblSurgeon.[Staff Name] FROM tblSurgeon UNION SELECT ALL 0 as [Staff ID], "All Staff" as [Staff Name] FROM tblSurgeon
ORDER BY [Staff Name];

which gives me an "All Staff" choice on my combobox, it doesnt actually work but if I leave it blank it returns all. not exactly sure how that works..

[Keefay78]

Might be choice but not sure how tht would work if I suddenly started accumulating lots of staff.

Could be worth a venture of course

[Welshgasman]

The way I seem to recall I did something like this was have the 0 as the ID, as that can never be a valid ID.
Then in the AfterUpdate event of the combo check for 0. If 0, no criteria is set, if not then criteria is set along the lines of "WHERE ID = " & Me.ComboID

HTH

[Micron]

I kinda skimmed, but the UNION is to get a list value that isn't part of the data (such as "Select All"). The query then needs to run either with the value that was selected OR run with no criteria. That takes code AFAIK. Now if you are actually trying to select all the combo list values (you seem to be involving a checkbox somehow) then you have a multi select combo and you might want to ditch that...
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

[ArviLaanemets]

Is combo bound or unbound? I'd use unbound one! Instead Null for ID in combo's rowsource, use some non-existing value (e.g. 0, or 9999). Like (on fly)

SELECT srg.[Staff ID], srg.[Staff Name] FROM tblSurgeon srg UNION Select 0 AS [Staff ID], "All" AS [Staff Name] ORDER BY 2

Unbound combo acts on selected value depending what you want to achieve - i.e. when it has to set filter for form, then when combo's value is 0, the form filter is cleared by combo's AfterUpdate event, otherwise it is set to selected [Staff ID]. When it has to run a query, the the querystring is edited accordingly, and the run by combo's AfterUpdate event, etc.