The concept of macro and macro. Control playback of macro commands Work order

All tips

In Microsoft Access, macros attached to user interface objects such as buttons, text fields, forms, and reports are called user interface macros. This allows you to distinguish them from data macros, which are attached to tables. Using UI macros, you can automate a number of actions, such as opening another object, applying a filter, running an export operation, and many other tasks. This article introduces the new Macro Builder and describes the basic tasks involved in creating a UI macro.

Note: This article does not apply to Access web apps.

General information

Macros can be contained in macro objects (sometimes called standalone macros) or embedded in event properties of forms, reports, and controls. Embedded macros become part of the object or control. Macro objects appear in the navigation pane in a group Macros; embedded macros are not indicated there.

Each macro consists of one or more macro commands. Depending on the context in which you are working, some macro commands may not be available.

Macro Builder

The main core functions of the Macro Builder are described below.

Macro directory Macros are organized by type and searchable.

IntelliSense. As you enter expressions, IntelliSense suggests possible values and lets you choose the one you want.

Keyboard shortcuts Use keyboard shortcuts to write macros faster and more conveniently.

Program flow Create more readable macros using comment lines and action groups.

Conditional statements Provides more complex logic execution with support for nested if and/or other functions.

Reusing a Macro The Macro Catalog displays other macros you've created, allowing you to copy them to the one you're working with.

Simplify sharing Copy the macro, and then paste it as an XML file into an email, newsgroup post, blog, or code sample.

This video provides a step-by-step guide to the main areas of the Macro Builder.

Creating a standalone macro

This procedure allows you to create a standalone macro object that will be displayed in the list Macros in the navigation area. It is useful to make a macro standalone if you want to reuse it in many parts of the application. By calling a macro from other macros, you can avoid duplicating code in multiple places.

Creating an embedded macro

This procedure allows you to create a macro that is embedded in an object's event property. Such macros do not appear in the navigation pane, but they can be called from events, for example or Button press.

Because the macro becomes part of the form or report object, embedded macros are recommended to automate tasks that are specific to a specific form or report.

Adding Macros to a Macro

Macro commands are individual commands that form a macro. The name of a macro reflects its purpose, for example FindRecord or CloseDatabase.

Step 1: View or search for macros

To add a macro, you first need to find it in the drop-down list Add a new macro or in the macro directory.

Notes:

Default in dropdown list Add a new macro and the macro directory displays only macros that run on untrusted databases. To view all macros:

On the tab Constructor in Group Show or hide select item Show all activities.

If the macro directory is not displayed, on the tab Constructor In chapter Show or hide click the button Macro directory.

You can find a macro in one of the following ways:

Click the drop-down arrow Add a new macro and scroll down the list to find the macro. Program execution controls are displayed first, followed by macro commands in alphabetical order.

Find the macro in the Macro Catalog area. Macros are grouped into categories. Expand categories to view macros. When you select a macro, a brief description of it is displayed at the bottom of the catalog.

Search for a macro by entering text in the Search box at the top of the Macro Catalog area. As you type, the list of macro commands is filtered and all macros that contain text are displayed. Access looks for entered text in macro names and descriptions.

Step 2: Adding a Macro to a Macro

Once you've found the macro you want, add it to your macro using one of the following methods:

Select a macro from the list Add a new macro or just start typing the name of the macro in the field. Access will add the macro where the list was displayed Add a new macro.

Drag a macro from the macro catalog to the macro area. An insertion bar appears showing where the macro will be inserted when you release the mouse button.

Double-click a macro in the Macro Catalog.

If you select a macro in the macro area, Access adds a new macro below the selected one.

If a block is selected in the macro area Group, If, Otherwise if, Otherwise or Nested macro, Access will add a new macro to it.

If there are no macros or blocks selected in the macro area, Access adds a new macro to the end of the macro.

Notes:
- If you have already created one or more macros, they are listed in the node In this database macro directory.
  - When you drag a standalone macro (which is listed in Macros) a macro command is created in the macro area Run Macro, which executes the dragged macro. You can then use the drop-down list to call nested macros, if any.
    
    If you just want to copy the macros from the standalone macro to the current one (instead of creating a macro Run Macro), right-click it in the macro directory and select the command Add a copy of the macro.
    
    When you drag an embedded macro (specified in a form or report object) onto the macro area, the macro commands from it are copied to the current macro.

Step 3: Entering Arguments

Most macros require at least one argument. To view a description of any of the arguments, highlight the macro, and then hover over the argument. For many arguments, you can select a value from a drop-down list. If you need to enter expressions for an argument, IntelliSense helps you do this by suggesting possible values as you type, as shown in the image below:

When the value you want to use appears, add it to the expression by double-clicking it or using the arrow keys to highlight it and then pressing Tab or Enter.

For more information about creating expressions, see Introduction to Using Expressions.

About using IntelliSense with Web Database Properties

When you create a UI macro embedded in a web-compatible form, IntelliSense allows you to add any form properties to the expression. However, in a web database, you can only access some form properties using UI macros. For example, if the form "Form1" has an "Item1" control, IntelliSense will allow you to add [Forms]![Form1]![Item1]. into an expression in a UI macro. But if you publish the database to Access Services, the macro containing the expression will throw an error when it runs on the server.

The table below lists the properties you can use in UI macros in web databases:

	Available Properties
	Caption, Dirty, AllowAdditions, AllowDeletions, AllowEdits
Set of tabs
	Caption, Visible, "Text Color", "Background Color"
The attachment	Visible, Enabled
	Caption, Visible, Enabled, "Text Color"
Text field	Enabled, Visible, Locked, "Text Color", "Background Color", Value

Image	Visible, "Background Color"
Combo box	Enabled, Visible, Locked, Value
	Enabled, Visible, Locked, Value
Web browser
Subform	Enabled, Visible, Locked
Navigation element	Enabled, Visible

Moving a Macro

Macro commands are executed in the order in which they are specified in the macro (top to bottom). To move an action up or down in a macro, use one of the following methods:

Drag the macro up or down to the desired location.

Select the macro, and then press Ctrl+Up Arrow or Ctrl+Down Arrow.

Select a macro and click the arrow Up or Down on the right side of the macro area.

Deleting a Macro

To delete a macro:

Highlight the macro and press DELETE. Alternatively, you can click the button Delete(X) on the right side of the macro area.

Notes:

If you delete a block of macro commands, for example a block If or Group, all macro commands in the block will also be deleted.

Teams Up, Down And Delete

Controlling program execution using "If", "Else If" and "Else" statements

If you want macros to be executed only when certain conditions are met, use a block If. It replaces the column Condition, which was used in previous versions of Access. You can add to the block If blocks Otherwise if or Otherwise, as in other sequential programming languages such as VBA.

The following figure shows a simple block If, including blocks Otherwise if And Otherwise:

Block If executed if the date in the ExpirationDate field is less than the current date.

Block Otherwise if executed if the date in the ExpirationDate field is equal to the current date.

Block Otherwise executed if none of the previous blocks executed.

That's it for this block If ends.

Adding an "If" Block to a Macro

Select operator If in the dropdown list Add a new macro or drag it from the Macro Directory area to the Macro area.

In the box at the top of the block If enter an expression that specifies when the block will be executed. The expression must be a Boolean (that is, its result must be "Yes" or "No").

Add macros to a block If Add a new macro in the block or by dragging them from the "Macro directory" area into the block If.

Adding "Else" and "Else If" blocks to an "If" block

Select block If and in its lower right corner click Add an "Else" block or Add an "Else if" block.

If you add a block Otherwise if, enter an expression that determines when the block will be executed. The expression must be a Boolean (that is, its result must be True or False).

Add macros to a block Otherwise if or Otherwise by selecting them from the drop-down list Add a new macro in the block or by dragging them from the "Macro directory" area into the block.

Notes:

Commands for adding blocks If, Otherwise if And Otherwise are also available in the context menu that appears when you right-click a macro.

At the blocks If There can be up to 10 levels of nesting.

Creating Nested Macros

Each macro can have several nested macros. A nested macro is called by name from macro commands Run Macro or OnError.

A nested macro is added to a macro in the same way as a macro command, as described in . After you add the nested macro, you can drag macros into it or select actions from the list Add a new macro, which is displayed in the block.

Notes:

You can also create a block Nested macro by selecting one or more macro commands, right-clicking them, and then choosing the command Make a nested macro block.

Nested macros should always be the last blocks in the macro; It is not possible to add any macros underneath them (except for other nested macros). If you run a macro that contains only sub-macros without specifying the sub-macro that you want, only the first sub-macro will be executed.

To call a nested macro (for example, in an event property or using macros Run Macro And OnError), use the following syntax:

macro_name.submacro_name

Grouping related macros

You can make your macros easier to understand by grouping your macro commands and giving the groups thoughtful names. For example, you could add macro actions that open and filter a form to a group named "Open and Filter a Form." This makes it easier to understand which macros are related to each other. Block Group has no effect on how macros are executed, and the group cannot be called or run individually. Its main purpose is to assign a label to a group of macro commands, which makes the macro more understandable. Additionally, when editing a large macro, you can collapse each group block to one line to reduce the use of scrolling.

If the macros you want to group are already in the macro, follow these steps to add them to the block Group:

Select the macros you want to group.

Right-click the selected macros and select Make a group block.

In the box at the top of the block Group enter a group name.

If macros are not added:

Drag the block Group from the macro directory to the macro area.

In the box at the top of the block Group enter a group name.

Drag macros from directory to block Group or select them from the list Add a new macro, which is output in the block.

Expanding and collapsing macros or blocks

When you create a macro, the Macro Designer displays the macro commands with all arguments. If the macro is large, editing it may require you to collapse some or all of the macros (and their blocks). This makes it easier to see the overall structure of the macro. You can expand the macros to change them if necessary.

Expand or collapse a single macro or block

Click the plus sign ( + ) or "minus" ( - ) to the left of the macro or block name. You can also select a macro or block by using the UP ARROW or DOWN ARROW key, and then press the LEFT ARROW or RIGHT ARROW key to collapse or expand it.

Expand or collapse all macros (but not blocks)

On the tab Constructor in Group Expand or collapse click Expand Macros or Collapse macros.

Expand or collapse all macros and blocks

On the tab Constructor in Group Expand or collapse click Expand all or Collapse all.

Advice: You can view a collapsed macro by hovering over it. The tooltip will show the macro's arguments.

Copying and pasting macros

If you want to repeat macro commands that you've already added to a macro, you can copy and paste them like paragraphs of text in a word processor. Macros are inserted under the selected macro. If a block is selected, macros are inserted into it.

Advice: To quickly duplicate selected macros, hold down the CTRL key and drag them to the location in the macro where you want to copy them.

Share a macro

If you copy macro commands to the clipboard, you can paste them as XML code into any application that allows you to enter text. This allows you to email a macro to a colleague or post it on a forum, blog, or other website. The recipient can then copy the XML and paste it into the Access 2010 Macro Designer, which will produce a macro exactly like yours.

Running a macro

You can run a macro using the following methods:

Double-click the macro in the navigation pane.

Call a macro using a macro command Run Macro or OnError.

Enter the name of the macro in the event property of the object. The macro will be executed when the event is triggered.

Debugging a Macro

If you are unable to run your macro, try the following methods to determine the source of the problem.

Adding Error Handling Macros to a Macro

It is recommended that you add error handling macros to every macro you create and do not remove them from it. When you use this method, Access displays a description of the errors that occur. Error descriptions help you understand what's wrong and fix the problem faster.

To add a nested macro for error handling:

Nested macro from the dropdown list Add a new macro.

In the field to the right of the word Nested macro enter the name of the nested macro, for example Error processing.

In the dropdown list Add a new macro, which is displayed in the block Nested macro, select macro Message Window.

In field Message enter the following text: =.

At the bottom of the macro, select OnError from the dropdown list Add a new macro.

For argument Go enter the value Macro name.

In field Macro name enter the name of the nested error handling macro (in this example - Error processing).

Drag a macro OnError to the very top of the macro.

The following figure shows a macro with a macro command OnError and nested macro Error processing.

Macro command OnError located at the top of the macro and calls the nested macro Error processing in case of error.

Nested macro Error processing only executed if it was called by a macro OnError, and displays an error message window.

Using the step command

Stepping is a macro debugging mode that can be used to execute a macro one macro at a time. After each macro command is executed, a dialog box is displayed with information about it and the codes of any error that occurred. However, since the Macro Step by Step dialog box does not display a description of the error, we recommend using the nested error handling macros method described in the previous section.

To switch to step-by-step mode:

Open the macro in the Designer.

On the tab Constructor in Group Service click the button Step by step.

Save and close the macro.

The next time you run the macro, a dialog box will open Step-by-step execution of a macro. It displays the following information about each macro:

macro name;

condition (for "If" blocks);

macro name;

arguments;

error number (number 0 means there is no error).

When executing macro commands step by step, you can use the following three buttons in the dialog box:

To view details about the next macro, click Step by step.

To stop all macros currently running, click Stop all macros. Step mode will still be in effect the next time you run the macro.

To exit step mode and continue running the macro, click Continue.

Notes:

If you press the button Step by step after the last macro command in the macro, step mode will still be applied the next time the macro is run.

To switch to step-by-step mode while the macro is running, press CTRL+BREAK.

To step into step mode at a specific point in a macro's execution, add a macro Step to the appropriate part of the macro.

Step mode is not available for web databases.

Converting a Macro to VBA Code

Macros provide a set of commands available in the Visual Basic for Applications (VBA) programming language. If you decide to have more functionality than macros can provide, you can easily convert a stand-alone macro object into VBA code and then use the extended set of functionality that VBA provides. However, keep in mind that VBA code will not run in the browser; VBA code added to a web database will only run if the database is open in Access.

Note: Embedded macros cannot be converted to VBA code.

To convert the macro to VBA code:

In the navigation pane, right-click the macro object and select Design.

On the tab Constructor in Group Service select team Convert Macros to Visual Basic.

In the dialog box Macro conversion specify whether you want to add error handling code and notes to the VBA module, and then click Convert.

Access confirms that the macro has been converted and opens the Visual Basic Editor. Double-click the converted macro in the project area to view and edit the module.

Note: This page has been automatically translated and may contain inaccuracies and grammatical errors. It is important to us that this article is useful to you. Was the information useful? For convenience also (in English).

Goal of the work: Explore the use of macros to automate the process of creating large documents.

Control questions for self-control when studying the topic:

What is a macro?
When does it make sense to create macros?
For what purposes are macros used?
In which MS Office applications can you create macros by automatically recording user-executed instructions?
How to quickly open a macro dialog?
What language is used when developing macros?
Where is the sequence of instructions recorded as a macro stored?
How can I run a macro?
What editor is used when editing/creating a macro?

Exercise:

Open any text file created in previous works.
Write a macro to set the font bold to 14 points for part of a text string. Name the macro bold14. When recording a macro, assign the combination CTRL+ SHIFT+F.
Select the message “Dear Mr...” in the letter and apply the created macro to it using a key combination.
Include the same macro in the menu item Service, placing it under the dividing line at the bottom ( Service "Settings"). View the Visual Basic macro text compiled by Word: Service "Macro" button "Edit" macro name.
Include the same macro in the UI, and based on your own drawing. To do this, use the command Change in the context menu on the created button.
Write a macro that allows you to copy a paragraph of text to the beginning of the current document (to do this, select the paragraph by pressing the F8 key three times). Assign a keyboard shortcut, an icon tool to the macro, and add it to the menu as you wish. Check all destination options.
Write a macro that restores an application window to its normal size. Ensure that a macro command is executed using a keyboard shortcut. Check the created command.
Write a macro to delete a word (by selecting it by pressing F8 twice), without placing it on the clipboard (DEL key). Associate it with an icon. Check and remove it. To do this, move it (in the mode Settings) using the mouse outside the UI.
Create a couple of macros to switch between a blue and white window background (a blue background reduces eye strain, but some objects are not always clearly visible). This setting is done with the commands Tools" Options" General tab " White text on a blue background checkbox.
Save the results to a file.

Guidelines:

Word allows you to automate many tasks by creating macros that implement a sequence of instructions in one command. Instructions may consist of commands, a sequence of keystrokes, or other actions (but not mouse manipulation). Instead of manually doing time-consuming and repetitive steps, you can create and run a single macro that will perform the task.

Macros are often used for the following purposes:

· to speed up frequently performed editing or formatting operations;

· to combine several commands, for example, to insert a table with specified dimensions and borders and a specified number of rows and columns;

· to simplify access to parameters in dialog boxes;

· to automate the processing of complex sequential actions in tasks.

Macros have a name and are equal to other Word commands.

You can create a macro in two ways:

· record the sequence of actions on a macro recorder;

· create a program in Visual Basic.

Before recording a macro, plan in advance the steps and commands it needs to execute. If an error is made during recording, all corrections will also be recorded in the macro. Unnecessary instructions can be removed later by editing the macro.

Try to predict all the messages that Word might display. For example, if the macro includes a command to close the document, then most likely Word will offer to save the changes made to the document. To avoid this message, save the document while recording before closing it.

If the macro calls the command Edit "Find/Replace", press the button More, and then select an option Everywhere on the list Direction. If the macro searches only forward or backward, when it reaches the end or beginning of the document, Word will pause the macro and prompt you to continue searching through the rest of the document.

Before using a macro recorded in another document, make sure that it is independent of the contents of that document.

If a particular macro is used particularly often, assign it a toolbar button (TB), menu command, or keyboard shortcut. This will allow you to quickly call a macro without opening a dialog box Macros.

The Macro Recorder allows you to quickly create macros with minimal effort. In this case, the macro is written as a sequence of instructions in the Visual Basic for Applications programming language. You can use the mouse to select commands and parameters. But actions performed in the document window using the mouse are not recorded. For example, you cannot use the mouse to move the cursor, copy or move objects, including by dragging. Use your keyboard to record these actions. Macro recording can be paused temporarily and then continued from where recording was stopped.

Record a macro.

· Double click on the REC indicator in the status bar;

· Service "Macro" Start recording. A command dialog box will appear on the screen.

· In the “Name” field, enter a unique name.

· Listed Macro available for select the template or document in which the macro will be stored.

· In the “Description” field it is useful to specify a description of the command and explanatory comments.

· If you don't want the macro to be assigned to a toolbar button, menu command, or keyboard shortcut, click OK to begin recording the macro.

In the “Assign” block, click on one of the buttons to assign to the macro (for later calling): PI buttons (Panels), keyboard shortcut (Keys). Select the macro to record from the list Teams and a) drag it onto the UI or menu or b) enter a keyboard shortcut in the field New keyboard shortcut and press the button Assign.

· Click the Close button to start recording the macro.

· Start recording a macro. All actions performed by the user are logged. Pause macro recording - button Pause(perform actions that should not be recorded) . To resume recording, press the button Resume recording.

· To end recording – button Stop recording.

Storing macros.

Macros are stored in templates or documents. By default, macros are saved in the Normal/dot template so that they are available to all Word documents. If the macro will only be used in one document, save it in the document. Individual macros in a document are stored in macro projects, which can be copied from one document to another. to copy, delete or rename a macro project: Service "Macro"Macros"Organizer.

The macro command is saved along with the document template in which it is recorded. To add a macro from another template, use the command File "Templates" Organizer button. Saving changes: File "Save all.

If the new macro is named after one of Word's built-in macros, the latter will be replaced.

For example, the FileClose macro is associated with the command File" Close. If you name the new macro FileClose, it will be associated with the Close command, and when you select the Close command, a new sequence of actions will be performed. To view a list of Word's built-in macros, select Service "Macro"Macros. On the list Macros from select Word commands.

Play a macro

There are several ways to play a macro:

Service "Macro"Macros"macro name" Execute.
Using a keyboard shortcut (if it was assigned during the macro recording process). The procedure for assigning a keyboard shortcut (if one has been assigned) is as follows:

· Service "Settings" Keyboard tab;

· V field "Categories" choose Macros" macro name. If keys have been assigned, their combination will be shown in the " Current keyboard shortcuts" If there is no assignment or if you wish, give a new one in the “ New keyboard shortcut» you should enter a new combination by simply pressing these keys;

Press the buttons in sequence Assign And Close.

III. Using PI (if the macro is associated with the PI button).

The procedure for assigning a tool as an icon can be performed with the command Service "Settings. After selecting the macro name, drag it with the mouse to its intended location in the icon menu. Existing tools are shifting. By calling the context menu on the macro button, you can give the button the desired appearance.

IV. Using a button field Macrobutton. Field Macrobutton allows you to run a macro directly from the document. Double-clicking this button runs the macro specified in the instructions for this field. The instructions look like this:

{Field name Macro name Instructions}

Inserting a field Macrobutton carried out by teams Insert "Field" category "Document Automation" field Macrobutton or keyboard shortcut.

By changing the order of commands in the palette list, you can also change the order in which the commands are executed. In addition, commands from one macro can be moved to others.

In order to change the order of commands, you need to “grab” the command line in the palette list and move it to a new position. The double line shows the team's position during movement.

Changing Macro Settings

When a macro is created, it receives a name and a color with which it is displayed in command button mode, as well as a key combination. If necessary, these parameters can be changed at any time using the command Action Options(Operation Options), which displays a dialog box identical to the New Action(New Operation), which is described in the next section.

Creating and recording macros

Creating a Macro ( action) is that during the execution of a particular sequence of commands, the Adobe Illustrator program writes them in the same order, including those parameters that were used by each specific command.

In this way, you can record any sequence of commands and use it in the future, reducing time and, most importantly, effort for performing routine operations, as well as creating “man-made” effects.

The development of such macro commands opens up wide opportunities for users to apply their creative powers. In terms of purpose, the result is close to plug-in modules, but it is easier to create and does not require special knowledge.

However, there are limitations: some commands and functions can only be included in macros using the notation ( recording). To include commands in an operation that cannot be written during recording, use the command Insert Menu Item(Insert menu item). For information on this, see the next section of this appendix.

Advice
One more very significant limitation should be noted. The success of executing a recorded macro command with another document and at another time largely depends on the parameters of the other document (color model, selected object, active layer, etc.), as well as on the current program settings (for example, fill and stroke colors and some others).

Since in order to record a sequence of commands (Fig. A1.3), they must first be executed, and erroneous actions are possible during execution, it is strongly recommended to experiment with copies (especially for important documents).

Advice
It should also be kept in mind that when using commands as macro elements Save As(Save As) or Save a Copy(Save a Copy) These commands do not require you to enter specific file names in the dialog boxes.

Rice. P1.3. An example of recording several actions in a macro command

Rice. P1.4. New Action Dialog Box

So, in order to create a macro using the recording method ( recording), you need to open the document you are editing, display the palette Actions(Operations), select a set of macro commands in it and perform one of two actions:

In both cases, a dialog box will be displayed. New Action(New operation) (Fig. A1.4), in which you can assign a name to the macro command (field Name(Name)), define a set of macro commands (list Set(Dial)) and the key combination to launch it (field Function Key(Function key)), select one of seven colors to display the macro command line in the palette (list Color(Color)).

Then you should press the button Begin Record(Record), after which the dot on this button will turn red.

You can now begin executing the commands that you want to save in the macro. If the command has a dialog box, then after setting the required values, click the button OK. If you intend to make changes to the settings while executing the macro, you can leave the default values, but even in this case you should definitely click the button OK. When exiting a dialog box by clicking a button Cancel(Cancel) This command is not written to the macro.

Stop recording Occurs when a button is pressed Stop Playing/Recording(Stop Executing/Recording) at the bottom of the palette Actions(Operations) (first from left).

The Actions palette is used to record, perform and edit a sequence of actions for image processing (such a sequence of actions, named and launched with one command, is usually called a MACRO COMMAND). In order to display the palette on the screen, you must execute the Actions command of the Window menu ( Window).

The Actions palette contains a set of commands in the form of lines that expand when you click on the triangular arrow pointing to the right. Each command opens a list of command parameters, if any.

In the left column there are fields that serve to enable or disable individual commands, and next to them fields for enabling or disabling the display of dialog boxes for the corresponding commands.

The following buttons are located at the bottom of the palette:
Stop Playing/Recording;
Record;
Play;
New Set;
New Action;
Trash (Trash).

However, the palette can have another form - in the form of command buttons. To present the palette in this form, you need to open the list of palette commands and select the Button Mode item. To return to the palette list mode, you must execute the same command again.

CHANGING THE ORDER OF COMMANDS IN A MACRO COMMAND

The user has the opportunity to modify the sequence of command execution. In addition, commands from one macro can be moved to others.

In order to change the order of commands, you need to grab the command in the palette list and move it to a new position. The thick line represents the position of the command line during movement.

CREATING AND RECORDING MACRO COMMANDS

Creating a macro command - in the slang of designers and graphics developers - an "action" is that while executing a particular sequence of commands, the Adobe Photoshop program writes them in the same order, including those parameters that were used by each specific command. Thus, you can write down any sequence of commands and use it in the future, reducing time and, most importantly, effort for performing routine operations.

The development of such macro-commands - "actions" - opens up wide opportunities for users to apply their creative powers. In terms of purpose, the result is close to the Plugin module, but it is easier to create and does not require special knowledge.

The macro program can use the actions of the Gradient, Marquee, Crop, Polygon LassoLine, Move, Magic Wand, PaintBucket and Type tools, as well as settings of the Paths and Channels palettes (Channels), Layers (Layers) and History (History).

However, there are also limitations: some commands and functions can only be included in macros using a procedure for recording the actions performed. To include commands in an operation that cannot be defined during recording, use the Insert Menu Item command.

It is interesting that the Play command from the palette menu and the Batch command from the File/Automate menu can be placed in a macro command.

One more very significant limitation should be noted. The success of running a recorded macro with another document and at another time largely depends on the parameters of the other document (color model, resolution, active layer, etc.), as well as on the current program settings (for example, main and background colors and some others).

For example, using a Gaussian Blur filter with a value of 3 pixels on a document with a screen resolution (72 dpi) is not at all the same as applying it to a high-resolution image (300 dpi). Therefore, macros created in this way can largely be of a purely situational nature. But this can also save a lot of time, especially when it comes to a large amount of similar image processing.

Since you need to execute a sequence of commands to record them, and since you can accidentally make a mistake, it is strongly recommended that you experiment with copies of especially important documents.

Also note that if you use the Save As or Save a Copy command macros as elements, you do not need to enter specific file names in those command dialog boxes.

GO!

So, in order to create a macro command using the recording method, you need to open the document you are editing, display the Actions palette, select a set of macro commands in it and perform one of two actions: - click on the New Action button; - use the command New Action palette menu.

In both cases, the New Action dialog box will be displayed, in which you can assign a name to the macro (Name field), define a set of macro commands (Set drop-down list) and the key combination to run it (Function field Key), and also select one of seven colors to display the macro name in the palette (Color list).

Then click on the Record button, after which the dot on the record button in the Actions palette will turn red.

You can now begin executing the commands that you want to save in the macro. If the command has a dialog box, then after setting the desired values, click on the OK button. If you intend to make changes to settings while running macros, then you can leave the default values, but in this case, clicking on the OK button is mandatory. When you exit the dialog box after clicking the Cancel button, this command is not recorded in the macro. Recording is stopped by clicking the Stop button at the bottom of the Actions palette.

INSERTING MENU ITEMS

Many commands that are not written at runtime (for example, drawing tool options, view commands, and some others) can be included in the list of macros using the Insert Menu Item command of the palette menu.

The difference between such inclusion and recording is that in this case the command as such is not executed (before the macro starts running), and therefore, no parameters of the command are fixed, and it goes without saying that no effect is exerted on the current document. This is a pure programming operation. When a macro is executed for such a command, a corresponding dialog box opens in which the user must define his own parameters or accept the default parameters and click on the OK button.

In order to insert a menu item into a macro command, you need to determine the insertion location in the existing macro command: select the macro name in the palette list, execute the Insert Menu Item command, which will display the dialog box of the same name, and then open and select the command, AFTER WHICH a new command will be inserted.

After clicking OK, a new command appears in the list of macros.

INSERTING STOPS

While running a macro, it often becomes necessary to interrupt the action for some time in order to carry out an action that cannot be recorded, for example, selecting a fragment of an image or making a stroke with one of the drawing tools. The macro continues to work by clicking on the Play button in bottom of the palette. In order not to forget about the purpose of the pause (stop), it is recommended to save for yourself and your descendants... a short FAQ in the Message (FAQ) field.

To insert a stop into the macro list, you must do one of the following:
determine the place to insert a stop in an existing macro command: select the macro name in the palette list, open and select the command after which the stop will be inserted;
execute the Insert Stop command from the palette menu, which displays the Record Stop dialog box; the parameters for this command are also displayed in the Actions palette.

In the Message (FAQ) field you can enter arbitrary text that will be displayed when the macro is stopped. When you enable the Allow Continue check box, a Continue button appears in the message dialog box.

EXECUTION OF MACRO COMMANDS

You can perform a sequence of macro actions from any command and in any combination (albeit in the same sequence).

In addition, it is possible to execute only one separate command. If necessary, any command can be excluded from execution. To exclude a command from execution, you must click on the left column, next to the command name, removing the “tick” means excluding the command. Clicking again returns the command to "executable".

If the command expects a dialog box, it may be displayed to set or change parameters. If the dialog box display is disabled, the program will use the current dialog box settings (and the dialog box itself will not be displayed). To determine which dialog box is displayed when the macro runs, you must click in the column next to the command name. If a symbolic image of a dialog box is displayed in this cell, the dialog box will be displayed, and vice versa.

APPLYING A MACRO COMMAND TO A SEPARATE DOCUMENT

The sequence of steps for applying macros to an individual document (as opposed to batch processing) could be as follows:
Open the required document.
In order to execute a macro command completely, you need to select the name of the macro, and if you need to execute the macro command not from the beginning, you should open and select the command with which to begin execution.
To run a macro, you can use the Play button at the bottom of the palette or the Play command in the palette menu.

Double-clicking the macro name in the palette displays the Actions Options dialog box, which is identical to the New Action dialog box. If you double-click while the key is pressed, the macro begins execution from beginning to end.

Keep in mind that because a macro is a sequence of commands (although at a minimum it can consist of one command), you cannot undo the entire macro using the undo command. The undo command only allows you to go back one step. ... alas, but this is so..

However, you can cancel the action of the macro if you use the capabilities of the History palette: take a “snapshot” of the image before executing the macro and then “return” to it.

ACTION SPEED

Complex action often requires debugging, but in some cases it is not so easy to determine where the failure occurs due to the execution of specific commands too quickly. If a debugging step is required, you can use the Playback Options command from the palette menu. A dialog box of the same name is displayed on the screen, in which you can determine the speed of macro execution.

It contains a number of parameters:
The Accelerated switch, enabled by default, provides normal speed, which is quite acceptable for a properly running macro program.
The Step by Step switch ensures that the macro is executed step by step, drawing the image after each individual command. This mode requires quite intense attention from the user in order to have time to record incorrect actions.
The Pause For ... seconds switch allows the user to determine the degree of his attention and enter the delay time in seconds between execution of commands.
The Pause For Audio Annotation checkbox allows you to pause to listen to an audio annotation.

EDITING MACRO COMMANDS

Recording macro commands can be supplemented with their subsequent editing, for example, you can change the order of commands, add new commands, repeat commands, change command parameters, and delete commands.

Directly in the Actions panel, you can move any macro command to another set. To do this, you need to grab the macro line and drag it into the desired set. In the same way, you can change the order of commands in the macro.

To record an additional command, you need to select the command after which you need to insert, and then, by clicking on the Record button at the bottom of the palette, execute the desired command or sequence of commands. Clicking the Stop button will stop recording.

The ability to repeat commands and macros in the Actions palette is very useful for editing the macros themselves and creating new macros based on them.

You can repeat a command or macro in one of the following ways:
When the button is pressed, you can grab and drag the desired command or macro to a new position in the palette list;
you can select a command or macro and execute the Duplicate command from the palette menu;
You can drag the name of a command or macro onto the New Action button at the bottom of the palette. To highlight multiple commands, you must use the or keys.

To change command parameters, simply double-click on the command line and open the corresponding dialog box.

At any time, you can delete an entire macro command or an arbitrary individual command included in a macro command. To do this, in the list of macro commands, select the desired (more precisely, unnecessary) macro command or command and click on the Trash button at the bottom of the palette or execute the Delete command ) palette menu. Both actions will display a window requesting deletion, and after some thought you can click on the OK button.

You can delete a command or macro without prompting by clicking the Trash button while holding down the key.

After LONGER THINKING, you can decide to delete all macro commands. To do this, you must execute the Clear Actions command in the palette menu and confirm your decision. If all user-created actions become redundant, you can get rid of them using the Reset Actions command in the palette menu. Clicking the Append button in the dialog box that opens will add a set of macro commands supplied with the program to the panel.

Since you cannot endlessly add new macros, the program provides the ability to create sets of macro commands and save them on disk in a special file with the .atn extension.

This allows you to more efficiently organize work on different projects or different types of images, as well as transfer them to other users to ensure identical image processing. You can only save entire sets of macro commands; individual macros or commands cannot be saved (unless they form a set); new macros are automatically saved in the Actions Palette.psp file (in the WindowsApplication DataAdobePhotoshopCS2Adobe Photoshop Settings folder). When you delete this file (in order to restore the original settings of the program), the set of macro commands will also be deleted, so it is quite reasonable to save the macros in an independent file in advance.

In order to create a new set of macro commands, you must execute the New Set command from the palette menu or click on the Create New Set button located at the bottom of the Actions panel. In both cases, a dialog box appears on the screen in which you need to enter the name of the set or accept the one proposed by the program.

The name of the set can be changed at any time by calling the same dialog box with the Set Options command. To save a set of macro commands, just select the required set and execute the Save Actions command from the palette menu. The Save dialog box will be displayed, allowing you to name the set of macros (or leave the current one) and specify a storage folder.

To replace the current set of macro commands with a previously saved one, you must execute the Replace Actions command from the palette menu. To load a new set of macro commands, which will be added to the end of the list of current macro commands, you must execute the Load Actions command from the palette menu. In both cases, The screen displays the Load dialog box, in which you can select a file with the .atn extension.
On a note..

The most popular way to add “actions” to Photoshop starting from version 7 and higher among designers is very simple; from any Windows window, a file with the .ATN extension is DRAGGED into the Photoshop window, to an empty space. It is immediately “ready to eat.” Fast and convenient.

The main set of Microsoft Access tools, which we discussed in previous chapters of the book, is aimed at users who do not know programming languages. For programmers, these tools have been supplemented with macros(small programs in the Access macro language) and modules(procedures in Visual Basic for Application, VBA). With their help, you can significantly expand the functionality of the application you create and customize it to the needs of specific users. In this chapter we will get acquainted with one of these tools - macros and one of the Access programming languages - the macro language. We will introduce you to modules and the VBA language V Ch. 13 ,

So, we will discuss the following questions:

Macro concept
Using macros in applications -
Creating Macros and Macro Groups
Setting conditions to control the macro execution process
Different ways to run macros
Tips for Debugging Macros
Processing events in forms and reports using macros

Using macros in a Microsoft Access database

Using macros, you can perform almost all actions on Access objects that were described in previous chapters.

A macro in Access is a structure consisting of one or more macro commands that are executed either sequentially or in an order specified by certain conditions. The set of macro commands in Access is very wide; with the help of macros you can implement much of what procedures in VBA can do. Each macro has a specific name and possibly one or more arguments that are specified by the user. For example, when using the OpenForm macro command, you must specify as arguments at least the name of the form to open and the mode for displaying it on the screen.

In table 11.1 contains a list of Microsoft Access 2002 macro commands, grouped into categories: working with data in forms and reports; execution of commands, macros, procedures and queries; working with objects; import/export of data and objects and others. The “Other” category contains all macro commands that allow you to influence the application interface.

Category	Purpose	Macro command
Working with data in forms and reports	Data selection	ApplyFilter
	Navigating through data	NextRecord (FindNext) FindRecord (FindRecord) TOControl (GoToControl) ToPage (GoToPage) ToRecord (GoToRecord)
	Refresh data or screen	Requery ShowAllRecords
Performance	Executing a command	Run Command (RunComraand)
	Execute a macro, procedure, or query	RunMacro (RunMacro) Zalusk Programs (RunCode) OpenQuery (OpenQuery) 3anyck 3anpoca SQL (RunSQL)
	Running another application	Running an Application (RunApp)
	Abort execution	Cancel Event (CancelEvent) Stop AllMacros (StopAllMacros) StopMacro (StopMacro)
	Quit Microsoft Access	Quit
Import Export	Transferring Microsoft Access objects to other applications	Output In Format (OutputTo) SendObject (SendObject)
	Data format conversion	Transfer Database TransferSQLDat abaseTransfer SpreadsheetTransferText
Working with objects	Copying, renaming and saving an object	CopyObject Copy Database File Rename Save
Others	Show or hide a built-in or custom toolbar. Play a sound.	Toolbar (ShowToolbar) Signal (Fan)

Table 11.1. Microsoft Access 2002 Macro Commands

As you can see from this table, the actions that can be performed using macros are very diverse. Even if you don't use macros in your applications, it's worth learning about these macros because they can also be used in VBA procedures, as we'll show later. (see chapter 13).

Compared to the previous version of Access, Microsoft Access 2002 introduced three new macro commands:

TransferSQLDatabase - transfers a Microsoft SQL Server 7.0 and higher database to another server;

CopyDatabaseFile - copies the current Microsoft SQL Server 7.0 and higher database attached to the Access project;

OpenFunction - Executes a user-defined function in an Access project. This function is a query that, using input parameters, returns results in the same way as a stored procedure.

The use of macros is justified by the fact that they are easy to create and do not require learning the syntax of a programming language. As we'll show in the next section, creating a macro requires only basic knowledge of Microsoft Access and Windows techniques, such as dragging objects out of a window Database(Database) in a special window - Macro constructor(Macro Design), selecting an action from a list and entering expressions as arguments to the macro. Therefore, if you really don’t want to learn the syntax of the VBA language or it seems too difficult, feel free to use macros and you will get a fairly functional application.

As mentioned above, the main purpose of macros is to create a convenient application interface: so that forms and reports open when you click buttons in the form or on the toolbar, or by simply selecting a menu command; so that when opening the application the user sees on the screen not a window Database(Database), filled with many tables, queries, forms and reports, but some understandable form with which you could immediately perform the desired actions, etc.

Using macros, you can create convenient menus and toolbars for your application, and we'll describe how to do this a little later.

However, the use of macros also has some disadvantages, which are worth mentioning here.

The capabilities of macro commands are limited compared to the capabilities of the VBA language, so in some cases programming in VBA is not necessary, although you must first be sure that these additional capabilities are really needed. VBA gives you more power to work with data, allowing you to use object programming to communicate with other applications, call functions from Windows dynamic load libraries (DLLs), and create your own custom functions.
Macros can be used almost anywhere that VBA procedures are used, but VBA procedures tend to run faster.
Macros are objects that exist separately from the forms and reports in which they are used, so when there are a lot of these objects, maintaining them is quite labor-intensive. VBA event procedures are an integral part of forms and reports, and this has its benefits. For example, when you migrate forms and reports from one database to another, the associated procedures are automatically migrated with them.

However, as already noted, in many cases the use of macros is quite justified, since it significantly simplifies and speeds up application development. Macros can be used to create small single-user applications, although when creating multi-user or client-server applications where the speed of the application is critical, it is better to use VBA.

Creating Macros

Microsoft Access has a special tool for creating macros - Macro Design. Let's look at the process of creating a macro using the Constructor.

Macro constructor

Open window Macro constructor(Macro Design) there are two ways to create a new macro:

Expand the list of macros in the window Database(Database) by left clicking on the shortcut Macros(Macros), and press the button Create(New) at the top of the window (Fig. 11.1).
Click the down arrow on the button New object(New Object) on the toolbar Database(Database) and select the element Macro(Macro).

The Macro Constructor window is built similarly to the Table Constructor window, i.e., it is divided horizontally into two parts: the descriptions panel and the arguments panel.

The upper part of the Designer window - the descriptions panel - consists of several columns. By default, this panel displays two columns: Action and Comments. The description panel allows you to define the sequence of macro commands that make up the macro.

The row in the Action column is a combo box from which you can select the desired macro action.

The line in the Comments column is a regular text field in which you can enter a comment describing the action being performed.

When the Action field is filled in, an argument bar appears at the bottom of the Macro Builder window, allowing you to enter argument values for the corresponding macro action. The list of fields in this panel depends on the selected macro and may not appear if the macro has no arguments. This way, when creating macros, you don't have to remember the list of arguments for each macro.

In Fig. Figure 11.1 illustrates the selection of the ApplyFilter macro command. The arguments panel has two fields that allow you to set the filter name and the condition for selecting records. To set a condition for selecting records, you can use the Expression Builder (See “Creating Expressions Using the Expression Builder” section, Chapter 4).

Rice. 11.1. Macro Builder and Arguments Panel

This macro can be used in a form or report to limit the number of records displayed.

Comment

To quickly switch between the description and argument panels, just as when working with tables and queries in Design mode, you can use the key .

There is another very simple way to enter a macro. Let's say you need to create a macro that should open the Customers form in the Northwind application. To do this in the window Database(Database) you need to click on the label Forms(Forms), then using the toolbar (method 2) open the window Macro constructor(Macro Design) and drag the “Customers” form with the mouse into the “Macro” (Action) field of the Macro Designer window.

Advice

To make it easier to drag objects from the Database window onto the form, select the Window, Tile Vertically command to arrange the windows side by side on the screen.

Then the OpenForm macro command will automatically appear in the Action field (Fig. 11.2), and the fields in the arguments panel will be automatically filled in: in the Form Name field the name of the form “Customers” will appear, in the “Mode” field (View) - the default value is Form, in the “Window Mode” field - the value Normal(Normal). The remaining fields will remain empty. In the same macro, we can set a filter for selecting records. To do this, enter in the “Filter Name” field the name of a filter, for example “Sales Managers”, which we built when studying queries (see section “Creating a request from a filter” in Chapter 4).

In addition, you can make entries in this form unavailable for changes, for which purpose in the field Data Mode(Data Mode) value must be entered Only reading(Read Only).

Rice. 11.2. Automatically create macros by dragging objects

Similarly, you can create macro commands by dragging from the window Database(Database) tables, queries, reports and other macros. In this case, the corresponding macro commands will appear in the Macro Constructor window: OpenTable (OpenTable), OpenQuery (OpenQuery), OpenReport (OpenReport), RunMacro (RunMacro).

To enter arguments to a macro, you most often need to select values from lists or enter expressions. To enter expressions, you can use the Expression Builder, the button of which is located to the right of the argument field. Another Expression Builder button is located on the toolbar. As in other cases, you must precede the expression with an equal sign (=). The exceptions are the argument Expression(Expression) macro command SetValue (Setvalue) and argument Number of repetitions(Repeat Count) macro commands - RunMacro. If you enter an equal sign before an expression specifying the value of these arguments, the expression will be evaluated twice, which may lead to undesirable results.

If you need to specify the name of a database object as an argument to a macro, you can either enter it using the keyboard, select it from a drop-down list, or specify the name of the object by dragging it from the database window.

So, the macro we created will open the “Customers” form in Read Only mode and show only sales managers in it. As mentioned earlier, a macro can contain a sequence of several macro commands. Therefore, in the same macro, in the next line, we will select the Message macro (MsgBox), which, after opening the form, will display a dialog box with a message about which clients are represented in the form. To do this, enter in the field Message(Message) in the argument panel the text of the message, for example The filter is set to Sales Managers, the remaining fields can be left unchanged. Argument Signal(Fan) determines whether the message output will be accompanied by a sound signal. Argument Type(Type) determines the appearance of the message dialog box. Argument Heading(Title) allows you to set the text that will be displayed in the title of the message window.

If the database is large, selecting records in the form may take some time, so it is recommended to display an hourglass cursor to indicate that data is being processed. In order to do this, we will use the Hourglass macro command. Macro commands in a macro are executed in the sequence in which they are written in the “Macro” (Action) column, i.e. from top to bottom. The Hourglass macro command should be placed first, since the cursor must change before the form is displayed. So we need to insert a new row in the description panel. To do this, place the cursor in the first line of the panel and press the button Add lines(Insert Rows) on the toolbar or just a key . A blank line will appear. In this line, in the “Macro” (Action) field, select the Hourglass macro command. The Hourglass On field in the Arguments panel will now display the default value Yes, which is what we want. To return the mouse pointer to its original appearance, at the end of the created macro you need to enter the same Hourglass macro command, but with the No argument value Turn on(Hourglass On).

Strictly speaking, it is better to place this macro command before the macro command for displaying a message on the screen. This is easy to do, since macro commands can be easily swapped using the mouse. To swap the last two macro commands, select the last line by left-clicking on the line selection area (on the left in the Design window) and drag the line up. The horizontal line shows the location of the new line position. Release the mouse button when this line is above the second to last line.

Now all that remains is to save the created macro. To do this, you can select from the menu File(File) command Save as(Save As) or press the key . The Save Macro window will appear. Enter the name of the macro in the input field, following the rules for naming Access objects, for example, Sales Managers.

Comment

Microsoft Word and Excel have tools for automatically recording macros. These tools are very useful for learning VBA because they allow you to automatically create a procedure by enabling macro recording, performing the necessary actions, and stopping the recording. Then all user actions performed after the recording is enabled and before it is stopped are converted into VBA procedure text. This text can then be viewed and edited using a VBA editor. In Microsoft Access, there are no tools for automatically recording macros, just as there are no tools for automatically generating VBA code (except for what wizards create).

Creating Macro Groups

When developing an application using macros, the number of macros can be very large. Therefore, it is important to organize access to the necessary macros well. To do this in one object Macro(Macro) You can combine several macros. For example, it is recommended that all macros associated with events in a form or report be combined into a separate object corresponding to this form or report. In order to do this, each group macro must have its own name, and the object name Macro(Macro) will be the name of the macro group.

To create a macro group:

Open the Macro Creator using one of the methods described in the previous section.
Click the button Macro names(Macro Name) on the toolbar. Another column will appear in the description panel in the Designer window - “Macro Name” (Fig. 11.3).
In this column, enter the name of the first macro. It is better to leave the remaining fields of this line empty - this will make it easier to move and copy macro commands.
Starting on the next line, enter all the macro commands in the macro and the corresponding arguments for each macro command.
Skip one line.
Repeat steps 3-5 for each macro.

Rice. 11.3. Creating a Macro Group

In Fig. 11.3 The “Employees” macro of the database (Northwind) is presented in the Designer window. To indicate the end of a macro, a separate macro is not required. Access continues executing a macro that is part of a group until it encounters a line containing a name in the Macro Name column or until it reaches the last line of the macro window.

When processing a macro, empty lines are ignored, so they are useful for separating macros in a group. This technique, as well as entering comments for complex macros consisting of a large number of macros, improves the readability of macros.

To run one of the group's macros, use the full name to reference the macro. The full name of the macro is formed as follows: Groupname.Macroname.

Comment

You can configure the system so that the column Macro name(Macro Name) was always displayed in the Macro Constructor. To do this, you need to set the appropriate parameters on the tab View(View) in the dialog box Options(Options) (see section “Configuring Access application options”, Chapter 14).

Applying conditions in macros

The macro examples we gave earlier used a sequential order of macro command execution. However, as with any program, the order in which macro commands are executed in a macro can be changed. To do this, conditions for executing or skipping macro commands are introduced. These conditions are specified as expressions in a special column that appears in the Macro Designer window if the button is clicked Conditions(Conditions) on the toolbar (Fig. 11.4).

Comment

As with macro groups, you can configure system settings so that this column always appears in the Macro Designer.

Rice. 11.4. Applying conditions in macros

In Fig. Figure 11.4 shows the Designer window for the “Customer Label Dialog” macro group, used to print a report in the form of labels on envelopes. Moreover, you can print stickers for all clients at once or for clients of one country selected from the list. The “Condition” column is visible in the window. If the condition specified in this column is true, the macro in that row is executed. If the condition is false, the corresponding macro is skipped and the next one is executed.

If you need to execute several macro commands at once when a condition is true, then for all macro commands except the first, an ellipsis (...) is placed in the “Condition” column. The condition to be checked is written on the line of the first macro. Then, if it is true, the entire set of macro commands is executed from this condition to the next macro command with the given condition, to the next macro, or to the end of the macro. If the condition is false, all commands marked with an ellipsis are skipped, including the macro command with the specified condition. Next, the macro command following the skipped ones is executed, which contains a new condition or the “Condition” field is not filled in. Thus, unlike “most programming languages, there is no alternative branching in macros. To create a macro with two alternative branches, you must first enter a condition and define the macros that are executed when that condition is met. Immediately after them, you must specify the opposite condition and define macro commands executed in the alternative branch. Then you can enter macro commands that are executed unconditionally. This branching method is used, for example, in the “Preview” macro, which is associated with a button View(Preview) in this form. If the user selected the option for all countries(AN Countries), which is determined by the value of 1 control Printing Stickers(PrintLabelFor), then the report opens in Preview mode and the form closes. If the radio button is selected for a specific country(Specific Country)" and the country is not selected (the value of the "SelectCountry" combo box is not set), then a message is displayed, the focus is set to the country selection combo box, and the macro stops executing. If a country is specified, the report for the selected country is displayed and the form is closed.

This macro uses the StopMacro macro command. It allows you to stop executing a macro, and all macro commands following the StopMacro macro command will not be executed.

Comment

It makes sense to use the StopMacro macro command only if it is followed by other macro commands that should not be executed under some condition. As mentioned above, no special macro is required to indicate the end of a macro.

Running and Debugging Macros

There are many ways to run macros. Among them there are direct methods, when the macro is launched directly by the user. These methods are commonly used when testing macros. When the macro is debugged, create a special menu command or button on the toolbar, or associate the macro with some event in the form or report. In this section we will look at all possible ways to run a macro.

A universal way to run a macro is to use the menu command (Tools, Macro, Run Macro) (Fig. 11.5).

In the dialog box that appears when you select this command, you need to specify (enter or select from the list) the full name of the macro, i.e. Group name. Macro name (Fig. 11.6). This method allows you to execute a macro from any active window, be it a macro window, a database window, or, for example, the Form Designer.

Rice. 11.5.

Rice. 11.6. Dialog window Running a macro

Running a macro from the Macro Designer window

This method is used to test a newly created or corrected macro. If there is only one macro in the Macro Constructor window, then to run it you just need to click the button Launch(Run) on the toolbar or select the command Launch, Step by step(Run, Single Step) (the latter method allows you to debug the macro). However, if there are several macros, then using this button or command you can run only the first macro in the group. To run any other macro from the group, you need to use the menu command Service, Macro, Run macro(Tools, Macro, Run Macro).

If you often use this method of running a macro, it makes sense to customize the toolbar by adding a standard button to it Launch(Run Macro). It is equivalent to executing the menu command Service, Macro, Run macro(Tools, Macro, Run Macro). (For customizing toolbars, see section “Creating and modifying toolbars,” Chapter 14.)

Running a Macro from the Database Window

To run a macro from a window Database(Database), click on the label Macros(Macros) in the object panel, find the desired macro in the list and double-click on it with the mouse button or click the button Launch(Run) on the window toolbar Database. The last method is only suitable if the selected object contains one macro. Otherwise, you need to use the universal method of executing a macro described above.

Comment

If macro conditions or arguments contain references to Access objects - forms, reports, etc. - they must be opened before running the macro, otherwise error messages will be generated.

Running a macro using a button on the toolbar

Direct methods of running macros are simple, but not the fastest. There are more convenient and faster ways. From our point of view, the most convenient way to launch a macro is to create a special button on the toolbar. True, there are many standard toolbars in Access, and corresponding panels appear on the screen in different operating modes. Therefore, the most sensible thing to do is to create a special toolbar on which you can place all the necessary buttons. However, the operation of adding a custom button to a standard toolbar is extremely simple.

To add a button to the standard Access toolbar:

Right-click on the toolbar and select the command from the context menu Settings(Customize).
Settings(Customize) expand the tab Toolbars(Toolbars).
Check the box for one of the standard toolbars, for example Query in Table View(Query Datasheet) so that it appears on the screen.
Close the dialog box Settings(Customize) by clicking the button Close(Close).
Click on the shortcut Macros Database(Database) and find the macro you need.
Drag the macro with your mouse onto the toolbar Request in mode tables (Query Datasheet).

Rice. 11.7. Creating a toolbar button to run a macro

Comment

If the main mode of operation in your application is working with forms, buttons for executing macros can be placed on the toolbar Form Mode(Form View).

To remove a toolbar to which you just added a button from the screen, right-click in the toolbars area and in the list of standard command bars, uncheck the box to the left of the panel name (Figure 11.8).

Rice. 11.8. Context menu with a list of command bars

Now let's look at creating a special toolbar and placing a macro call button on it.

Click on the shortcut Macros(Macros) on the object panel in the window Database(Database) and select the desired macro.
Select the command (Tools, Macro, Create Toolbar from Macro). An empty toolbar appears on the screen.
Drag the desired macro onto this panel using your mouse.

A button with the already familiar macro icon appears on the panel. The button gets the same name as the name of the macro for which it was created.

If you need to create a toolbar to call several macros, we recommend combining these macros into one group (creating a macro group was described in the previous section). Then when executing the menu command Tools, Macro, Create toolbar from macro(Tools, Macro, Create Toolbar from Macro) a toolbar will be created, on which each macro in the group will have its own button. In this case, the names of the corresponding macros are displayed on the buttons. The new toolbar is given the same name as the macro group name. Try running this command against one of the macro groups in the Northwind database, such as Customers. If desired, you can replace the text on the buttons with icons. Additional information about customizing application toolbars, and, in particular, about removing buttons from panels and removing the panels themselves, changing the appearance of these buttons, is presented in

Running a macro using a menu command

Another common way to run a macro is to run it using a menu command specially created for this purpose. The methods for creating menu commands to run macros are very similar to those for creating toolbar buttons. For example, you can use the menu command for this Service, Macro, Create menu from macro(Tools, Macro, Create Menu from Macro).

Creating and configuring custom menus is discussed in more detail in section “Creating and modifying command panels”ch. 14. Therefore, in this section we will not consider creating a menu command from a macro.

Run a macro using a keyboard shortcut

You can assign a keyboard shortcut to run a macro. To do this, you need to create a special group of macros - “AutoKeys”. This macro group must, for each key combination you assign, contain a macro command to run the corresponding macro. An example of the “AutoK”eys macro group is given in the Northwind database. Open this macro group in Design mode. It contains one macro (Fig. 11.9). The macro name is a record of the keyboard shortcut +

The macro itself consists of one macro command, RunMacro, which runs the macro “Customer Phones. Print" (Customer Phone List.Print). The "AutoKeys" macro is viewed every time the user enters special key combinations, e.g. +

If the entered key combination is found in "AutoKeys", then the corresponding macro is run.

Rice. 11.9. Example of the AutoKeys macro

This method of running a macro is certainly the fastest, but it has a significant drawback: there are very few available key combinations. And although the combinations defined in "AutoKeys" have higher priority than the standard ones (for example, +- copy), replacing the standard assignment of key combinations is not recommended. Allowed key combinations are given in table. 11.2. Key combinations in this case they are not used, since they are used to launch menu commands and press buttons.

Table 11.2. Allowed key combinations

Run a macro when opening a database

When you open a Microsoft Access database, certain actions may occur. Most often, this is opening a special form, the so-called Main Button Form, displaying special menus or toolbars, hiding standard menus, etc. A dialog box is used to determine these actions Launch parameters(Startup). However, sometimes you need to perform a more complex set of actions when starting an application than what this dialog box allows you to specify. For example, you might want to open some forms in advance (without showing them on the screen) so that they don't take too long to display later, check some conditions, or prompt you to enter some data. All this can be done using a special macro called “AutoExec”. When you open the database, Access checks for this macro and, if it exists, executes it. When creating an "AutoExec" macro, remember that Access will first perform the actions defined in the window Launch parameters(Startup) and then the "AutoExec" macro, so they should not have conflicting actions.

Running a macro from another macro

Sometimes you need to call a macro from another macro. This can be done using the RunMacro macro command. We have already encountered this macro when describing the AutoKeys macro. It should be noted here that this macro command has three arguments: in addition to the macro name, the number of repetitions of the macro command and the repetition condition are specified. Thus, this macro allows you to organize loops. Argument Number of repetitions(Repeat Count) specifies the number of macro calls. Argument Repeat condition A Repeat Expression is an expression that can be true or false. Before executing the RunMacro macro, the value of this expression is checked. If it is True, then the macro is executed, if False, then the macro is not executed and control is transferred to the next macro command. If these two arguments are not given, the macro is executed only once. If both of these arguments are specified, the call loop ends when the macro has been executed the specified number of times, or when the specified condition fails and evaluates to False.

Comment

The macro name in the argument of the RunMacro macro command must be fully qualified, i.e. it must have the form Groupname. Macroname, even if the macro being called is in the same group as the caller. If the repeat condition is set in such a way that it is always true, then the loop will be endless. You can interrupt it using a keyboard shortcut +. If this does not help, you will have to interrupt Access by pressing the key combination ++.

Assigning a Macro to an Event

The most common use of macros in an Access application is to handle events. Event - it is any action that is recognized by an object, and the object's reaction to an event can be determined. Events occur as a result of user actions, execution of VBA statements, or are generated by the system. An example of events is displaying a form, a report, entering data into a text field, or pressing a mouse button or key. Each of these events can be assigned a macro or VBA procedure that will be automatically executed in response to the event. Almost all programming in Access comes down to writing macros or procedures that process events, that is, programming the reaction of objects to events. There are a large number of different kinds of events to which objects react, and often not one, but a whole sequence of events occurs. Therefore, it takes some skill to decide which event to assign the macro or VBA procedure you create to. All the subtleties of this choice will be discussed in Sect. “Programming in Forms and Reports” Ch. 13 . Here we will describe only the method of assigning a macro to an event and give examples of processing events using macros.

Let's start with the simplest. Let’s open the “Customers” form in the “Northwind” database. This form displays information about the client. Let's say we would like to see not only this information, but also data about the products purchased by the client. It would be nice to create a button Customer orders, when clicked, the “Orders” form would appear with orders from only the customer currently selected in the “Customers” form. In order to get what we want, let's create a macro that will be executed when the event occurs Button press(On Click) in the “Customers” form.

To create a macro that defines the described application response to an event Button press(OnClick):

Open the Customers form in Design mode.
Create a button in the title area of the form. In this case, the wizard button on the toolbar must be released, because otherwise you will be asked to create an event procedure, not a macro.
Open the properties window of the newly created button, if it is not already open, and expand the tab Events(Event).
Notice how many different events are associated with just the command button. In addition to the usual click, which we will now use, the set of button events includes gaining and losing focus, double-clicking the mouse button, simply moving the mouse pointer over the button, etc. This variety of events gives the developer great opportunities to create a convenient user interface. Now find the event in the list Button press(On Click) and place the cursor in the appropriate cell. This is a combo box and is currently empty. If you open this list, then its first element will be (), and then there is a list of all macros that exist in the application (Fig. 11.10).
Since the macro we need is not in the list, let's create it. To do this, click the Builder button located to the right of the field. A window will appear Builder(Choose Builder), which asks you to choose one of three Builders: Expressions(Expression Builder) Macros(Macro Builder) and Programs(Code Builder) (Fig. 11.11).
Select Macros(Macro Builder) and click the button OK. A macro window and a dialog box will open in which you need to enter the name of the macro to be created. Enter the name Sales Orders.

Rice. 11.10. Button Properties Dialog Box

Rice. 11.11. Dialog window Transition Sequence

You need to add a single macro command, OpenForm, to the macro. The values of the arguments of this macro are given in Table. 11.Z.

Table 11.3. Macro Argument Values OpenForm

Argument Mode(View) defines the mode in which the form should be opened. It can take the following values: Form(Form), Constructor(Design), View(Print Preview), Table(Datasheet) Pivot table(PivotTable) and Pivot chart(Pivot Chart). Argument Selection condition(Condition) defines the condition for selecting records displayed in the form. The condition is an expression. In this case, this expression contains a reference to the control Client code(CustomerlD) in the “Customers” form, which allows you to select all records from the “Orders” table in which the customer ID is equal to the value specified in the “CustomerID” field of the “Customers” form. (For more information on using references in macros and expressions, see the “Using Macros” section of this chapter.) Argument Data Mode(Data Mode) determines the method of working with data and can take one of the following values: Addition(Add) Change(Edit) or Only reading(Read Only). And finally, the argument Window mode(Window Mode) determines the window type: Normal(Normal) Invisible(Hidden) Icon(Icon) and Dialog window(Dialog).

Close the macro window and save your changes. In the properties window of the button in the field Button press(On Click) the macro name “Customer Orders” will appear.
Expand the Layout tab (Format) and enter in the field Signature(Caption) button name: Customer orders. It is recommended to enter the same name in the field Name(Name) tab Other(Other).

Now all that remains is to switch to Forms mode and check how the macro processes the event Button press(On Click). If you made no mistake anywhere, you should see a picture on the screen similar to the one shown in Fig. 11.12.

Rice. 11.12. Form "Orders"

However, we have not completed all the necessary steps yet. If you move to the next record in the “Customers” form, the data in the “Orders” form will no longer reflect the situation correctly - it does not change. You need to make sure that this data changes synchronously with the transition to other records in the “Customers” form, or that this window simply closes. Let's consider the implementation of the second option. The Orders form opens when we click the button Customer orders, and becomes active. You need to make it close when the “Customers” form becomes active. To do this, you need to select the appropriate event and set a procedure or macro for processing it. If you open the window Properties(Properties) form “Customers” and expand the tab Events(Event), then among the many events you will see an event Inclusion(On Activate). This is exactly the event we need. Let's select this event, but now we will not create a new macro group, but will add a new macro to the "Customers" macro group, which already contains macros for the "Customers" form. Select this macro group from the drop-down list and click the Builder button. A window will open showing two macros. Let's add one more macro to them. Let's call it “Closing” and enter the Close macro command with the appropriate parameters (Table 11.4). But before closing the form, you need to check whether it is open. To do this, we will use the isLoaded function, which returns True if the form is open, and False otherwise. You need to pass the name of the form as an argument to the isLoaded function. So, in the column Condition(Condition), opposite the Close macro command, enter IsLoaded (“Orders”) (or IsLoaded (“Orders”)).

Argument	Meaning
Object Type	Forms
Object Name	Orders
Save	No

Table 11.4. Close macro argument values

Argument Preservation Save allows you to determine whether Access displays a dialog box when closing a form to confirm that the changed data is saved. Since we opened the Orders form in read-only mode, changing the data is prohibited, so this argument must be assigned a value No(No). The finished macro is shown in Fig. 11.13.

Close the macro window and save your changes. Change the name of the macro assigned to the event Inclusion(On Activate). To do this, select from the list or enter the name Customers.Close and press the key . Now save the form, go to Forms mode and check how the macros you created work.

Rice. 11.13. Macro for closing a form

Calling a Macro from a VBA Procedure

As we have already said, VBA procedures and macros in Access are similar objects and are often interchangeable, that is, you can use a VBA procedure instead of a macro and vice versa. There are ways to run a macro from a VBA procedure, run a VBA procedure from a macro, and Access allows you to convert a macro to a VBA procedure. This transformation is described in section "Converting Macros to VBA Procedures" in this chapter.

To run a VBA procedure from a macro, there is a special macro command RunProgram(KipCode). This macro takes one argument, the name of the procedure to call, although only a function can be called, not a subroutine.

To run a macro from a VBA procedure, use the special RunMacro method of the DoCmd object, for example:

DoCmd.RunMacro "Macro!"

The DoCmd object is used in a VBA procedure to execute Access macros. In this case, the English name of the required macro must be specified as a Method of the DoCmd object, for example, a procedure line

DoCmd.OpenForm "Clients"

allows you to open the “Clients” form. Here “Clients” is the macro argument. Arguments are listed in the DoCmd clause, separated by commas. Most macro commands can be executed in this way.

We will look at creating VBA procedures below, in Ch. 13.

Debugging macros and finding errors

Typically, to debug a macro, it is performed in step-by-step mode. To enable this mode, you must press the button Step by step(Single Step) on the toolbar in the Macro Designer window or select the menu command Launch, Step by step(Run, Single Step). Then, before executing each macro command, a dialog box (Macro Single Step) will appear (Fig. 11.14).

This window displays the name of the macro, the name of the macro command being executed, its execution condition, and arguments. At the same time, in the fields Condition(Condition) and Arguments(Arguments) the values of the conditional expression and the arguments of the macro are visible. If a condition is not specified on the macro line, the Condition field will always show the value True(True). The buttons to the right of the fields in this window allow you to execute the next macro command, interrupt the execution of the macro (for example, if it is clear that the arguments have been calculated incorrectly) and continue execution as usual, that is, not step by step.

Rice. 11.14. Dialog window Step-by-step execution of a macro

If an error occurs while running a macro, a dialog box appears Macro execution error(Action Failed), similar to the window Step-by-step execution of a macro(Macro Single Step), but without the ability to continue the process (buttons Step(Step) and Continue(Continue) are not available). You can only interrupt the execution of a macro by pressing the button Abort(Halt). The window displays a macro containing an error.

Finding errors in macros, as a rule, does not cause much difficulty. This is due to the fact that:

most syntax errors are checked as you enter and the system simply will not allow you to enter the wrong name of a macro command or conditional expression (however, it will not protect against entering the wrong name of an object, for example, a non-existent form or a control in a form);
macros are usually quite simple and do not contain long sequences of macro commands, otherwise it is better to write the procedure in VBA, since the VBA language provides more features and has significantly better debugging tools.

The main errors that occur in macros are either because object names are entered incorrectly so that Access cannot find the corresponding object, or because the objects referenced by the macro are not accessible, such as the form is not open. Therefore, when an error occurs, you first need to check whether the object names are entered correctly and whether the forms used in the conditions or arguments of macro commands are open.

Advice

Before using references to a form or control, it is recommended that you check whether the form is open by using the isLoaded() function. Unfortunately, this function is not a standard Access feature, but is usually included in the Utility Function module of the Northwind database. You can copy it from there to your database.

A standard technique for debugging both procedures and macros is to display special messages on the screen indicating the passage of certain stages of the process. In macros, this is done by inserting additional Message Output (MsgBox) macro commands to display such messages. Moreover, in the message you can display the values of the properties of the control elements, for example: “Product price = ” & Forms!Products!Price

You can easily disable the display of debug messages by entering the value False in the “Condition” column opposite the corresponding macro commands.

Another standard technique is setting breakpoints. It is used to stop the execution of a macro at a certain point until the process completes. To do this, use the StopMacro macro command. The macro will be executed until the StopMacro macro command is used, after which you can check the results. In this way, it is possible to localize the error with precision down to the macroinstruction.

Using Macros

Macros can be used to solve a wide variety of problems. In the first section of this chapter, we provided a table of macro commands grouped by category. Now let's look at the use of macro commands in some categories.

Working with data in forms and reports

This category includes a set of macro commands that allow you to select data, navigate through data, and update data in forms. Before we move on to examples of using macros, we'll show you how to use links to forms, reports, and controls in the arguments and conditions of macros, since such links are used very often.

Links to forms, reports and their properties

To link to a form or report, you must first determine which collection the object you are linking to belongs to. (For more information about families and their constituent objects, see Chapter 13) All open forms are included in the Forms family, and open reports are included in the Reports family. A full link to a form or report must consist of two parts: FamilyName!ObjectName. Moreover, if the object name contains spaces or special characters, then it must be enclosed in square brackets. If spaces are not used in the name, the parentheses can be omitted. Thus, the link to the form will look like this: Forms![Customer Orders] OR Forms!Customers For the report, the links look similar:

Reports! [Sales Report] OR Reports!Price List A form or report property reference consists of three parts: Familyname!Objectname.Propertyname For example:

Forms!Customers.Visible or Reports![Sales for the period].MenuBar Property Output on display(Visible) determines whether the form will be visible on the screen or hidden, and the property Menu(MenuBar) allows you to associate a special menu with a report or form.

Links to form and report controls and their properties

To create a reference to a control or its property, you must specify its name. If the name contains spaces, it is enclosed in square brackets. A reference to a control on a form or report consists of three parts:

FamilyName!ObjectName!ElementName

Forms![Customer Orders]![Order Number]

Reports![Sales for the period]![Amount]

Family name!Object name!Element name.Property name

For example:

Forms![Customer Orders]![Order Number].Enabled

Property Access(Enabled) allows you to deny or allow access to the control.

An object can have a default property. This property is used when the property name is not explicitly specified in the reference. For example, the default property for controls is Meaning(Value), so the Forms link! Goods! Price allows you to access the value displayed in the Price text box.

Links to subforms and reports

You can reference a subform or report just like any other control because subforms and subreports are both types of controls. For example:

Forms!Orders!SubformProducts

Here, SubformProducts is the name of the control on the Orders form, which is a subform.

But the link to a control element in a subform or report has a special structure: after the name of the control element, which is a subform, you must first specify a special property: Form - for forms or Report - for reports, and then the name of the control element on which the link:

Forms!Orders!ProductsSubform.Form![Product Code]

Filtering records in forms, reports, tables

Let's move on to examples of using Access macro commands. To select records in forms, reports, and tables, use the ApplyFilter macro command. You can set a filter in two ways: either by specifying the name of a pre-created filter in the argument Filter name(Filter Name), or by directly specifying the selection condition in the argument Selection condition(Where Condition). If the filter needs to be applied immediately when opening the form, then with the event Opening(On Open) of the form, you must associate a macro containing the ApplyFilter macro command. If you need to change the set of displayed records in an open form dynamically, proceed as follows:

create a set of fields in this form in which you can set selection conditions;
create a button Apply filter, to which a macro containing the ApplyFilter macro command is associated. As an argument value Selection condition(Where Condition) for this macro specifies an expression containing references to these fields.

An example of such a solution is shown in Fig. 11.15. This figure shows the “Products” form, which allows you to view products with a selection based on various criteria.

This form is built on the basis of the “Products” table using the Automatic Ribbon Form Generation Wizard. Then, in Form Builder mode, fields for defining selection criteria and buttons for applying and canceling the filter are added to it. Relevant macros associated with the event Button press(On Click), shown in Fig. 11.16. To cancel the filter, use the ShowAllRecords macro command.

Rice. 11.15. Form for selecting records based on specified criteria

Rice. 11.16. Macros for setting and canceling a filter in the “Products” form

In Fig. 11.16 presents two macros: SetFilter and CancelFilter. In the dialog box Input area(Zoom) displays the value of the argument Selection condition(Where Condition) macro commands ApplyFilter. Please note that the condition turned out to be quite complex, and the field length Selection condition(Where Condition) is limited to 255 characters. Therefore, if we included another field in the selection conditions, for example, “Supplies stopped,” we would have to separately create a special filter and specify its name in the argument Filter name(Filter Name).

In the “CancelFilter” macro, in addition to the ShowAllRecords macro command, two more macro commands are needed to clear the “Supplier Selection” and “Type Selection” fields, i.e. assign them a value Empty(Null). This is done using the SetValue macro command.

Navigating through data

This group of macros deals with navigating through records and controls. As an example of using macro commands of this group, consider a situation where a user is working with the “Customers” form and wants to find the order of the current customer, whose number is known to him. Let's go back to the example given in section “Assigning a macro to an event.” Then we created a button in the “Clients” form Customer orders to display the orders of the selected customer. Let's try to improve these forms. If the order number is known to the user, he can enter it in the text field Order search, which can be added to the Customers form. Then in the “Orders” form that opens, you should immediately show a record containing the corresponding invoice. Let's add the "Order Search" field to the form header and make sure that when the "Orders" form is opened, the value of this field is checked and, if it is not empty, the invoice with the specified number is searched and the transition to the corresponding record is performed. Otherwise, the first record in the filtered set of records would become current. In Fig. Figure 11.17 shows the Customers form with a new field and the Order Search macro, which is associated with the On Load event of the Orders form.

Because the added event macro references a control on the Customers form, it should only be executed if that form is open, and furthermore, the search should only be performed when the Order Search field is not empty . Based on this, macro conditions are formed. Before searching for a record by pattern in one of the fields, you must activate this field, for which you use the macro command KElementuControl (GoToControl). With its help, focus is set on the “Order Code” field. But this field is not available in the Orders form, so you need to change the property value first Access(Enabled) of this field. Otherwise, executing the GoToControl macro will result in an error.

The macro command "GoToControl" is used, as a rule, if you need to change the standard order of transition between fields in the form. Typically, changing between fields is done using the key<Таb>, however, sometimes you want to skip a number of fields and move focus to a specific control. This may depend on some conditions, such as the value of a field. In this case, for the event After update(After Update) a macro is assigned to this control, which moves the focus to the desired control on the form. The short name of the control must be specified as an argument to the macro. If the name is too long, navigation to the control will not occur and an error message will be displayed.

Rice. 11.17. Example of a macro for moving through data

Updating data in forms and controls

The last group of macro commands in this category is related to updating data in active forms, tables, and queries. If several people on the network simultaneously change data, then the forms and tables for a particular user may not reflect the latest data. To ensure that the displayed data matches the current state of the database, it must be updated using the command Posts, Update(Record, Refresh) (see section Working with records, Chapter 2).

A similar situation occurs even in single-user mode if the form uses a combo box whose data source is a table or query. If records were added to the source table, they will not automatically appear in the combo box - you need to run the query again. In addition to combo boxes, controls that require display data to be updated include list and subform controls, OLE objects, and calculated controls that contain aggregate functions on a subset of records, such as DLookUp() or DSum().

To update records in forms, tables, or controls, use the Requery, ShowAllRecords, and RepaintObject macro actions.

The Requery macro update the data in a database object by revisiting the data source. The macro has one argument containing the name of the object to update. If you are updating an active object, such as a form, then the argument field should be left blank. In this case, the macro will re-execute the request specified in the property Data source(RecordSource) of this form.

Let's look at an example of using a macro to update data. In the “Customers” form there is a “Country” combo box. The data source for this field is a query that selects values from the Country field of the Customers table:

SELECT DISTINCT Clients.Country FROM Clients;

If a new country name is added to the table when entering a customer, then this country will not appear in the list, since the request will be repeated only the next time the form is opened. To update the list of countries earlier, you should assign an event After update(After Update) macro form Clients. Updating the list of countries, which consists of one macro command Update (Requery) with the value of the argument “Country” (Fig. 11.18).

Rice. 11.18. Assigning a Macro to a Form Event After update

Comment

The Requery macro only updates one object, so if there are controls on a form that require display data to be updated, and the macro assigned to that form uses the Requery macro with an empty argument value, only the records in the form will be updated. For each such control element, you need to use a separate macro command. If this macro contains the name of a control as an argument, then when it is executed, only that element is updated. Records in the form itself are not updated.

Using the Requery macro command, you can update data in an inactive form, or rather, not in the form to which the macro is assigned. However, in this case, you must first execute the SelectObject macro command, which will move the focus to the desired form in order to activate it (then the focus can be returned back).

The ShowAllRecords macro command, as already noted, cancels the filter and re-examines the record source. It is often used to update data in a subform.

The RepaintObject macro applies only to a database object (table, query, form, report, page, macro, and module) and does not apply to a control. It immediately updates the specified open object (if no object name is specified, the active object is updated), although it does not re-run the query against the data source. Updating an object has no effect. displaying new and deleted records, as happens when executing the Requery macro command. Typically you use the RepaintObject macro to display the results of changing data using the SetValue macros, and to recalculate the values of expressions in calculated controls.

Working with objects

Let's first look at how to use the SetValue macro action, which lets you set property values for controls on forms and reports. We have already used this macro in previous examples.

Setting the properties of controls allows you to dynamically make these controls unavailable or invisible, depending on conditions. Here are some examples.

You can create one form in which, depending on certain conditions, different fields will be visible. This allows you to use one form in several cases without having to create an additional form for each situation. When there are a lot of forms in an application, this feature becomes very useful. To make a control invisible, set the value Lie(False) for property Output on display(Visible) of this element. To show the control on screen, set this property to True(True).
Depending on your current state of working with your data, you can change the availability of action buttons or other controls on your form. To make the control unavailable, set the value Lie(False) to its Access property (Enabled). To make the control available, set the value True(True) for this property.
You can prevent the user from changing data in a form. For this property Access(Enabled) corresponding fields should be set to Lie(False), and the property Blocking records(Locked) - value True(True). If you want to disable changes in all fields, set the value Lie(False) for the following form properties: Allow changes(Allow Edits) Allow adding(Allow Additions) Allow deletion(Allow Deletions). These properties can be changed dynamically, that is, while the user is working with the form. For example, you can allow or disable data editing after checking user rights.
Using the SetValue macro action, you can dynamically change the value of a property Record Source(RecordSource) for the form, which allows you to control the content of the displayed data.

There are macro commands for opening and closing Access objects:

OpenForm, OpenQuery, OpenReport, etc. To open an object of each type, a separate macro command is used, and to close an object, the Close macro command common to objects of all types is used. The type of object to which this macro is to be applied is specified as one of its arguments. The previous examples already used the OpenForm and Close macro commands for the active object.

The OpenQuery macro command allows you to execute any type of query, including a request to change data. If the argument of this macro is the name of a select query or cross-query, the result of executing the macro is to display the selected records. If the argument is the name of a request to change data, then the macro will execute the request, changing the data in the tables accordingly.

When you run a query that changes data, warning messages will appear on the screen. To turn off these messages, use the SetWarnings macro action with the argument value No(No). Just don’t forget to turn on the output of system messages again after executing the request, using the same macro command, but with the Yes argument. Otherwise, Access will not display any system messages, which may cause the application to perform unwanted actions.

The OpenView macro is similar to the OpenQuery macro, but it is used in Access 2000 projects and is designed to work with data stored on the server.

The OpenStoreProcedure macro command allows you to execute or open a server stored procedure in edit mode .

Not all macro commands are described in this section. For complete information, use the Access help system. To quickly get help:

Select a team Help, Microsift Help Access (Help, Microsoft Access Help).
Expand the tab Answer Wizard(Answer Wizard) and enter in the field Choose an action(What would you like to do?) name of the macro. Click the button Find(Search).

Converting Macros to VBA Procedures

If you've created a lot of macros and then decide to switch to VBA programming, you don't have to worry about having to rewrite all the macros you created in VBA again. Access has the ability to automatically convert macros into VBA procedures. This applies to both macros that are associated with events on forms and reports, which are converted into event procedures, and general macros that are not associated with forms and reports. All of them are converted into separate standard modules.

Let's try to convert the macros assigned to the events of the Customers Labels Dialog form into event handling procedures:

Open the Customers Labels Dialog form in Design mode.
Select a team Tools, Macro, Convert Form Macros(Tools, Macro, Convert form’s Macros to Visual Basic).
In the dialog box Converting a Form Macro(Convert form macros) (Fig. 11.19) click the button Convert(Convert), leaving the checkboxes selected by default.

Rice. 11.19. Converting a Macro to a VBA Procedure

When the conversion is complete, in the Customers Labels Dialog form, right-click e.g. Cancel, and select a team Properties(Properties).
In the dialog box Properties(Properties) expand the tab Events(Events).
In the event field Button press(On Click) turned out to be set value [Event Handling Procedure](). Click on the Builder button located to the right of the field - a VBA editor window will open with the text of the procedure (Fig. 11.20).

Rice. 11.20. Procedure derived from a macro

This macro-derived procedure uses a DoCmd object to execute a macro to close a form. According to the checkboxes selected in the window Macro conversion(Convert Macro), comments in a macro have been converted to comments in a procedure and error handling code has been added that displays an error message when an error occurs (see chapter 13).

Macros assigned to report events are converted in the same way.

Now let's consider converting a macro that is not associated with a form or report event into a VBA procedure, for example, the “Sales Managers” macro we created earlier.

Click on the shortcut Macros(Macros) in the Northwind database window. A list of macros will appear. Select the “Sales Managers” macro.
Select a team Tools, Macro, Convert Macros(Tools, Macro, Convert Macros to Visual Basic).
The same dialog box will open as in the previous example (see Fig. 11.19), in which you need to click the button Convert(Convert). After the conversion process is completed, a message indicating the successful completion of the conversion process will appear and a VBA editor window will open, where you can view the resulting VBA code (Fig. 11.21).
The macro is converted into a function with the same name Sales_Managers (spaces are replaced with underscores).

Rice. 11.21. The result of the transformation of the “Sales Managers” macro

After the conversion, the original “Sales Managers” macro was not deleted, and a new module named “Converted Macro - Sales Managers” appeared in the list of modules in the Northwind database window.

There is another way to convert macros into a VBA module:

Select the Sales Managers macro in the Northwind database window.
Select a team File, Save As(File, Save As).
In the dialog box that appears Preservation(Save As), in the As (As) drop-down list, select the item Module(Module) (Fig. 11.22) and press the button OK.

Rice. 11.22. Saving a macro as a module

conclusions

In this chapter, we introduced you to macros, how to create and run them, gave a brief overview of existing macro commands, and showed examples of their use. We did not describe all events in forms and reports and their processing using macros. This material is presented in detail in Ch. 13. There is no need to pay special attention to event handling using macros, since the same actions can be implemented in VBA procedures.

In conclusion, I would like to emphasize once again that the use of macros allows you to easily create applications that automate many user actions. It is recommended that you start by creating such applications before diving into the VBA programming language. However, complex multi-user applications should be developed immediately using the VBA programming language, without creating complex combinations of macros and modules.

The concept of macro and macro. Control playback of macro commands Work order

In this article

General information

Macro Builder

Creating a standalone macro

Creating an embedded macro

Adding Macros to a Macro

Step 1: View or search for macros

Step 2: Adding a Macro to a Macro

Step 3: Entering Arguments

About using IntelliSense with Web Database Properties

Moving a Macro

Deleting a Macro

Controlling program execution using "If", "Else If" and "Else" statements

Adding an "If" Block to a Macro

Adding "Else" and "Else If" blocks to an "If" block

Creating Nested Macros

Grouping related macros

Expanding and collapsing macros or blocks

Expand or collapse a single macro or block

Expand or collapse all macros (but not blocks)

Expand or collapse all macros and blocks

Copying and pasting macros

Share a macro

Running a macro

Debugging a Macro

Adding Error Handling Macros to a Macro

Using the step command

Converting a Macro to VBA Code

Changing Macro Settings

Creating and recording macros

Using macros in a Microsoft Access database

Creating Macros

Macro constructor

Creating Macro Groups

Applying conditions in macros

Running and Debugging Macros

Running a macro from the Macro Designer window

Running a Macro from the Database Window

Running a macro using a button on the toolbar

Running a macro using a menu command

Run a macro using a keyboard shortcut

Run a macro when opening a database

Running a macro from another macro

Assigning a Macro to an Event

Calling a Macro from a VBA Procedure

Debugging macros and finding errors

Using Macros

Working with data in forms and reports

Links to forms, reports and their properties

Links to form and report controls and their properties

Links to subforms and reports

Filtering records in forms, reports, tables

Navigating through data

Updating data in forms and controls

Working with objects

Converting Macros to VBA Procedures

conclusions

Popular articles

Latest articles

Sections

Pages

Special projects

Contacts