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.
Enjoy.
| 