Combo boxes in Access

Factual Questions
1

Does anyone know if it’s possible in MS Access to have the first value in a combo box be blank or null?

I’m getting the data from a table.
I would like to make the first choice blank so that the person can “turn off” the toggle so to speak.

Hope that’s clear enough

Thanks…

2

Wooba,
If the person does not select any data from the combo box, it will remain blank. If your combo is based on say, the combo.rowsourcetype = table/query, and you’re using a “select fieldname from tablename order by fieldname;” statement as the rowsource, the combo box should be blank. When you click on the down arrow to display list, and do not click on anything, the field will stay blank. HTH.

3

Thanks BF,

Yes, that’s all correct.
However, I’ve got several combo boxes which the user can select one or more of to create a custom SQL statement when he/she presses the Filter button.
I can make a “Clear Fields” button which nulls the value in every box, but that’s a bit extreme if the user only wanted to remove the value from one box.

Thanks…

4

Ok, then use this. It’s predicated on at least two fields in the select statement.

courtesy of Dev Ashish…
You can then easily add “(All)” or “” 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 some other value in the bound field.

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as AllChoice , “” as Bogus From Customers ORDER BY CustomerID;

This will give you a blank selection in your list.

5

Thanks BF,

Worked great, although it didn’t work with my back end server unforutnately… but it’s good to know for future use.

Thanks again…

6

Hmmm… It “worked great” on what? A development platform, but not when you tried it on a production server? The SQL suggested by BF is pretty standard. What server are you using?

7

It worked great using it with a local table inside of Access. When I tried it using a table that’s linked from the server I got an ODBC error. The bank end is a package called Pervasive SQL.

8

I’m not familiar with Pervasive SQL, but I assume that it’s just another DBMS backend. You might try experimenting with BF’s SQL as a plain ol’ query, and perhaps leave out the AS clasues like this:

SELECT CustomerID, CompanyName FROM Customers UNION Select Null, “” From Customers ORDER BY CustomerID;