---Posted by Dev Ashish---
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 ********