Microsoft Access Office VB VBA Help and Examples

Adding an Extra Row to a Data Bound Combo Box

Okay so you've created a combo box and made it look up all the values in a table as it's source. When someone picks a value from it it filters the form or jumps to a record or does something equally snazzy. But now you've been asked to have another item in that box and you stand there saying well no you can't because it looks up the values in the table and you can't change that. I know let's add a dummy record or lets build the combo box in code, that will work, oh how cumbersome oh how time consuming oh if only........

What if you could write a query that did all this for you.......

Here's a typical combo box rowsource which is used to show all the different types of entry in a specific field. This time City in tbl_Customers

SELECT City FROM tbl_Customers GROUP BY City

So now we can use this combo box to filter the form for a particular city. Typically if you want to switch the filter off you would put a button next to the combo box to switch off the filter. What if we could add another line....

SELECT City FROM tbl_Customers
UNION SELECT 'All' AS City FROM tbl_Customers;

This will select all the cities as before and add another record with the word All in it. So now we have a combo box which is part dynamic and part fixed. Very useful indeed, and WOW... NO CODE, NO DUMMY ENTRIES.

Lets go a step further. Typically we will filter by the value so what we really want is an asterix as the extra row as a like clause will then return all records.

SELECT City AS Filter, City FROM tbl_Customers
UNION SELECT '*' AS Filter, "All Records" AS City FROM tbl_Customers

Now we have two columns. The first contains a list of cities and an asterix. The second contains a list of cities and 'All Record' as a value. So now we can hide column one and show column two in the drop down list. Use column one as the key and use that for a like clause in the query.


The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.