Creating Combo Boxes in Access 2007 that Accept New Entries
Summary: Learn two different ways to display a custom pop-up form that gives users the option to add new items to a lookup table in a combo box; by using techniques built into Microsoft Office Access 2007 or by writing some VBA code to manage the process. (7 printed pages)
Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007
Adapted from Access Cookbook, 2nd Edition by Ken Getz, Paul Litwin, and Andy Baron. Copyright © 2004, O'Reilly Media, Inc. All rights reserved. Used with permission.
Ken Getz, MCW Technologies, LLC
This article demonstrates how to create combo boxes that accept new entries by using the NotInList event and the OpenArgs property of forms. To implement the solution, you use the NotInList event of a combo box to trap the error that occurs when a user types a value that is not in the underlying list. Then, you write an event procedure attached to that event. The event procedure opens a pop-up form to gather information about the new entry, adds the new entry to the list, and then continues where the user started.
To begin, load the sample database AddItemsToComboBox.accdb and open the
frmDataEntry form in form view. Use the form to select a U.S. state from the combo box. For the sake of this example, the list of states in the combo box is incomplete. To add a new state, type its abbreviation in the form and answer Yes when asked whether you want to add a new record. Type the state name in the pop-up form that appears, and then click Done to return to the original data entry form, where your newly added state is already selected in the combo box.
Figure 1. Adding a new record by using the sample form
The sample form,
frmDataEntry, uses some VBA code attached to the NotInList event of the combo box to pass information from the main form to the pop-up form, and then refresh the main form once the user closes the pop-up form.
The technique that is built in to Access 2007 simplifies the process, but gives up some flexibility. In the sample database, open the
frmDataEntryAutomatic form, and add a new state. When you try to type a value that is not in the list, Access displays the following alert.
Figure 2. Adding a new item by using the technique that is built in to Access 2007
When you click Yes, Access displays the same bound form as before, (
frmState), but does not fill in any of the data for you; you must know that you are supposed to type the same state information that you already typed on the form, along with the state name. When you close the pop-up form, Access adds the item to the underlying table for you, and refreshes the combo box on the main form.
Access can do all of this for you if you set the LimitToList property of the ComboBox to Yes, and set the ListItemsEditForm property to the name of a bound form that Access should display if you attempt to type a value that is not in the list (see Figure 3). Note however, that this technique works only in Access 2007, and does not necessarily make the process easy for the user, even if it is easier for the developer.
Figure 3. Access does the work for you if you specify these property values
Although it takes a bit more effort to handle the NotInList event manually, doing so gives you far more control over the behavior of the forms in your application.
To add the control to your own combo boxes
Make sure that your application has a table that contains lookup values. For example, a table similar to
tblStatein the sample database.
basNotInListmodule from AddItemsToComboBox.accdb into your application.
Open your existing form in design view and create the combo box that you want to use to add and display records. Use the values in the following table to set the combo box properties.
Table 1. Combo Box Property Settings
<Any table or query>
Create an event procedure attached to the NotInList event of the combo box control. Add the following code to the event procedure (shown here for a control named
Replace the arguments in the call to
AddToCombowith the appropriate arguments for your own database. That is, specify the name of the form that you will use to add new records to the combo box, and the name of the control on the data entry form that matches the first displayed column of the combo box.
Create the pop-up form (
frmState, in the sample) that you will use to add new values to the combo box. Use the values in the following table to set the form properties.
Table 2. Form Property Settings
The same table or query as the combo box's row source
Add an event handler for the Load event of the form by adding the following code.
Add controls to the pop-up form for all table fields that you need the user to complete. One of them should be the field that corresponds to the first visible column of the combo box; you supplied a name for this field in Step 4.
Save the pop-up form using the name that you supplied in Step 4.
Now open the main form that contains the combo box. Type a new value in the combo box. You should be prompted with a message box that asks you if you want to add a record. If you click Yes, the pop-up form opens to display the information that you typed in the combo box control. Type any other required information and then close the pop-up form to add the new information to the combo box list and view the new value already selected in the combo box.
Notes on the code
When you set the LimitToList property to Yes in a combo box, Access generates a NotInList event when a user types in a value that is not in the list. In addition, it displays an error message by default. If you create a NotInList event procedure, however, you can intercept the error message before it occurs and add the record to the list yourself.
After you process the event, set the Response argument that Access provides to one of the following constants.
Table 3. Return Value Constants for the Response Argument
Access displays the default error message.
Access continues without displaying the error message.
Access does not display the error message, and instead, requeries the underlying list. Use this return value when you add the value yourself.
The solution in this article uses a general-purpose function in
AddToCombo, to add a new record. To allow for the possibility that the user does not want to type a new value, the function displays a simple message box and quits if the user selects No. In that case, use acDataErrContinue as the return value so that Access suppresses the default error message, but does not try to add the new value to the combo box.
The following is the code for the
Public Function AddToCombo( _ addFormName As String, controlName As String, _ newData As String) As Integer ' Add a new record to a table by calling a form, and then ' requery the calling form. Designed to be called from ' NotInList event procedures ' ' addFormName - the form to be opened to add a record ' controlName - the control on the add form that matches ' the displayed info in the calling combo box ' newData - the data as supplied by the calling combo box On Error GoTo HandleErr ' First, confirm the user really wants to type a new record. If MsgBox("Add new value to List?", vbQuestion + vbYesNo, _ "Warning") = vbNo Then AddToCombo = acDataErrContinue Exit Function End If ' Open up the data add form in dialog mode, feeding it ' the name of the control and data to use DoCmd.OpenForm FormName:=addFormName, _ DataMode:=acAdd, _ WindowMode:=acDialog, _ OpenArgs:=controlName & ";" & newData ' Before control returns to the calling form, ' tell it we've added the value AddToCombo = acDataErrAdded ExitHere: Exit Function HandleErr: MsgBox "Error " & Err.Number & ": " & Err.Description, , _ "AddToCombo" Resume ExitHere End Function
If the user wants to add the new record, the function opens the pop-up form in dialog mode. That action pauses the function because a form open in dialog mode does not give up the focus until it is closed or hidden. The paused funtion, in turn, gives the user the opportunity to type the required data to complete the record.
' Open up the data add form in dialog mode, feeding it ' the name of the control and data to use. DoCmd.OpenForm FormName:=addFormName, _ DataMode:=acAdd, _ WindowMode:=acDialog, _ OpenArgs:=controlName & ";" & newData
Another issue that arises here is that you cannot fill in controls on the form before it is opened, and you cannot fill them in after it is opened because the form is open in dialog mode. The
AddToCombo function gets around the issue by using the OpenArgs property of the pop-up form, which allows you to pass a text string to the form. (More on that a bit later though, when you look how the form uses the OpenArgs property to fill in its key field.)
After you close the pop-up form, the code must set the appropriate return value. In this case, returning acDataErrAdded tells Access that you added the value to the underlying table and now that value can be used as the value for the combo box.
' Before control returns to the calling form, ' tell it we've added the value. AddToCombo = acDataErrAdded
If you look carefully at the code that the main form calls when it first opens the pop-up form, you'll see the following call to the DoCmd.OpenForm method.
DoCmd.OpenForm FormName:=addFormName, _ DataMode:=acAdd, _ WindowMode:=acDialog, _ OpenArgs:=controlName & ";" & newData
This code concatenates the control name and its new data into a semi-colon-delimited pair. The pop-up form must retrieve the value from its OpenArgs property, parse it into two pieces, and place the new data into the correct location on the pop-up form.
When the pop-up form opens, the Load event calls the
CheckOpenArgs function, which takes a form variable from the active form as its only parameter. This function processes the OpenArgs property of the form, which is where the form places the parameter that was passed to it when it was opened. The
CheckOpenArgs procedure uses the following code.
Public Function CheckOpenArgs(frm As Form) ' Designed to be called on loading a new form ' Checks the OpenArgs and if it finds a string of ' the form ' controlname;value ' loads that value into that control Dim controlName As String Dim controlValue As String Dim semiColonPosition As Integer On Error GoTo HandleErr If IsNull(frm.OpenArgs) Then Exit Function Else semiColonPosition = InStr(frm.OpenArgs, ";") ' Was there a semi-colon? If semiColonPosition > 0 Then controlName = Left(frm.OpenArgs, semiColonPosition - 1) controlValue = Mid(frm.OpenArgs, semiColonPosition + 1) ' Possibly this OpenArgs belongs to someone else ' and just looks like ours. Set the error handling ' to just ignore any errors on the next line. On Error Resume Next frm.Form(controlName) = controlValue End If End If ExitHere: Exit Function HandleErr: MsgBox "Error " & Err.Number & ": " & Err.Description, , _ "CheckOpenArgs()" Resume ExitHere End Function
CheckOpenArgs function must avoid errors because it is called each time the form is opened. First, it is possible that no OpenArgs argument was passed in (perhaps you opened the form on its own). Second, the OpenArgs argument might be there for another reason--perhaps a different form opened the pop-up form and passed its own OpenArgs value to the form. Thus, if OpenArgs does not parse out as expected (in the format “ControlName;Value”), the procedure ignores it.
If OpenArgs is in the correct format, the code parses out the value to place in the corresponding control on the form.
The solution in this article is designed to be reusable, but you might find that you need a more specific function for a particular combo box. For example, you might add a Cancel button to the pop-up form for those users who decide against adding a new record. Or, you might want to add context to the data entry by using unbound text boxes on the data entry form to display pertinent information from the main form.