Databases running Microsoft Access contain data with which they perform arithmetic and logical operations. In addition, when the user is working with the database, operations are performed when certain events occur, for example, pressing a button or changing a value in a form field. How these operations are performed, we consider 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 the database data or user interface.
There are two kinds of macros: events and data. The first view is responsible for actions in the user interface. It is attached to the elements of forms and the forms themselves. This type includes performing operations when a button is pressed, acquiring or losing focus, opening and closing a form, etc.
Based on these events, the system saves the record or field value, applies a filter, exports data, etc. Thanks to the new constructor, creating macros in Access has become a simple procedure available for beginning developers. This constructor has the advantages that its predecessors were deprived of:
- the system offers a catalog of microcommands with predefined actions and intuitive names from which the necessary is selected;
- a macro is used more than once, but is copied from a previously created one;
- teams use conditions;
- hot key combinations to simplify and speed up work;
- adding comments that later will help to understand the program;
- groups of commands that run one after another.
Macros
To add Access macros in the "Create" menu, select the "Macro". The system opens a new macro window with a list of commands available for adding to the right. In addition to the list of macros, here you will find macros already available in this database, and the functions of managing the creation of the procedure for execution.
Macros are located in folders according to the categories of actions they perform. Folder names display the area to which operations relate. These are interface operations, tasks of transferring data from or to a database, management of database objects, program windows and object elements.
To add a command to the macro, it is found in the directory and double-click on the name. You can simply select the desired operation in the macro drop-down list or drag it from the catalog into the macro field.
Macro Designer
Access macro development steps are performed in the constructor. The command is selected from the list and added to the macro window. Here the operation expands and asks for the input of parameters.
Some of these parameters are selected from the drop-down lists, for example, if an operation is performed on an object, the command has an argument to indicate this object, and the input field for this argument is a drop-down list with available objects of the current database. In some fields, the developer enters a parameter on his own, for example, indicates the name of the filter for the operation of opening the form.
The new constructor has added one more function. It is called IntelliSense and helps in entering expressions in macros in the Access database. When the user enters the value of the argument, the function prompts him with possible options, from which the necessary one is selected.
The designer allows you to add microcommands, swap them and delete, if necessary. To change the order of execution of commands in the upper right corner of the command are buttons with green arrows. In the first-order operation, the arrow indicates only down, in the last - only up; for commands in the middle of the list, movement is possible both down and up. Pressing the cross deletes the command from the macro.
Event macros
Macros on the Access form are added immediately to the control. When you create an element, a wizard automatically opens that defines the actions that the macro performs in response to an event from that element. A striking example is the action when you click on the form button.
In the wizard, select the category “Miscellaneous” - “Run Macro” and select the previously created object from the list of proposed ones. Thus, the program is attached to the form element and responds to an event from the side of this element.
The macro is attached to the control element already added to the form in the properties window. The tab “Events” contains a list of events for which it is possible to assign a sequence of commands. For the desired item, select the macro from the drop-down list, which is to be executed.
The procedure starts in response to a change in focus, form update, keystroke and button movement, mouse pointer movement, etc. The list of events depends on the control.
Data macros
In Access 2010 and later, data macros are available. They are caused by changes that occur in the elements of an object, or are independent and are called by name. Thanks to this type of macro, Access gains the capabilities of "advanced" DBMS (database management system).
Now it is possible to record the history of changes in the table row, check the parameters of the added record before inserting it - this way triggers work in the DBMS, for example, MS SQL Server. When editing individual fields, the system can generate and send notifications to users' emails.
These program units are added in table mode. On the tab "Work with tables" - "Table" is selected when the macro will fire: before or after changing / deleting, after insertion. Here you can add a named macro called by name. The list of macros here contains operations that are performed with data - errors in values, fields, table entries.
Conditions
It is often necessary that a macro does not work every time, but only if a condition is met. To set this condition, the If macro from the catalog is added to the Access macro. Here the condition is specified under which the next operation will be performed. If necessary, the command “Otherwise” or a whole additional block “Otherwise if” is also added.
In the above example, we close the "Applicants" form if the "Last Name" field is empty. If this is not so, we save the record and only after that close the form.
Thanks to the new macro designer in Access, starting with version 2010, the developer can flexibly configure the system and provide greater data security in the tables. We wish you good luck in mastering the new features of MS Access.