• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

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?
0
aehrenwo
Asked:
aehrenwo
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now