Building all of the individual parts of a database is only a small part of the task that you have to complete in designing a database. An equally important part of the design process is making sure that the individual parts will be able to work together seamlessly and that anyone using the database will find it simple, intuitive, and efficient to use. You will want to develop macros and modules to automate frequently used or complex tasks. You will also want to either make sure that everyone using the database knows Access or that they do not need to know Access to get around your database. In other words, you want to make sure that your database calls attention to the data rather than the design behind the data.
You can use macros to make your forms, reports, queries, and tables work together more efficiently and intelligently. Macros can automate routine tasks, such as printing a weekly report or a monthly ATN Training Center Schedule. Macros also offer versatile customizing abilities, such as fairly complex data validation on forms. You can even use macros to create your own custom menu bars for individual forms.
A macro is a mini-program which automatically carries out a task or series of tasks for you. Each task that you want Access to perform is called an action, and Access provides a list of 51 actions for you to choose from when creating a macro. When you run the macro, Access carries out the actions in the sequence they're listed in, using the objects or data that you have specified. For example, you can create a macro to automatically open a table and a form that you frequently use together. This macro would require two actions: one to open the table and a second to open the form. If you were to add data on the form, you could then use another macro to check the data and display an error message (if needed), or to open another form. Any repetitive or routine task you find yourself doing in Access is a good candidate for a macro. Automating routine tasks improves both efficiency and accuracy into your database because a macro performs the task the same way each time. Some common areas where you would use macros include:
- Making your forms and reports work together.
- Finding and filtering records automatically.
- Setting values in controls.
- Ensuring data accuracy.
- Setting properties of forms, reports, and controls.
- Automating data transfers.
When you create a macro, you open a new Macro window. Most of the time, you will begin creating a macro by clicking the Macro tab in the Database window and then clicking on the New button. Much like a table's Design view, you use the top half of the Design Window to add actions and comments to the macro and the lower half of the window to better define the Arguments (or Properties) of the action. You can add comments when you design the macro to explain the reasoning behind each action or to tell which objects are being operated on. Access will simply ignore the comments when running the macro, but they are very helpful when you have to debug the macro to figure out why something is not working properly.
There are two ways to begin adding actions to your macros: selecting actions from the action list and dragging and dropping. To do the former, click on the first empty cell in the Action column. Then, either select the action you want from the list that appears when you click on the down arrow at the column's right edge or type the action into the box. Then you will need to add the appropriate arguments for the action. You could then enter a comment in the Comment column to complete the step. To add an action by dragging and dropping, make sure that both the Macro Design window and the Database window are visible. Then, click the button for the type of object you want to drag in the Database window and drag the object from the Database window to an action row in the Macro window. If you drag a table, query, form, or report to the Macro window, Access adds an action that opens the object. If you drag a macro, then Access adds an action that runs the macro
Most actions have arguments that give Access necessary information to carry out the action, such as which object to use or open. For instance, the OpenTable action has a Table Name argument that tells Access which table in the database to open. After you add an action, you set the arguments for that action in the lower half of the window by typing in the arguments or selecting them from drop down lists in some instances. If you added the action to the macro by dragging and dropping, then many of the arguments will already be set; however, you should always check them anyway.
Many actions have an argument that calls for the name of a database object. You can use a similar drag and drop technique to set these and other arguments. Simply drag the object from the Database window to the lower portion of the Macro window and drop it on the argument you wish to set. You can also type the object's name into the cell; however, the expression builder is often the best way to define an argument.
You have to save the macro before you can run it. By saving the macro, you define it as an object that can be opened or run from the Database window. To save a macro, choose Save from the File menu or the Macro toolbar. If you're saving the macro for the first time, enter a name for the macro and choose OK.
Most macros are not going to run perfectly the first time you try them. The more complicated the task that you need the macro to perform, the greater the likelihood of something going awry. The easiest way to debug or check out your macro is to step through it one step at a time. You can activate this feature by clicking on the Single Step button on the Macros toolbar. When you run the macro, you will be prompted before the macro proceeds to the next step. If there is an error, you will need to decide whether you want to terminate the macro or to move ahead to the next step anyway.
You can run a macro from several places in your database, even in response to an event that occurs in a form or report. To run a macro from the Macro window, choose Run from the Macro menu or from the Macro toolbar. To run a macro from the Database window, click the Macro button and then double click on the macro you wish to run. To run a macro from other windows in your database, choose Run Macro from the File menu and type the name of the macro you wish to run in the dialog box. Then, choose OK to run the macro. To run a macro from inside another macro, add the RunMacro action to your macro and set the Macro Name argument to the name of the desired macro.
You can also set a macro to run automatically in response to an event on a form or report by setting the appropriate event property to the name of the macro. For example, you could run a macro automatically when a form is opened or when a command button is pressed. You could also set up a macro to run just before you exit a control to make sure the value entered in a field is allowed.
When you use a form or report, Access recognizes certain things that happen on the form as events. For example, moving from one control to another is an event, as is double-clicking on a control. You can use a macro to respond to any event. Each form and control event has a corresponding event property. You specify how the form responds to the event by using a macro or a module. All you have to do to set the event property to a particular macro is type the macro's name in the event property cell.
There are six main types of events in Access: Window events, Data events, Focus events, Keyboard events, Mouse events, and Error and Timing Events. Window events occur when you open, resize, or close a form and include the OnOpen, OnResize, and OnClose properties. These events take place exactly when their names imply that they should. Data events occur when data is entered, deleted, or changed in a form or a control, or when the focus moves to another record. Data events include OnCurrent, which occurs when the form is opened and when you move to another record, OnDelete, which occurs when a record is deleted but before the deletion is confirmed and actually performed, and BeforeUpdate, which occurs before a control or record is updated with changed data.
Focus events occur when a form or control loses or gains the focus, or becomes active or inactive. In Access, a control gains the focus when you move to it and the cursor flashes inside it (only text and combo boxes). When you leave the original control to move to a new one, the following event sequence takes place (event properties will follow in parentheses). You hit <Tab>, the control is exited (OnExit), the control loses the focus (OnLostFocus), the new control is entered (OnEnter), the new control gets the focus (OnGotFocus), and the cursor tells you the control is now active. These events are good places to put validation checks or programmed lists based on other values.
Keyboard events occur when you type on the keyboard or when keystrokes are sent using the SendKeys macro action. Mouse events occur when a mouse action, such as pressing down or clicking a mouse button occurs. OnClick and OnDblClick are the most useful mouse properties to use, especially when you want to move between two forms linked by a common value. Error and Timing events are used for error handling and synchronizing data refreshing on forms (necessary for a multi-user database). The best discussion that I have seen on event ordering in Access is the QUE book on Access, though the manual does a fine job in its own right.
To create a response to an event, you must first identify the form or control event that you want the macro to respond to. Then, you write a macro which contains all the actions you want carried out when the event occurs. Finally, set the event property to the name of the macro. You can use the Macro Builder, which is available for each event property, to create a macro and set the event property automatically.
To use the builder for creating a macro and specifying it for a property, open the form in Design view and display the property sheet by clicking on the Properties button on the toolbar. Then, select the form or control. In the property sheet, click the event property you want to respond to and then click the Build button immediately to the right of the property box. In the Choose Builder dialog box, select the Macro Builder and choose OK. Type a name for the new macro you want to create and again choose OK. When the Macro window appears, enter the actions you want to be carried out and then choose Close from the File menu. Save the changes to the macro when you are prompted, and when the event occurs on the form your macro will run.
Unfortunately, not all macros are perfectly straightforward. Most of the time, you can set up a macro so that every action entered can and should be executed. Sometimes, however, that simply is not the case because you may need to control or limit macro actions based on a value in a particular field or control. For instance, you can design a form that automatically fills in several field values when a particular value is entered in the first field on the form, making data entry automatic and relatively error-free. In order to make this happen, you will need to set up a macro that checks to make sure that certain conditions are met. For instance, you might set up a record collection database with a macro to insure that anytime the Artist Last Name field value equals "Beatles" then the Label field value will automatically be set to "Apple." All of the macros that you have built so far have two key points in common:
- All of the actions entered into these macros are executed. These macros end after the last instruction is carried out.
- The instructions are executed in the order they were entered into the macro.
Once you start using conditions in your macros, however, you will need more control to make a macro specific enough to be useful. What happens if the Artist Last Name does not equal "Beatles" or the value in that field is changed to something else? A simple macro does not really account for this kind of situation. We need a more complicated macro, something that corresponds to an If ... Then ... Else loop in computer programming (as in the Access Basic modules that work behind the scenes in Access). Access macros have to provide for the same situation in a more roundabout manner, involving four separate guidelines:
The logical structures available for macros are all based on these rules. You need to use all of them to solve even simple problems with an Access macro.
- Macro conditions and actions are evaluated in the order in which they are entered, starting with the first row in the macro sheet.
- Once started, a macro terminates when Access encounters a blank row (no conditions or actions, usually the end of the macro) or when the StopMacro action is executed.
- If a condition is false, the macro skips the action on that line and moves to the next line of the macro sheet.
- You can link a series of actions to a single condition by placing an ellipsis (...) in the Condition column.
One of the most common tasks that many database builders want to do is make their database easy for people to navigate and work in. Most users (especially people doing data entry) have neither the time nor inclination to learn how Access works in order to do their jobs, so you must make it easy for them whenever possible. Frequently, you will want to hide the database window/explorer and replace it with a "switchboard" form that takes your users to where they need to go. The questions should be: "What actions do I need to execute to hide the database window and load the form?" followed by "How do I make this execute automatically on startup?"
The actions are pretty straightforward. You need to carry out two actions: hiding the database window and opening the form. Hiding any window in Access is accomplished through the Window menu, so you will need to use the DoMenuItem action with the appropriate arguments. Opening the form is carried out with the OpenForm action, specifying the appropriate form name in the arguments.
The second part of this problem is making the database open the macro automatically on startup. In order for this to occur, you simply name the macro AutoExec when you save it. This name enables Access to recognize and load the macro when you open the database.
Most of the time, you will be able to use macros to do anything you need to do; however, there are occasions when you need something a little more powerful to solve a really complex problem. Almost every time, creating a module in Access Basic will be your path of last resort, but you need to know that it's there.
Access Basic is the programming language for Access. You use it very much as you use macros, attaching it to specific event properties. The difference is that Access Basic provides more power and greater control than you get using macros alone. Access Basic is a modern programming language that is based on the increasingly popular Visual Basic. It closely resembles most of the popular, structured programming languages such as Pascal and C by offering all the program structures that they provide. Most importantly, Access Basic retains the English-like feel of all Basic languages. Still, you probably will not want to venture into Access Basic if you have never programmed before. It can be quite complex when you start manipulating data in the database.
You store your Access Basic code in units/objects called modules, which provide a way of organizing your Access Basic procedures. Your database can contain two kinds of modules: Form or Report Modules and Global Modules. The former type is local to a single form or report in your database. Each form in a database has a built-in form or report module that contains event procedures that occur on the form or report. These modules are part of the object's design, so if you copy a form or report to another database, the underlying go will tag along. Global modules, on the other hand, are created as separate objects in your database. You use Global modules to store code that you want executed from anywhere in the database (such as functions that you want to calculate which are not part of the standard function list).
The most common reasons to use Access Basic instead of macros are:
- They make your application easier to maintain because macros are separate objects from the forms and reports that use them, an application containing a lot of macros that respond to events on forms and reports and gets unwieldy. By using Access Basic to respond to events, the code is built into the form or report's definition, and if you move a form to another database the code goes along while macros remain behind.
- You can create your own functions. While Access contains many functions, Access Basic gives you the ability to create your own without needing to create many complicated expressions.
- They mask error messages. When something unexpected happens, and it will, Access displays an error message. If your users don't use Access regularly, they will probably either panic or ignore the message. Access Basis allows you to create an internal response to the error and explain it better to your users.
- Creating or manipulating objects. In most cases, it's easier to do this in the object's Design window; however, in some situations, you might want to manipulate the object in code (especially if you come to Access from DBase or Paradox).
- Manipulating records one at a time. You can use Access Basic to step through a recordset one record at a time and perform an operation on each record. Macros, in contrast, work with entire recordsets at once.