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

MORE INFORMATION

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.