Adding a Row to a Combo Box List

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:

acDataErrAdded If your event procedure enters the new value in the record source for the list or provides a way for the user to do so, set the Response argument to acDataErrAdded. Microsoft Access then requeries the combo box for you, adding the new value to the list.

acDataErrDisplay If you don't add the new value and want Microsoft Access to display the default error message, set the Response argument to acDataErrDisplay. Microsoft Access requires the user to enter a valid value from the list.

acDataErrContinue If you display your own message in the event procedure, set the Response argument to acDataErrContinue. Microsoft Access doesn't display its default error message, but still requires the user to enter a value in the field. If you don't want the user to select an existing value from the list, you can undo changes to the field by using the Undo method.

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."

Adding a Row to the Combo Box's List on the Orders Form

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.

Casa de Bender