How to Change Access Field Data With a Macro

by Megan Corwin

Macros, small snippets of code, automate repetitive tasks in a "Microsoft Access" database and in other Microsoft Office applications. The use of macros enables an advanced user quickly create programming code quickly without having to write it from scratch.

Create an Update Query

Open the "Microsoft Access" database.

Click on the "Create" tab, and then on the "Query Design" icon.

Select "table" from the "Show Table" dialog box, and click "Add." After you have added the table to the query design, click "Close" to remove the "Show Table" dialog box.

Double-click the field you would like to update. The query design updates with the field name.

Click the "Update Query" icon, a black exclamation point with a pencil beside it. You will now see the row "Update To" in the query design.

Input the text or value to which you wish to update the field on the "Update To" line.

Close the query. A dialog box appears asking if you wish to save the changes to the query. Click "Yes."

Give the query a name when prompted, and then click "OK."

Automate the Query Using a Macro

Click the "Create" tab, and then on the "Macro" icon. A new macro window opens.

Select "Open Query" from the drop-down menu on the first line under the "Action" column.

Select the update query from the Query Name drop-down box in the Action Arguments area.

Close the macro. A dialog box will appear asking if you wish to save the changes to the macro. Click "Yes."

Give the macro a name when prompted, and then click "OK."

Run Macro to Update Field Data

Double-click the macro, which now shows in the database object window. You will receive an alert telling you that running the macro will update the data in your table.

Click "Yes" to continue.

Click "Yes" when the dialog box appears telling you the number of records included in the update.

Tip

  • check If you want to add other actions to the macro, use the next line under the "Open Query" command in the macro window.

Items you will need

About the Author

Megan Corwin has been a freelance writer since 2004. She enjoys sharing her knowledge about travel, do-it-yourself projects and small business. Corwin earned the title of "Effective Communicator" from the International Training in Communication organization. She holds a Bachelor of Science in psychology from The Ohio State University.