Microsoft KB Archive/125648

= ACC2: Use NotInList Event to Add a Record to a Combo Box =

Article ID: 125648

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q125648



SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.

This article demonstrates two methods of using the NotInList event to add a new record to a combo box.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the "Building Applications" manual.



MORE INFORMATION
The following examples use the Orders form in the sample database NWIND.MDB to demonstrate two methods of using the NotInList event to add a record to a combo box. The first method uses Access Basic code to add a new record directly to the Customers table when you enter a unique value in the Bill To combo box on the Orders form. The second method opens the Customers form when you enter a unique value in the Bill To combo box so that you can add the new customer information yourself before continuing.

CAUTION: Following the steps in these examples will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

Method 1: Using Access Basic Code to Add a Record Directly to a Table
 Open the sample database NWIND.MDB, and then open the Orders form in Design view.  Note that the Bill To combo box's LimitToList property is set to Yes. Set the Bill To combo box's OnNotInList property to the following event procedure: Sub Customer_ID_NotInList (NewData As String, Response As Integer) Dim DB As Database Dim RS As Recordset Dim Msg As String Dim CR As String: CR = Chr$(13)

' Exit Sub if the user cleared the selection. If NewData = "" Then Exit Sub

' Ask if the user wants to add the new customer. Msg = "'" & NewData & "' is not in the list." & CR & CR        Msg = Msg & "Do you want to add it?" If MsgBox(Msg, 32 + 4) = 7 Then ' If the user chooses No, instruct the user to try again. Response = DATA_ERRCONTINUE MsgBox "Please try again." Else ' If the user does not choose No, create a new record in the ' Customer table. On Error Resume Next

' Open the Customer table. Set DB = DBEngine.Workspaces(0).Databases(0) Set RS = DB.OpenRecordset("Customers", DB_OPEN_DYNASET)

RS.AddNew Msg = "Please enter a unique 5-character Customer ID." RS![Customer ID] = InputBox(Msg) RS![Company Name] = NewData RS.Update

' If an error occurred while adding the record... If Err Then ' ...instruct the user to try again. Response = DATA_ERRCONTINUE Beep: MsgBox Error$, 48 MsgBox "Please try again." Else ' If no error occurred, add the element to the combo box ' list. Response = DATA_ERRADDED End If

End If     End Sub 

Method 2: Using a Form to Add a New Combo Box Record
 Open the sample database NWIND.MDB, and then open the Orders form in Design view.  Note that the Bill To combo box's LimitToList property is set to Yes. Set the Bill To combo box's OnNotInList property to the following event procedure: Sub Customer_ID_NotInList (NewData As String, Response As Integer) Dim Result Dim Msg As String Dim CR As String: CR = Chr$(13)

' If the user cleared the selection, exit now. If NewData = "" Then Exit Sub

' Ask if the new customer should be added. Msg = "'" & NewData & "' is not in the list." & CR & CR        Msg = Msg & "Do you want to add it?" If MsgBox(Msg, 32 + 4) = 6 Then ' If Yes, launch the Customers form in data entry ' .. mode as a DIALOG form, passing the unique company name ' .. in the NewData variable as an argument to be used as           ' .. the default company name in the new Customer record. DoCmd OpenForm "Customers",, , , A_ADD, A_DIALOG, NewData End If

' See if the user created the customer... Result = DLookup("[Company Name]", "Customers", _                 "[Company Name]=""" & NewData & """") If IsNull(Result) Then ' ... if not then instruct the user to try again... Response = DATA_ERRCONTINUE MsgBox "Please try again!" Else ' ... otherwise, add the element to the combo list. Response = DATA_ERRADDED End If     End Sub  Save and then close the Orders form. Open the Customers form in Design view.</li>  Set the form's OnLoad property to the following event procedure: Sub Form_Load ' If OpenArgs is not null (contains a new company name)... If IsNull(Me.OpenArgs) = False Then ' ...use the contents as the Company Name field. Me![Company Name] = Me.OpenArgs End If     End Sub </li> Save and then close the Customers form.</li></ol>

<div class="references_section">