This article describes the macros available in the databases access dataand how they can help you save time by automating frequently performed tasks. It covers the basics of creating and using macros, and mentions improvements to macros in Access.

What is a Macro?

Macro in Access is a tool that lets you automate tasks and add functionality to forms, reports, and controls. For example, by adding a button to a form, you can bind its event OnClick with a macro that includes the commands to be executed when this button is pressed.

In Access, you can think of macros as a simplified programming language that you code in as a list of actions to take. When you create a macro, you select each action from the drop-down list and then enter the required information for it. You can use macros to add functionality to forms, reports, and controls without having to write code in a Visual Basic for Applications (VBA) module. There is a subset of VBA commands available in macros, and most people find it easier to create a macro than to write VBA code.

Let's say you want to create a report directly from one of your data entry forms. You can add a button to the form and then create a macro that opens the report. A macro can either be stand-alone, i.e. a separate object in the database that is associated with an event OnClick buttons, or injected directly into the event OnClick... In any case, when you click the button, the macro runs and opens the report. These macros are commonly referred to as user interface macros.

The concept of data macros was first introduced in Access 2010. With data macros, you can automate tasks and add functionality directly to tables. Data macros and their actions are nested in specific table events, such as adding new entry to the table.

You can create a macro using the Macro Builder shown in the following figure.

Note: Starting in Access 2010, the Macro Designer interface was redesigned to make it even easier to create, edit, and share Access macros.

To open the User Interface Macro Designer, do the following:

In the tab Creature in Group Macros and code press the button Macro.

To open the Data Macro Designer, do the following:

Open any table in table view. In the tab Table in groups Before the events and After the events select one of the event commands, for example Before the change or After insertion.

To learn more about the Macro Designer and how to create and test user interface and data macros, see the related articles under see also at the end of this article.

Macro functions in Access

In earlier versions of Access, many popular functions couldn't be performed without writing VBA code. In current versions of Access, new features and macro actions have been added to eliminate the need for code. This makes it easier to add functionality to the database and helps protect it.

Embedded macros You can embed macros on any events provided by a form, report, or control. The embedded macro does not appear in the Navigation Pane. It becomes part of the form, report, or control in which it was created. If you create a copy of a form, report, or control that contains embedded macros, these macros will also be present in the copy.

Enhanced security If the button Show all actions is not selected in the macro designer, only those macro actions and RunCommand arguments that do not require the status of a trusted database are available for use. A macro created using these actions will only run if the database is in disconnected mode (when VBA execution is prohibited). Databases that contain macro actions that are not trusted (or databases with VBA code) must be explicitly trusted.

Error handling and troubleshooting Access provides macro actions including OnError (similar to the "On Error" statement in VBA) and ClearMacroErrorthat allow you to perform certain actions if an error occurs while the macro is running. Also, the action of the macro SingleStep allows you to switch to the mode step by step from anywhere in the macro to see how it works step by step.

Temporary variables Three actions of macros ( SetTempVar, RemoveTempVar and RemoveAllTempVars) allow you to create and use temporary variables in macros. They can be used in conditional expressions to control running macros, pass data to or from reports or forms, or perform any other task that requires temporary storage for a value. These temporary variables are also available in VBA, so they can also pass data to and from VBA modules.

Learn more about macro actions

As you work in the macro designer, you can get more information about an action or argument by clicking on it and reading the description in the box in the lower right corner of the macro designer window. In addition, a Help article is associated with each action of the macro. To learn more about an action, select it in the list of actions and press F1.

Access 2010 introduces a new Macro Designer that makes it easier to create complex macros and helps reduce coding errors. To do this, the designer uses drop-down lists, IntelliSense, reuse existing macros, drag and drop, and copy and paste from the clipboard.

Developers will appreciate the improved user interfacethat allows you to create conditional blocks to form a logical sequence of actions. You can share macros with other users via email, web pages, blogs, and newsgroups.

Some of its benefits are listed below.

Ø Macro catalog. Macros are organized by type and are searchable.

Ø IntelliSense. IntelliSense prompts you as you enter expressions possible options and allows you to select the one you want.

Ø Keyboard shortcuts. Keyboard shortcuts make creating macros faster and easier.

Ø Program flow. Comment lines and grouping commands allow you to create more readable macros.

Ø Conditional expressions. Allows you to create more complex logic using nested If, Else, and Else If blocks.

Ø Reuse of macros. The macro catalog contains macros that were created earlier and can be copied into the current macros.

Ø Simplified sharing. Now you can copy the macro and then paste it as XML into the message email, a newsgroup post, blog post, or website with code examples.

If you had to create macros in previous versions Microsoft Access, you probably remember the three-column macro constructor (see Figure 3). Conditional statements were added to the Condition column, macros were added to the Macro column, and the specified parameters were added to the Arguments column.

Figure: 3. Outdated version of the macro constructor

The new Macro Designer for Access 2010 looks more like text editor... There are no more three columns. Instead, macros and conditional statements are displayed in drop-down lists in a format familiar to programmers. The arguments are displayed in an embedded dialog box.

Let's start creating a simple action macro in Access 2010 Macro Design.

The designer provides several ways to add an action to a macro. The simplest one is to use the drop-down list (see Figure 5, page 17).

Figure: 4. Appearance macro constructor

Figure: 5. Drop-down list of the macro designer

Figure: 6. Macro builder: a) Constructor tab, b) list of commands, c) command directory

When working with a macro, the "Constructor" tab appears (see Fig. 6a). To add a command to a macro, it is enough to select it from the list (see Fig. 6b) or double-click it in the command directory (see Fig. 6c). When you add a command, additional options appear in the Macro Builder. For example, adding an If command (if) provides options for creating complex nested conditions.

Access 2010 includes 86 built-in macros. Many of them contain additional arguments to make them more flexible and functional. For example, the macro OpenFormoffers a list of all external forms from the database, as well as a number of additional arguments (see Figure 7, page 19). For example, by changing the Mode argument, you can open the selected form not only in form mode, but also in design, layout, and other modes.

As noted above (p. 17, Fig. 5), a drop-down list is used to add actions, which can also be used to add blocks ( If ... Otherwise).

Figure: 7. Macro OpenForm

In the previous macro designer microsoft versions Access in the Condition column (see Figure 3, page 16), you could create simple conditional statements. More general operators can be created in Access 2010 Macro Designer IF A (If) by adding operators Otherwise If (ElseIf) and ELSE (Else). To add these operators, select the block IF A and click in the lower right corner of the code block text ELSE If or ELSE... For example, if you click the text ELSE If, a dialog box opens ELSE If... As you enter code into a condition field, Microsoft Access uses IntelliSense to display identifiers, functions, and other database items.

Counting the number of records in the entire report

1.Open the report in design mode.

2.Add a calculated field to the report note.

3.To open the Properties window, make sure the required field is selected and click the Properties button on the toolbar.

4. Enter the expression \u003d Count (*) in the cell of the Calculated Field Data property. This expression uses the Count function to count all records in the report, including records that have blank values \u200b\u200bin some of the fields.

Macro. A macro or collection of macros used to automatically perform certain operations.

Macro refers to a set of one or more macros that perform specific operations, such as opening forms or printing reports. Macros can be useful for automating frequently performed tasks.

Working with forms and reports is greatly facilitated through the use of macros. Microsoft Access provides over 40 macros that you can include in macros. Macros perform actions such as opening tables and forms, executing queries, running other macros, selecting menu items, resizing open windows, etc. With them, you can even launch other applications that support dynamic data exchange (DDE). for example Microsoft Excel, and exchange data between your database and other applications. Macros can be combined into macros and conditions for their execution can be specified.

Microsoft Access provides various types macros to automate the application. You can use macros to do the following:

Opening a table, query, form, or report in any available mode.

With macros you can close an open table, query,

form or report;

Opening a report in preview mode or direct output to a printer. You can output data from a report to an RTF file (.rtf), plain text format (.txt), or a Microsoft Excel file (.xls) and then open it in Microsoft Word, WindowsNotepad, or Microsoft Excel;

Executing a select or change request. Wherein

query parameters can use control values

any open form;

Taking actions based on values \u200b\u200bin a database, form, or

report. Macros can run other macros and call functions

VBA. You can interrupt the execution of the current macro or all

macros, cancel the event that triggered the macro, or even exit

applications;

Sets the value of any form or report control. You can emulate keyboard operation and pass keyboard input to system dialog boxes. Using macros, you can update values \u200b\u200bin any controls whose data source is a query;

Apply a filter, navigate to any record, and search for data in a base table or form query;

Definitions of a custom menu bar that replaces the standard one. You can make an item of a special menu available or inaccessible, including a contextual one, uncheck or mark it. In addition, using macros, you can open or close any of the standard or special toolbars;

Executing a command from any Access menu;

Move, resize, minimize, or restore any window within the Access workspace. You can give focus to any of the windows or any control in the window, select the report page and display it in preview mode;

Display informational messages and sound alerts to draw attention to your messages. You can also turn off some of the warning messages during change requests;

Rename any database object, copy the selected object to the current or another Access database. You can use macros to save or delete objects from your database. In addition, macros allow you to import, export or link tables from other databases, import or export spreadsheets and text files;

Launching the application, as well as to exchange data with the application using the DDE mechanism or the clipboard. You can output data from a table, form, query or report to an output file and open it in the appropriate application. You can also pass a sequence of keystrokes to an application that is waiting for keyboard input.

Let's consider other possibilities of macros. Command buttons that open and place forms and set field values \u200b\u200bwill help you switch from one task to another. You can create sophisticated editing routines that validate form data, including validating data in other tables.

Creating simple macros

1. To start working with macros, in the database window, left-click on the Macros tab, the following window will appear on the screen:

Tab selected Macros

Figure 2

3. The upper part of the window is used to define the macro, and the lower part is used to enter the values \u200b\u200bof the arguments of the macros included in it. At the top, there are at least two columns with the headings Macro and Note. By clicking the Macro Names (1) and Conditions (2) buttons on the toolbar, you can see all four columns shown in Figure 3

Figure 3

Note the area (4) in the lower right part of the macro window, in which

a brief help is displayed. The content of the message changes depending on the position of the cursor at the top of the macro window (remember: you can always press F1 to get context help).

4. In the Macro column, one of the 49 macros provided by

Access. If you click in any cell in the Macro column, a button (3) with a down arrow appears at the right end of the cell. Clicking this button opens the list of macros shown in Fig. 4

For example, select the action message Message from the list. It is used to open a modal dialog box with a message. This is a great way to display all kinds of warning or informational messages in your application without creating a special form for this.

For example, suppose the message is a greeting. In the corresponding cell in the Comment column, enter the text Invitation. The Note column is especially useful for documenting complex macros that contain many macros. In this column, you can enter additional comments by placing them on lines that do not contain macros.

After you select a macro, such as Message, Access displays its arguments at the bottom of the macro window.

(You can use the F6 key to move from the top of the macro window to the bottom and back.)

Figure 4

The value of the argument Message is the text displayed by Access in the dialog box, enter there Invitation... The Signal argument is used to play a sound signal when a dialog window appears, enter Yes... The Type argument allows you to put an icon in the message box, for example, a red circle with a white cross, choose from the alternatives given Information... Enter the text displayed in the title of the dialog box in the Title argument cell (Full text of the message: Welcome to the new database.).Those. a window for creating a simple macro using the macro command The message will look like this (Fig 5.):

Figure 5

In the Macro column, using the button (6) you can add another line for the macro command, and using the button (5), you can delete any line.

5 The macro must be saved before running. From the Microsoft Access menu bar, select File | Save (or File | Save As / Export). When using the command File | Save Access will open the dialog box shown in Figure 6. Enter your name Macro1 and click OK to save the macro.

6 Some macros (once created) can be run directly from the database window or macro window, since they do not depend on the controls of an open form or report. If the macro depends on a form or report, it must be associated with the corresponding event and run when it occurs. Before running a macro, it's a good idea to test its work by running the macros in step by step.

To start step by step check, press the button (7) Step by step, and then - Start (8). Access will display the Step-by-Step Macro dialog box, as shown in Figure 1-4. 7

Figure 6

Figure 7

7 If you click the (9) Step button, the macro shown in the dialog box will run, and Access will display a modal dialog box with the message we created, shown in Figure 8:

Figure 8

8 Click OK to close the window. If several macros are defined in a macro, after the first step, you will return to the Step-by-Step Macro dialog box, which will display the next ready-to-execute macro. Since in our case the macro consists of just one macro command, Access will return you to the macro window (Figure 5). When finished, press the (7) Step by step button again to cancel step by step. Otherwise, you will remain stepping through any macro until you close and restart Access.

Note:

If a macro encounters an error while running the application, Access first displays a dialog box explaining the error. Then you will see a Macro Stepping through the Macro Stepping dialog box, Macro Execution Error, with information about the macro that caused the error. At this point, you can only click the Abort button and edit the macro to eliminate the cause of the error.

Macro Is a set of several commands in the language Visual basic, which allows you to simplify or program operations with the database. As a rule, macros are written in automatic mode, so there is no need to know the Visual Basic language. Macros program routine operations, with their help it is convenient to create main menu application system and subordinate menus, organize data retrieval, etc.

As a rule, macros are associated with the “reaction to the event” of the managers. access elements 2007. Events determine what happens when you click a button, enter or exit a database field, open or close a form or report.

Creating buttons to launch forms

Let's create a form "Button Panel" to call some forms Buyer, Product and Order our database. To do this, select the tab Creature on the DBMS toolbar, and then click the button Macro . A window will appear on the screen Macro1. It has a table with two fields. The first is for macros, the second for arguments.

Click on the DB panel tab The form,select the "Buyer" form and drag it to the first row of the macro definition table in the column Macro,the text will appear OpenForm, the name of the form will appear on the right side of the window. Let's close the macro and save it with the name “Buyer”.

Comment. You can directly select a macro from the list of macros OpenForm,and then in the parameters (at the bottom of the screen) specify the form "Buyer".

Now let's create a form for placing buttons. Select the tab Creature in the database window , then the command Empty form. Switching to design mode, drag the "Buyer" macro we created onto the form. There will appear a button labeled Buyer... When browsing the list, you can find that in the property Button press the name of the macro "Buyer" is recorded.

Close the form and save it with the name “Button Panel”. To test the work, open the form and press the button Buyer... The “Buyer” form we created earlier will appear on the screen, with the help of which you can view and enter data on buyers.

The task

1. Create buttons in the form "Button bar" Productand Orderfor the "Product" and "Order with a combo box" forms.
2. Add the title Basic Data Entry Forms to the "Button Panel" form.
3. Set a different type of design for form elements.

Macros. Automatic launch of a macro when opening a database

When you open an Access database, it is convenient to display a certain form, for example, a panel of application control buttons. Let's make it so that when our database is opened, the form “ Button bar”.

To do this, select the tab Creature,press the button Macro, this will bring up an empty macro window in design mode. Click on the field in the column Macro, select the macro command from the list OpenForm... In the Form name field, we will indicate the form "Button panel". Close the macro and save it with the name “AutoExec”.

Now let's close our database and open it. This will automatically open the "Button Panel" form. To open the database without automatically opening this form, you must hold down the Shift key while opening the database.

Now we will cancel the automatic launch of the "Button Panel". To do this, rename the “AutoExec” macro to “AutoExec1.

The task is to implement an autostart function for your database.

LESSON OUTLINE

ON THE TOPIC OF:

« Working with macros inMS Access.

Technique for creating a macro»

Performed : Sumtsova L.S.,

teacher

Center for professional

technical education number 2

kharkov city

Kharkiv, 2016

The topic "Working with macros in MS Access ... Technique for creating a macro "

Purpose:

study theoretical material;

learn how to create macros using the Macro Designer;

develop attentiveness, accuracy, perseverance;

to educate the information culture of students.

annotation Lesson in learning a new topic. Students should be clear on how to create a macro inMS Access using the Macro Designer. The guys should figure out when it is more convenient to create a macro and use it.

Plan

Organizing time.

Learning new material.

Definition of a macro.

Macros in MS Access.

Macro constructor.

Creating a button to run a macro on the standard toolbar. Removing a button.

Lesson summary.

Homework

During the classes

1. Organizational moment, roll call, clarification of reasons for absence, appointment of attendants.

2. Actualization of basic knowledge.Frontal poll.

Defining a report in MS Access

Creating a report as a database object - how?

Report structure in Design mode

Print report

3. Learning new material.