"All" option in a combo box

[dkwong]

I have a question about a method to do this described at

http://www.mvps.org/access/forms/frm0043.htm:

If the RowSourceType of the control is a "Table/Query", there are two ways of doing this. One requires the use of an Union query, and the other one requires a callback function to fill the control. Generally using an Union query is easier. Callback functions are important in certain cases, but perhaps an overkill in this situation.

For example, if your combo's RowSource is this SQL statement
SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID;you can then easily add "(All)" as the first choice. Also, if CustomerID is the bound field but it's width is set to zero (so that the user only sees CompanyName), you can store a NULL (if the bound field is not the primary key of the table), or someother value in the bound field. SELECT CustomerID, CompanyName FROM Customers UNION Select Null as AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

What if I'm trying to do this with a combo box based on a Category table with the fields CategoryID (primary key) and CategoryName. His method suggests storing the NULL if the bound field is not a primary key of the table.

Thanks.

 

[BoxHead]

You can use another value other than ISNull if your bound column is a primary key. The tip you quoted suggests using Null so it will be certain to be the first choice.

You can use the three zeros as below as long as 000 will order ahead of your actual key field values.

Another thing, I got a data type mismatch until I moved the quotations into the parentheses on "(All)".

SELECT CustomerID, CompanyName FROM Customers UNION Select (000) as AllChoice , ("All&quot as Bogus From Customers ORDER BY CustomerID;


HTH


John

Use what you have,
Learn what you can,
Create what you need.