Microsoft KB Archive/161007

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

Article ID: 161007

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q161007



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



SUMMARY
This article shows you two methods of using the NotInList event to add a new record to a combo box on a form.

This article assumes that you are familiar with Visual Basic for Applications and that you are also familiar with creating Microsoft Access applications by using the programming tools that are provided with Microsoft Access. For more information about Visual Basic for Applications, refer to your version of the "Building Applications with Microsoft Access" manual.



MORE INFORMATION
The following examples use the Orders form in the Northwind sample database to demonstrate the two methods of using the NotInList event to add a record to a combo box. The NotInList event fires when you type a new company in the CustomerID (labeled Bill To:) field on the Orders form.

The first method uses Visual Basic for Applications code to programmatically add a new record to the Customers table. The second method opens the Customers form and, then allows you add a new record yourself.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Using Visual Basic for Applications Code to Add a Record to a Table
 Open the sample database Northwind.mdb. Open the Orders form in Design view. Note that the LimitToList property of the CustomerID combo box is set to Yes.  Set the OnNotInList property of the CustomerID combo box to the following event procedure: Private Sub CustomerID_NotInList (NewData As String, Response As _                                       Integer) Dim Db As Database Dim Rs As Recordset Dim Msg As String Dim NewID As String

On Error GoTo Err_CustomerID_NotInList

' Exit this subroutine if the combo box was cleared. If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new customer. Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr Msg = Msg & "Do you want to add it?" If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then ' If the user chose not to add a customer, set the Response ' argument to suppress an error message and undo changes. Response = acDataErrContinue ' Display a customized message. MsgBox "Please try again." Else ' If the user chose to add a new customer, open a recordset ' using the Customers table. Set Db = CurrentDB Set Rs = Db.OpenRecordset("Customers", DB_OPEN_TABLE) ' Ask the user to input a new Customer ID. Msg = "Please enter a unique 5-character" & vbCr & "CustomerID." NewID = InputBox(Msg) Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID) ' If the NewID already exists, ask for another new unique ' CustomerID Do Until Rs.NoMatch NewID = InputBox("Customer ID " & NewID & " already exists." & _                      vbCr & vbCr & Msg, NewID & " Already Exists") Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID) Loop ' Create a new record. Rs.AddNew ' Assign the NewID to the CustomerID field. Rs![CustomerID] = NewID ' Assign the NewData argument to the CompanyName field. Rs![CompanyName] = NewData ' Save the record.

Rs.Update ' Set Response argument to indicate that new data is being added. Response = acDataErrAdded End if             Exit_CustomerID_NotInList: Exit Sub Err_CustomerID_NotInList: 'An unexpected error occurred, display the normal error message. MsgBox Err.Description 'Set the Response argument to suppress an error message and undo 'changes. Response = acDataErrContinue

End Sub

 Open the Orders form in Form view. To add a new order, type ABC Wholesalers in the Bill To: field, and then enter ABCWH when you are prompted for the Customer ID.

The code in the OnNotInList event procedure runs, and then adds a new customer to the Customers table.</li></ol>

Using the Customers Form to Add a New Record
<ol> Open the sample database Northwind.mdb.</li> Open the Orders form in Design view.</li> Note that the LimitToList property of the CustomerID combo box is set to Yes.</li>  Set the OnNotInList property of the CustomerID combo box to the following event procedure: Private Sub CustomerID_NotInList (NewData As String, Response As _                                       Integer) Dim Result Dim Msg As String Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared. If NewData = "" Then Exit Sub

' Ask the user if he or she wishes 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, vbQuestion + vbYesNo) = vbYes Then ' If the user chose Yes, start the Customers form in data entry ' mode as a dialog form, passing the new company name in           ' NewData to the OpenForm method's OpenArgs argument. The ' OpenArgs argument is used in Customer form's Form_Load event ' procedure. DoCmd.OpenForm "Customers",, , , acAdd, acDialog, NewData End If

' Look for the customer the user created in the Customers form. Result = DLookup("[CompanyName]", "Customers", _                 "[CompanyName]='" & NewData & "'") If IsNull(Result) Then ' If the customer was not created, set the Response argument ' to suppress an error message and undo changes. Response = acDataErrContinue ' Display a customized message. MsgBox "Please try again!" Else ' If the customer was created, set the Response argument to           ' indicate that new data is being added. Response = acDataErrAdded End If     End Sub

</li> Save, and then close the Orders form.</li> Open the Customers form in Design view.</li>  Set the OnLoad property of the form to the following event procedure: Private Sub Form_Load If Not IsNull(Me.OpenArgs) Then ' If form's OpenArgs property has a value, assign the contents ' of OpenArgs to the CompanyName field. OpenArgs will contain ' a company name if this form is opened using the OpenForm ' method with an OpenArgs argument, as done in the Orders ' form's CustomerID_NotInList event procedure. Me![CompanyName] = Me.OpenArgs End If     End Sub </li> Save, and then close the Customers form.</li> Open the Orders form in Form view.</li> To add a new Order, type ABC Distributors in the Bill To: field. When the Customers form opens, type ABCDI in the Customer ID field. You can type anything you want to for the remaining customer information.</li></ol>

<div class="references_section">