Access: Specialty Combo Boxes, Part 2

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Give it a try. You can unsubscribe at any time.

This article describes several more variations of combo boxes, making them even more useful.

Linked Combo Boxes

You can use a set of linked combo boxes to filter a list, making it easier to get at the specific item you want to select. As an example, in the sample database (Specialty Combo Boxes, Part 2), I set up three linked combo boxes to select an employee first by Sales District, then Supervisor, and finally by Employee. The frmLinkedComboBoxes form is opened from a button on the main menu:

This form demonstrates two methods for linking the combo boxes – one by direct reference to a form control, and one using custom database properties. In either case, you first select a sales district from the top combo box, then a supervisor from the middle one, which shows only the supervisors in that district, then an employee from the bottom combo box, which shows only the employees supervised by the selected supervisor:

The first method is easier to set up, and is fine in most cases; the second is more robust, and lets you check your query data even if the form is closed.

Method 1

The code for the combo boxes for this method is listed below.

Private Sub cboSalesDistrict1_AfterUpdate()

On Error GoTo ErrorHandler

   Me![cboSupervisor1].Requery
   Me![cboSupervisor1].SetFocus
   Me![cboSupervisor1].Dropdown
   
   'Clear old employee value
   Me![cboEmployee1].Value = Null
   
   'Clear Method 2 combo box values
   Me![cboSalesDistrict2].Value = Null
   Me![cboSupervisor2].Value = Null
   Me![cboEmployee2].Value = Null
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

Private Sub cboSupervisor1_AfterUpdate()

On Error GoTo ErrorHandler

   Me![cboEmployee1].Requery
   Me![cboEmployee1].SetFocus
   Me![cboEmployee1].Dropdown
   
ErrorHandlerExit:
   Exit Sub

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

End Sub
Private Sub cboEmployee1_AfterUpdate()

On Error GoTo ErrorHandler
   
   strQuery = "qrySelectedEmployee1"
   
   'Open Employees form to selected record
   DoCmd.OpenForm FormName:="frmEmployees", filtername:=strQuery
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

The code on the AfterUpdate event of cboSalesDistrict1 clears values in other combo boxes and then requeries and drops down the filtered cboSupervisor1 list; code on the same event of cboSupervisor1 just requeries and drops down the filtered cboEmployee1 list. Code on the AfterUpdate event of cboEmployee1 opens frmEmployees filtered by the selected employee:

 

 

The row source query for cboSupervisors1 is filtered by a reference to the value of cboSalesDistrict1 on the form:

The row source query of cboEmployee1 is similarly filtered by the selection in cboSupervisor1. If you open one of these row source queries when the form is closed, you will get an error, which makes it more difficult to troubleshoot the queries.

Method 2

This method uses custom database properties for filtering the second and third combo boxes; this method takes more code to set up, but it allows you to open the row source queries and see the data filtered by the last selection, whether or not the form is open.

The code for the cboSalesDistrict2 combo box is similar to the Method 1 combo box, except for the code segment listed below, which saves the selected SalesDistrictID value to a custom database property:

   lngID = Me![cboSalesDistrict2].Value
   strPropertyName = "SalesDistrictID"
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, lngID)

This property is used to filter qrySupervisors2, instead of the form control reference:

cboSupervisor2 has a similar code section that saves the SupervisorID to a custom database property. cboEmployee2 saves the EmployeeID to a custom database property, and then opens frmEmployees filtered by the selected record, as with Method 1.

Adding All and Nothing Selections to a Combo Box

Combo boxes let you select just one item from a list (to select multiple items, use a listbox, as described in Access Archon #197, Filtering by Multiple Selections). But sometimes a user selects an item, then decides decides it isn’t correct, and wants to revert to a blank combo box. That isn’t possible, since the combo box doesn’t offer a choice of a blank selection. An “All” selection can also be useful, say if the combo box selection is used to create a report’s record source.

To create the two special selection items, I use a double Union query with one UNION statement for the blank selection, and another for the All selection. To start, look at the SQL for the row source of the cboSelectEmployee combo box on frmAllOrNothingComboBox. It is a standard query with just two fields (I have removed the redundant table name from the first field):

SELECT EmployeeID, [LastName] & ", " & [FirstName] AS EmployeeName
FROM tblEmployees
ORDER BY [LastName] & ", " & [FirstName];

The next step is the blank selection, which is done by creating a second SQL statement with a zero for the EmployeeID and a placeholder for the employee name; here is the union query SQL with the blank selection:

SELECT EmployeeID, [LastName] & ", " & [FirstName] AS EmployeeName
FROM tblEmployees
UNION SELECT 0 AS EmployeeID, "[No selection]" As EmployeeName
ORDER BY EmployeeName;

To add an All selection, since the EmployeeID field is used for selection, I use a different query as the combo box’s row source, qryEmployeesTextID, with a field that converts the EmployeeID field value into text. This allows me to set different values for the Blank and All selections, which would not be possible with the AutoNumber EmployeeID field. Here is the double Union query with both Blank and All selections:

SELECT TextEmployeeID, EmployeeName, EmployeeID
FROM qryEmployeesTextID
UNION SELECT "00" As TextEmployeeID, "[No selection] " As EmployeeName, 0 AS EmployeeID
FROM qryEmployeesTextID
UNION SELECT "01" As TextEmployeeID, "[All]" As EmployeeName, 0 AS EmployeeID
FROM qryEmployeesTextID
ORDER BY EmployeeName;

The two special selections will automatically sort to the top of the list:

The AfterUpdate procedure on the cboSelectEmployeeWithBlankAndAll combo box uses the selected value to enable cmdPrint so you can print a report with all employees (if the [All] item is selected, or a single employee, if one employee is selected. If the [No selection] item is selected, cmdPrint is disabled.

Private Sub cboSelectEmployeeWithBlankAndAll_AfterUpdate()

On Error GoTo ErrorHandler

   'Save selected Employee ID to custom database property
   lngID = Me![cboSelectEmployeeWithBlankAndAll].Column(2)
   strPropertyName = "EmployeeID"
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, lngID)
   
   'Cancel report if blank is selected; otherwise preview it
   If Me![cboSelectEmployeeWithBlankAndAll].Value = "00" Then
      Me![cmdPrintReport].Enabled = False
   Else
      Me![cmdPrintReport].Enabled = True
   End If
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

The report for all employees is shown below:

Dynamic Source Combo Box

If you need to select a date, or a month-year combination, from a combo box, quite often you will need a way to ensure that the list is up to date (otherwise you may end up with a list of dates in the past, which is not useful for scheduling an event).  frmDynamicMonthAndYear is shown below:

The code listed below uses the value from the top textbox to determine how far back to go in preparing the list of month-year combinations, and runs the FillMonthYearTable procedure to clear and refill a table that holds the month-year values.

Private Sub txtHowManyYears_AfterUpdate()

On Error GoTo ErrorHandler

   Dim intYears As Integer
   
   intYears = Nz(Me![txtHowManyYears].Value)
   
   If intYears <> 0 Then
      strPropertyName = "NumberOfYears"
      lngDataType = dbInteger
      Call SetProperty(strPropertyName, lngDataType, intYears)
      Call FillMonthYearTable
      
      intChoice = Nz(Me![fraSelection].Value, 1)
      
      If intChoice = 1 Then
         strQuery = "qrySelectMonthAndYear"
      ElseIf intChoice = 2 Then
         strQuery = "qrySelectMonthAndYearFiltered"
      End If
      
      Me![cboSelectMonthAndYear].RowSource = strQuery
      Me![cboSelectMonthAndYear].SetFocus
      Me![cboSelectMonthAndYear].Requery
      Me![cboSelectMonthAndYear].Dropdown
   Else
      strTitle = "Problem"
      strPrompt = "Please enter a number of years"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   End If
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

The fraSelection option group lets you choose between displaying all the month-year combinations (useful for selecting a month and year for performing an action that could work with any month-year combination, such as creating a calendar), while the Filtered option shows only the month-year combinations that actually have data (in this case, HireDate values). Depending on your choice, one or another row source query is assigned to cboSelectMonthAndYear.

Private Sub fraSelection_AfterUpdate()

On Error GoTo ErrorHandler
   
   intChoice = Nz(Me![fraSelection].Value, 1)
   
   If intChoice = 1 Then
      strQuery = "qrySelectMonthAndYear"
   ElseIf intChoice = 2 Then
      strQuery = "qrySelectMonthAndYearFiltered"
   End If
   
   Me![cboSelectMonthAndYear].SetFocus
   Me![cboSelectMonthAndYear].RowSource = strQuery
   Me![cboSelectMonthAndYear].Requery
   Me![cboSelectMonthAndYear].Dropdown
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

After selecting a month-year combination, clicking the cmdPrintReport button prints the report. If the All option was chosen, and there are no hire dates in that month and year, you will get a message to that effect; to avoid this annoyance, select the Filtered option, which will only display month-year combinations that have at least one hire date. Here is the report for October 1993:

References

The code in the sample database does not need any special references.

Supporting Files

Document Name Document Type Place in
Specialty Combo Boxes, Part 2 (AA 255).accdb Access 2007-2010 database (can also be used in higher versions of Access) Wherever you want

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.