ACC: How to Add New Record to a Combo Box with a Double-Click

ID: Q88148

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes how to set up a macro so that you can add new values to a combo box. The article uses the Orders form in the sample database Northwind.mdb (or Nwind.mdb in earlier versions) as an example.

MORE INFORMATION

Follow these steps to modify the Orders form so that you can add new values to the Salesperson combo box:

Open the sample database Northwind.mdb.

Create the following new macro, and save it as Macro1:


    In Microsoft Access 97
    ----------------------

       Macro Name   Condition                   Action
       -----------------------------------------------------
       NewRecord                                OpenForm
       OnClose      [EmployeeID] Is Not Null    RunCommand
                    ...                         SelectObject
                    ...                         ReQuery
                    ...                         SetValue

       NewRecord Actions
       ---------------------------------------------------------
       OpenForm
          Form Name: Employees
          View: Form
          Data Mode: Add
          Window Mode: Normal

       OnClose Actions
       -------------------------------------------------------------------
       RunCommand
          Command: SaveRecord
       SelectObject
          Object Type: Form
          Object Name: Orders
          In Database Window: No
       ReQuery
          ControlName: EmployeeId
       SetValue
          Item: Forms!Orders![EmployeeID]
          Expression: Forms!Employees![EmployeeID]

    In Microsoft Access 7.0
    -----------------------

       Macro Name   Condition                   Action
       -----------------------------------------------------
       NewRecord                                OpenForm
       OnClose      [EmployeeID] Is Not Null    DoMenuItem
                    ...                         SelectObject
                    ...                         ReQuery
                    ...                         SetValue

       NewRecord Actions
       ---------------------------------------------------------
       OpenForm
          Form Name: Employees
          View: Form
          Data Mode: Add
          Window Mode: Normal

       OnClose Actions
       -------------------------------------------------------------------
       DoMenuItem
          Menu Bar: Form
          Menu Name: Records
          Command: Save Record
       SelectObject
          Object Type: Form
          Object Name: Orders
          In Database Window: No
       ReQuery
          ControlName: EmployeeId
       SetValue
          Item: Forms!Orders![EmployeeID]
          Expression: Forms!Employees![EmployeeID]

    In Microsoft Access 1.x and 2.0
    -------------------------------

       Macro Name   Condition                   Action
       -----------------------------------------------------
       NewRecord                                OpenForm
       OnClose      [Employee ID] Is Not Null   DoMenuItem
                    ...                         SelectObject
                    ...                         ReQuery
                    ...                         SetValue

       NewRecord Actions
       ---------------------------------------------------------
       OpenForm
          Form Name: Employees
          View: Form
          Data Mode: Add
          Window Mode: Normal

       OnClose Actions
       -------------------------------------------------------------------
       DoMenuItem
          Menu Bar: Form
          Menu Name: File
          Command: Save Record
       SelectObject
          Object Type: Form
          Object Name: Orders
          In Database Window: No
       ReQuery
          ControlName: Employee Id
       SetValue
          Item: Forms!Orders![Employee ID]
          Expression: Forms!Employees![Employee ID]

Change the properties of the EmployeeID (or Employee ID in versions 1.x and 2.0) combo box on the Orders form as follows:

OnDblClick: Macro1.NewRecord
StatusBarText: Double-click to Add a New Employee

Change the OnClose property of the Employees form as follows:

On Close: Macro1.OnClose

To see how this works, open the Orders form in Form view, and then double- click the Salesperson combo box. Note that the Employees form opens. After you add a new record, close the Employees form and the new employee's name appears on the Orders form.

Additional query words: doubleclick update record

Keywords : FmsEvnt
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto