How can I include a “Select All” option in my combo box?

I have a combo box that has the list of my distributors, when I select a distributor in the combo box I have a sub-form that list the transactions of that specific distributor.

Now I want to include a "Select All" option in the combo box that will display all the transactions of my distributors. I tried to do it through the UNION Query and so far it hasn’t worked.

Here is the code below:

SELECT Sous_Compte.Sous_Compte_ID, Sous_Compte.Numero_Sous_Master, Sous_Compte.Prenom & " " & Sous_Compte.Nom AS Nom 
FROM Sous_Compte

UNION SELECT "*" AS Sous_Compte_ID, "(All)" as Numero_Sous_Master, " " AS Nom from Sous_Compte

ORDER BY Sous_Compte.Numero_Sous_Master;

And this the error message I get whenever I select "(All)":

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Answer

Setting Master/Child Links properties won’t work because * character can’t be a key value. But can reference combobox in a dynamic parameterized query, such as:

SELECT * FROM tablename WHERE ID LIKE Forms!formname!comboboxname;

Then that query can be RecordSource of a form.

After selecting item from combobox, must refresh/requery form. Can use combobox AfterUpdate event or a button Click event. If using a single form then simply: Me.Requery. If using a subform, must reference through the subform container control: Me.subformcontainername.Requery. (Note this is correcting my comment that subform would fail when opening – a subform will work.)

This dynamic parameterized query can also be the RecordSource of a report. Or if you don’t want this same query as report RecordSource, can use WHERE CONDITION argument of OpenReport method:

DoCmd.OpenReport "reportname", , , "ID LIKE Forms!formname!comboboxname"