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

• Microsoft Access 2000

SUMMARY

This article shows you two methods of using the NotInList event to add a new record to a combo box on a form.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

MORE INFORMATION

The following examples use the Orders form in the Northwind sample database to demonstrate two methods of using the NotInList event to add a record to a combo box. The NotInList event is triggered when you type a new company in the CustomerID (labeled Bill To:) field on the Orders form.

The first method uses Visual Basic for Applications code to programmatically add a new record to the Customers table. The second method opens the Customers form and lets you add a new record yourself.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Method 1: Using Code to Add a Record to a Table

1. Open the sample database Northwind.mdb.



2. Open the Orders form in Design view.



3. Note that the LimitToList property of the CustomerID combo box is set to Yes.



4. Set the OnNotInList property of the CustomerID combo box to the following event procedure:




Private Sub CustomerID_NotInList(NewData As String, _
            Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_CustomerID_NotInList

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a customer, set the Response
        ' argument to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again."
    Else
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)

        ' Ask the user to input a new Customer ID.
        Msg = "Please enter a unique 5-character" & vbCr & "Customer ID."
        NewID = InputBox(Msg)
        Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
        ' If the NewID already exists, ask for another new unique
        ' CustomerID
        Do Until Rs.NoMatch
           NewID = InputBox("Customer ID " & NewID & " already exists." & _
                    vbCr & vbCr & Msg, NewID & " Already Exists")
           Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
        Loop
        ' Create a new record.
        Rs.AddNew
        ' Assign the NewID to the CustomerID field.
        Rs![CustomerID] = NewID
        ' Assign the NewData argument to the CompanyName field.
        Rs![CompanyName] = NewData
        ' Save the record.
        Rs.Update

        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded

    End If

    Exit_CustomerID_NotInList:
       Exit Sub
    Err_CustomerID_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue

 End Sub 

5. Open the Orders form in Form view.



6. Add a new order by typing ABC Wholesalers in the Bill To field, and enter ABCWH when you are prompted for the Customer ID. The code in the OnNotInList event procedure runs and adds a new customer to the Customers table.

Method 2: Using a Form to Add a New Record

1. Open the sample database Northwind.mdb.



2. Open the Orders form in Design view.



3. Note that the LimitToList property of the CustomerID combo box is set to Yes.



4. Set the OnNotInList property of the CustomerID combo box to the following event procedure:




Private Sub CustomerID_NotInList (NewData As String, Response As _
                                        Integer)
Dim Result
Dim Msg As String
Dim CR As String

    CR = Chr$(13)

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Ask the user if he or she wishes 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, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start the Customers form in data entry
       ' mode as a dialog form, passing the new company name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Customer form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "Customers", , , , acAdd, acDialog, NewData
    End If

    ' Look for the customer the user created in the Customers form.
    Result = DLookup("[CompanyName]", "Customers", _
             "[CompanyName]='" & NewData & "'")
    If IsNull(Result) Then
       ' If the customer was not created, set the Response argument
       ' to suppress an error message and undo changes.
       Response = acDataErrContinue
       ' Display a customized message.
       MsgBox "Please try again!"
    Else
       ' If the customer was created, set the Response argument to
       ' indicate that new data is being added.
       Response = acDataErrAdded
    End If
End Sub 

5. Save and close the Orders form.



6. Open the Customers form in Design view.



7. Set the OnLoad property of the form to the following event procedure:




Private Sub Form_Load ()
   If Not IsNull(Me.OpenArgs) Then
      ' If form's OpenArgs property has a value, assign the contents
      ' of OpenArgs to the CompanyName field. OpenArgs will contain
      ' a company name if this form is opened using the OpenForm
      ' method with an OpenArgs argument, as done in the Orders
      ' form's CustomerID_NotInList event procedure.
      Me![CompanyName] = Me.OpenArgs
   End If
End Sub 

8. Save and close the Customers form, and then open the Orders form in Form view.



9. Add a new order by typing ABC Distributors in the Bill To field. When the Customers form opens, enter ABCDI in the Customer ID field, and type anything that you like for the remaining customer information.

REFERENCES

For more information about the NotInList event, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "NotInList event" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the LimitToList property, click Microsoft Access Help on the Help menu, type "LimitToList property" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:

Keywords : kbdta AccCon KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto