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