CODEWITHSUNDEEP
sqlvbams-accessms-access-forms
Douk
Douk

Reputation: 37

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.

Upvotes: 0

Views: 1305

Answers (2)

June7
June7

Reputation: 21379

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"

Upvotes: 0

Gustav
Gustav

Reputation: 55981

The fields of the two queries must match:

SELECT 
    Sous_Compte.Sous_Compte_ID, Sous_Compte.Numero_Sous_Master, Sous_Compte.Prenom & " " & Sous_Compte.Nom AS Nom 
FROM 
    Sous_Compte
UNION 
SELECT 
    0, "(All)", ""
FROM
    Sous_Compte
ORDER BY 
    Sous_Compte.Numero_Sous_Master;

Upvotes: 0

Related Questions