dblookupcombobox has a null row

Question

I have a question about DBLookupComboBox.

I have a program that has a database I wrote. It has everything, except when I open DBLookupComboBox it must have a row with null value for when user wants to select nothing. But there isn't one. How can I make a null row show up?

Answer 1

You must either add a row that says 'Nothing' or 'All', which ever suits. The usual solution is a UNION query, which can be used as the RowSource of the combobox. The Union query can be used to add virtual fields.

If the combo comtains only unique values, you can say:

SELECT "Nothing" As Description
FROM ATable
UNION 
SELECT Description
FROM ATable

UNION eliminates duplicates, UNION ALL< does not, so if there are matching rows, you can say:

SELECT DISTINCT "Nothing" As Description
FROM ATable
UNION ALL
SELECT Description
FROM ATable

If you want "Nothing" to sort first, you must juggle a little and use " Nothing", or "-Nothing", but if you have an ID or Key column you can get a nice sort, like so:

SELECT 0 As ID, "Nothing" As Description
FROM ATable
UNION 
SELECT ID, Description
FROM ATable