Assign values to Access records automatically

by Mary Ann Richardson | Jan 23, 2007 8:00:00 AM

Tags: Microsoft Office, Databases, Mary Ann Richardson, Click New, new record, Microsoft Access, SetValue, Action Arguments, Employee Data Form, Dependents, Microsoft Office Suite Access Tips Newsletter

Takeaway: Use macros in Access to give your data a life of its own, complete with its own values and record assignments. Mary Ann Richardson explains how to add identity to your Access records.

You can save data entry time by creating macros that will automatically assign values entered in one Access form to create a new record in another form. For example, after creating a record for a new employee in the Employee Data table, suppose you need to create a new record for the employee in the Dependents table. Follow these steps:

1. Open the Employees database.
2. In the database window, click Macro under Objects.
3. Click New.
4. Click in the first action cell and enter OpenForm.
5. Under Action Arguments, click in the Form Name box and enter Employee Data Form.
6. Click in the Data Mode box and select Add from the drop-down list.
7. Click in the next Action cell and select SetValue.
8. Under Action Arguments, click in the Item box and enter [Forms]![Dependents]![EmployeeID].
9. Click in the Expression box and enter [Forms]![Employee Data]![EmployeeID].
10. Close and Save the macro as AssignValue.
11. Open the Employee Data form in Design View.
12. Right-click the form and select Properties.
13. In the Event tab, click in the AfterUpdate property box, and select AssignValue.

This macro will create and display the new record with the common information. You can add additional SetValue actions to fill in other fields in the new record. For example, you might want to create a SetValue action to add the Employee's first and last name to the Dependents record.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.