Forms: Adding "All" to a listbox or combobox

    If the RowSourceType of the control is a "Table/Query", there are two ways of doing this. One requires the use of an Union query, and the other one requires a callback function to fill the control. Generally using an Union query is easier. Callback functions are important in certain cases, but perhaps an overkill in this situation.

    For example, if your combo's RowSource is this SQL statement

SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID;

    you can then easily add "(All)" 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 someother value in the bound field.

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

    If the RowSourceType is set to "Value List", you can simply concatenate "(All)" as the first choice when the form opens. So if the RowSource of the control Combo0 is

"Hello"; "World"

Then this code will change it to

"(All)";"Hello"; "World"

'******* Code Start ********
Private Sub Form_Open(Cancel As Integer)
  With Me.Combo0
    .RowSourceType = "Value List"
    .RowSource = "(All);" & .RowSource
  End With
End Sub
'******* Code End ********