Basics of creating macros. Macros available for use in macros Methods for executing macros

The macro language is a programming language that allows you to implement user tasks by performing the necessary actions on Access objects and their elements. Macro is a program consisting of a sequence of macros. A macro is an instruction focused on performing a specific action.

For example, using a macro command, you can open a form, a report, print a report, run a query, apply a filter, assign a value, create your own menu for a form or report. Macro SetCommandMenu allows you to execute any given menu command. The set of macros available in Access implements almost any action that is required to solve a problem.

The macro language allows you to accomplish most tasks without resorting to Visual Basic programming. Macros, being an add-on over Visual Basic, provide the user with tools for solving problems that do not require detailed programming knowledge. The macro language is a higher level language than Visual Basic.

Macros define a set of methods for processing objects. Access provides a means of providing event-based interaction between macros and objects. Events occur when certain actions are performed, which primarily include user actions. The latter circumstance makes it possible to control the execution of the program from the outside. The user, performing various actions in the forms, initiates the execution of macros that automate the solution of subtasks related to user actions. This approach significantly distinguishes programming in the macro language from programming in the traditional sense, in which only the program controls the process.

Note that the presence of a device for launching programs through events triggered by user actions does not exclude the possibility of writing programs in the language of macros, which can solve the necessary tasks without user intervention. In this case, the task is solved by a number of interconnected macros.

To organize branches in a program, a macro defines the conditions for executing macros. The user launches the main program for execution and then all control of its execution is carried out from within. The program itself opens the necessary objects, selects and processes data, calls other macros, following the algorithm leading to the solution of the problem. If necessary, the program initiates a dialogue with the user.

Constructing a Macro

Creation of macros is carried out in dialog mode and is reduced to recording in the macro window a sequence of macros for which parameters are set. Each macro is assigned a name. When a macro is executed, the macros are executed sequentially in the order in which they appear. This uses the objects or data specified in the arguments of the macros. It is possible to define the conditions for executing a macro.

Execution of macros is initiated by a simple operation and can be reduced to opening it, as is done for other database objects. In addition, Access provides the ability to automatically initiate the execution of a macro when an event occurs. To associate a macro with an event, it is enough to enter the name of the macro into the line of this event in the form of properties of an object or its control. The events that a macro can be associated with are defined in the properties of forms and reports and their controls. Macro creation begins in the database window, where you need to select the Macros tab and click the New button. After that, the macro window opens.

Formation of macros in the macro window

The macros that make up the macro are entered in the Macro column. To enter a macro, you can click the macro drop-down button in this column and select the one you want. You can enter the name of the macro using the keyboard. The macro arguments are displayed at the bottom of the window. The values ​​of the arguments are set by selecting them from the list that opens in the argument string. An explanation message for the selected argument is displayed in the box to the right of the argument strings.

An optional comment is added to the lines in the Comment column to describe the result of the macro execution. Each new macro is added on the next line of the form. The order in which macros are placed in the form determines the sequence of their execution.

After entering all the macros into the macro, it must be saved using the File / Save menu command or the button on the macro toolbar.

You can click the Run toolbar button to run the macro. If the macro is already closed, then to execute it, select the macro in the database window and click the Run button in this window. Thus, at the initiative of the user, the problem can be solved many times.

To view and edit an existing macro, select it in the database window and press the Constructor button.

An example of developing a macro

Suppose it is necessary to calculate the number of students in groups and enter these data in the QUANTITY field of the GROUP table. Counting the number of students implemented by request Number of students in a group... Updating the QTY field based on the data received in this request executes the request Update GROUP_COL... Sequential execution of these requests solves the problem.

1. First, let's create an update request.
   Let's design the query constructor window as shown in the figure:

Added the Group table and the table to the query data schema Number of students received after executing the create request Number of students in a group.

1. Let's transform the fetch query into an update query. To do this, execute the menu command Request / Update... In the Update line, enter the name of the table field Number of students from which the values ​​to update are fetched.
   Let's save the update request under the name Update GROUP_COL.
2. Let's create a macro consisting of the macros shown in the designer window:

The Comments column contains comments for macros. Depending on the selected macro, the lower part of the designer window changes - Macro arguments... In the figure, the SizeShift macro is active, which sets the position and size of the Group table window displayed after its refresh.

Let's save the macro under the name Calculation of the number of students in groups... After clicking the Run button, we will receive the results of the macro execution shown in the figure.

A message is displayed on top of the Group window according to the last macro command.

Control questions

1. How are macros in Access different from macros in Word and Excel?
2. How to organize the launch of a macro on a certain event?
3. What are the similarities and differences between macros and modules in Access?
4. How can I get help about a particular macro command?
5. What messages are displayed in this example that the DisplayOnScreen macro disables?

If you liked the macro Spatter frame, then you will inevitably have a desire to adapt it in accordance with your needs. Some of this work can be done without changing the macro. For example, you can change the filter parameters to control how rough the edges are. By default, this macro does not ask for any parameters when it is executed, but uses the parameters specified when it was written. You can tell the macro to issue queries for any command that has any parameters. Let's demonstrate using the example of filter parameters Spatter.

1. Select the first shot in the palette History... This will return the image to its original state.
2. Find in the palette Actions command line Spatter and click the empty rectangle to the left of the command name. An icon representing the dialog box shown in the figure will appear in it.
3. Spatter frameb.
4. Actions.
5. When executing the command Spatter Photoshop will open a filter dialog box where you can choose the options you want. Try, for example, to decrease the value Smoothness up to 2.
6. Click the button OK... The macro will continue execution.

Note that each new execution of the macro creates a new snapshot of the original document state. This is very useful, but not always necessary. For example, if you select the command parameters, then a new snapshot will appear with each sample. On the other hand, deleting the command to create a snapshot is also undesirable - you can irrevocably lose the results of previous work if the macro command is not suitable. The solution is to temporarily cancel the execution of an unnecessary command.

1. Click on the checkmark to the left of the command name Make snapshot in the palette Actions.
2. Select the name of the entire macro Spatter Frame.
3. Run the macro command by clicking on the corresponding icon in the palette Actions.
4. Open the palette History and make sure no new snapshot has been taken. Disabling a command temporarily is very useful when creating your own macros and when debugging them. Further changes will certainly affect the macro itself and will require the recording of new commands and the removal of existing ones.

Macro recording.

This section describes how to create a simple but very useful macro that allows you to center an image on the current layer. Let's use one feature of the command Paste: it places the image exactly in the center of the layer. Any image with a layer is suitable for recording a macro command. Background and one more layer. The image that is already on the screen meets these criteria. When creating a macro command, we will assume that the layer that requires centering is active by the time the macro command is launched.

1. Activate the layer Layer 1.
2. Let's place the macro in a new set of macros. In the palette Actions click the create new macro set button Create new set.
3. In the opened dialog box New Set enter a name for the new set, for example "My Actions".
4. Click OK. A new set has appeared in the palette.
5. In the palette Actions select the icon for creating a new macro Create new action.
6. In the dialog box that appears New Action enter the name of the macro in the field Name eg "Center (Layer)". Below this field is a list of all the sets of macros present in the palette. By default, the selected set is selected in it, My Actions. We will deal with the rest of the elements of this dialog box a little later.

Advice. When you create your macro, specify what it works with. If the macro requires the image to work on a separate layer, specify "Layer" in parentheses. If it needs a selection to function, specify "Selection". Such designations are accepted in the exchange of macros.

1. Click the button Record... From now on, all your actions in the program will be recorded in the Center (Layer) macro. After executing each command, immediately look at the palette Actions checking the result of her recording. The record icon in the palette is highlighted in red.
2. Select a team All menu Select or press keys CTRL + A... In the palette Actions the first team appeared Set Selection with parameter That corresponding to the selection of the entire canvas ( All).
3. Select a team Cut menu Edit Actions.
4. Select a team Paste menu Edit... The corresponding command will appear in the palette. Actions.

Advice. If, while recording a macro, you mistakenly did the wrong action and immediately found it, select the command Undo menu Edit... Wrong command from palette Actions it will not disappear, but the image will return to its previous state. Then just enter the correct command. The incorrect command remaining in the palette is easy to delete after the recording is finished. To do this, you just need to drag the command line to the delete icon.

1. All actions of the macro have been completed and it is time to stop recording. Click on the stop recording icon in the palette Actions.

We strongly recommend that each macro you create be provided with a comment describing its requirements for the initial state of the document, because one requires an object located on a layer to work, another requires a selected area, the third requires no layers, etc. When you have a library of macros , you will probably find it difficult to remember the details of how each function works.

1. Select in the palette Actions the title of the created macro.
2. Run the command Insert Stop ... from the palette menu Actions... In the opened dialog box Record Stop see the field Message, in which you can enter several lines of comment, and the checkbox Allow Continue, which determines whether or not to allow further execution of the macro. Enter your comment text and check the box Allow Continue.
3. Click the button OK.
4. Move the command Stop in the palette Actions to the first place in our macro in the same way that we changed the order of layers in the layers palette - Drag and Drop. We have added a comment to the macro that will be able to stop the execution of the macro if the user clicks Stop in the dialog box that opens.

In programming, even this elementary one, it is required that the program free up all the occupied computer resources. This macro does not satisfy this requirement, since all the contents of the layer remain on the clipboard. If the image is large enough, then the occupied memory will negatively affect the performance of the program; On the other hand, when switching to any other program and vice versa, the contents of the clipboard will be transferred between them. The ego will lead to annoying delay. As an independent work, supplement the macro command with the command Purge from the menu Edit.

Note. To write a contour into a macro command, you must first create it, and then use the command Insert path palette menu Actions... When playing the macro command, the outline will be reproduced on a new image. Recording macros in Photoshop has significant limitations. First, the actions of drawing tools (Brush, Stamp, Eraser, etc.) are not recorded. Secondly, there is no way to control the progress of a macro. A macro cannot call various commands depending on any conditions, it is impossible to organize a cyclic execution of a group of commands, to obtain and use information about the state of a document (size, resolution, color model, number and types of layers and channels, etc.).

The main set of Microsoft Access tools, which we discussed in the previous chapters of the book, is aimed at users who do not speak programming languages. For programmers, these tools are supplemented with macros(small programs in the macro language of the Access system) 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 introduce one of these tools, macros, and one of the Access programming languages, the macro language. We will introduce you to VBA modules and language v ch. thirteen ,

So, we will be discussing the following questions:

• Macro concept
• Using macros in applications
• Creating macros and macro groups
• Setting conditions for controlling the process of macro execution
• Different ways to run macros
• Tips for Debugging Macros
• Handling events in forms and reports using macros

Using macros in a Microsoft Access database

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

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

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

Category	Appointment	Macro
Working with data in forms and reports	Data sampling	Apply Filter
	Navigating through the data	NextRecord (FindNext) FindRecord (GoToControl) On Page (GoToPage) ForRecord (GoToRecord)
	Refresh data or screen	Update (Requery) ShowAllRecords (ShowAllRecords)
Performance	Executing a command	Run Command (RunComraand)
	Executing a macro, procedure, or query	RunMacro RunCode OpenQuery 3anyc to 3anpoca SQL (RunSQL)
	Executing another application	RunApp
	Aborting execution	Cancel Event (CancelEvent) Stop AllMacros (StopAllMacros) StopMacro (StopMacro)
	Sign out of Microsoft Access	Quit
Import Export	Passing Microsoft Access Objects to Other Applications	Output In Format (OutputTo) SendObject (SendObject)
	Data format conversion	Transfer Database TransferSQLDat abase Transfer Spreadsheet TransferText
Working with objects	Copying, renaming and saving an object	CopyObject CopyDatabase File Rename Save
Other	Display or hide the built-in or dedicated toolbar Sounding a beep	Toolbar (ShowToolbar) Signal (Fan)

Table 11.1. Microsoft Access 2002 Macros

As you can see from this table, the actions that can be performed using macros are very varied. Even if you will not use macros in your applications, it makes sense to learn these macros, as they can be used in VBA procedures, as will be shown below. (see chapter 13).

Compared to the previous version of Access, Microsoft Access 2002 has three new macros:

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

CopyDatabaseFile (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 for this you do not need to learn the syntax of the programming language. As you will see in the next section, to create a macro, you only need to know the basic techniques of working in Microsoft Access and Windows, such as dragging and dropping objects from the window. Database(Database) into a special window - Macro constructor(Macro Design), selecting an action from a list, and entering expressions as arguments to a macro. Therefore, if you really do not 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 are opened when you click buttons in a form or on a toolbar, or by choosing a menu command; so that when the user opens 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 an application, and a little later we will describe how to do this.

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

• The capabilities of macros are limited compared to the capabilities of the VBA language, therefore, in some cases, programming in VBA is indispensable, although first you need to be sure that these additional capabilities are really needed. The VBA language provides richer possibilities for working with data, allows you to use the object programming mechanism to communicate with other applications, call functions from Windows dynamic loading libraries (DLLs), and create your own specialized functions.
• Macros can be used almost anywhere VBA procedures are used, but VBA procedures are generally 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, their maintenance is quite laborious. VBA event routines are an integral part of forms and reports, and this has its advantages. For example, when you move forms and reports from one database to another, associated procedures are automatically transferred with them.

Nevertheless, as already noted, in many cases the use of macros is quite justified, since this greatly simplifies and speeds up the development of the application. You can create small, single-user applications using macros, although VBA is a better choice when building multi-user or client / server applications where application speed is critical.

Creating macros

Microsoft Access has a special tool for creating macros - the Macro Design. Let's consider 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:

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

The Macro Constructor window is structured similarly to the Table Constructor window, that is, it is divided horizontally into two parts: the description panel and the arguments panel.

The upper part of the Design 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 macros that make up the macro.

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

The line in the Comments column is a simple text field in which you can enter a comment describing the action to be taken.

When the Action field is filled, the Arguments panel appears at the bottom of the Macro Builder window, where you can enter the values ​​of the arguments of the corresponding macro. The list of fields in this panel depends on the selected macro and may be missing if the macro has no arguments. Thus, when creating macros, you do not need to remember the list of arguments for each macro.

In fig. 11.1 illustrates the selection of the ApplyFilter macro. The arguments panel has two fields that allow you to specify the filter name and the criteria for selecting records. To set a condition for selecting records, you can use the Expression Builder (see the section "Creating expressions using the Expression Builder" 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 like 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 want to create a macro to open the Customers form in the Northwind application. For this in the window Database(Database) you need to click on the shortcut Forms(Forms), then using the toolbar (method 2), open the window Macro constructor(Macro Design) and drag the Customers form into the Action field of the Macro Designer window.

Advice

For easy dragging and dropping of objects from the Database window to the form, select the Window, Tile Vertically command to arrange the windows side by side on the screen.

Then the OpenForm macro will automatically appear in the Action field (Fig. 11.2), and the fields will be automatically filled in the arguments panel: in the Form Name field, the name of the Customers form will appear, in the field "View" - the default value of the Form (Form), in the field "Window Mode" - the value Common(Normal). The rest of the 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 the section "Creating a query from a filter", Chapter 4).

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

Rice. 11.2. Automatic creation of macros by dragging and dropping objects

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

To enter arguments for 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 on the toolbar. As in other cases, the expression must be preceded by an equal sign (=). The exceptions are the argument Expression(Expression) macros Setvalue and argument Number of repetitions(Repeat Count) Macro-RunMacro (RunMacro). If you enter an equal sign in front of an expression that specifies the value of these arguments, it will be evaluated twice, which can lead to undesirable results.

If you need to specify the name of a database object as an argument to a macro command, you can either enter it using the keyboard, select it from the drop-down list, or specify the name of the object by dragging it out of 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 macros. Therefore, in the same macro, on the next line, we will select the Message (MsgBox) macro command, which after opening the form will display a dialog box with a message about which clients are presented in the form. To do this, enter in the field Message(Message) on the arguments panel the text of the message, for example The Sales managers filter is installed, the rest of the fields can be left unchanged. Argument Signal(Fan) determines whether the message will be accompanied by a beep. Argument A type(Type) defines 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 box.

If the database is large, it may take some time to select records in the form, so it is recommended that you display an hourglass cursor to indicate that data is being processed. To do this, we will use the Hourglass macro. Macros in a macro are executed in the order in which they are written in the Action column, that is, from top to bottom. The Hourglass macro should be placed first because the cursor must change before the form is displayed. Thus, we need to insert a new line in the description pane. To do this, place the cursor on the first line of the panel and press the button Add lines(Insert Rows) on the toolbar or just the key ... An empty line will appear. In this line, in the Action field, select the Hourglass macro. The Hourglass On field in the Arguments panel will display Yes, which is the default, which is what we want. To restore the original view of the mouse pointer, at the end of the created macro, you need to enter the same macro Hourglass, but with the value No (No) argument Turn on(Hourglass On).

Strictly speaking, it is better to put this macro in front of the macro for displaying a message on the screen. It is very easy to do this, since macros can be easily swapped with the mouse. To swap the last two macros in places, 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 new position of the line. Release the mouse button when this line is over the penultimate 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 a name for the macro in the input box, observing the naming conventions for Access objects, such as Sales Managers.

Comment

Microsoft Word and Excel have automatic macro recording tools. These tools are very convenient for learning VBA because they allow you to automatically create a procedure by turning on the macro recording, performing the necessary actions, and stopping the recording. Then all user actions performed after starting the recording and before stopping it are converted into the text of the VBA procedure. This text can then be viewed and edited using the VBA editor. There is no automatic macro recorder in Microsoft Access, just like there are no automatic VBA code generation tools (except that the 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 required macros well. For this in one object Macro(Macro) multiple macros can be combined. 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 be able to do this, each group macro must have its own name, and the name of the object Macro(Macro) will be the name of the macro group.

To create a macro group:

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

Rice. 11.3. Create a macro group

In fig. 11.H in the Design window there is a macro "Employees" of the database (Northwind). No separate macro is required to indicate the end of a macro. Access continues to run the macro in the group until it finds a row that contains the name in the Macro Name column, or until it reaches the last row in the Macro window.

When processing a macro, blank lines are ignored, so it is convenient to use them to separate macros in a group. This technique, as well as the introduction of comments for complex macros consisting of a large number of macros, can improve the readability of macros.

To run one of the macros in the group, the full name is used to refer to the macro. The full name of a 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 Designer. To do this, you need to set the appropriate parameters on the tab View(View) in a dialog box Parameters(Options) (see the section "Setting the parameters of the Access application" chapter 14).

Applying conditions in macros

In the examples of macros that we gave earlier, the sequential order of execution of macros was used. However, as in any program, the order of execution of macros in a macro can be changed. For this, the conditions for the execution or skipping of macros are introduced. These conditions are specified in the form of expressions in a special column that appears in the Macro Designer window if the button Conditions(Conditions) on the toolbar (fig. 11.4).

Comment

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

Rice. 11.4. Applying conditions in macros

In fig. 11.4 shows the Constructor window for the "Customer Label Dialog" macro group used to print a report in the form of stickers on envelopes. Moreover, you can print stickers for all customers at once or for customers of one country selected from the list. The window displays the Condition column. If the condition specified in this column is true, the macro in this row is executed. If the condition is false, the corresponding macro is skipped and the next one is executed.

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

This macro uses the StopMacro macro. It allows you to stop the execution of the macro, while all the macros following the StopMacro macro in order will not be executed.

Comment

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

Executing and Debugging Macros

There are many ways to run macros. Among them there are direct ways when the macro is run directly by the user. These methods are usually used when testing macros. When the macro is debugged, create a special menu command or button on the toolbar, or associate the macro with an event on a form or report. In this section, we will look at all the 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.GroupName.MacroName (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 revised macro. If there is only one macro in the Macro Designer window, then to run it you just need to press the button Running(Run) on the toolbar or select the command Run, 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 Running(Run Macro). It is equivalent to executing the menu command Service, Macro, Run Macro(Tools, Macro, Run Macro). (For customizing toolbars, see the section "Creating and modifying toolbars", Chapter 14.)

Running a macro from the database window

To run a macro from a window Database(Database) click the shortcut Macros(Macros) in the object panel, find the required macro in the list and double-click on it or press the button Running(Run) on the window toolbar Database. The latter method is suitable only if the selected object contains one macro. Otherwise, you need to use the universal method for executing a macro described above.

Comment

If the conditions or arguments of macros contain references to Access objects - forms, reports, etc. - they must be open before running the macro, otherwise you will receive error messages.

Running a macro using a button on the toolbar

Direct ways to run 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 modes of operation. Therefore, the most reasonable thing is to create a special toolbar on which you can place all the buttons you need. However, the operation of adding a custom button to a standard toolbar is extremely simple.

To add a button to the standard Access toolbar:

1. Right-click on the toolbar and select the command from the context menu Customization(Customize).
2. Customization(Customize) expand the tab Toolbars(Toolbars).
3. Check the box for one of the standard toolbars, for example Table View Query(Query Datasheet) to display on the screen.
4. Close the dialog Customization(Customize) by clicking the button Close(Close).
5. Click on the shortcut Macros Database(Database) and find the required macro.
6. Drag the macro with the mouse to the toolbar Request in mode tables (Query Datasheet).

Rice. 11.7. Create 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 the toolbar to which you have 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 toolbar name (Fig. 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.

1. Click on the shortcut Macros(Macros) in the object pane in the window Database(Database) and select the required macro.
2. Select the command (Tools, Macro, Create Toolbar from Macro). An empty toolbar appears on the screen.
3. Drag the required macro to this panel using the mouse.

A button with the 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 for calling several macros, we recommend combining these macros into one group (creating a group of macros was described in the previous section). Then when you execute 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 buttons display the names of the corresponding macros. The new toolbar is named the same as the macro group name. Try this command on one of the macro groups in the Northwind database, such as Customers. You can optionally replace the text on the buttons with icons. Additional information on customizing application toolbars, and, in particular, on 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 specially created menu command. The methods for creating menu commands for running macros are very similar to those used for creating toolbar buttons. For example, you can use the menu command for this Tools, Macro, Create Menu From Macro(Tools, Macro, Create Menu from Macro).

For more details on creating and configuring custom menus, see sect. "Creating and modifying command bars" chap. 14. Therefore, in this section, we will not consider creating a menu command from a macro.

Running 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 group of macros must contain a macro to run the corresponding macro for each key combination you assign. An example of the "AutoK" eys macro group is provided 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 key combination +

The macro itself consists of one macro RunMacro (RunMacro), which runs the macro “Client phones. Print ”(Customer Phone List.Print). The "AutoKeys" macro is watched every time the user enters special key combinations, such as +

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

Rice. 11.9. An example of the "AutoKeys" macro

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

Table 11.2. Allowed key combinations

Running a macro when opening a database

When you open a Microsoft Access database, some actions may take place. Most often this is opening a special form, the so-called Main Button Form, displaying special menus or toolbars, hiding standard menus, etc. To define these actions, use the dialog box Launch parameters(Startup). However, sometimes it is necessary to perform a more complex set of actions when starting an application than is allowed by this dialog box. For example, you may need to open several forms in advance (without showing them on the screen) so that later they will not take much time to display, check some conditions, or ask 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, runs it. When creating an "AutoExec" macro, remember that Access will first perform the actions defined in the window. Launch parameters(Startup) followed by the "AutoExec" macro, so there should be no 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. We have already met this macro when describing the "AutoKeys" macro. It should be noted here that this macro has three arguments: in addition to the name of the macro, the number of times to repeat the execution of the macro and the condition of the repeat are set. Thus, this macro allows you to organize loops. Argument Number of repetitions(Repeat Count) sets the number of calls to the macro. Argument Repeat condition(Repeat Expression) is an expression that can be True or False. The value of this expression is checked before executing the RunMacro macro. If it is True, then the macro is executed, if it is False, then the macro is not executed and control is transferred to the next macro command. If these two arguments are not specified, the macro is executed only once. If both of these arguments are given, the call loop ends when the macro has been executed the specified number of times, or when the specified condition is not met and is set to False.

Comment

The name of the macro in the argument of the RunMacro macro must be fully qualified, that is, it must be of the form GroupName. MacroName, even if the called macro is in the same group as the caller. If the repeat condition is set so that it is always true, then the loop is infinite. You can interrupt it with the keyboard shortcut +... If that doesn't work, you'll have to interrupt Access by pressing the keyboard shortcut ++.

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 recognized by an object, and the object's response to an event can be determined. Events occur as a result of user action, execution of VBA instructions, or 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 a key. Each of these events can be assigned a macro or VBA procedure that will automatically execute in response to an event that occurs. Almost all programming in Access boils down to writing macros or procedures that handle events, that is, the reaction of objects to events is programmed. There are a large number of different kinds of events to which objects react, and often there is not one, but a whole sequence of events. Therefore, you need to have some skill to decide which event you want to assign to a macro or VBA procedure you create. All the subtleties of this choice will be discussed in sect. "Programming in forms and reports" chap. thirteen . Here we will describe only how to assign a macro to an event and give examples of event handling using macros.

Let's start with the simplest. Open the Customers form in the Northwind database. This form displays customer information. 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 who is currently selected in the Customers form. In order to get what we want, we will 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 reaction to an event Button press(OnClick):

1. Open the Customers form in Design view.
2. 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 prompted to create an event handling procedure, not a macro.
3. Open the properties window of the button you just created, if it is not already open, and expand the tab Events(Event).
4. Notice how many different events are associated with the command button only. In addition to the usual pressing, which we will now use, the set of button events includes obtaining and losing focus, double-clicking the mouse, simply moving the mouse pointer over the button, etc. Such a 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 position 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 existing in the application (Fig. 11.10).
5. Since the macro we need is not in the list, let's create it. To do this, click the Builder button to the right of the field. A window will appear Builder(Choose Builder), which offers to choose one of three Builders: Expressions(Expression Builder), Macros(Macro Builder) and Programs(Code Builder) (fig. 11.11).
6. Please select Macros(Macro Builder) and click the button OK. This will open the "macro window" and a dialog box in which you must 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

1. You need to add a single OpenForm macro to the macro. The values ​​of the arguments of this macro are shown 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 on 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 a condition for selecting the records displayed in the form. The condition is an expression. In this case, this expression contains a link 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 equals 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) defines the way to work with data and can take one of the following values: Adding(Add), The change(Edit) or Only reading(Read Only). Finally, the argument Window mode(Window Mode) defines the type of window: Common(Normal), Invisible(Hidden), Icon(Icon) and Dialog window(Dialog).

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

Now it remains to switch to the Form mode and check how the macro works out the event Button press(On Click). If you were not mistaken 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 is simply closed. Let's consider the implementation of the second option. The Orders form opens when we click the button Customer orders, and becomes active. You want 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 to handle it. If you open a window Properties(Properties) of the "Customers" form and open the tab Events(Event), then among the many events you will see an event Turning on(On Activate). This is exactly the event that 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 dropdown list and click the Builder button. A window will open displaying 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 if it is open. To do this, we will use the isLoaded function, which returns True if the form is open, and False otherwise. The name of the form must be passed as an argument to the isLoaded function. So, into the column Condition(Condition) next to the Close macro, enter IsLoaded (or IsLoaded (Orders)).

Argument	Meaning
Object Type	Form (Forms)
Object Name	Orders
Save	No (No)

Table 11.4. Close Action Argument Values

Argument Preservation(Save) lets you control whether Access displays a dialog box when you close the 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 set to Not(No). The completed 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 Turning on(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've created work.

Rice. 11.13. Macro for closing a form

Calling a Macro from a VBA Procedure

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

To start a VBA procedure from a macro, there is a special RunProgramme (KipCode) macro. This macro has 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 the Method of the DoCmd object, for example, the procedure line

DoCmd.OpenForm "Clients"

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

We will consider creating VBA procedures below, in ch. thirteen.

Debugging macros and finding errors

Usually, to debug a macro, it is executed in a step-by-step mode. To enable this mode, press the button Step by step(Single Step) on the toolbar in the Macro Builder window or select the menu command Run, Step by step(Run, Single Step). Then, before the execution of each macro command, the (Macro Single Step) dialog box will appear (Fig. 11.14).

This window displays the name of the macro, the name of the executable macro, the condition for its execution and arguments. Moreover, in the fields Condition(Condition) and Arguments(Arguments) the values ​​of the conditional expression and the arguments of the macro are visible. If no condition is specified in 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 macro execution (for example, if you can see that the arguments are calculated incorrectly) and continue execution as usual, that is, not in steps.

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

If an error occurs during the execution of a macro, a dialog box appears. Macro execution error(Action Failed), similar to 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 the macro by clicking the button Abort(Halt). A macro containing an error is displayed in the window.

Finding errors in macros is usually straightforward. This is due to the fact that:

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

The main errors that occur in macros are associated either with the incorrect entering of object names, and then Access cannot find the corresponding object, or with the fact that the objects referenced by the macro are not available, for example, the form is not open. Therefore, when an error occurs, first of all, you need to check whether the names of the objects are entered correctly and whether the forms used in the conditions or arguments of the macros are open.

Advice

Before using links to a form or control, it is recommended that you check if the form is open by using the isLoaded () function. Unfortunately, this functionality 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.

The standard technique for debugging both procedures and macros is to display special messages on the screen that indicate the passage of certain stages of the process. In macros, this is done by inserting additional MsgBox macros to display such messages. Moreover, in the message you can display the values ​​of the properties of the controls, for example: "Product price =" & Forms! Products! Price

You can easily turn off the display of debug messages by entering the value False in the Condition column opposite the corresponding macros.

Another common trick is setting breakpoints. It is used to stop the execution of a macro at a certain point before the process ends. To do this, use the StopMacro macro. The macro will run before the StopMacro macro, after which you can check the results. Thus, it is possible to localize the error down to macro precision.

Applying macros

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

Working with data in forms and reports

This category includes a set of macros that select data, navigate through data, and update data in forms. Before moving on to examples of using macros, we will show you how to use references to forms, reports, and controls in the arguments and conditions of macros, since such references are used very often.

Links to forms, reports and their properties

To reference a form or report, you first need to determine which collection the referenced object belongs to. (For more information on families and their constituent objects, see Chapter 13) All open forms are in the Forms family, and open reports are in the Reports family. A complete 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, parentheses can be omitted. Thus, the link to the form will look like this: Forms! [Customer Orders] OR Forms! Clients For the report, the links look similar:

Reports! [Sales report] OR Reports! Price list A link to a form or report property consists of three parts: FamilyName! ObjectName.PropertyName For example:

Forms! Clients.Visible or Reports! [Period Sales] .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.

References to controls of forms, reports and their properties

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

FamilyName! ObjectName! ElementName

Forms! [Customer Orders]! [Order Number]

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

FamilyName! ObjectName! ItemName.PropertyName

For instance:

Forms! [Sales 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 link. For example, controls have the default property Meaning(Value), so the Forms link! Products! 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 in the same way as any other control, because the subform and subreport are one type of control. For instance:

Forms! Orders! SubformProducts

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

But the link to a control in a subform or report has a special structure: after the name of the control, 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 on which it is executed link:

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

Filtering records in forms, reports, tables

Let's move on to examples of using Access macros. To select records in forms, reports, tables, use the ApplyFilter macro command. There are two ways to define a filter: either by specifying the name of a previously 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 upon opening the form, then with the event Opening(On Open) the form must bind a macro containing the ApplyFilter macro. If you need to dynamically change the set of displayed records in an open form, proceed as follows:

• create in this form a set of fields in which you can set the selection conditions;
• create a button Apply filter, with which to associate the macro containing the ApplyFilter macro. 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 browse products with a selection based on various criteria.

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

Rice. 11.15. Form for selecting records by specified criteria

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

In fig. 11.16 presents two macros: Set Filter and Cancel Filter. In the dialog box Input area(Zoom) the value of the argument is displayed Selection condition(Where Condition) of the ApplyFilter macro. Please note that the condition turned out to be rather complicated, and the field length Selection condition(Where Condition) is limited to 255 characters. Therefore, if we included one more field in the selection conditions, for example, "Deliveries are discontinued", we would have to separately create a special filter and set its name in the argument Filter name(Filter Name).

In the "UndoFilter" macro, in addition to the ShowAllRecords macro, two more macros are needed to clear the "SupplierSelect" and "TypeSelect" fields, that is, to assign them a value Empty(Null). This is done using the SetValue macro.

Navigating through the data

This group of macros is associated with navigation through records and controls. As an example of using the macros of this group, consider a situation when a user works with the "Customers" form and wants to find the order of the current customer, whose number he knows. Let's go back to the example given in sect. "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 user knows the order number, he can enter it in the text field Search for an order, which can be added to the Customers form. Then, in the opened "Orders" form, you should immediately show the record containing the corresponding invoice. Add the field “Search for order” to the header of the form and make it so that when the form “Orders” is opened, the value of this field is checked and, if it is not empty, the invoice with the specified number is searched for and the transition to the corresponding record is made. Otherwise, the first record in the filtered recordset would become the current one. In fig. Figure 11.17 shows the Customers form with a new field and a Find Order macro that is associated with the On Load event of the Orders form.

Since the added event macro refers to a control on the Customers form, it should only be executed if the form is open, and in addition, the search should only be performed when the Order Search field is not empty ... Based on this, the conditions of the macro are formed. Before searching for a record by pattern in one of the fields, you must activate this field, for which you use the KElementControl macro (GoToControl). It sets the focus to the Orderld field. But this field is not available in the Orders form, so first you need to change the property value Access(Enabled) this field. Otherwise, execution of the GoToControl macro will result in an error.

The macro KElementControl (GoToControl) is used, as a rule, "if you want to change the standard order of transition between fields in a form. Usually, the transition between fields is done using the key<Таb>however, sometimes you want to skip over a number of fields and move the focus to a specific control. This may depend on some condition, such as the value of a field. In this case, the event After update(After Update) a macro is assigned for this prlya, 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 Macro for Navigating Data

Refreshing data in forms and controls

The last group of macros 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 of a particular user may not reflect the actual data. In order for the displayed data to correspond to the current state of the database, it must be updated using the command Posts, Update(Record, Refresh) (see the section Working with records, Chapter 2).

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

The macros Requery, ShowAllRecords, and RepaintObject are used to update records in forms, tables, or controls.

The Update macro (Requery) refreshes the data in the database object by revisiting the data source. The macro has one argument that contains 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. The Customers form has a Country combo box. The data source for this field is a query that fetches values ​​from the Country field of the Customers table:

SELECT DISTINCT Clients.Country FROM Clients;

If, when entering a customer, a new country name is added to the table, then this country will not appear in the list, since the query 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) of the form Macro Clients. Updating the list of countries, which consists of one Macro 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 updates only one object, so if there are controls on the form that require the displayed data to be updated, and the macro assigned to that form uses the Requery macro with an empty argument value, then only the records in the form are updated. For each such control, you need to use a separate macro. If this macro contains the name of a control as an argument, then only that control is updated when it is executed. The entries in the form itself are not updated.

Using the Update macro (Requery), you can update data in an inactive form, more precisely, not in the form that the macro is assigned to. However, in this case, you first need to execute the SelectObject macro, which will transfer the focus to the desired form in order to activate it (then the focus can be returned back).

The ShowAllRecords macro action, as already noted, cancels the filter and re-views the source of the records. It is often used to update data in a subordinate form.

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

Working with objects

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

Setting the properties of controls allows you to dynamically, depending on conditions, make these controls inaccessible or invisible. 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 and not create an additional form for each situation. When there are a lot of forms in the application, this feature becomes very useful. To make the control invisible, set the value Lie(False) for property Output on display(Visible) of this item. To display the control on screen, set this property to True(True).
• Depending on the current state of work with data, you can change the availability of buttons designed to perform certain actions, or other controls on the form. To make the control unavailable, set the value Lie(False) to its Enabled property. To make the control accessible, set the value True(True) for this property.
• You can prevent the user from modifying the data in the form. For this, the property Access(Enabled) the corresponding fields should be set to Lie(False), and the property Locking records(Locked) - value True(True). If you want to prohibit changing in all fields, set the value Lie(False) for the following form properties: Allow changes(Allow Edits), Allow adding(Allow Additions), Allow deletion(Allow Delitions). These properties can be changed dynamically, that is, while the user is working with the form. For example, you can enable or disable data editing after checking the user's 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 macros for opening and closing Access objects:

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

The OpenQuery macro action allows you to execute any type of query, including a query to modify data. If the name of a select query or a cross query is specified as an argument to this macro, then the result of the macro execution will be to display the selected records. If the argument is the name of the request to change the 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 be displayed on the screen. To disable the display of these messages, use the SetWarnings macro with the argument value Not(No). Just remember to re-enable the display of system messages after executing the query, using the same macro, but with the Yes argument. Otherwise, Access will not display any system messages, which can lead to unwanted actions in the application.

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

The OpenStoreProcedure macro action lets you execute or open a server stored procedure in edit mode. .

Not all macros are described in this section. Use the Access help system for complete information. To get quick help:

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

Converting Macros to VBA Procedures

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

Let's try to convert macros assigned to events in the Customers Labels Dialog form into event routines:

1. Open the Customers Labels Dialog form in Design view.
2. Select a team Service, Macro, Convert Form Macros(Tools, Macro, Convert form's Macros to Visual Basic).
3. In the dialog box Form macro conversion(Convert form macros) (fig. 11.19) click the button Convert(Convert), leaving the default checkboxes selected.

Rice. 11.19. Convert Macro to VBA Procedure

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

Rice. 11.20. Procedure derived from a macro

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

Conversion of macros assigned to report events is performed in the same way.

Now let's look at converting to a VBA procedure a macro that is not associated with an event on a form or report, such as the Sales Managers macro we created earlier.

1. Click on the shortcut Macros(Macros) in the Northwind Database window. A list of macros appears. Select the Sales Managers macro.
2. Select a team Service, Macro, Convert Macros(Tools, Macro, Convert Macros to Visual Basic).
3. The same dialog box as in the previous example will open (see Fig. 11.19), in which you need to click Convert(Convert). After the end of the conversion process, a message will appear about the successful completion of the conversion process and a VBA editor window will open, in which you can view the resulting VBA code (Fig. 11.21).
4. The macro is converted to a function with the same name Sales_Managers (spaces have been replaced with underscores).

Rice. 11.21. Result of transformation of the "Sales Managers" macro

After the conversion, the original Sales Macro was not removed, and a new module named Converted Macro was added to the module list in the Northwind database window.

There is another way to convert macros to VBA module:

1. Select the Sales Managers macro in the Northwind Database window.
2. Select a team File, Save As(File, Save As).
3. In the dialog box that appears Preservation(Save As), from the As drop-down list, select an 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 macros and showed you examples of their use. We have not described all events in forms and reports and their processing using macros. This material is detailed in ch. thirteen. There is no need to pay special attention to handling events 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 delving deeper 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.

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

In order to change the order of commands, it is necessary to "grab" the command line in the palette list and move it to a new position. The double line represents the position of the team during the move.

Changing Macro Parameters

When it is created, a macro command receives a name and color, which it is displayed in the mode of command buttons, as well as a key combination. If necessary, these parameters can be changed at any time using the command Action options(Operation Parameters), which displays a dialog box identical to the one 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 sequence of commands, the Adobe Illustrator program writes them in the same order, including those parameters that were used by each specific command.

Thus, you can record any sequence of commands and use it in the future, reducing the time and, most importantly, efforts to perform routine operations, as well as creating "hand-made" effects.

The development of such macros opens up ample opportunities for users to apply their creative powers. By design, the result is close to plug-in modules, but it is easier to create and does not require special knowledge.

However, there are also limitations: some commands and functions can be included in macros only by writing ( 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
Another very significant limitation should be noted. The success of the recorded macro execution with another document and at a different 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 of all be executed, and in the process of execution erroneous actions are possible, it is strongly recommended to experiment with copies (especially for important documents).

Advice
It should also be borne in mind that when using the commands Save as(Save As) or Save a copy(Save a copy) no need to enter specific filenames in these command dialogs.

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

Rice. A1.4... New Action Dialog Box

So, in order to create a macro using the write method ( recording), you need to open the edited document, display the palette Actions(Operations), select a set of macros 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 macros (list Set(Dial)) and a key combination for launching it (the Function Key(Function key)), select one of the seven colors for displaying the macro string in the palette (list Color(Color)).

Then press the button Begin Record(Record), then the dot on this button will turn red.

Now you can start executing the commands that you want to save in the macro. If the command has a dialog box, then after setting the required values, press the button OK... If you intend to make changes to the settings during the execution of the macro, you can leave the default values, but in this case, be sure to press the button OK... When you exit the dialog by clicking the button Cancel(Undo) this command is not written to the macro.

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

1. The concept of a macro.

2. Event overview

3. Creation of macros.

3.1. Setting the conditions for the execution of macros.

3.2. Creation of a group of macros.

4. Running the macro.

1. The concept of a macro

To automate operations in the MS Access DBMS, two tools are provided: macros and modules... With the help of these tools, it is possible to determine the application's reaction to various events that occur in forms and reports: clicking a button, receiving focus by a control, switching to a new record, changing data, opening a form, opening a report, etc.

Macro Is an MS Access database object that is a set of one or more macros and is designed to automate frequently performed tasks.

Macro - the main component of a macro, a closed instruction, independently or in combination with other macros, which determines the actions performed in the macro. Each macro has a specific name and possibly one or more user-supplied arguments.

Functionally, macros can be divided into several categories:

- work with data in forms and reports;

- execution of commands, macros, procedures and queries;

- work with objects;

- import / export of data and objects;

- others.

Table 11.1 shows 56 MS Access DBMS macros grouped by categories.

Table 11.1

	Appointment	Macro
Working with data in forms and reports	Data sampling	ApplyFilter
	Navigating through the data	NextRecord (FindNext). FindRecord, To the control element GoToPage, OnRecord (GoToRecord)
	Refresh data or screen	Update (Requery) ShowAllRecords
Performance	Executing a command	RunCommand
	Executing a macro, procedure, or query	RunProgram (RunCode), RunMacro (RunMasgo), OpenQuery (OpenQuery), RunQuerySQL (RunSQL)
	Executing another application	RunApp
	Aborting execution	CancelEvent StopAllMacros (StopAIIMacros), Stop Macro
	Sign out of Microsoft Access	Quit
Import/ export	Passing Microsoft Access Objects to Other Applications	OutputIn Format (OutputTo), SendObiect
	Data format conversion	ConvertDatabase (TransferDatabase), ConvertElectronicSheet (TransferSpreadsheet) TransferText
Working with objects	Copy, rename, or save an item	CopyObject, Rename, CopyDatabaseFile (CopyDatabaseFile)
	Deleting an Object	DeleteObject
	Change the size or position of a window	Expand (Maximize), Minimize MoveSize, Restore
	Opening or closing an object	OpenForm, OpenModule, OpenQuery, OpenReport, OpenTable, OpenFunction OpenPage (OpenDataAccessPage) Open diagram (OpenDiagram) OpenStoreProcedure OpenView Close
	Print object	Print (PrintOut)
	Selecting an object	SelectObject
	Setting the value of a field, control, or property	SetValue
	Object update	UpdateObject
	Create a custom or general menu bar, custom or global context menu	AddMenu
	Setting the state of menu items in a dedicated or general menu bar	SetMenultem
	Displaying information on the screen	OutputOnScreen (Echo), Hourglass, Message (MsgBox), SetWarnings
	Generating keystrokes	Keyboard Commands (SendKeys)
	Display or hide the built-in or dedicated toolbar	Toolbar (ShowToolbar)
	Sound signal	Signal (Fan)

More complete information on macros can be found in the section referenceMicrosoft Access| Programming inMicrosoft Access| Macros.

The actions that can be performed using macros are very diverse, therefore, knowledge of macros is required to create macros. Knowledge of macros will also be useful when creating procedures in VBA, since many macros are methods of the DoCmd object.

According to the storage location, macros are divided into two types:

– simple(an object consisting of a set of macros);

– group(an object consisting of a set of logically related macros, each of which has its own name and a set of macros).

According to the method of execution, macros are divided into two types:

– linear(each macro in a macro is executed sequentially one after the other);

– with the condition(macros are executed depending on the fulfillment of certain conditions).

2. Event overview

Event Is any action that is recognized by an object. Events occur as a result of user action, execution of VBA instructions, or generated by the system. Examples of events are: opening a form, moving from one record to another in a form, closing a report, clicking a command button in a form, etc.

Almost all programming in MS Access comes down to writing macros or procedures that handle events, that is, the reaction of objects to events is programmed. Events of various MS Access objects are shown in Figures 11.1 - 11.3.

Events can be divided into the following categories, grouping them according to their functional characteristics (operations performed):

- opening and closing forms and reports;

- data change;

- change the focus of input;

- events of working with the keyboard;

- mouse events;

- seal;

- error tracking;

- expiration of a period of time.

Figure 11.1. The Events tab of the form properties window.

Figure 11.2. The Events tab of the properties window of the subordinate form / report control.

Figure 11.3. The Events tab of the control properties window. Button (a) and Combo box (b).

3. Creating macros

Macro creation consists of two steps:

1. In the database window, go to the tab Macros and click on the button Create .

2. In the window that appears Macro constructor create a macro.

Window Constructor macros consists of two areas (Figure 11.4):

- the upper area is intended for describing the logical structure of the macro;

- the bottom area is for describing the arguments of macros.

In the upper area Constructor macros two columns are displayed by default Macro and Note... Column display Macro name and Condition carried out using the corresponding buttons on the toolbar Constructor macros.

Cells in a column Macro are used to select from a list of macros included in the macro.

Cells in a column Note are used to describe the action performed by a macro (may be blank).

Cells in a column Macro name are used to name a macro in a macro group.

Cells in a column Condition are used to specify the sequence of execution of macros.

Figure 11.4. Macro Designer window

To quickly switch between the top and bottom area of ​​the window Macro constructor you can use the key < F 6> .

There is another way to enter macros.

In the database window, an object is selected: form, report, macro, query or table and using the mouse is dragged into the window Macro constructor... In a column box Macro a macro will appear automatically (for example, OpenForm) and some arguments of the macro command will be filled in automatically (object name, mode, window mode).

There are two ways to enter values ​​for the arguments of a macro:

- selection of a value from the list of acceptable values;

- entering a value manually or using a dialog box Expression Builder.

An example of a macro that allows you to open the "Clients" form in the form mode, read-only and display clients only from Spain, is shown in Fig.11.5.

Figure 11.5. Macro Constructor window with filled argument values

3.1. Setting conditions for the execution of macros

To create macros for a nonlinear structure, you must display the column Condition and specify the condition for executing macros in it.

Macro execution starts from the first macro command and continues until an empty line is encountered (a sign of the end of a macro), the name of another macro, or a macro StopMacro... Macros for which conditions are defined will be executed only if the condition written in the macro line has the value True... To propagate the condition to subsequent macros, it is necessary in the column Condition indicate ellipsis ( … ).

An example of creating a macro of a nonlinear structure

In the shape of Suppliers there are two fields The country and Index... It is necessary to check the correctness of the entered index for some countries before updating the record (before saving).

If the field The country is NULL, then save the record, since it is impossible to check the index for belonging to the country, otherwise check the value in the field Index:

- if the field The country has the meaning France, Italy or Spain, and in the field Index introduced not 5 characters, then do not save the entry if 5 - save;

- if the field The country has the meaning Australia or Singapore, and in the field Index introduced not 4 sign, then do not save the record if 4 - save;

The macro algorithm in the form of a block diagram is shown in Figure 11.6, and the implementation of the macro in the window Macro constructor, shown in Figure 11.7.

This macro is associated with the property Before the update in the tab Events shape Suppliers.

Figure 11.6. The algorithm of the macro

Figure 11.7. Implementation of a macro with a non-linear structure

3.2. Create a macro group

A significant number of macros are required to automate an application. If you create macros for each individual operation, then in this case hundreds of different macros will accumulate in the database.

To reduce the number of macros as database objects, there is another way to organize them - by combining several macros into a group. Combining macros into a group can be done in two ways:

- belonging to the automation object (for each form and report);

- belonging to the type of automated operations.

A group of macros is stored in one object and therefore has one common name - the name of the object. To run a macro included in a group, use the full name: [Group_name]. [Macro_name_in_group].

To create a group of macros, do the following:

1. Open Macro constructor.

2. Display the column Macro name.

3. In a column cell Macro name enter a name for the macro unique to the group.

4. In the cells of the column Macro starting from the line containing the name of the macro, enter all the macros that make up the macro and their arguments.

5. Skip one line.

6. Repeat steps 3 - 5 for each macro.

An example of creating a group of macros is shown in Figure 11.8.

Figure 11.8. Implementation of a group of macros

4. Running a macro

There are many ways to run macros.

Among them, there are ways where the macro is run by the user. These methods are used mainly when testing macros.

After debugging a macro, to run it, a command button is created in the form, a menu command or a button on the toolbar, or the macro is associated with an event in the form or report. A macro can be run from another macro, as well as programmatically - from a VBA procedure.

Running a macro from the Macro Designer.

To run a simple macro or the first macro from a group, click on the button Running on the toolbar Macro constructor.

To run any macro from the group, execute the command Service | Macro | Run Macro ... and in the dialog Running macro select the name of the macro.

Running a macro from the database window.

To run a simple macro or the first macro from a group, go to the tab Macros, select a macro and click the button Running in the database window.

See above for launching any macro from the group.

1. Open the form or report in the mode Constructor.

2. Select the automation object (control, form or report, section of the form or report).

3. Open a dialog box Object properties.

4. Select an event on the tab Events to which the macro is assigned.

5. Select the full name of the macro from the list.

The macro will run every time an event occurs for which the macro is assigned. An example of assigning a macro to an event is shown in Figure 11.9.

Figure 11.9. Assigning a Macro to an Event

Macro named Autoexec starts automatically when the database is opened. To cancel the actions specified in this macro, you must open the database while pressing the key > .