Combo boxes are commonly used to display a list of values in a table or query for a user to select from. By responding to the NotInList event, you can provide a way for the user to add values that aren’t in the list.
Often the value displayed in a combo box is looked up from a record in a related table. Because the list is derived from a table or query, you must provide a way for the user to enter a new record in the underlying table. Then you can use the Requery method to requery the list, so it contains the new value.
When a user types a value in a combo box that isn’t in the list, the NotInList event of the combo box occurs as long as the combo box’s LimitToList property is set to Yes, or a column other than the combo box’s bound column is displayed in the box. You can write an event procedure for the NotInList event that provides a way for the user to add a new record to the table that supplies the list’s values. The NotInList event procedure includes a string argument named NewData that Microsoft Access uses to pass the text the user enters to the event procedure.
The NotInList event procedure also has a Response argument where you tell Microsoft Access what to do after the procedure runs. Depending on what action you take in the event procedure, you set the Response argument to one of three predefined constant values:
For example, the following event procedure asks the user whether to add a value to a list, adds the value, then uses the Response argument to tell Microsoft Access to requery the list:
Private Sub ShipperID_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim dbs As Database, rst As Recordset
intAnswer = MsgBox("Add " & NewData & " to the list of shippers?", _
vbQuestion + vbYesNo)
If intAnswer = vbYes Then
' Add shipper stored in NewData argument to the Shippers table.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Shippers")
rst.AddNew
rst!CompanyName = NewData
rst.Update
Response = acDataErrAdded ' Requery the combo box list.
Else
Response = acDataErrDisplay ' Require the user to select
' an existing shipper.
End If
rst.Close
End Sub
See Also For more information on the NotInList event, search the Help index for “NotInList event.”
When taking a new order in the Orders sample application, a user, typically a sales representative, starts by looking up the customer in the BillTo combo box at the top of the Orders form. If the customer is new and doesn’t appear in the list, the user needs a way to add the customer to the Customers table and update the combo box so it displays the new customer in the list.
You can let the user add a new customer by simply typing the new customer’s name in the combo box. To do this, write an event procedure for the NotInList event of the combo box.
Step One: Write the event procedure for adding a new customer This event procedure asks for confirmation that the user wants to add a new customer (and hasn’t just typed the name of an existing customer incorrectly), and then provides a way to do it.
The following code example shows the event procedure. An explanation of what it does follows the code example.
Private Sub BillTo_NotInList(NewData As String, Response As Integer)
' Allows user to add a new customer by typing the customer's name
' in the BillTo combo box.
Dim intNewCustomer As Integer, strTitle As String
Dim intMsgDialog As Integer, strMsg As String
Const conClrWhite = 16777215
Const conNormal = 1
' Check if user has already selected a customer.
If IsNull(CustomerID) Then
' Display message box asking if the user wants to add a new customer.
strTitle = "Customer Not in List"
strMsg = "Do you want to add a new customer?"
intMsgDialog = vbYesNo + vbExclamation
intNewCustomer = MsgBox(strMsg, intMsgDialog, strTitle)
If intNewCustomer = vbYes Then
' Remove text user entered from the combo box and assign
' it to the CompanyName control and the ShipName control.
BillTo.Undo
CompanyName.Enabled = True
CompanyName = NewData
ShipName = NewData
' Enable and move focus to CustomerID.
CustomerID.Enabled = True
CustomerID.Locked = False
CustomerID.BackColor = conClrWhite
CustomerID.BorderStyle = conNormal
CustomerID.SetFocus
' Enable the other customer information controls.
Address.Enabled = True
City.Enabled = True
Region.Enabled = True
City.Enabled = True
PostalCode.Enabled = True
Country.Enabled = True
ContactName.Enabled = True
ContactTitle.Enabled = True
Phone.Enabled = True
Fax.Enabled = True
MsgBox "Enter the new customer's ID, address, and contact information."
' Continue without displaying default error message.
Response = acDataErrContinue
Else
' Display the default error message.
Response = acDataErrDisplay
End If
Else
' User has already picked a customer; display a message and undo the field.
strMsg = "To modify this customer's company name, edit the name in the "
strMsg = strMsg & "box below the Bill To combo box. To add a new customer, "
strMsg = strMsg & "click Undo Record on the Records menu and then type the "
strMsg = strMsg & "new company name in the Bill To combo box."
MsgBox strMsg
BillTo.Undo
' Continue without displaying default error message.
Response = acDataErrContinue
End If
End Sub
In this code, you use the MsgBox function to ask if the user wants to add a new customer. If the user chooses Yes, the event procedure uses the Undo method to remove the text from the combo box. It then uses the NewData argument to assign the text the user entered in the combo box to the CompanyName control and the ShipName control. With the value cleared from the combo box, you can move the focus down to the customer controls.
Because the user can’t use the Orders form to change the CustomerID for an existing customer, the CustomerID control is normally locked, disabled, and displayed with a background that matches the form’s background, so it doesn’t look like a control the user can edit. Now that the user is entering a new customer, your code unlocks and enables the control, and displays it with a white background and borders. It also enables the other customer information controls. An event procedure for the form’s AfterUpdate event, which occurs after the record is saved, locks and disables the CustomerID control again, and displays it without a white background.
Note In the Orders form in the Orders sample application, all the fields from the Customers table are located on the Orders form, so users can add a complete record for a new customer directly in the fields on the Orders form. If you don’t want to include all the fields from the underlying table on your form, you can still let users add a new record to it. When the user wants to add a row to a combo box, display a separate form with all the fields from the underlying table on it. After the user saves and closes this separate form, you can requery the combo box so the new item appears in its list. For an example of this approach, see the Developer Solutions sample application.
Step Two: Write the event procedure that updates the combo box Your NotInList event procedure lets the user add a new customer and a new order at the same time. Once the order is saved and the new customer record is in the Customers table, you can update the BillTo combo box so it includes the new customer. You do this by writing an event procedure for the form’s AfterUpdate event that requeries the combo box using the Requery method, as follows:
Private Sub Form_AfterUpdate()
BillTo.Requery
End Sub
Tip If your combo box list includes a large number of rows, requerying the list every time you save a record may slow down your form’s performance. In this case, you can improve performance by opening a separate form for the user to add a new customer, and then requerying the combo box only when the customer information on the separate form is saved.