Adding a <<All Suppliers>> to a Combo Box List.

A lot of times you may want to add a choice to a combo box that allows you to select all the items in the list. An example of this would be if you wanted to print a activity report for either a particular supplier or all suppliers.

The steps you took to create the last combo box will also be used in this case except the Row Source. We are going to use a Union SQL statement to add our <<All Suppliers>> choice to the front of the list. Prior to this technique you had to either use Access Basic Code or add the choice to a table. (For an example of this technique, see the "Combo 2" form in AC208.MDB.)

Here is the Select statement that is used in the Row Source:

Select 0 as [Supplier ID], "<<All Suppliers>>" as [Company Name] From Suppliers Union (Select [Supplier ID], [Company Name] From Suppliers) Order By [Company Name];

The expression for testing this case can be found in the "qCombo2" query in the query grid. The expression is:

CheckSupplierToPrint: IIF(Forms![Combo 2]![txtSupplierToPrint] = 0, True, Forms![Combo 2]![txtSupplierToPrint]=[Supplier ID])

The criteria simply contains a the statement True.

Now if <<All Suppliers>> is chosen, the CheckSupplierToPrint expression equals True, otherwise it compares each Supplier ID to one used on the form.