A @Rmacro@r is a set of one or more actions that automates a sequence of operations.
|
You can create Access 97 macros that automate the response of your database to certain events.
|
You can also define condition checking within a macro, so that different actions are performed depending on specific values.
|
Access provides various types of macro actions that can help you to automate tasks in your database.
|
For example, you can use macros to
ò open (or close) tables, queries, forms,
or reports in any available view
|
ò execute a select or action query
|
ò move, size, minimize, maximize, or
restore any window within the Access
workspace
|
Other functions of macros include
ò executing commands on any Access menus
|
ò displaying warning or information messages
|
ò sending data from a table, query, form, or
report to an output file, and then opening
that file in the appropriate application
|
With 49 macro actions to choose from in Access 97, the possibilities for macros are extensive.
|
Suppose you have a database called Product Info, in which you would like to keep a copy of the current database's Sales table.
|
You could create a simple macro that copies the table to the Product Info database.
|
Then, whenever you update the Sales table in the current database, you can easily copy the updated table to the other database using the macro.
@@
To create a macro in Access, you click the @UNew@u button on the Macros tabbed page in the Database window.
|
Alternatively, you can select @UMacro@u on the @UNew Object@u button's drop-down menu from any window in the database.
|
The top pane of the Macro window is where you define your new macro.
|
You start by selecting a macro action from the drop-down list in the Action column.
|
The bottom pane of the Macro window is for entering arguments (settings) for your chosen macro action.
|
The right-hand side of the Action Arguments pane displays a brief help message, which changes depending on where in the window the cursor is located.
|
You can press @UF6@u to move from one pane to the other.
|
In the Destination Database field, you type the full path of the database to which you want to copy the object.
|
When you use the @UTab@u key to move to the next field, the help message changes accordingly, telling you to enter a new name or leave the field blank to retain the current name.
|
The Source Object Type drop-down list allows you to choose the type of database object (in this case @UTable@u) you want to copy.
|
The Source Object Name drop-down now shows the names of all the objects of the selected type in the current database.
|
Remember, you can press @UF1@u at any stage to obtain help.
|
When you place the cursor in the Comment column, you are prompted to type an appropriate comment.
|
This is optional but can prove useful, as you may later need to distinguish this CopyObject macro action from a similar action in another macro.
|
Now you can select a second macro action from the drop-down list in the Action column.
|
You may want to choose @UMsgBox@u, so that you can set up a message box confirming that the first macro action has been carried out.
|
You can now tab to the Comment column and type a comment.
|
Then you switch panes, and type the text you want displayed in your message box in the Message field.
|
If you want a beep to sound when the message box is shown, you select @UYes@u from the drop-down list at the Beep field.
|
From the Type drop-down list, you choose the type of icon you want the message box to show.
|
Next you type the title you want to appear in the title bar of your message box.
|
You must save a macro before you can run it, so you choose @UFile - Save@u (or click the @USave@u button) to save it in the current database.
|
You can save a macro in another database by selecting @USave As/Export@u from the @UFile@u menu.
|
After entering a name for the macro in the Save As dialog box, you click @UOK@u.
@@
Access 97 provides a way for you to test your macro actions in single steps.
|
To activate single-stepping, you click the @USingle Step@u button on the Macro toolbar (or choose @URun - Single Step@u).
|
Now you click the @URun@u button (or select @URun - Run@u).
|
Access now opens the Macro Single Step dialog box before executing each step.
|
In this dialog box you will see the macro name, macro action, and action arguments associated with a particular step - in this case the first step.
|
You click the @UStep@u button to carry out the macro action named in the Action Name field.
|
If you click the @UContinue@u button, all the remaining steps in the macro will be carried out - here we do not wish to do that.
|
The next macro action is now shown in the Macro Single Step dialog box.
|
You could use the @UHalt@u button to prevent any further steps in the macro from being activated.
|
Here, let's run the next action - you click the @UStep@u button.
|
The message box you designed using the MsgBox macro action is now shown (and a beep sounds, as requested).
|
You click @UOK@u to remove the message box.
|
Finally you can click the @UClose Window@u button to return to the database window.
@@
Sometimes you may want to convert a macro you designed in Access to a VBA (Visual Basic for Applications) module.
|
To do this you choose @USave As/Export@u from the @UFile@u menu.
|
The top radio button is selected by default in the @USave As@u dialog box.
|
You select the @USave as Visual Basic@u @UModule@u radio button and click @UOK@u.
|
Then you click the @UConvert@u button.
|
The Modules tabbed page becomes active in the Database window, and a message box tells you that the conversion is finished - you click @UOK@u to remove the Macro to VBA Conversion dialog box.
|
When you click @UOK@u the converted macro is available on the Modules tabbed page.
|
You click @UDesign@u to open the VBA module.
|
Now you can see the converted macro in Visual Basic.
|
You click the @UClose Window@u button to return to the Database window.
@@
Usually, when you open a database in Access 97, the Database window is not usually maximized.
|
You can create a simple macro to maximize the Database window automatically when you open a file.
|
Access 97 runs a macro automatically (on opening the database) if you name the macro "AutoExec".
@@
Normally you would run a macro directly (for example, from the Macro window, or Database window) only to test it.
|
You can run a macro from the Database
window by selecting @UMacro - Run Macro@u
from the @UTools@u menu. (If the Macros
tabbed page is not active, or if no macro
is selected on the Macros tabbed page,
you will be prompted to choose the macro
you want to run.)
|
After that, you could create a custom menu command or toolbar button to run a macro.
|
You can also attach a macro to a form, report, or control so that it runs in response to an event.
|
An event is an action, such as a mouse click or key press, or the opening of your database.
|
Suppose you would like to add a command to the @UTools@u menu to run the macro "Copy Sales to Product Info".
|
First you choose @UView - Toolbars - Customize@u (or right-click the toolbar or menu bar, and click @UCustomize@u).
|
Now you click the @UCommands@u tab in the Customize dialog box.
|
You use the scroll bar in the Categories box to find the category of command you wish to add to the menu.
|
When you select @UAll Macros@u from the list, the macros in the database are displayed in the Commands box.
|
You select the macro you require, and drag it towards the @UTools@u menu.
|
When you drag the command over the @UTools@u menu, the usual menu commands appear - you point to where you want the command to be placed on the menu (where you see the black line).
|
When you release the mouse, the command is shown on the menu.
|
Finally, you click the @UClose@u button in the Customize dialog box.
|
Now you can run the macro from anywhere in the Database window, irrespective of which tabbed page is active.
|
When you point to the @UMacro@u option on the @UTools@u menu, the command you added (to run the macro) is available on the @UMacro@u submenu.
|
To remove the command from the menu, you would first open the Customize dialog box, then click the @UMacro@u option on the @UTools@u menu. You can then right-click the command you wish to remove, and select @UDelete@u from the shortcut menu.
|
You can follow a similar procedure to add a button for running a macro to a toolbar.
|
Suppose you would like to place a button on the Standard toolbar for running the macro "Copy Sales to Product Info".
|
With the toolbar to which you want to add the button showing, you open the Customize dialog box, and select the required command.
|
You then drag the command to the position (indicated by the black line) where you want to place the button on the toolbar.
|
When you release the mouse, the button is inserted on the toolbar, inside a heavy black outline.
|
You can click the @UModify Selection@u button to customize the button.
|
For example, you may want to change the icon shown on the button.
|
Then you click @UClose@u to return to the Database window.
@@
Another way that you can run a macro is by assigning an action (or set of actions) to a specific key combination.
|
Then Access carries out the action(s) when you press the key combination.
|
Suppose you would like to use a key combination to take you back five records in your Customers form.
|
You begin by using the @UNew Object@u button to create a new macro.
|
Next you click the @UMacro Names@u button (or select @UView - Macro Names@u).
|
This inserts a Macro Name column to the left of the Action column, allowing you to create a @Rgroup@r of named macros.
|
A macro group can contain a single named macro, but in general macro groups contain more than one macro.
|
You type @R%004^5@r in the Macro Name field to assign the key combination @UCtrl+5@u to the macro.
|
If you assign a set of actions to a key combination already used by Access (for example, @UCtrl+S@u is the key combination for Save), your assignment will override the Access key assignment.
|
Then you select the macro action you require - in this case @UGoToRecord@u.
|
When you have chosen the action arguments needed to go to the fifth previous record, you click the @USave@u button (or choose @UFile - Save@u).
|
In this case the required arguments are
ò Object Type - Form
ò Object Name - Customers
ò Record - Previous
ò Offset - 5
|
The name you enter in the Macro Name field will actually be the name of the macro group you are creating.
|
You must call the macro group AutoKeys in order to run the macro you named %004^5 using the key combination @UCtrl+5@u.
|
You click @UOK@u to save the macro group AutoKeys.
|
The new key assignment is ready to use, and you can click the @UClose Window@u button.
|
Now you can test the new key assignment on the Customers form.
|
When you press @UCtrl+5@u, the fifth previous record is shown.
@@
Suppose you wish to use the key combination @UCtrl+F@u to open the Customers form.
|
You can do this by adding a key combination called %004^F to an appropriate macro group.
