Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Import the basNotInList module from

   09-06.MDB
   into your application.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.