Macros and Macro Groups (Chapter 8)

1. Macros and Macro Groups(Chapter 8) Peter Rob and Elie SemaanDatabases: Design, Development,and DeploymentUsing Microsoft Access Second Edition

2. What a Macro Is and What It Does • A macro is an instruction or a set of instructions that triggers one or more actions. These actions include opening and closing forms; writing error messages; producing dialog boxes; changing tab orders; updating, modifying, and deleting records; processing input, and so on. Macros can also call up and execute other macros. Therefore, macros can be used to automatically perform the many actions that change a sometimes-bewildering collection of database components into an easy-to-use and comprehensive system.

3. What a Macro Is and What It Does (Cont) • Although the code that underlies the macros may be complex, creating and using macros is relatively easy. In effect, macros involve the use of a high-level computer language that hides the complexity of the actions from you. All you need to do is tell the computer what to do; you do not have to tell the computer how to do it.

4. What a Macro Is and What It Does (Cont) • Access allows you to pick the various actions from a list, so you won’t even need to remember the exact commands. (Of course, you will have to know what actions you need!) After you select an action, Access will even give you a list of appropriate action arguments.

5. What a Macro Is and What It Does (Cont) • For example, if you select the macro action Open, a set of action arguments automatically appears. Because Open can involve different types of objects, Access will provide a list of possible objects, and each object type triggers its own object name options. Logical conditions can be created via the familiar Expression Builder. In short, although you will have to supply the logic of the system’s actions, Access makes macro creation as simple as possible.

6. What a Macro Is and What It Does (Cont) • The reason for creating and using macros is simple: Macros can be used to hide the system’s complexities from the end users. In short, the macro-based system can be used to guide end users to perform many different types of database tasks, without forcing these users to understand how the system works. Assuming that the system enables the end users to do their jobs more efficiently and intelligently, making the system easy to use is what helps make it such a powerful tool.

7. What a Macro Is and What It Does (Cont) • To use an analogy, how useful would a car be to you if you had to know how to operate each of its components in order to drive? Just think of how difficult it would be to manage the engine’s ignition sequences manually. Instead, you get in your car, turn the key, and expect the engine to work. Or suppose you had to know all about the hydraulic system to successfully activate its components manually?

8. What a Macro Is and What It Does (Cont) • (We suspect that there would be many more accidents if drivers had to initiate a complex and lengthy set of actions manually to brake the car.) Fortunately, you simply press the brake pedal, and the rest happens automatically. In short, although the system’s behind-the-scene activities may be complex and numerous, making the system easy to use is crucial to its success!

9. What a Macro Is and What It Does (Cont) • In the same vein, end users do not care about what macros are and what they do. What end users want is a system that is easy to use and one that helps them become more productive. In other words, the macros can combine all the components you have written thus far into a tightly controlled system that guides the end user to properly use the database environment.

10. Macro Groups • Macros are best placed in logical modules known as macro groups. Each of the modules defines the activities within a logically defined portion of the applications environment. For example, the main menu we developed in Chapter 6 (see Section 6.7) contains many command buttons that transfer control to other forms and/or processes.

11. Macro Groups (Cont) • The main menu also shows that the buttons are arranged in logical groups. These four button groups transfer control to four distinct functions: data management or maintenance, transactions, menus, and archiving.

12. Macro Groups (Cont) • Although no standards govern which macros must be stored in what macro group, it is absolutely necessary to create macro groups based on some logical footing. Ultimately, you must make professional decisions in answering questions such as: • How many macros should be stored in a macro group? There is no hard and fast rule here, but too many macros in one macro group will make it hard to manage. If you put too few macros in a macro group, you’ll wind up with too many macro groups. In the extreme case, if each of your macro groups contains a single macro, there is no point in creating macro groups at all. So here’s some truly dangerous advice: Use common sense!

13. Macro Groups (Cont) 2. At what point do the transactions become sufficiently complex to warrant their own macro group? Once again, there is no hard and fast rule here. Clearly, if a transaction involves simple actions, such as merely opening and closing forms and performing a single data update, there is no point in putting it in its own macro group. On the other hand, if a transaction involves multiple steps and each step involves a complex process, it’s ready to be placed in its own macro group. The answer to question 1 surely has an effect on the answer to question 2, and vice versa. And the advice remains the same: Use common sense.

14. Macro Groups (Cont) • Do you store all of the transaction macros in one transaction macro group, or do you break out each transaction type and create a macro group for it? The answer to this question is clearly influenced by your answer to questions 1 and 2. In turn, your answer to question 3 affects how you answer questions 1 and 2. • How, and to what extent, do you break down the logical processes? The answer to this question is clearly influenced by how you approached questions 2 and 3. And the answer to questions 2 and 3 are clearly influenced by the answer to question 4.

15. Macro Groups (Cont) • No matter what decisions you make on the basis of the preceding questions, do use macro groups. Do not use freestanding macros. • Putting macros within macro groups has several benefits: • The macros will be easier to manage. If a macro malfunctions within a logically structured macro group, it will be easier to repair. Tracing a logical problem is much easier if the problem is contained within a logical sequence!

16. Macro Groups (Cont) 2. Because macro groups conform to the system modules, it’s easy to add similar modules later. The familiar Edit/Copy/Paste routine will greatly simplify your applications development process. For example, if you have multiple menu structures, first create a menu template form and its macros and then duplicate it with the File/Save as option. Finally, edit the duplicate to rename it and its components, and you have just saved yourself a lot of development time!

17. Macro Naming Conventions • Throughout this book we’ve emphasized the need to make the database environment as self-documenting as possible. We continue this emphasis during the macro development process. Therefore, we suggest the following naming conventions: • Use the prefix mcr to identify macro groups. This naming convention makes it easy to distinguish between the form frm, the query qry, and the macro group mcr.

18. Macro Naming Conventions(Cont) 2. Use descriptive names. The name mcrFindEmployee indicates the macro’s function. The name mcrEmpTransFind2 leaves you wondering just what this thing might be, and a name like mcrY3-TR98 leaves you completely baffled. Trying to figure out what some other developer had in mind when you are asked to repair or update an application is bad enough. Trying to figure out what on earth you had in mind when you try to update your own work is positively humiliating.

19. Macro Naming Conventions(Cont) • Use descriptive names that tie the macro to its transactions. For example, if the macro group contains macros that open a dialog box form named frmFindEmployee to trigger a query named qryFindEmployee, it would be useful to name the macro group mcrFindEmployee.

20. Macro Naming Conventions(Cont) 4. Within the macro groups, use macro names that indicate their actions. For example, if a macro within the mcrFindEmployee macro group must open an employee search form, this macro might appropriately be named OpenEmployeeSearchForm. Note that the macros within the macro group do not carry the mcr prefix. (The prefix would be redundant because the macro is contained within a macro group that already carries the mcr prefix.)

21. Creating a Macro Group • To make the macro building and editing process as flexible as possible, Access lets you invoke the macro builder via many different sources. In fact, you can even create and/or edit a macro group by clicking on a form’s command button—while the form is in its design mode -- to use its properties box to open the macro builder. Such flexibility is particularly desirable when you want to edit an existing macro group.

22. Creating a Macro Group(Cont) • However, we’ll build our first macro group via the database window from which we launched queries and forms. Therefore, start at the database window, select the macro folder tab, and select the New option to produce the macro builder screen.

23. Creating the Macros within the Macro Group • Remember that macros must be named to reflect their actions. • Type in the macro name in the Macro Name. • Insert a condition if you need to. • Select an action from the Action column and set the options for the action you selected. • Type in the description of the macro. • On the next line select another action if you the macro to execute multiple actions.

24. Attaching the Macro to the Command Button • To attach the macros to any of the form’s command buttons, complete the following steps. (We will use the Close the Query Menu button to illustrate the process.) • Open the form in the design mode. (As you can tell by looking at Figure 8.11, we have selected the frmQueryMenu form.) • Click on the object you want to attach the macro to. (Check Figure 8.11 to see that that we have selected the form’s Closethe Query Menu button. • Open the object’s properties box – in this example, the Close the Query Menu button -- and cursor down to the properties box’s On Click property. (See Figure 8.11.)

25. Attaching the Macro to the Command Button (Cont) • Click on the properties box’s On Click property line to generate a drop-down list of available macros within the macro groups. (See Figure 8.11.) • Cursor down the drop-down list of available macros within the macro group to find the macro you want to attach to the Close the Query Menu button object. (Check Figure 8.11 and note that we cursored down to the CloseQueryMenu macro in the mcrQuery macro group.)

26. Attaching the Macro to the Command Button (Cont) • Click on the selected CloseQueryMenu macro to write it in the On Click property line. (Figure 8.11 shows that we have already done that.) • Save the frmQueryMenu form again, which now has the CloseQueryMenu macro attached to the Close the Query Menu button object.

27. The Startup Screen • The startup screen can launched from two different places: • You can use the Tools/Startup from the drop down menu and select the Form you want to launch when the application start from the Display Form/Page drop down list in the Startup window. • Create a macro to open the Form you to launch when the application start without typing in the macro name. Save the macro and name it AutoExec.

28. Using Macros to Build Basic Application Systems • There is no practical way to explore all the available macros. You probably noticed that the macro action drop-down list contained a long list of possibilities. Macro can do more than opening and closing a Form. Macro can be used to: • Detect data entry errors and to produce appropriate error messages to alert the end user to the problem. • Transfer input from a form to a report.

29. Using Macros to Build Basic Application Systems (Cont) • Perform a form-based data search that uses the segmented search process you learned about in Chapter 5. • Find a customer on the invoice form you created in Chapter 6. • Calculate the invoice totals.

30. Managing Processes through Macros • Macro groups are to be process based, each process must be broken into a series of simple steps, each one of which would be governed by a macro. For example, invoicing is a process that may become the basis for a macro group named mcrInvoice. The macros within the mcrInvoice macro group would reflect process components such as • Open the invoice form. • Match the invoice to the customer. • Write the invoice lines. • Update the inventory. • Calculate the total.

31. Managing Processes through Macros (Cont) • If any process contains many components, it may be useful to break out each set of logically related components to become the basis of a new macro group. This technique helps control the proliferation of macros within a single macro group, thus making it easier to manage the macro-based system. • Any complex procedure, such as the segmented search you first saw in Chapter 5, can be presented as a process.

32. Macros and Visual Basic for Application • Because macros play such a vital role in Access applications development. You should know that macros have limited transaction capability. Therefore, they are used primarily to facilitate rapid applications development and prototyping.

33. Macros and Visual Basic for Application (Cont) • If you need more flexible database applications and you work with complex database transactions, you ought to explore contributions of Visual Basic for Applications (VBA) to the Access database environment. (Because this is not a VBA book, we do not cover VBA development.) However, once you know how to design and implement databases and you know how to prototype the applications, you’ll find that detailed knowledge of VBA programming will become a very desirable tool in your applications development work. Of course, do keep in mind that VBA is not particularly useful until you know how to design and implement databases and how to design and implement an attractive, flexible, and user-friendly applications environment. That’s why you selected this book in the first place!

34. Macros and Visual Basic for Application (Cont) • Finally, in spite of our VBA praises, we do not mean to imply that macros lack functionality. Macros pay the proverbial freight by letting you produce applications quickly and efficiently. Therefore, macros are highly valued prototyping tools, and your macro skills will be very desirable. But keep in mind that you can move a thousand tons of sand more efficiently in a dump truck than in a pickup truck. However, that fact does not render a pickup truck useless.

35. The END