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

• Microsoft Access 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.

You can find information, instructions, and examples in the Solutions sample application (SOLUTION.MDB) included with Microsoft Access version 2.0. For more information about using the NotInList event, open the SOLUTION.MDB database usually located in the ACCESS\SAMPAPPS directory. In the Select A Category Of Examples box, select "Get more mileage from combo boxes, list boxes, subforms, and subreports." In the Select An Example box, select "Add a new record to a list."

Additional query words:

Keywords : kbusage FmsCmbo
Version : WINDOWS:2.0
Platform : WINDOWS
Issue type : kbhowto