Access Cookbook

What We Left Out

To keep this book to a reasonable length, we have made some assumptions about your skills. First and foremost, we take it for granted that you are interested in using Microsoft Access and are willing to research the basics in other resources. This isn’t a reference manual or a “getting started” book, so we assume you have access to that information elsewhere. We expect that you’ve dabbled in creating Access objects (tables, queries, forms, reports, and pages) and that you’ve at least considered working with VBA (Visual Basic for Applications, the programming language included with Access). We encourage you to look in other resources for answers to routine questions, such as “What does this Option Explicit statement do?”

To get you started, though, following are basic instructions for what you’ll need in order to use the solutions in this book. For example, you’ll encounter requests to “create a new event procedure.” Rather than including specific steps for doing this in each case, we have gathered the most common techniques you’ll need into this section. For each technique we’ve included a help topic name from the Access online help, so you can get more information. The procedures here are not the only way to get the desired results, but rather are single methods for achieving the required goals.

How Do I Set Control Properties?

In the steps for many of the solutions in this book, you’ll be asked to assign properties to objects on forms or reports. This is a basic concept in creating any Access application, and you should thoroughly understand it. To assign properties to a control (or group of controls), follow these steps:

1. In design mode, select the control or group of controls. You can use any of the following methods (each of the items here refers to form controls but works just as well with reports):

   Single control

   Click on a single control. Access will mark it with up to eight sizing handles—one in each corner, and one in the middle of each side of the control, if possible.

   Multiple controls

   Click on a single control, then Shift+Click on each of the other controls you want to select. Access will mark each of them with sizing handles.

   Multiple controls

   Drag the mouse through the ruler (either horizontal or vertical). Access will select each of the controls in the path you dragged over. If partially selected controls don’t become part of the selection and you’d like them to, open Tools → Options → Forms/Reports and look at the Selection Behavior option. It should be set to Partially Enclosed.

   Multiple controls

   If you need to select all but a few controls, select them all and then remove the ones you don’t want. To do this, choose the Edit → Select All menu item. Then Shift+Click on the controls you don’t want included.

2. Make sure the properties window is visible. If it’s not, use View → Properties (or the corresponding toolbar button).

3. If you’ve selected a single control, all the properties will be available in the properties window. If you’ve selected multiple controls, only the intersection of the selected controls’ properties will be available in the properties window. That is, only the properties all the selected controls have in common will appear in the list. As shown in Figure P-1, select a property group and then assign the value you need to the selected property. Repeat this process for any other properties you’d like to set for the same control or group of controls.

Figure 1. The properties window shows the intersection of available properties when you’ve selected multiple controls

Tip

For more information, browse the various topics under properties; setting in Access online help.

How Do I Create a New Module?

VBA code is stored in containers called modules, each consisting of a single declarations section, perhaps followed by one or more procedures. There are two kinds of modules in Access: global modules and class modules. Global modules are the ones you see in the database window, once you choose the Modules tab. Class modules are stored with either a form or a report and never appear in the database window. (Actually, you can also create standalone class modules, which do appear in the database window. The use of these types of modules, which allow you to define the behavior for your own objects, is beyond the scope of this book.) There are various reasons to use one or the other of the two module types, but the major factor of concern is the availability of procedures and variables. Procedures that exist in global modules can, for the most part, be called from any place in Access. Procedures that exist in a class module generally can be called only from that particular form or report and never from anywhere else in Access.

You’ll never have to create a form or report module, because Access creates those kinds of modules for you when you create the objects to which they’re attached. To create a global module, follow these steps:

1. From the Database Explorer, click on the Modules tab to select the collection of modules, then click on the New button (or just choose the Insert → Module menu item).

2. When Access first creates the module, it places you in the declarations section. A discussion of all the possible items in the declarations section is beyond the scope of this Preface, but you should always take one particular step at this point: if you don’t see Option Explicit at the top of the module, insert it yourself. Then use the Tools → Options menu from within the VBA editor to turn on the Require Variable Declaration option (see Figure P-2). With this option turned on, all new modules you create will automatically include the Option Explicit statement. If you don’t insert this statement and Access encounters a reference to an unknown variable, Access will create the variable for you. With the Option Explicit statement, Access forces you to declare each variable before you use it.

   

   Figure 2. Use the Tools → Options dialog from within VBA to turn on the Require Variable Declaration option

   Don’t Skip This Step!

   Although this may seem like an unnecessary burden for a beginner, it’s not. It’s an incredible time saver for all levels of users. With the Option Explicit statement in place, you can let Access check your code for misspellings. Without it, if you misspell a variable name, Access will just create a new one with the new name and go about its business.

3. If you are asked to create a new function or subroutine, the simplest way to do so is to use Insert → Procedure. For example, if the solution instructs you to enter this new procedure:

   Function SomeFunction(intX as Integer, varY as Variant)

   you can use Insert → Procedure to help you create the function.

4. Click OK in the Add Procedure dialog, as shown in Figure P-3. Access will create the new procedure and place the cursor in it. For the example in Step 3, you must also supply some function parameters, so you’ll need to move back up to the first line and enter intX as Integer, varY as Variant between the two parentheses.

   

   Figure 3. The Add Procedure dialog helps you create a new function or subroutine

How Do I Import an Object?

In this book’s solutions, you’ll often be asked to import an object from one of the sample databases. Follow these steps:

1. With your database open on the Access desktop, select the database window by pressing F11. (If you’re in the VBA editor, first press Alt+F11 to get back to Access.)

2. Choose File → Get External Data → Import, or right-click on the database window and choose Import.

3. Find the database from which you want to import a module, and click Import.

4. In the Import Objects dialog, select all of the objects you’d like to import, moving from object type to object type. When you’ve selected all the objects you want to import, click OK.

If a solution instructs you to import a module from one of the sample databases that you’ve already imported (for a different solution), you can ignore the instruction. Any modules with matching names in the sample database contain the exact same code, so you needn’t import it again.

How Do I Create an Event Macro?

Programming in Access often depends on having macros or VBA procedures reacting to events that occur as you interact with forms. You’ll find that most of the solutions in this book use VBA code rather than macros, because code provides better control and safety. But occasionally a macro is the right tool for the job. To create a macro that will react to a user event, follow these steps:

1. Select the appropriate object (report, form, or control) and make sure the properties window is displayed.

2. Choose the Event properties page on the properties window, or just scroll down the list until you find the event property you need.

3. Click on the “...” button to the right of the event name, as shown in Figure P-4. This is the Build button; it appears next to properties window items that have associated builders. In this case, clicking the Build button displays the Choose Builder dialog, shown in Figure P-5. Choose the Macro Builder item to create a new macro. (If you don’t often use macros, in the Tools → Options dialog, on the Forms/Reports page, you can choose to “Always use event procedures”. The Build button will immediately take you to the Visual Basic Editor.)

   

   Figure 4. Press the Build button to invoke the Choose Builder dialog

   

   Figure 5. The Choose Builder dialog: choose Macro Builder for macros and Code Builder for VBA

4. Give the macro a name, so Access can save it and place its name in the properties window. You can always delete it later if you change your mind. Give your new macro the name suggested in the solution, and fill in the rows as directed. When you’re done, save the macro and put it away.

5. Once you’re done, you’ll see the name of the macro in the properties window, as shown in Figure P-6. Whenever the event occurs (the Change event, in this case), Access will run the associated macro (mcrHandleChange).

   

   Figure 6. The properties window with the selected macro assigned to the OnChange event property

6. If you want to call an existing macro from a given event property, click on the drop-down arrow next to the event name, rather than the Build button. Choose from the displayed list of available macros (including macros that exist as part of a macro group).

Tip

For more information on attaching macros to events, see macros; creating in Access online help.

How Do I Create an Event Procedure?

Programming in Access often depends on having VBA procedures react to events that occur as you interact with forms or reports. To create a VBA procedure that will react to a user event, follow these steps:

1. Select the appropriate object (report, form, or control) and make sure the properties window is displayed.

2. Choose the Event Properties page on the properties window, or just scroll down the list until you find the event property you need.

3. Select the property, then click the down arrow button next to the property. Select [Event Procedure] from the list of options.

4. Click the “...” button to the right of the event name, as shown in Figure P-7. This is the Build button, and it appears next to properties window items that have associated builders. In this case, clicking the Build button takes you to a stub for the event procedure you need to create.

   

   Figure 7. Press the Build button to invoke the Choose Builder dialog

Property Names Versus Event Names

The naming of event properties, as opposed to the events themselves, is rather ambiguous in Access. The event properties, in general, have an “On” prefix, as in “OnClick” or “OnActivate.” The events themselves, however, are named without the “On” prefix, as in “the Click event” or “the Activate event.” We’ve tried to be consistent throughout the book, but there are some places where the context just doesn’t indicate which is the correct usage. You’ll need to be aware that with or without the “On” prefix, when the event occurs, it activates the procedure whose name is listed in the properties window for that event.

When you create a new event procedure, Access creates the subroutine name, fills in the parameters that it passes, and places the subroutine into the form or report’s class module. The name of the procedure is always the name of the object, followed by an underscore and the name of the event. For example, had you created the Click event procedure for the cmdClose command button, you’d see a code skeleton like this:

Sub cmdClose_Click(  )

End Sub

Now follow these steps to complete the process:

1. If the solution asks you to enter code into the event procedure, enter it between the lines of code that Access has created for you. Usually, the code example in the solution will include the Sub and End Sub statements, so don’t enter them again.

2. When you’re done, close the module window and save the form. By saving the form or report, you also save the form’s module.

How Do I Place Code in a Form or Report’s Module?

When a solution asks you to place a procedure in a form or report’s module that isn’t directly called from an event, follow these simple steps:

1. With the form or report open in design mode, choose View → Code, press F7, or click on the Code button on the toolbar, as shown in Figure P-8.

   

   Figure 8. Click on the Code toolbar button to view a form or report’s module

2. To create a new procedure, follow the steps in Section P.5.2, starting at Step 3.

3. Choose File → Save, close the module, then save the form, or just click on the Save icon on the toolbar.

How Do I Know What to Do with Code Examples?

In most cases, the solutions suggest that you import a module (or multiple modules) from the sample database for the particular solution, rather than typing in code yourself. In fact, code that isn’t referenced as part of the discussion doesn’t show up at all in the body of the solution. Therefore, you should count on importing modules as directed. Then follow the instructions in each solution to finish working with and studying the code.

If the solution tells you to place some code in a form’s module, follow the steps in Section P.5.6. If you are instructed to place code in a global module, follow the steps in Section P.5.2. In most cases, you’ll just import an existing module and won’t type anything at all.