Add table items and a custom item to a combo

[Guest]

I have a combo box on a form whose Row Source is a Distinct query on a table;
but I want to add one additional item at the top of the list called: All

It seems I can only add items from my table though. How can I do this
eloquently?

Thanks in advance.

 

[Damon Heron]

I guess you want to be able to select everything on the list? or you want to
change the query to include all items in the table?
If you want to include all items in the table in the combobox, you could use
the double-click event to write a new sql string, assign it to the
combobox.rowsource and requery. If you want to select everything on the
list, you might want to switch to a listbox instead of a combobox.

HTH
Damon

 

[Fred Wilson]

I do not have my database in front of me but I do believe the combobox
may not be bound to the table. Secondly, the row.source has some kind of
join query in it. That might spur the juices for the other creative folks.

 

[Rob Parker]

The standard way of doing this (for a combo-box which is not bound to a
field, but is used for filtering the records in a form) is to set up a Union
query to provide the additional item(s).

You cannot create a Union query using the QBE grid; you must manually enter
the SQL for the query. However, you can use the QBE grid to set the first
part of the query, then switch to SQL view to include the additional
item(s). Your SQL will look something like:

SELECT DISTINCT Item FROM tblItems
UNION
SELECT "All" FROM tblItems;

NB: there must be the same number of fields in each section of the union
query. So, if you were selecting both a part number and an item, and only
displaying the item in your combo-box, you would need something like:

SELECT DISTINCT PartNumber, Item FROM tblItems
UNION
SELECT 0, "All" FROM tblItems;

You can add an Order By clause to the union query to set the display order;
if you do this for text field, you might find it useful to set your
additional string to "<All>" to force it to appear at the top of the list
(since it's unlikely that a normal item will start with a character earlier
in the ASCII set than "<").

HTH,

Rob