Link to home
Start Free TrialLog in
Avatar of Adam Ehrenworth
Adam EhrenworthFlag for United States of America

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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:

User generated image
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