Access Cookbook - Ken Getz [190]
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
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