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 |