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

• Microsoft Access version 2.0

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

1. Open the sample database NWIND.MDB, and then open the Orders form in Design view.

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

1. Open the sample database NWIND.MDB, and then open the Orders form in Design view.

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

3. Save and then close the Orders form.

4. Open the Customers form in Design view.

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

6. Save and then close the Customers form.

REFERENCES

For more information about the NotInList event, search for "NotInList," and then "NotInList Event" using the Microsoft Access Help menu.

For more information about the LimitToList property, search for "LimitToList," and then "LimitToList Property" using the Microsoft Access Help menu.