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

Last reviewed: May 8, 1997
Article ID: Q125648
The information in this article applies to:
  • 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.

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


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


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.