10  Macros

Keywords

Access, databases, tables, queries, forms, reports, macros, textbook

OBJECTIVES

• Understand what macros can do.
• Learn how to create macros.
• Learn how to connect macros to a form.

10.1 About Creating Macros

A macro is a small program that performs automated tasks. For example, a macro can open a form or search for a record. Macros are often linked to controls such as command buttons. When you click the button, the macro executes its list of commands.

In Access, a macro consists of a sequence of actions. Many actions require additional arguments to specify how they should be performed.

Figure 10.1: Macro editor with action catalog shown on the right.

You don’t need any programming experience to create a macro. Each macro is saved as an object within the database.

Security note:

Access classifies macro actions into two types:

• Safe actions that are always allowed
  
• Potentially unsafe actions, such as Print, which could send unwanted jobs to the printer

By default, Access only shows safe actions. To view all actions, including potentially unsafe ones, enable Show All Actions (tab Design > group Show/Hide) in the macro window.

10.2 Customer Search by Code

Goal

Add a list box to a form to search for a customer by their customer code. When a code is selected, the form displays the matching record.

Analysis

Start with a standard form. Add a Combo Box that lists customer codes. Then create a macro with two actions:

1. Move focus to the customer code field
   
2. Search for the matching record

Task 10.1 File: candy365.accdb

1. Open the database.

2. Select the Customers table (do not open it).

3. Go to Create > Form (Forms group). A form is created and opened in Layout View.

4. Save the form as Search customer by code, using the button on the Quick Access toolbar.

5. Switch to Design View.

6. Go to Design > Combo Box (group Controls) and draw a combo box, , in the right section of the form header.

Figure 10.2: Form with newly added combo box placeholder.

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

7. The Combo Box Wizard will open automatically. Answer the prompts as follows:

   • The combo box should get values from a table or query.
   • Choose the Customers table.
   • Include only the CustomerCode field.
   • Sort ascending by CustomerCode.
   • Accept the default column width.
   • Store the selected value for later use.
   • Label: “Search by customer code:”.

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

8. If the label overlaps the combo box, reposition them by dragging the top-left handle of the controls.

9. Close and save the form.

10. Go to tab Create > Macro (Macros & Code group).

Figure 10.3: Macro window ready for defining actions.

10. In the Add New Action dropdown, choose GoToControl. In the Control Name field, enter CustomerCode.

Figure 10.4: First macro action: GoToControl targeting CustomerCode field.

11. Add another action: FindRecord. Set Find What to =[searchcode]. Leave the other fields at their default values.

Figure 10.5: Complete macro to search customer by code.

12. Close and save the macro as Search customer by code.

13. Reopen the form Search customer by code in Design View.

14. Select the combo box and set its Name property (tab Other) to searchcode.

Figure 10.6: Setting the Name property of the combo box to ‘searchcode’.

15. While the combo box is still selected, go to the [Event] tab in the Property Sheet. For After Update, select the macro Search customer by code.

Figure 10.7: Assigning macro to the combo box’s After Update event.

16. Save and close the form.

17. Open the form and test the combo box functionality.

10.3 Customer Search by Name

Goal

This task builds on the previous one, Task 10.1. You’ll add a second combo box that lets you find customers by name (last name followed by first name).

Analysis

Start by copying the form Search customer by code. Add a second Combo Box that displays a sorted list of full names. Then create a macro to search the correct record and synchronize the two boxes.

Task 10.2 File: candy365.accdb

1. Open the database.

2. Right-click the Search customer by code form, choose Copy, then right-click again and choose Paste.

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

4. Drag the top edge of the [Detail] section downward to make space in the header.

Figure 10.8: Expanding the form header to make room for an additional combo box.

5. Add a second Combo Box below the first one. In the Combo Box Wizard:

   • Get values from a table or query.
   • Choose the Customers table.
   • Add fields in this order: [LastName], [FirstName], [CustomerCode]
     
   • Sort by last name, then first name (ascending).
   • Accept default width and hide the key column (CustomerCode).
   • Store the value for later use.
   • Label: “Search by customer name:”.

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

6. Align the labels and combo boxes neatly.

Figure 10.9: Form layout with two combo boxes: one for code, one for name.

7. Select the second combo box. In the Property Sheet (tab Other), set Name to searchname. In the After Update event (tab Event), enter Search customer by name.

The macro does not exist yet, you’ll create it in the next step.

8. Save and close the form.

9. Go to tab Create > Macro (Macros & Code group).

Figure 10.10: Creating a new macro to handle name-based search.

10. Add the action GoToControl, and set Control Name to CustomerCode.

Figure 10.11: Macro step: GoToControl set to the CustomerCode field.

11. Add the action FindRecord, and set Find What to =[searchname].

To keep both combo boxes synchronized, you’ll need to use SetValue to update the first combo box when the second is used.

12. Enable Show All Actions in the macro design window to access SetValue.

13. Add the SetValue action with the following parameters:

    • Item: [Form]![Search customer by name]![searchcode]
    • Expression: [Form]![Search customer by name]![CustomerCode]

Figure 10.12: Macro with SetValue to synchronize both combo boxes.

14. Save the macro as Search customer by name and close the macro window.

15. Open the form Search customer by name and test both combo boxes.

If you want the first combo box to update when the second is used, you’ll need to add a similar SetValue action to the other macro as well. However, doing so may affect the original form Search customer by code.

10.4 Combo Box: Search by Box Code

Goal

This task is similar to searching by customer code. You’ll create a form that displays box details and includes a combo box to search by BoxCode.

Analysis

Start with a standard form. Add a Combo Box, and create a macro that:

1. Moves focus to the BoxCode field.
2. Searches for the selected record.

Task 10.3 File: candy365.accdb

1. Create a new form with a combo box, and name it Search box by code.

2. Create a macro named Search box by code that performs the lookup.

3. Open the form and test that the combo box finds and displays the correct record.