Using macros in a Microsoft Access database, Explore Access's ability to create macros. Macros in Access: concept, forms and creation Algorithm for creating macros in the ms access database

Databases running Microsoft Access contain data with which arithmetic and logical operations are performed. In addition, when the user works with the database, operations are performed when certain events occur, for example, clicking a button or changing a value in a form field. We will look at how these operations are performed in the article.

Macro concept

Access databases consist of objects for storing and processing values in tables. One of these objects is Access macros - program units used to make changes to database data or the user interface.

What is a macro?

A 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 that should be executed when the button is pressed.

In Access, you can think of macros as a simplified programming language in which code is created in the form of a list of required actions. When you create a macro, you select each action from a drop-down list and then enter the required information for it. Using macros, you can add functions to forms, reports, and controls without having to write code in a Visual Basic for Applications (VBA) module. A subset of VBA commands are available in macros, and most people find that creating a macro is easier than writing 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 standalone, i.e. a separate object in the database that is associated with an event OnClick buttons, or embedded directly in the event OnClick. In either case, when the button is clicked, the macro runs and opens the report. Such macros are usually called user interface macros.

The concept of data macros was first introduced in Access 2010. Using data macros, you can automate tasks and add functions directly to tables. Data macros and their actions are nested within specific table events, such as adding a new record to a table.

You can create a macro using the Macro Builder, which is shown in the figure below.

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

To open the UI Macro Builder, do the following:

On the tab Creation in the group Macros and code click the button Macro.

To open the Data Macro Designer, do the following:

Open any table in Datasheet view. On 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 Builder and how to create and test UI and data macros, see the related articles in See also at the end of this article.

Macro functions in Access

In earlier versions of Access, many popular functions could not be performed without writing VBA code. Current versions of Access have added new functions and macro actions that eliminate the need for code. This makes it easier to add features to the database and helps protect it.

Embedded Macros You can embed macros in any event exposed by a form, report, or control. An 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, those macros will also be present in the copy.

Increased security If the button Show all activities is not selected in the macro designer, then only those macro actions and RunCommand arguments that do not require trusted database status are available for use. A macro created using these steps will only run if the database is in disabled mode (where VBA execution is disabled). Databases that contain macro actions that are not included in the trusted list (or databases with VBA code) must be explicitly granted trusted status.

Error Handling and Troubleshooting Access provides macro actions including OnError(similar to the "On Error" statement in VBA) and ClearMacroError, allowing you to perform certain actions if an error occurs while the macro is running. In addition, the macro action SingleStep allows you to switch to step-by-step execution mode from any point in the macro to view its operation step by step.

Temporary Variables Three macro actions ( 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 be used to pass data to and from VBA modules.

More information about macro actions

While you are working in the Macro Designer, you can get more information about an action or argument by clicking it and reading the description in the box in the lower right corner of the Macro Designer window. In addition, each macro action has a Help article associated with it. To learn more about an action, select it in the list of actions and press F1.

It is not possible to record a macro in Access as, for example, in Word, but in Access you can create a macro in the Macro Designer window or in the Visual Basic for Application (VBA) editor window.

Macros in Access are primarily created to handle events. An event is any action that is recognized by an object and to which a response can be determined.

Events to which you can assign macros include, for example, pressing a button to display a query, form, report, etc. An example of creating a macro would be creating a button to call the "Open Query" macro in the "Queries" subbutton form in the Student_Achievement database.

Thus, the main purpose of macros is to create a convenient application interface in which forms and reports would open when you click buttons in forms or on toolbars. So that when you open the application, not the Database window is displayed on the screen, but the main button form, which is created for the purpose of navigating through the database, i.e. as the main menu of the database.

The article Macros in the Access 2003 database discusses the creation of macros in the Macro Designer window, which is opened with the Create command in the database window on the Macros tab. The Macro Constructor window is divided horizontally into two parts: the descriptions panel and the arguments panel. The Descriptions panel displays two columns by default: a column for entering macro commands and a column for entering notes. A macro is an instruction that defines the actions to be performed in a macro.

In the Macro column row, you can select the required macro command from the drop-down list, and in the Note column row, you can describe the action performed by the macro command.

Rice. 1.

The Macro Arguments panel, which appears in the Macro Designer window after entering a macro, allows you to select the required arguments from the list that appears for this macro. Macros can contain either a set of macro commands or a group of macros.

To quickly create a macro in the Macro Designer window, you can use the following technology. The first thing you need to do is open the database and select (highlight) the required object in the database window. Then, in one of the ways (from the toolbar, from the menu bar, or from the database window), open the Macro Constructor window and use the mouse to move the selected object from the database window to the macro command line in the Macro Constructor window.

To create a macro that opens the “Disciplines” table, you need to move this table with the mouse from the DB window to the Macro command line of the Macro Constructor window. In addition, you can set the macro arguments to Mode - View, Data Mode - Read Only.

Rice. 2.

You can create macros in a similar way: Open Form, Open Query, Open Report, or Run Macro.

Rice. 3.

To run the created macros from the toolbar, you need to create a button on one of the toolbars. Let's consider creating a button to run the Open_query macro on the standard toolbar, which opens in the application window by default. First, you need to select a macro in the database window on the macros tab and drag it to the toolbar. An Open_query button will appear on the toolbar.

Rice. 4.

To remove a button from the toolbar, right-click on the toolbar and select Settings from the context menu. In the Settings dialog box that appears, on the Commands tab, in the Categories list, select the line All macros. Drag a button from the toolbar with the left mouse button into the Commands list of the Settings dialog box. As a result, the Open_query button will be removed from the toolbar.

Rice. 5.

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 recorded automatically, so there is no need to know Visual Basic. Macros program routine operations, making it convenient to create main menu application system and submenus, organize data search, etc.

Typically, macros are associated with the “event response” of Access 2007 controls. Events determine what happens when a button is clicked, a database field is entered or exited, or a form or report is opened or closed.

Creating buttons to launch forms

Let’s create a “Button Panel” form to call some forms Buyer, Product And Order our database. To do this, select the tab Creation on the DBMS toolbar, and then click the button Macro . A window will appear on the screen Macro1. It contains a table with two fields. The first of them is intended for macro commands, the second for arguments.

Click on the bookmark on the database panel Form, select the “Buyer” form and drag it into the first row of the macro definition table into the column Macro command 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 macro commands OpenForm, and then in the parameters (at the bottom of the screen) specify the “Buyer” form.

Now let's create a form to place the buttons. Select a tab Creation in the database window , then the command Blank form. Switching to design mode, drag the “Buyer” macro we created onto the form. There will be a button saying Buyer. When viewing the list, you will notice that in the property Pressing a button the macro name “Buyer” is recorded.

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

Exercise

Create buttons in the “Button Panel” form Product And Order for the “Product” and “Order with combo box” forms.
Add the title Basic data entry forms to the “Button Panel” form.
Set different design types for form elements.

Macros. Automatically run a macro when opening a database

When opening an Access database, it is convenient to display a certain form, for example, a panel of application control buttons. Let’s make sure that when we open our database, the form “ Button panel”.

To do this, select the tab Creation, press the button Macro, This will cause an empty macro window to appear in design mode. Click on the field in the column Macro command, select a macro command from the list OpenForm. In the Form Name field, specify the “Button Panel” form. Let’s 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 button while opening the database.

Now let’s cancel the automatic launch of the “Button Panel”. To do this, rename the macro “AutoExec” to “AutoExec1.

Exercise is to implement an autorun feature for your database.

Creating macros inAccess 2007

A macro is a set of macro commands that is created to automate frequently performed tasks.

Macro constructor

Macros are created in the Macro Constructor window specially designed for this purpose. To create a macro you need to select the tab Create and in the drop down button Macro select a team Macro . A macro designer window will open, which consists of a description panel located at the top of the window and an argument panel at the bottom. The default description panel contains three columns Macro command , Arguments And Note .

In the Macro command field, you can select the required command (OpenTable, OpenQuery, OpenReport, RunMacro, etc.) by clicking the mouse button. Otherwise in line Macro command you can simply drag and drop any database object (table, form, etc.).

After selecting a macro command, lines may appear in the arguments panel of the macro designer window to set the values of the arguments of the corresponding macro command. The set of lines in this panel depends on the specific macro. The specified argument values also appear in the second column Arguments description panels.

Column Note serves to enter a comment that describes the action being performed.

Let's look at a simple way to create a macro. For example, let's develop it to open a table Groups databases Student .

You have already opened the window Macro constructor . Now use your mouse to drag the table Groups from Transition areas to column Macro command . As a result, the Open table macro command will appear in it, and Access will automatically fill in the fields of the argument panel (Fig. 1).

In the Data Mode field, set the value Read only , which will make the entries in this table unavailable for editing.

To enter the value of a macro command argument, you can select the argument from the list or enter an expression in its field. To the right of the fields of some arguments there is a builder button, in the window of which various expressions are entered. The expression must be preceded by an equal sign (=), except for the argument Expression macros SetValue and argument Number of repetitions macros Run Macro .

A macro can contain several macro commands that are executed sequentially. Let's add to the macro command we created, which opens the Groups table in Read-Only mode, add a macro command that informs the user that the table Goods available for viewing information only.

Execution sequence:

Go to the next line of the Macro Designer window and in the column Macro command select macro Message .

In the field Message in the argument bar enter the text " Data available only For viewing " Argument Signal serves to determine whether the message output will be accompanied by an audible signal. In argument Type select the dialog box type. Specify, for example, the value Warning !. In the argument field Heading to set the title text for the message box, enter the text "About data" .

Save the macro as Open groups .

After running the macro, the Groups table opened in view mode and the message shown in Fig. 2 will be displayed.

Let's look at another example. Let's say you need to create a macro that opens a form List .

Open the macro designer window and drag the form from the database window into it List .

Argument Form name contains a list of all database forms.

Argument Mode defines the mode in which the form should be opened. It can take the following values: Form, Designer, View, Table, etc.

Argument Filter name used to select and/or sort records in a form. This can be a query or a filter written as a query.

Argument Selection condition defines the condition for selecting records displayed in the form. The condition is an expression.

Argument Data Mode defines the way to work with data and can take one of the values: Add, Change or Read Only.

Argument Window mode defines the window type: Normal, Invisible, Icon and Dialog Window.

In the field Name filter using the previously created filter SR-11 , enter text SR-11 .

On the new macro line, select the macro Message , and in the argument bar enter the message text: Filter group 11 . Set the message dialog box type by selecting Informational argument Type .

Save the macro as 11 group .