【 Create Macros in Access 】 Step by Step Guide ▷ 2022

macros They are an ideal tool that offers to automate a large number of tasks and that allow you to add functionality to , and controls. It could be said that they represent a simplified programming language integrated into Access, which is written through the creation of a of actions that can be performed.

When a macro is createdthe next thing is select from a drop-down list the action you want to be executed and, finally, fill in the necessary information for each type of action. In short, they are perfect for simplifying and automating processes.

In this post, we will tell you everything you need to know about How to create a macro to automate any task in Microsoft Access from scratch?. Likewise, we will teach you what types of tasks can be automated, and some security tips to use them without risk.

What tasks can be automated in Access with a macro?

With the help of Access macros, it is possible chain and organize different instructions in order to facilitate the generation of systems and expedite routine work. First of all, it should be clarified that Access databases are made up of a series of objects such as forms, queries, , reports, etc Over time, the database may be very large, so it is convenient to choose to automate processes and thus speed up the execution of certain tasks.

For this there are macros in Access, which are very similar to those that can be recorded in word and other programs Office. However, in this case, the macros They are made up of a series of direct instructions that are called “actions”., who perform all kinds of tasks according to the order that is indicated. So, it can be said that it is a really beneficial tool for all types of users. As to What tasks can be automated in Access with a macro?,

We summarize them in the following list:

• Search for certain values in the forms base table.
• To transfer records.
• Add a menu.
• run a query that involves a record filter.
• Rename of any section of a database.
• maximize or minimize window.
• move or change the size of a window.
• To close window.
• Undo a record.
• Establish a menu item.
• Open a query.
• Open A table.
• Select an object.
• To print an object.

These are some of the actions you can set to run through an Access macro. However, the list is very extensive. Within the program you can choose the one that best suits your needs.

Access Macro Generator What is this tool and how does it work?

As the name implies, is the window through which it is possible to create a macro in Access. With the update that was made from the version of 2010, A number of changes were made that significantly improved the builder interface to make it much more user friendly. In this way, it is much easier to create, modify and share the macros of Access.

In order to display the UI Macro Builder you have to:

• go to group macros and tab code To create.
• Once there, click on Macro.

To display the Macro Builder for data macros, follow the procedure below:

• Open a table in Datasheet view.
• inside tab Table, specifically in the groups past events Y Later events, click on one of the event commands. For example, you can click Before Change or After Insert.

Apart from all that, it is necessary that you know some of the main features offered by the Access macro generator.

These are:

• Stock catalogue: all actions are organized by type and are easily searchable.
• IntelliSense: when the user types an expression, IntelliSense it suggests possible values ​​and allows you to select the correct one if necessary.
• Keyboard shortcuts: allows you to use key combinations for much easier and faster macro writing.
• Program flow: with this it is possible to create much more readable macros with comment lines as well as action groups.
• Conditional instructions: Thanks to this feature, the execution of more complex logic is allowed with compatibility with if…
• Easier Sharing: this means that you can copy a macro and then paste it with XML in an email, a blog, a code sample website, or even in a newsgroup.
• Macro reuse: the action catalog shows other macros you’ve created and allows you to copy them to the one you’re working on.

Learn step by step how to create a Macro in Access from scratch

As we indicated above, through macros you can automate tasks that are usually repetitive. In Access You have the possibility to create three types of macros: Standalone, embedded or a sub macro.

We explain the step by step in each case:

Independent

Through this procedure, a separate macro object is created which is displayed in macros within the navigation pane. Its use is recommended when you want reuse the macro in many places in the application. By calling it from Macros, you avoid duplicating the same code in multiple places.

To create an independent macro you have to:

• go to group macros and codeinside the tab To create.
• Click on Macro. Then, the macro builder will open.
• Inside of Quick-access tool barpress on Save.
• In the frame Save as, type whatever name you want for the macro. Then tap on To accept.
• Choose the action you want run with your macro in panel Stock catalogue.
• Add said action to your macro in the list Add new action.
• Fill in the arguments. Most actions require at least one argument.

embedded

Using the embedded macro creation procedure, you create a macro that will be embedded in an event property of an object. Note that it doesn’t appear in the navigation pane, but can be called from events like Loading either On click. Keep in mind that the macro will become part of the form or report object, so it is recommended to use embedded macros to automate specific tasks of a particular report or form.

The step by step in these cases is:

• Right-click on the form or report that will contain the macro in the navigation pane. Then tap on presentation view.
• In case the Property Sheet is not displayedyou must press F4 to show it.
• Click the control or section which has the event property in which you want to insert the macro. Similarly, you can select the section or control with the help of the dropdown list located under selection typeat the top of the property sheet.
• Enter the tab Eventin the task pane property sheet.
• Select in the property box for the event you want to trigger the macro on. For example, if you want a macro to run when a command button is clicked, press in the property box on click.
• In case the property box has the words “Embedded Macro”, it means that a macro has already been created for this particular event. You should know that it is possible to edit it.
• If the box contains the words “Event Procedure”means that a procedure was created Visual Basic for applications (VBA) for this event. Before you insert the macro into the event, you must remove the procedure.
• To do this, you can delete the words “Event Procedure”, but you need to examine that procedure to ensure that removing it doesn’t break necessary database functionality. In such cases, you can re-create the functionality of the procedure VBA via an embedded macro.
• Tap on the button Trigger.
• If the dialog is displayed choose generatormake sure that the macro generator. Subsequently, press To accept.
• The Macro Builder will open.where you can add the actions you want.

submacro

Instead of creating multiple macros individually, you can choose to create submacros. A submacro is named by its name in the macro’s actions RunMacro or in onError. To add a submacro block to a macro, you must do it in the same way as a normal macro action. Once you’ve added a sub-macro block, you can drag macro actions onto it or select a series of actions from the Add New Action list displayed inside the block.

Some aspects to consider regarding this topic are:

• It is possible to create a block of submacros selecting one or several actions, pressing with the secondary button. Then you must select Make the submacro block.
• Submacros must always be the last blocks of a macro, that is, you can’t add any action (except more submacros) below a submacro. If you run a macro that only contains submacros without specifically naming the submacro you want, only the first one will be executed.
• To be able to name a submacrouse the syntax below: Submacroname.

Macro safety tips to use them safely

There are some basic tips that you can take into consideration when creating macros to use them without any risk. First you have to know that in previous versions of Access there were many functions that could not be done without writing code VBA.

Nevertheless, newer versions have added new features and macro actions that help eliminate the need to write code. What this does is make the process of adding functionality to a database easier, and it reduces the margin of error, which means that it makes the procedure much safer.

Some of the new features that we recommend you take advantage of are:

embedded macros

Through them, the user has the ability to embed macros in any of the events provided by a report, control or form. These are not available in the Navigation Pane, because they become part of the form, report, or control in which it was created. Other than that, if you create a copy of a form, control, or report that contains embedded macros, the macros will also be present in the copy.

Greater security

If the button show all actions is not highlighted in the Macro Builder, the only RunCommand actions and arguments that are available for use are those that do not require a trusted state to run. A macro that has been generated through these actions will be executed even when the database is in disabled mode.

When they are databases that contain macro actions that are not present in the trust list (or in a database that have VBA code)it will be necessary Explicitly grant them the…