How to Creating an embedded macro

image13An embedded macro is a macro that is not displayed in the Navigation Pane under Macros; it is stored in the event properties of forms, reports, or controls. This can make your database easier to manage because you don’t need to monitor separate macro objects that contain macros for a form or a report. To create an embedded macro, follow the steps below:

  1. Open the form or report that will contain the macro in Design view or Layout view. To open a form or report, right-click it in the Navigation Pane, and then click Design View or Layout View.image7
  2. If the property sheet is not yet displayed, press F4 to display it.
  3. Click the section that contains the event property in which you want to embed the macro. To select the entire form or report, choose Form or Report from the drop-down list at the top of the property sheet.image8
  4. In the property sheet, click the Event.image9
  5. Click the event property in which you want to embed the macro, and then click next to the box.image11
  6. In the Choose Builder dialog box, click Macro Builder, and then click OK.image12
  7. Choose an action from the drop-down list.
  8. Fill in any required information for the action.
  9. Repeat steps 7 and 8 until your macro is complete.
  10. Click Save to save your macro.

The macro will run each time the event is triggered. Access lets you create a macro group as an embedded macro. However, only the first macro in the group runs when the event is triggered. Succeeding macros are disregarded unless they are called from within the embedded macro itself (for example, by the OnError action).

Example: Embedding a macro in the On No Data event of a report

When you run a report and its data source does not have any records, the report shows an empty page. You may prefer a message box shown instead, and that the report not be displayed at all. An embedded macro is the perfect answer for this circumstance.

  1. Open the report in Design view or Layout view.
  2. Press F4 to display the Property Sheet.
  3. Click the Event tab on the property sheet.
  4. Click On No Data.
  5. Click .
  6. In the Choose Builder dialog box, click Macro Builder, and then click OK.
  7. Add MessageBox as the first action.
  8. Enter the following arguments.image13
  9. Add CancelEvent as the second action.image14
  10. .Close the macro. You should be able to see [Embedded Macro] in the On No Data.image15
  11. Save and close the report.

When you run the report and no records are found, the message box will be shown. If you click OK in the message box, the report will cancel without displaying the empty page.

Edit a macro

To insert an action

Click the action below which you want to insert the new action, then select from the dropdown option an action that you want to be inserted.

To delete an action

Click the action you want to delete, then clickimage16

To move an action

Click the action that you want to position, drag and drop it at the desired place or you may position it by clicking image17 Or image18

Run a macro

Standalone macros can be run in any of the following ways: directly, in a macro group, from another macro, from a VBA module, or in response to an event that occurs on a form, report, or control. Embedded macros can be run by clicking the Run Macro button which can be found on the Design tab while the macro is in Design view; otherwise, the macro will only run when its associated event is triggered.

Run a macro directly

To run a macro directly, do one of these steps:

  • Locate the macro in the Navigation Pane, then double-click the name.
  • On the Database Tools tab, in the Macro group, click Run Macro, then select the macro from the list, and then click OK.

Run a macro that is in a macro group

To run a macro that is in a macro group, do one of the following:

  • On the Database Tools tab, in the Macro group, click Run Macro button, and then select the macro from the list. Access includes an entry for each macro in each macro group, in the format macrogroupname.macroname
  • Click OK.
  • Specify the macro either as an event property setting on a form or report or as the Macro Name argument of the RunMacro action. Refer to the macro by using this syntax: macrogroupname.macroname.
  • Run a macro that is in a macro group from within a VBA procedure by using the RunMacro method of the DoCmd object, and by using that syntax to refer to the macro.

Run a macro from another macro or from a VBA procedure

Add the RunMacro action to your macro or VBA procedure.

  • To add the RunMacro action to a macro, select RunMacro from the action list, then set the Macro Name argument to the name of the macro that you want to run.
  • To add the RunMacro action to a VBA procedure, add the RunMacro method of the DoCmd object to your procedure, and then specify the name of the macro that you want to run.

Run a macro in response to an event on a form, report, or control

While you can now insert macros directly into event properties of forms, reports, and controls, you can also still create stand-alone macros and then bind them to events.

  1. After building your stand-alone macro, open the form or report in Design view or Layout view.
  2. Open the property sheet for the form or report, or for a section or control on the form or report.
  3. Click the Event tab.
  4. Click the event property for the event that you want to trigger the macro.
  5. In the drop-down list, click the name of the standalone macro.
  6. Save the form or report.

Creating a standalone macro

Introduction to Macros – Part 2image2

A standalone macro is a macro that is displayed under the Macro in the Navigation Pane.

To create a standalone macro, take the following steps:

  1. Click the Create
  2. Click Macro in the Macros & Code
  3. You should be able to see the Macro Designer by now. To add an action, type in the macro action or click on the drop-down menu to display the list from which you choose the action that you want to use.
  4. To add more actions to the macro, move to another action row, and then repeat the previous step. Access carries out the actions in the order in which you list them.
  5. Click Save when your done.

Creating a macro group

Create a macro group for several related macros.

  1. Click the Create
  2. Click Macro in the Macros & Code
  3. Once the designer is displayed, select Group from the drop-down list.image5
  4. Input the name you want to use for the macro group in the text box.image6
  5. Start adding the actions that you want to use. Input the macro action or use the drop-down list to select an action.
  6. Fill in the required information for the action.
  7. To add more actions, repeat steps 5 and 6.
  8. Click Save.

What is the new Macro Features from the release of Access 2010

image2

In previous versions of Access, many usually used functions could not be executed without writing VBA code. With the release of Access 2010, new features and macro actions have been added to help remove the need for code. This makes it less demanding to add functionality to your database and helps make it more secure.

  • Embedded macros: You can now embed macros in any of the events given by a form, report, or control. An embedded macro is not displayed in the Navigation Pane; it becomes part of the form, report, or control in which it was created. If you make a duplicate of a form, report, or control that has embedded macros, the macros are also present in the duplicate.

 

  • Increased security: When the Show All Actions button is not highlighted in the Macro Builder, the only macro actions and RunCommand arguments that are accessible for usage are those that do not require trusted status to execute. A macro built with these actions will execute even when the database is in disabled mode. Databases that contain macro actions that are not on the trusted list need to be explicitly granted trusted status.

 

  • Error handling and debugging: Access 2010 provides new macro actions, which include OnError and ClearMacroError, that enable you to run certain actions when errors occur while your macro is running. Moreover, the new SingleStep macro action lets you enter single-step mode at any point in your macro, so that you can see how your macro performs one action at a time.

 

  • Temporary variables: Three new macro actions — SetTempVar, RemoveTempVar, and RemoveAllTempVars — let you make and use temporary variables in your macros. You can use these in conditional expressions to control running macros, or to pass data to and from reports or forms, or for any other purpose that needs a temporary storage place for a value.

    Create a macro

    As of Access 2010, a macro or macro group can be held in a macro object, or a macro can be inserted into any event property of a form, report, or control. Embedded macros become part of the object or control in which they are inserted. Standalone macros are viewable in the Navigation Pane, under Macros, but embedded macros are not.

    Macro Designer features

    Access 2010 (including the later versions too) has a new Macro Designer that allows effortless creation of complex macros at the same time decreasing coding errors.

    The designer is used to build the list of actions that you want to execute when the macro runs.

    To display the Macro Designer, click Macro on the Create tab.

    When you first open the designer, Access will display a drop-down list from which you select an action.image4 

The following table shows the commands that are available on the Design tab of Macro.

Group Command Description
Tools Run Performs the actions listed in the macro.
  Single Step Executes the macro one at a time.  When you click the Single Step and then click Run, the Macro Single Step dialog box displays, and you will see three command buttons: Step, Stop All Macros, and Continue. Once you click Step command button, the macro executes from one macro to another. To stop all the macro actions, click Stop All Macros command button. To continue to run the macro, click Continue command button.
  Convert Macros to Visual Basic Use this to convert a macro to Visual Basic code.
Collapse/Expand Expand Actions Click this when you want to display the actions of a macro.
  Collapse Actions This works opposite to the Expand Actions tool.
  Expand All It expands the macro actions and the collapsed block in the Macro Designer.
  Collapse All It collapses the macro actions and expanded block in the Macro Designer.
Show/Hide Action Catalog Click this if you want to display the Action Catalog pane.
  Show All Actions When selected, all the macro actions are displayed in the drop-down list of the Action column. If you choose a macro action from this list, you may need to grant the database explicit trust status before you can run the action.

 

What is a macro?

image1

In this article, you will learn all about macros — what they are and how they automate tasks for users to save time. It examines the essentials of making and using macros.

A macro is a tool that lets you automate tasks and integrate functionality into your forms, reports, and controls. For instance, if you incorporate a command button to a form, you link the button’s OnClick event to a macro. The macro will have the commands that you want the button to perform every time it is ticked.

In Access, it is useful to consider macros as a simplified programming language that you compose by building a list of actions to perform. When you create a macro, you choose every action from a drop-down list then fill in the needed information for every action. Macros allow you to add functionality to forms, reports, and controls without writing code in a Visual Basic for Applications (VBA) module. Macros give a subset of the commands that are accessible in VBA, and most people find it simpler to construct a macro than to write VBA code.

For instance, assume that you need to begin a report right from one of your data entry forms. You can add a button to your form then make a macro that starts the report. You can either have a standalone macro, which is then bound to the OnClick event of the button, or embed the macro directly into the OnClick event of the button itself. In any case, when you tap the button, the macro runs and starts the report.

You create a macro by using the Macro Builder as shown in the illustration below.image1

On the Create tab, in the Macro & Code group, click Macro.image2

Understand Macros

The term macro is frequently used to denote independent macro objects. A macro object can contain several macros— referred to as a macro group. A macro group is shown in the Navigation Pane as a single macro object though it contains multiple macros. Unquestionably, it is possible to make each macro in a separate macro object, but it bodes well to gather some related macros into a single macro object. The name in the Macro Name column distinguishes every macro.

A macro comprises individual macro actions. Most actions involve one or more arguments. Moreover, you can assign names to every macro in a macro group. You can also add conditions to control how each action is run. The succeeding sections will explain every feature in detail.

Macro names

If your macro object contains just a single macro, macro names are needless. You can simply refer to the macro by the name of the macro object. But in the case of a macro group, you need to give a unique name to every macro.

Arguments

An argument is a value that gives information to the action, for example, what string to show in a message box, which control to operate on, and so on. Some arguments are compulsory while others are discretionary.

When you choose the data macro that you wish to run in the macro designer, Access identifies if the data requires parameters. If it does, it displays text boxes where you can type in the arguments.image3

Conditions

A condition determines specific criteria that must be met before an action will be run. You can use any expression that evaluates to True/False or Yes/No. The action will not be performed if the expression evaluates to False, No, or 0 (zero). If the expression evaluates to some other value, the action will be executed.

One condition can control two or more actions. You can achieve this by writing an ellipsis in the Condition column for each succeeding action that you need the condition to apply to. If the expression evaluates to False, No, or 0 (zero), none of the actions are executed. If the condition evaluates to some other value, all actions are run.

In Access 2010, the Macro Designer layout looks a lot like a text editor. The three columns are replaced with actions and conditional statements presented in a familiar top-down format that is used by programmers.

Use this expression to carry out the action If:
[City]=”Florida” Florida is the City value in the field on the form from which the macro was run.
DCount(“[OrderID]”, “Orders”)>20 There are more than 20 entries in the OrderID field of the Orders table.
DCount(“*”, “Order Details”, “[OrderID]=Forms![Orders]![OrderID]”)>2 There are more than 2 entries in the Order Details table for which the OrderID field of the table matches the OrderID field on the Orders form.
[ShippedDate] Between #1-Oct-2017# And #1-Nov-2017# The value of the ShippedDate field on the form from which the macro is run is no earlier than 1-Oct-2017 and no later than 1-Nov-2017.
Forms![Products]![UnitsInStock]<4 The value of the UnitsInStock field on the Products form is less than 4.
IsNull([FirstName]) The FirstName value on the form from which the macro is run is null (has no value). This expression is equivalent to [FirstName] Is Null.
[Country/state]=”US” And Forms![SalesTotals]![TotalOrds]>50 The value in the Country/state field on the form from which the macro is run is US, and the value of the TotalOrds field on the SalesTotals form is greater than 50.
[Country/state] In (“Florida”, “California”, “Texas”) And Len([PostalCode])<>5 The value in the Country/state field on the form from which the macro is run is Florida, California, or Texas, and the postal code is not 5 characters long.
MsgBox(“Confirm changes?”,1)=1 You click OK in a dialog box in which the MsgBox function displays “Confirm changes?”. If you click Cancel in the dialog box, Access ignores the action.
[TempVars]![MsgBoxResult]=2 The temporary variable that is used to store the result of a message box is compared to 2 (vbCancel=2).

To make Access temporarily skip an action, input False as a condition. Temporarily skipping an action can be helpful when you are troubleshooting a macro.

Macro actions

Actions are the essential building blocks of macros. Access gives many actions from which to pick, enabling a wide assortment of commands. For instance, some of the more frequently used actions can open a report, search a record, show a message box, or apply a filter to a form or report.

Create a Report in Microsoft Access 2016 – Part 2

As a reminder, Please review Create a Report in Microsoft Access 2016 –Part 1

Report Design

Reports can be designed and formatted in the same way as we do with forms. When you create a report, four new tabs show up in the Ribbon particularly for working with reports.image12

Report Header and Footer

The report header and footer are shown just once in the report.

The report header is shown at the topmost part of the first page, and the report footer is shown at the lowest portion on the last page.

In the example above, the colour name will only be shown once — at the top of the first page of the report.

Page Header and Footer

The page header and footer are shown on every single page of the report. If for example, a report has 10 pages, the page header and page footer will be shown on all 10 pages. Continue reading “Create a Report in Microsoft Access 2016 – Part 2”

Create a Report in Microsoft Access 2016 – Part 1

image2Access has quite a few processes for generating reports. Any user can create a report quickly in Access, whether they’re a beginner or an expert.

With MS Access 2016, you can create a report from a table or query with the click of a button. You may also make use of the Report Wizard to guide you through the method. Similarly, more experienced users can begin with a blank report and build it exactly as they want.

Create a Report based on a Query

We will make a report in view of the query we created earlier — Women’s Tops.

This query will require user input which would tell the query the colour they would like to find from the Women’s Tops list. However, the query does not show the cloth colour in the output, so we will need to show this parameter on the report as well. If not, the reader would not know the colour of the garment. Continue reading “Create a Report in Microsoft Access 2016 – Part 1”

Microsoft Access Database Forms – Part 2

As a reminder, please review Microsoft Access Database Forms – Part 1

Formattingaccessdatabasetutorial 11

The Form Layout Tools tab contains the form customisation fields. Selecting it will display a screen like an image above. Now, feel free to play around with some of the settings to learn their role in form design and the stylish choices available to you. You may also visit the Home tab and find the View drop-down option where you can cycle through the form view options available.accessdatabasetutorial 12

Form Wizard Makes Form Creation Uncomplicated

Understanding the societal desires of people in order to constantly boost efficiency, Microsoft has incorporated a fairly helpful Form Wizard to enable the skipping of the grubby, hands-on detail Continue reading “Microsoft Access Database Forms – Part 2”

Microsoft Access Database Forms – Part 1

accessdatabasetutorial 7Access Forms can make data entry tasks handy for database users. Make an easy to understand database environment without uncovering the inner-workings of a database and give yourself peace of mind that your data is protected.

This instructional exercise will cover the MS Access Form creation, modification, design control and form properties to give you control over your database design.

In this tutorial, we will make use of the open-source US Senators Contact Information Sheet. Download the file and unzip it to a folder on your computer. Continue reading “Microsoft Access Database Forms – Part 1”

Designing A Multi-Table Query – Part 3

To view Part-1 of this series, (by Ben Beitler ), Click here >>

Creating A Multi-Table Query

Since we have planned our question, we are prepared to design and run it. If you have made written plans for your query, make certain to reference them frequently all through the ms access query design process.

These are the steps in creating a multi-table query:

1. Choose the Query Design from the Create tab on the Ribbon.image12. In the Show Table dialog box that shows, choose each table you want to include in your query, then click Add. After adding the tables, click Close. In our example, we needed information from the Customers and Orders table, so we’ll add them. Continue reading “Designing A Multi-Table Query – Part 3”

Designing A Query in Access 2017 – Part 1

image2The true strength of a relational database lies in its capability to instantaneously fetch and analyse your data by running a query. Queries enable you to pull data from one or multiple tables based on a set of search conditions you define.

This tutorial will teach you how to make a basic one-table query. Then you will figure out how to plan and run a slightly complex multi-table query.

What are ms access query ?

Queries are a method to search for and compile data from one or multiple tables. Running a query is like asking a detailed enquiry of your database. When you build a ms access query, you are defining particular search conditions to get exactly the data you need. Continue reading “Designing A Query in Access 2017 – Part 1”