Access: Specialty Combo Boxes, Part 1

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.

A regular combo box that gives you a choice of an item from a list (usually displaying items from a query or table) is very useful, but sometimes you need something more.   This article describes several ways to expand the utility of a combo box.

Hard-Coded List Combo Box

When you have a small number of choices, and there is little or no chance that there will be any changes to the list, you can type them in to the combo box’s property sheet, separated by semicolons, as for the cboTitleOfCourtesy control on frmEmployees in the sample database:

Set Limit to List to Yes to restrict entries to the hard-coded list, or to No to allow free-form entries that won’t be added to the list for future selection.

Add-to Combo Boxes

Combo boxes offer a choice of Limit to List (Yes or No). If you select Yes, only items in the list can be selected; if you select No, anything can be typed in, and the entry will be saved to the combo box’s bound field for that record, but that new entry won’t appear in the combo box list for future selection. I have two methods for adding a new entry to a combo box list on-the-fly; one is suitable for adding items to a simple lookup table, with just a single data field and possibly an AutoNumber ID field; the other method is needed for more complex additions, with several fields that need data for the new item to be added to the combo box’s row source.

The simple version of the Add-to combo box is illustrated by the cboSalesDistrict combo box, whose row source is tblSalesDistricts:

The procedure on the NotInList event is listed below (note that I have added standard prefixes to the two arguments). The entries in red text are specific to this combo box.

Private Sub cboSalesDistrict_NotInList(strNewData As String, _
   intResponse As Integer)

'Set Limit to List to Yes

On Error GoTo ErrorHandler

    Dim cbo As Access.ComboBox
    Dim intMsgDialog As Integer
    Dim intResult As Integer
    Dim rst As DAO.Recordset
    Dim strEntry As String
    Dim strFieldName As String
    Dim strMsg As String
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strTable As String
    Dim strTitle As String

    'The name of the table that is the combo box's row source
    strTable = "tblSalesDistricts"

    'The type of item to add to the table
    strEntry = "Sales District"

    'The field in the lookup table in which the new entry is stored
    strFieldName = "SalesDistrict"

    'The add-to combo box
    Set cbo = Me.ActiveControl

    'Display a message box asking whether the user wants to add
    'a new entry.
    strTitle = strEntry & " not in list"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intResult = MsgBox(strMsg, intMsgDialog, strTitle)

    If intResult = vbNo Then
        'Cancel adding the new entry to the lookup table.
        intResponse = acDataErrContinue
        cbo.Undo
        GoTo ErrorHandlerExit
    ElseIf intResult = vbYes Then
        'Add a new record to the lookup table.
        Set rst = CurrentDb.OpenRecordset(strTable)
        rst.AddNew
        rst(strFieldName) = strNewData
        rst.Update
        rst.Close

        'Continue without displaying default error message.
        intResponse = acDataErrAdded
     End If

ErrorHandlerExit:
   Exit Sub

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

End Sub

The boilerplate code in the text file (Add-to Combo Box (no form).txt) can be used for any combo box that needs the add-to capability; just replace the underscores with the appropriate data for that specific combo box.

To see how this procedure works, if you type “Hawaii” into the cboSalesDistricts combo box and press Enter, you will get a dialog box asking if you want to add it as a new entry:

If you select Yes, Hawaii is added to tblSalesDistricts, and is available in the combo box list from now on.

Note: it is possible to set up a lookup in a field’s property sheet in a table, but I don’t recommend this method, since it is much more difficult to modify the lookup if needed, especially if you are working with a back end/front end database.

The cboReportsTo combo box illustrates a more complex situation; its row source is a query based on tblSupervisors, and that table has a number of fields that need to be filled in for a new entry. This requires opening another form where basic information can be added for the new supervisor.

The NotInList event procedure for cboReportsTo is listed below; it includes code to parse out the first and last names from the entry, which is presumed to be first name first (if that is not the case, the name can be corrected on the New Supervisor form that is opened from this procedure). Items specific to this particular combo box are highlighted in yellow.

Private Sub cboReportsTo_NotInList(strNewData As String, _
   intResponse As Integer)

'Set Limit to List to Yes

On Error GoTo ErrorHandler
   Dim cbo As Access.ComboBox
   Dim frm As Access.Form
   Dim intMsgDialog As Integer
   Dim intResult As Integer
   Dim intSpacePos As Integer
   Dim lngID As Long
   Dim rst As DAO.Recordset
   Dim strDescription As String
   Dim strEntry As String
   Dim strFieldName1 As String
   Dim strFieldName2 As String
   Dim strFilter As String
   Dim strFirstName As String
   Dim strForm As String
   Dim strLastName As String
   Dim strMsg As String
   Dim strMsg1 As String
   Dim strMsg2 As String
   Dim strTable As String
   Dim strTitle As String
   'The name of the table that is the combo box's row source
   strTable = "tblSupervisors"
   'The name of the form where the new item can be edited
   strForm = "frmNewSupervisor"

   'The type of item to add to the table
   strEntry = "Supervisor"

   'Parse entry (if needed)
   intSpacePos = InStr(strNewData, Chr(32))
   strFirstName = Left(strNewData, intSpacePos - 1)
   strLastName = Mid(strNewData, intSpacePos + 1)
   Debug.Print "First name " & strFirstName & "; last name " & strLastName

   'The field (or fields) in the lookup table in which the new entry is stored
   strFieldName1 = "FirstName"
   strFieldName2 = "LastName"
   'The add-to combo box
   Set cbo = Me.ActiveControl

   'Display a message box asking whether the user wants to add
   'a new entry.
   strTitle = strEntry & " not in list"
   intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
   strMsg1 = "Do you want to add "
   strMsg2 = " as a new " & strEntry & " entry?"
   strMsg = strMsg1 + strNewData + strMsg2
   intResult = MsgBox(strMsg, intMsgDialog, strTitle)
   If intResult = vbNo Then
     'Cancel adding the new entry to the lookup table.
     intResponse = acDataErrContinue
     cbo.Undo
     GoTo ErrorHandlerExit
   ElseIf intResult = vbYes Then
     'Add a new record to the lookup table.
     Set rst = CurrentDb.OpenRecordset(strTable)
     rst.AddNew
     rst.Fields(strFieldName1) = strFirstName
     rst.Fields(strFieldName2) = strLastName
     rst.Update
     lngID = rst![SupervisorID]
     rst.Close
     cbo.Undo
 
    'Continue without displaying default error message
     intResponse = acDataErrContinue
     'Open form for adding more data for new item
     DoCmd.OpenForm strForm
     Set frm = Forms(strForm)
     strFilter = "[SupervisorID] = " & lngID
     Debug.Print "Filter string: " & strFilter
     frm.FilterOn = True
     frm.Filter = strFilter
   End If

ErrorHandlerExit:
   Exit Sub

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

End Sub

After selecting Yes on the dialog asking if you wish to add a new supervisor, frmNewSupervisor opens, with the last and first names parsed from the new entry; they can be corrected if needed, and the other fields filled in. Only a few basic fields from tblSupervisors appear on this form; the remaining fields can be filled in later, on frmSupervisors.

Clicking the Discard button discards the new record; clicking the Save button saves it and returns to the calling form, with the new entry now in the list for future use.

Boilerplate code is provided in the text file Add-to Combo Box (with form).txt; to use it, just replace the underscores with data for the specific combo box.

Part 2 of this article will describe three more ways to enhance combo boxes.

References

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

Supporting Files

The zip file containing this article, in Word 2007-2010 format, plus the supporting file(s), may be downloaded from accarch254.zip

Document Name Document Type Place in
Specialty Combo Boxes, Part 1.accdb Access 2007-2010 database (can also be used in higher versions of Access) Wherever you want
Add-to Combo Box (with form).txt Text file with procedure code Wherever you want
Add-to Combo Box (no form) Text file with procedure code Wherever you want

Want More?

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