ID: Q161007
The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.
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 with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
The following examples use the Orders form in the Northwind sample database to demonstrate 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 lets you add a new record yourself.
CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind database.
1. Open the sample database Northwind.mdb.
2. Open the Orders form in Design view.
3. Note that the LimitToList property of the CustomerID combo box is set
to Yes.
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 CR As String
CR = Chr$(13)
' 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." & CR & CR
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)
' Let code execution continue if a run-time error occurs.
On Error Resume Next
' Create a new record.
Rs.AddNew
' Ask the user to input a new Customer ID.
Msg = "Please enter a unique 5-character Customer ID."
Rs![CustomerID] = InputBox(Msg)
' Assign the NewData argument to the CompanyName field.
Rs![CompanyName] = NewData
' Save the record.
Rs.Update
If Err Then
' If a run-time error occurred while attempting to add a new
' record, set the Response argument to suppress an error
' message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox Error$ & CR & CR & "Please try again.",vbExclamation
Else
' If a run-time error did not occur, set Response argument
' to indicate that new data is being added.
Response = acDataErrAdded
End If
End If
End Sub
6. Add a new order, typing ABC Wholesalers in the Bill To field, and
enter ABCWH when you are prompted for the Customer ID. The code in
the OnNotInList event procedure runs and adds a new customer to the
Customers table.
1. Open the sample database Northwind.mdb.
2. Open the Orders form in Design view.
3. Note that the LimitToList property of the CustomerID combo box is set
to Yes.
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
6. Open the Customers form in Design view.
7. Set the form's OnLoad property 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
view.
the Customers form opens, type ABCDI in the Customer ID field, and type
anything you like for the remaining customer information.
For more information about the NotInList event, search the Help Index for "NotInList event," or ask the Microsoft Access 97 Office Assistant.
For more information about the LimitToList property, search the Help Index for "LimitToList property," or ask the Microsoft Access 97 Office Assistant.
You can find more information, instructions, and examples in the Developer Solutions sample application (Solutions.mdb) included with Microsoft Access 97. Open the database and click "Work with combo boxes, list boxes, subforms, and subreports" in the Select a Category of Examples box; then click "Add a new record to a list" in the Select an Example box.
Additional query words:
Keywords : kbusage FmsEvnt FmsCmbo FmrHowto MdlRcd
Version : 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: December 19, 1998