10  Macros

OBJECTIVES

  • What you can do with macros.
  • How to create macros.
  • How to combine macros with a form.

10.1 About creating macros

A macro is a little program that can perform certain actions. For example, you can give a macro the command to open a form or to search a record. A macro is often assigned to a control such as a command button. When clicking on the button, then the commands in the macro are run. This way you can assign additional functions to forms and reports.

A macro in Access is a list of actions to be carried out consecutively. For most actions, one or more arguments are needed.

Figure 10.1: Macro window with action catalog.

No programming knowledge is required for creating macros. A macro is stored as an object in the database.

A point of concern with macros is the aspect of safety. Access divides the possible macro actions into two types:

  • harmless actions always, regardless of how they are used
  • dangerous actions

Even an action as Printing is considered hazardous because these unwanted can send commands to the printer.

When you add actions in the macro window, by default, Access shows only the safe actions. To get a complete list of actions, including dangerous actions, you need to change a setting. Then choose in the macro window tab Design > Show All Actions (group Show/Hide.

10.2 Customer search by code

On a form is a choice list and through an assigned macro the corresponding record is looked up.

First, a form with the data of a customer should be created. To make it easier to find a particular customer with known customer code, the form should contain a list box where you can enter the customer code and after that, the data of the corresponding customer should be displayed in the form.

ANALYSIS

The base of the form can be made with a default form. The drop-down list box is the control Combo Box. Furthermore, you have to create a macro that performs two actions. First, go to the drop-down list box to get the entered customer code and then find the corresponding record.

Task 10.1 File: candy365.accdb

  1. Open the database.

  2. Select table Customers. It’s not necessary to open the table.

  3. Click tab Create > Form (group Forms). The form is created and opened in Layout View.

  4. Save the form with the name Search customer by code. The easiest way to do that is through button Save in the Quick Access toolbar.

  5. Switch to Design View.

  6. Select Design > Combo Box (group Controls) and then draw in the right part of the form header a rectangular framework for the list box.

Figure 10.2: Form with combo box.

After drawing the framework, the Combo Box Wizard is automatically started.

  1. Answer the consecutive questions of the Wizard as follows:

    • The Combo Box should get the values from a table or a query.
    • Table: Customers should provide the values for the combo box.
    • Only field CustomerCode should be added.
    • Sort records ascending on CustomerCode.
    • Accept the default width of the column.
    • Remember the selected value for later use.
    • Label text: Search on customer code:

    After completing the wizard you are back in the Design View.

  2. When the label and the Combo Box are partly overlapping each other, you have to move one or both of them. You do this by dragging the upper-left corner of the object with a pressed left mouse button.

  3. Close form Search customer by code and save the changes.

  4. Choose tab Create > Macro (group Macros & Code).

Figure 10.3: Macro window.

  1. Click on the selection arrow in the box Add New Action and select action GoToControl from the list.

  2. Enter CustomerCode in the box Control Name.

Figure 10.4: Action GoToControl CustomerCode.

  1. Click on the selection arrow in the box Add New Action and select action FindRecord.

  2. Enter value =[searchcode] in box Find What. The other arguments are already filled in by Access with their default values and can remain.

Figure 10.5: Macro Search customer by code.

  1. Close the macro window and save the macro with name Search customer by code.

  2. Open form Search customer by code in Design View.

  3. Select the Combo Box and change in the Property Sheet (tab Other) the name in searchcode.

Figure 10.6: Combo Box property Name.
  1. With still the Combo Box selected, click in Property Sheet (tab Event) on the selection arrow in box After Update and select macro Search customer by code.
Figure 10.7: Combo Box property After Update.

  1. Close the form and save the changes.

  2. Open form Search customer by code and test if the Combo Box works properly.

10.3 Customer search by name

Searching a record with two combo boxes on a form.

To perform this task you must have finish Task 10.1 where you created the form Search customer by code that you will need in this task.

There should be a form created with the data of a customer and with two Combo Boxes. Through the first list, a customer should be searched by the customer code and through the second list by name. The second Combo Box should display a sorted list of last names followed by first names. After choosing via one of the two lists, the customer’s details must be shown in the form.

ANALYSIS

The basis of the new form is the previously created form Search customer by code. In Access, you can create a copy of an object and save it under a different name. The second Combo Box must then be made on this. And you need to create a macro that will execute the actions. First, go to the drop-down list box to get the entered customer code and then find the corresponding record.

Task 10.2 File: candy365.accdb

  1. Open the database.

  2. Right-click on form Search customer by code, choose Copy. Then right-click again and choose Past.

  3. Name the new form Search customer by name and open this form in Design View.

  4. Create more space for the form header. Position the mouse pointer over the top edge of Detail until it changes as shown in Figure 10.8. Then press the left mouse button and drag the border down so that there is enough space for the second Combo Box.

Figure 10.8: Enlarging form header.

  1. Create a second Combo Box below the first one.

  2. Answer the consecutive questions of the Wizard as follows:

    • The Combo Box should get the values from a table or a query.
    • Table: Customers should provide the values for the combo box.
    • Add in sequence the fields LastName, FirstName, CustomerCode.
    • Sort records ascending on lastname and then firstname.
    • Accept the default width of the column and select to hide the key column (this is CustomerCode).
    • Remember the selected value for later use.
    • Label text: Search on customer name:

    After completing the wizard you are back in the Design View.

  3. Take care that labels and Combo Boxes are properly aligned.

Figure 10.9: Form with 2 combo boxes.
  1. Select the second Combo Box and change in the Property Sheet (tab Other) the name in searchname. Click on the selection arrow in the box After Update and type Search customer by name.

This macro does not exist yet and will be created in the next step.

  1. Close the form and save the changes.

  2. Choose tab Create > Macro (group Macros & Code).

Figure 10.10: Macro window.

  1. Click on the selection arrow in the box Add New Action and select action GoToControl from the list

  2. Enter CustomerCode in the box Control Name.

Figure 10.11: Action GoToControl CustomerCode.

  1. Click on the selection arrow in the box Add New Action and select action FindRecord.

  2. Enter value =[searchname] in box Find What. The other arguments are already filled in by Access with their default values and can remain.

To ensure that the value of the first list box also changes to the right value when choosing a name, you had to create an action for this. The value for searchcode must be equal to the value of CustomerCode of the found record.

  1. Add an action with the name Setvalue. This action considers Access as a dangerous action that is not shown by default. It should be made visible in the list by using the setting Show All Actions (group Show/Hide). This action has two parameters, Item and Expression, which should get the following values:
  • Item: [Form]![Search customer by name]![searchcode]
  • Expression: [Form]![Search customer by name]![CustomerCode]
Figure 10.12: Macro customer by name.

  1. Close the macro window and save it with the name Search customer by name.

  2. Open form Search customer by name and test the operation of both Combo Boxes.

When the first list box is used, its name does not appear in the second list box. To achieve this, you also need to add an action SetValue to this macro. But that has consequences for the operation of the Combo Box on form Search customer by code.

10.4 Choice list boxcode

This task is a variant on the task in which the customer is searched by customer code. Now you should create a form with the data of a box and on that form a combo box for searching the BoxCode.

ANALYSIS

The base of the form can be made with a default form. The choice list is a Combo Box. You need to create a macro for performing two tasks. First, go to the Combo Box and get the box code from there. Then find the corresponding record..

Task 10.3 File: candy365.accdb

  1. Create a new form with Combo Box and name it Search box by code.

  2. Create the macro and name it Search box by code.

  3. Open form Search box by code and test the working of the Combo Box.