Are you trying to make a database in Access for end user, mainly for intuitive and user-friendly environment? Well for such task making use of macro is the best option. As, Access macros are specifically designed to automate tasks and for adding functionality to your forms, reports, and controls within an Access database.
Apart from this, Access database macros function is quite handy for both end users and administrators. It also allows administrator to hand-off day to day tasks performed on the database to others. To use this Access Macro Function end-user doesn’t need to be a programmer or having knowledge of VBA code.
Just follow down this post as it contains complete information on what macros are in Access database. And how they can save your time by automating tasks which you perform very often by yourself. This post also explores the fundamental behind creation and usage of macros.
So, go grab it….!
What Is Macro In MS Access?
MS access macro is a set of action which run automatically and also on demand. Basically, it is used to perform repetitive task, which saves time and effort of the users.
You can create macro in Access either for any single tasks or for operating multiple tasks at once. The best thing about this MS Access Macros, is that you can perform many tasks one after other just by clicking down a single button. It is configured to run automatically as soon as the database is opened.
Macros are designed to run when any particular event take place. For this you just need to specify that particular event at the time of macro creation.
Mainly Macro are created in two different ways, one which contained in macro objects is called standalone macros. This can be embedded within the event properties of controls, reports and forms.
Second one is embedded macro. You can use embed macros within any event given by reports, control or forms. Embedded macro is hidden in the navigation pane. So, you can say it ‘s a part of the report, form or control in which it is created.
What Is The Use Of Macros In MS Access?
In previous access version, there is no such option to use Access function without writing VBA code. But in Access latest versions, many new features and macro actions are added which relive you from writing such bulky and complicated VBA code. So, now it’s been so easy to add functionality in your Access database which will make your access database more secure and convenient to use.
- Increased security:
Previously built macro also run when the Access database is in disabled mode (i .e when the VBA restricts to run). As, these actions doesn’t require any trusted status to get run.
But now the database which contains the macro actions which are not on the trusted list or database having VBA code requires to take explicitly granted trusted status.
- Error handling and debugging:
Access gives macro actions, including OnError (similar to the “On Error” statement in VBA) and ClearMacroError, which helps in performing actions meanwhile the occurrence of error during macro execution.
Along with that the SingleStep macro action helps you to switch into a single-step mode in your macro at any point of time. So, that you can see how your macro actually works at a time.
- Temporary variables:
Access offers three macro actions SetTempVar, RemoveTempVar, and RemoveAllTempVars which helps you to create and make use of temporary variables within the macros.
These temporary variables can be used in conditional expressions to control execution of macros or to migrate data to or from report/forms. It can also be used for other purposes in which temporary storage places is required for the value. You can access these kinds of variables in VBA also. So, it can also used for communicating data to or from VBA modules.
Ways To Create Macros In MS Access Database
Way 1: Create A Simple Macro
in this section we will learn to create a macro which opens up a form automatically whenever a database is opened. Well we are having a previously created form so we are using that only for this purpose. In this form user can enter new albums into the database.
- From the Create tab on the ribbon, tap to the Macro This will open a blank macro, which is ready to use.
- From the combo box, select OpenForm. Or else you can make double tap on the OpenForm from action catalog present on the right side of the screen.
- From the form Name combo box, choose Set view to form & window mode to normal
- From the combo box, choose GoToRecord. Alternatively, make double tap on the GoToRecord from the Action Catalog present at the right side of the screen.
- From the Recordcombo box, now choose the new option.
- Now it’s time to save your macro, for this you just need to make right click on macro’s tab and choose the save option from the contextual menu.
- After then assign name AutoExecto your macro and tap to the OK option.
It’s compulsory to give this name AutoExec. As, it is a special name which automatically run your macro whenever your database is get opened.
Your macro is now created so either you can run it or try some other ways to create macros in Access.
Way 2: Create A Standalone Macro
In this section we will learn to create standalone macro object which appears within macros in navigation pane. Standalone macros are very helpful especially when you need to save yourself from applying the macro in several places of your database.
As this macro type offers macro calling function using other macros. thus, this prevents the duplication of macro in multiple places within your database.
- From the Create tab, choose the Macros & Code group and tap to the Macro
- this will open the macro builder on your screen.
- Now on the Quick Access Toolbar, tap to the Save
- In the opened Save As dialog box, assign name for your macro and hit to the OK
Way 3: Create An Embedded Macro
The third way of macro creation is embedded macro. In this method the created macro is embedded within the object’s event property. This type of macro won’t appear in navigation pane so events like On Load or On Click is used to call it.
- From the Navigation Pane, make a right-click to the form or report which contains the macro, and then hit the Layout View.
- If in case the property sheet is not appearing. Then hit the F4 key to display it.
- Tap to the control or section which contains of your event property in that you need to embed the macro. Besides that, you can also choose the section/control or the complete form/report by making use of the drop-down list present within the Selection Type of property sheet.
- Now tap to the Event tab, in the property sheet task pane.
- Tap to the property box for event in which you want to target your macro.
Suppose, you are making a command button, which is when tapped it will run the macro automatically. For this you need to tap to the On Click property box.
- If in the property box, you will see word like [Embedded Macro], then it means that a macro is already created for that specific event. So now you can edit this macro by following with the remaining steps of this procedure.
- Appearance of words [Event Procedure], in the property box, also indicates that Visual Basic for Applications (VBA) procedure is already been made for the specific event.
- Before embedding macro in the event, you firstly need to remove the procedure. For this, just delete the words [Event Procedure], but firstly check out the event procedure so that removing this will, don’t break any database functionality.
- Whereas in some cases you can reform the functionality of VBA procedure by making use of the embedded macro.
- Hit to the Build button .
- If in case the Choose Builder dialog box opens on your screen. Be sure that the Macro Builder is kept selected, and then hit the OK
- After performing all this tasks Access will opens the Macro Builder.
How To Run The Macro In Access?
After getting knowledge on how to create macros in access. Now it’s time to learn how to run macros in Access. to test this perfectly, make sure to run this when the database is opened.
But to test it properly, we need to make sure that it will run when the database is opened. Therefore, we’ll need to close the database and open it.
- from the ribbon, tap to the File After then tap to the Close option from Menu.
- Now one more time, tap to the File tab on the ribbon.
- After then tap to the open option from the menu.
- Now hit the database to open it. In the shown image we have clicked on the Music.
- This will open the database and your form will automatically appears.
- Not only this, but the form will open with blank record. Now you can enter your record in the form.
How To Recover Corrupt/Deleted Macros In Access?
It’s very common to render data disaster in Access database which at the end leads to data loss if no corrective measures are taken at the right time. If you too get stuck in such situation, then make of a specialized recovery application like Access Repair And Recovery Software that can fix any kind of issue conquered in the accdb or mdb databases file.
This software effectively repairs corrupt or deleted Access database items like tables, macros, forms, reports, Indexes and also restores all objects including deleted records. This Access recovery tool firstly scan the corrupt access database and after performing the repair process save it in a separate Microsoft Access file.
After reading the complete post you must have got enough knowledge on MS Access Macros, it’s uses, how to create macros and steps to run macros. Now it’s time to try those steps to create macros in Access. So, go through it…..!
If you get stuck in any issue meanwhile performing the following steps then let us know by sharing your problems with us in our FAQ section.