sql - How to add functionality for ComboBox <Select All> in Microsoft Access forms

Question

So I posted a question the other day about an access form that needed to have a "Select All" option added to the 2 ComoBoxes. I was able to add in the option to the 2 of them using a union. However, the options do nothing as of yet. I found the Query that takes the form parameters from the ComboBox and this is where I need to add in the option to select all, except after staring at it for hours I've got no clue again.

The Database wasn't written by me, it's approximately 10 years old and was was given to me to add some new features. I have done that, and the owner complained that the "Select All" buttons have never worked. After research, the buttons have VB script that clears the ComboBox input to nullified value. I am planning on scrapping those now since I have added the option to the ComboBox itself.

The SQL query that reads combo input looks like this:

PARAMETERS [Forms]![ReportCentre]![cboTreatmentType] Short, [Forms]![ReportCentre]!      [cboTreatmentDate] Short;


SELECT addresses.*,
       [firstname] & "" & [lastname]
       AS Name,
       [street] & "," & [suburb] & "" & [stateorprovince] & "" & [postalcode]
       AS
       Address
FROM   addresses
WHERE  ( ( ( addresses.treatmentid ) = [forms] ! [reportcentre] !
                                                [cbotreatmenttype].[Value] )
         AND ( ( addresses.treatmentdate ) = [forms] ! [reportcentre] !
                                                 [cbotreatmentdate].[Value] )
         AND ( ( addresses.birthmonth ) LIKE [forms] ! [reportcentre] !
                                             [txtbirthmonth].[Value]
                                                 & "*" ) )
        OR ( ( ( addresses.treatmentid ) IS NULL )
             AND
       ( ( addresses.treatmentdate ) = [forms] ! [reportcentre] !
                                           [cbotreatmentdate].[Value] )
             AND ( ( addresses.birthmonth ) LIKE [forms] ! [reportcentre] !
                                                 [txtbirthmonth].[Value]
                                                     & "*" ) )
        OR ( ( ( addresses.treatmentid ) = [forms] ! [reportcentre] !
                                                 [cbotreatmenttype].[Value] )
             AND ( ( addresses.treatmentdate ) IS NULL )
             AND ( ( addresses.birthmonth ) LIKE [forms] ! [reportcentre] !
                                                 [txtbirthmonth].[Value]
                                                     & "*" ) )
        OR ( ( ( addresses.treatmentid ) IS NULL )
             AND ( ( addresses.treatmentdate ) IS NULL )
             AND ( ( addresses.birthmonth ) LIKE [forms] ! [reportcentre] !
                                                 [txtbirthmonth].[Value]
                                                     & "*" ) )
        OR ( ( ( addresses.treatmentid ) IS NULL )
             AND
       ( ( addresses.treatmentdate ) = [forms] ! [reportcentre] !
                                           [cbotreatmentdate].[Value] )
             AND ( ( addresses.birthmonth ) IS NULL ) )
        OR ( ( ( addresses.treatmentid ) = [forms] ! [reportcentre] !
                                                 [cbotreatmenttype].[Value] )
             AND ( ( addresses.treatmentdate ) IS NULL )
             AND ( ( addresses.birthmonth ) IS NULL ) )
        OR ( ( ( addresses.treatmentid ) = [forms] ! [reportcentre] !
                                                 [cbotreatmenttype].[Value] )
             AND
       ( ( addresses.treatmentdate ) = [forms] ! [reportcentre] !
                                           [cbotreatmentdate].[Value] )
             AND ( ( addresses.birthmonth ) IS NULL ) ); 

I know it's messy and hard to understand, which is why im asking for help. How do I get that to validate a "Select All" option for both ComboBoxes?

Answer 1

One very easy way is to set the bound column of the combo to *:

 SELECT "*" As ID, "Select All" As AText 
 FROM Table1 
 UNION SELECT Table1.ID, Table1.AText 
 FROM Table1;

Using your combo:

 Select "*" As TreatmentID, "<<All Records>>" As Treatment 
 FROM Treatment 
 UNION 
 Select Treatment.TreatmentID, Treatment.Treatment 
 From Treatment;

You can then use LIKE:

SELECT Table1.ID
FROM Table1
WHERE Table1.ID Like [forms]![MainForm]![Combo]

Using your SQL:

... WHERE (((Addresses.TreatmentID) 
  Like [Forms]![ReportCentre]![cboTreatmentType]) AND ...

If you only have a single column, you can use:

SELECT Table1.Atext
FROM Table1
WHERE AText Like 
   IIf(Forms![MainForm]!Combo="Select All","*",Forms![MainForm]!Combo)