Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 9.6 Create a Combo Box That Accepts New Entries

9.6.1 Problem

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?

9.6.2 Solution

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.

Figure 9-18. Adding a new record to the underlying table

To add this functionality to your own combo boxes, follow these steps:

Import the basNotInList module from 09-06.MDB into your application.
Open your existing form in design view and create the combo box to which you wish to add records. Set the combo box properties as shown in Table 9-3.

Table 9-3. Property settings for combo box

Property

Setting

RowSourceType

Table/Query

RowSource

Any table or query

LimitToList

Yes

Create an event procedure attached to the NotInList event of the combo box control. (If you're unsure of how to do this, see How Do I Create an Event Procedure? in the the preface of this book.) Add the following code to the event procedure (shown here for a control named cboState):
```
Private Sub cboState_NotInList(NewData As String, Response As Integer)
    Response = acbAddViaForm("frmState", "txtAbbreviation", NewData)
End Sub
```
Replace the arguments to acbAddViaForm with the appropriate arguments for your own database: the name of the data entry form used 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 that will be used to add new combo box values. Set the form properties as shown in Table 9-4.

Table 9-4. Property settings for the pop-up form

Property

Setting

RecordSource

The same table or query as the combo box's row source

DefaultEditing

Data Entry

OnLoad

=acbCheckOpenArgs([Form])

Add controls to the pop-up form for all table fields that you need the user to fill in. One of them should be the field that corresponds to the first visible column of the combo box; this field's name is the one you supplied in Step 3.
Save the pop-up form, using the name you supplied in Step 3. Now open the main form with the combo box on it. Type a new value into the combo box. You should be prompted with a message box asking if you want to add a record (Figure 9-19). Click on Yes, and the pop-up form will appear with the information you typed in the combo box control. Fill in the rest of the required information and close the pop-up form. The new information will be added to the combo box list and the new value will be selected in the combo box.

Figure 9-19. Prompt for new record

9.6.3 Discussion

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.

When you're done processing the event, set the Response argument provided by Access to one of three possible constants:

acDataErrDisplay tells Access to display the default error message.
acDataErrContinue tells Access not to display the error message but to otherwise continue.
acDataErrAdded tells Access not to display the error message but to requery the underlying list. This is the return value to use when you add the value 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

See Recipe 7.5 in Chapter 7 for more information on working with list and combo boxes.

[ Team LiB ]