Home > Articles > Home & Office Computing > Microsoft Applications

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Access Macros Redux

a1212.jpg Access 2010 treats macros as full-fledged objects, and the Access team encourages their use by new users and seasoned developers alike. As of Office 97, which replaced Access Basic with VBA, macros were deprecated. VBA was designated the strategic programming language for automating Access applications, and Office 97 included a macro-to-VBA converter to ease the upgrade effort. (The Database Tools ribbon's Macro group includes a Convert Form's Macros to Visual Basic button.)

Original Access macros had two basic defects: no error-handling capability and the lack of an equivalent to form and report Class Modules (also called code behind forms, or CBF). Access 2007 overcame the first limitation with the new On Error macro action, which lets you specify how errors are handled with one of the following values of the Go To argument:

  • Next disregards the error, and execution proceeds to the next macro action.
  • MacroName stops executing the current macro and jumps to the named macro.
  • Fail stops execution and displays an error message.

Embedded macros handle the missing CBF equivalent. Each form or report event has a builder button that opens a Choose Builder dialog that lets you select a Macro Builder, Expression Builder, or Code Builder.

As an example, an embedded macro in the \Access2010\Chaptr01\NavPane.accdb database's Form1 (Customers List) hides the prebuilt Object Type, Tables and Related Views, Modified Date, and Created Date categories and locks the NavPane when you open the form (see Figure 1.19). Ordinarily, the AutoExec macro would execute these actions.

Figure 1.19

Figure 1.19 This embedded macro in Access 2010's new Macro Builder prevents users from seeing tables and queries in the NavPane's prebuilt categories.

A similar macro that enables the prebuilt categories and unlocks the NavPane executes when you open Form2 (Orders List).

Chapter 19, "Automating Access Applications with Macros," and Chapter 30, "Handling Events with Macros and Procedures," show you how to write Access macros to handle simple tasks. VBA is better used for complex application automation chores, but web databases don't support VBA. Therefore, the Access team added Data Macros to handle events associated with adding, deleting and updating rows of Access tables.

  • + Share This
  • 🔖 Save To Your Account