Search code examples
ms-accesscomboboxvbams-access-2016

Adding value to combobox based on recordset

I have a combobox, who's rowsource is set as :-

Set rs = CurrentDb.OpenRecordset("SELECT...")
Set cboBroker.Recordset = rs

Is it possible for me to add a value at the top of the list called "<ALL>", and if so, how?

Solution

  • If a combo boxes row source is directly bound to a recordset, any modification needs to be done in the recordset. You can do that though:

    SELECT "<ALL>" FROM MSysObjects WHERE ID = (SELECT First(ID) FROM MSysObjects)
UNION ALL
SELECT ...

    But you shouldn't. This will cost some performance, and will make it difficult to add columns.

    Instead, you could use a non-editable value list, and populate it like this:

    cboBroker.RowSource = ""
cboBroker.AddItem "<ALL>"
Set rs = CurrentDb.OpenRecordset("SELECT...")
Do While Not rs.EOF
   cboBroker.AddItem rs.Fields(0).Value
   rs.MoveNext
Loop