|[ Team LiB ]|
Recipe 9.6 Create a Combo Box That Accepts New Entries
You're using combo boxes for data entry on your forms, and you want to allow users to add a new entry to the list of values in the combo box. Can you do this without forcing users to close the data entry form, add the record using a different form, and then return to the original form?
You can use the NotInList event to trap the error that occurs when a user types into a combo box a value that isn't in the underlying list. You can write an event procedure attached to this event that opens a pop-up form to gather any necessary data for the new entry, adds the new entry to the list, and then continues where the user started. This solution demonstrates how to create combo boxes that accept new entries by using the NotInList event and the OpenArgs property of forms.
Load the sample database 09-06.MDB and open the frmDataEntry form in form view. This form allows you to select a U.S. state from the combo box, but the list is purposely incomplete for the example. To enter a new state, type its abbreviation in the form and answer Yes when Access asks whether you want to add a new record. A form will pop up, as shown in Figure 9-18, to collect the other details (in this case, the state name). When you close the form, you'll be returned to the original data entry form with your newly added state already selected in the combo box.
To add this functionality to your own combo boxes, follow these steps:
When you have a combo box with its LimitToList property set to Yes, Access generates the NotInList event when the user types in a value that's not in the list. By default, this displays an error message. However, by creating a NotInList event procedure, you can intercept this message before it occurs and add the record to the list yourself.
This solution uses a generic function, acbAddViaForm, to handle the record addition. To allow for the possibility that the user may not want to enter a new value (perhaps he or she mistyped the entry), the function displays a simple message box and quits if the user selects the No button. You also have to tell the original event procedure what to do with the data. The acDataErrContinue constant tells Access to suppress the default error message, but not to try to add the new value to the combo box. The code for acbAddViaForm is:
Public Function acbAddViaForm(strAddForm As String, _ strControlName As String, strNewData 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 ' OnNotInList event procedures. ' ' strAddForm - The form to be opened to add a record ' strControlName - The control on the add form that matches ' the displayed info in the calling combo box ' strNewData - The data as supplied by the calling combo box On Error GoTo HandleErr ' First, confirm that the user really wants to enter a new record. If MsgBox("Add new value to List?", vbQuestion + vbYesNo, _ "Warning") = vbNo Then acbAddViaForm = 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:=strAddForm, DataMode:=acAdd, _ WindowMode:=acDialog, OpenArgs:=strControlName & ";" & strNewData ' Before control returns to the calling form, ' tell it we've added the value. acbAddViaForm = acDataErrAdded ExitHere: Exit Function HandleErr: MsgBox "Error " & Err.Number & ": " & Err.Description, _ , "acbAddViaForm" Resume ExitHere End Function
If the user wants to add the new record, the function opens the pop-up form in dialog mode. This pauses the function at this point (because a dialog-mode form won't give up the focus until it is closed or hidden) and lets the user enter 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:=strAddForm, DataMode:=acAdd, _ WindowMode:=acDialog, OpenArgs:=strControlName & ";" & strNewData
However, this leads to another issue. You can't fill in controls on the form before it's opened, and you can't fill them in after because the form is open in dialog mode. The acbAddViaForm function gets around this by using the OpenArgs property of the form, which allows you to pass a text string to the form. You'll see later in this solution how this property is used by the form to fill in its key field.
After the pop-up form is closed, all you have to do is set the appropriate return value. In this case, acDataErrAdded tells Access that you've added the value to the underlying table and that it can be used as the value for the combo box:
' Before control returns to the calling form, ' tell it we've added the value. acbAddViaForm = acDataErrAdded
When the pop-up form opens, the OnLoad event property calls the acbCheckOpenArgs function, which takes a form variable from the active form as its only parameter. This function is used to process the OpenArgs property of the form (which is where the form places the parameter that was passed to it when it was opened). Its code is:
Public Function acbCheckOpenArgs(frm As Form) ' Designed to be called on loading a new form. ' Checks OpenArgs and, if it finds a string of ' the form "ControlName;Value", loads that ' value into that control. Dim strControlName As String Dim strControlValue As String Dim intSemi As Integer On Error GoTo HandleErr If IsNull(frm.OpenArgs) Then Exit Function Else intSemi = InStr(1, frm.OpenArgs, ";") If intSemi = 0 Then Exit Function End If strControlName = Left$(frm.OpenArgs, intSemi - 1) strControlValue = Mid$(frm.OpenArgs, intSemi + 1) ' This OpenArgs property may belong to someone else ' and just look like ours. Set the error handling ' to just ignore any errors on the next line. On Error Resume Next frm.Form(strControlName) = strControlValue End If ExitHere: Exit Function HandleErr: MsgBox "Error " & Err.Number & ": " & Err.Description, _ , "acbCheckOpenArgs( )" Resume ExitHere End Function
The acbCheckOpenArgs function has to be careful to avoid errors because it's called every time the form is opened. First, it's possible that no OpenArgs argument was passed in. Second, the OpenArgs argument might be there for another reason. Thus, if OpenArgs doesn't parse out as expected (in the format ControlName;Value), it's ignored.
If OpenArgs is in the correct format, the code parses out the value to be placed in the corresponding control on the form.
This solution is designed to be generic. You may find that you need a more specific function for a particular combo box. For example, you could allow users to cancel out of the pop-up form in case they decide against adding a new record, or you could use unbound text boxes on the data entry form to display pertinent information from the main form, adding context for data entry.
9.6.4 See Also
|[ Team LiB ]|