How to create a Microsoft Access macro that opens/runs a query:
When creating databases for end users, the goal is to make the database
user-friendly and intuitive. This can be done with the use of macros
which are used to automate tasks within the database. This is handy
for both the administrator(s) and the end-user(s). It allows the administrator
the ability to "hand-off" the day-to-day operations of the
database to others. The end-users do not need to be programmatically
familiar with the database in order to use it. Macros and other features
within Access allow this type of flexibility.
This article provides step-by-step details regarding how to automate
the opening and running of a query through the creation of a macro.
By opening a query, it also runs it. At the conclusion of the "how-to"
will be a macro that will open a query based upon the administrator’s
desired outcome. Each time the macro is run, the query will run also.
For example, if the macro opens the query in design view, each time
the macro is run, the query will open in the "Design" view.
So, anyone that runs this macro will be able to change any design
aspect of the query such as the field names, the fields that appear,
etc.
How-to:
- Select the "Macros" option of the "Objects"
menu to open the macros view.
- Double-click "New" in the icon menu to create a new
macro.
- Go to the "Action" field and click the drop-down arrow.
- Select "OpenQuery" or type "OpenQuery" into
the field.
- Press the "F6" key to access the "Action Arguments"
panel or click in the "Query Name" field in the "Action
Arguments" panel.
- Type the name of the query or use the drop-down arrow to access
the desired query. Remember, by right-clicking, the "Zoom…"
option will appear. This will allow full viewing of the name of
the query.
- Click in the "View" field. This field gives the option
of opening the query using several views. Keep the end-user in mind
when selecting how the query will appear when the macro is run.
The available views are:
- Datasheet: when the query opens it looks like a spreadsheet
- Design: the query will open in the query design view. Changes
can be made to the query such as field names, etc.
- Print Preview: the query opens as it will appear if it is
to be printed, similar to the "Print Preview" mode
in other Microsoft Office applications.
- PivotTable: the query will open as a pivot table, similar
to the pivot tables that are created in Excel.
- PivotChart: the query will open as a graphical pivot chart,
again, similar to the pivot charts that are created in Excel.
- Click in the "Data Mode" field. Keep the end-user and
the purpose of the database in mind when selecting what will be
done with the data. There are three options available in the "Data
Mode" field:
- Add: the end-user will be able to add records to the query.
- Edit: the end-user will be able to edit the records returned
by the query and/or filter the data that is returned by the
query.
- Read Only: the end-user will be able to only view the returned
records.
- Select the "Data Mode" type depending on how the data
should be displayed to the end-user.
- Name and save the macro by clicking the disk icon in the upper
left-hand corner or by going to "File", "Save"
in the window’s menu.
In order to test the macro, press the exclamation point in the icon
menu. To step through each action of the macro, select the icon to
the right of the exclamation point (indented block paragraph with
the arrow).
Any type of query can be embedded into a macro. The "OpenQuery"
option will activate any query including action queries which are
primarily append, delete, make table, and update queries. It is also
possible to use the "OpenQuery" option as many times as
needed within a macro, to get to the desired outcome.