Access Form Combo Box based on Query - Add manual "All choice"

I have a combo box that is getting populated by a query

SELECT DISTINCT RAMP2015.RAMP2015Company AS Company FROM RAMP2015 WHERE (((RAMP2015.RAMP2015Company) Is Not Null)) ORDER BY RAMP2015.[RAMP2015Company];

I want to add a manual option for "All" to the list in the dropdown... can this be done in the Properties or does it need to be done in the SQL?
Adam EhrenworthLead Technology AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You do this with a UNION Query:

SELECT "ALL" AS RAMP2015Company FROM Company
UNION
SELECT DISTINCT RAMP2015.RAMP2015Company AS Company FROM RAMP2015 WHERE (((RAMP2015.RAMP2015Company) Is Not Null)) ORDER BY RAMP2015.[RAMP2015Company];

If the choices don't Order properly, you might need to change that to " All" (notice the space in front) or "*ALL*", or something along those lines. This will generally force Access to put that at the top of the list.

Your code then needs to catch the "All" item and react accordingly. This is generally done in the AfterUpdate event.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
I usually wrap the "ALL" in square brackets so it sorts to the top of the drop-down list.  Here is some typical code to work with such a selection:

Public Sub EmailPlainText(strSelectedCustomerID As String)
'Created by Helen Feddema 2-Mar-2015
'Last modified by Helen Feddema 6-Mar-2015

On Error GoTo ErrorHandler
   
   Dim appOutlook As New Outlook.Application
   
   Debug.Print "Selected Customer ID: " & strSelectedCustomerID
   
   If strSelectedCustomerID = "ALL" Then
      'Create emails for all customers
      Set rstCustomers = CurrentDb.OpenRecordset("tblCustomers")
      strQueryOrders = "qrySelectedOrders"
      
      Do While Not rstCustomers.EOF
         'Create filtered recordset of orders for this customer
         strEMail = Nz(rstCustomers![Email])
         strCompany = Nz(rstCustomers![CompanyName])
         strSubject = "Orders for " & strCompany
         strCustomerID = Nz(rstCustomers![CustomerID])
         
         If strCustomerID <> "" Then
            strSQL = "SELECT * FROM qryOrdersWithDetailsDateRange WHERE " _
               & "[CustomerID] = " & Chr(39) & strCustomerID & Chr(39) & ";"
         End If
      
         Debug.Print "SQL for " & strQueryOrders & ": " & strSQL
         lngCount = CreateAndTestQuery(strQueryOrders, strSQL)
         Debug.Print "No. of items found: " & lngCount
         If lngCount = 0 Then
            GoTo NextCustomer
         Else
            'Create email for this customer
            Set msg = appOutlook.CreateItem(olMailItem)
            msg.To = strEMail
            msg.Subject = strSubject
               
         'Create header line
         strHeader = "Order Date" & "    " & "Quantity" _
            & "    " & "ProductName"
         strBody = strHeader & vbCrLf & vbCrLf
         
         'Put orders data into body of email
         Set rstOrders = CurrentDb.OpenRecordset(strQueryOrders)
         'Debug.Print "Processing orders for " & strCompany
         
         Do While Not rstOrders.EOF
            dteOrder = Nz(rstOrders![OrderDate])
            strItem = Nz(rstOrders![ProductName])
            lngQuantity = Nz(rstOrders![Quantity])
            strQuantity = FormatNo(lngQuantity)
            
            'Create line of body text
            strLine = Format(dteOrder, "mm/dd/yyyy") & "     " _
               & strQuantity & "       " & strItem
            'Debug.Print "Line text: " & strLine
            strBody = strBody & strLine & vbCrLf
            
            rstOrders.MoveNext
         Loop
         
         msg.body = strBody
         msg.Display
            
         End If

NextCustomer:
         rstCustomers.MoveNext
      Loop
   Else
      'Create email for selected customer
      strSQL = "SELECT * FROM qryOrdersWithDetailsDateRange WHERE " _
         & "[CustomerID] = " & Chr(39) & strSelectedCustomerID _
         & Chr(39) & ";"
      strQueryOrders = "qrySelectedOrders"
      'Debug.Print "SQL for " & strQueryOrders & ": " & strSQL
      lngCount = CreateAndTestQuery(strQueryOrders, strSQL)
      'Debug.Print "No. of items found: " & lngCount
      If lngCount = 0 Then
         strPrompt = "No records found; canceling"
         strTitle = "Canceling"
         MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
         GoTo ErrorHandlerExit
      Else
         Set rstCustomer = CurrentDb.OpenRecordset(strQueryOrders)
         strEMail = Nz(rstCustomer![Email])
         strCompany = Nz(rstCustomer![CompanyName])
         strSubject = "Orders for " & strCompany
         
         'Create email for this customer
         Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEMail
         msg.Subject = strSubject
            
         'Create header line
         strHeader = "Order Date" & "    " & "Quantity" _
            & "    " & "ProductName"
         strBody = strHeader & vbCrLf & vbCrLf
         
         'Put orders data into body of email
         Set rstOrders = CurrentDb.OpenRecordset(strQueryOrders)
         'Debug.Print "Processing orders for " & strCompany
         
         Do While Not rstOrders.EOF
            dteOrder = Nz(rstOrders![OrderDate])
            strItem = Nz(rstOrders![ProductName])
            lngQuantity = Nz(rstOrders![Quantity])
            strQuantity = FormatNo(lngQuantity)
            
            'Create line of body text
            strLine = Format(dteOrder, "mm/dd/yyyy") & "     " _
               & strQuantity & "       " & strItem
            'Debug.Print "Line text: " & strLine
            strBody = strBody & strLine & vbCrLf
            
            rstOrders.MoveNext
         Loop
         
         msg.body = strBody
         msg.Display
         
      End If
   End If
   
ErrorHandlerExit:
   Set appOutlook = Nothing
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in EmailPlainText procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


and here is a screen shot of the drop-down on the main menu:

Selecting ALL
Note that I consider not making a selection equivalent to selecting ALL.
The CustomerID is fed from the AfterUpdate event of the combo box.

The code and screen shot are from my Access Archon article on emailing order lists to customers:

http://www.helenfeddema.com/Files/accarch240.zip
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.