When you create a new database, you typically begin by creating a few database objects such as tables, forms, and reports. Eventually, you reach a point where you need to add some programming to automate certain processes and tie your database objects together. This article helps orient you to the programming tools in Microsoft Office Access 2007 and points you to some resources where you can learn more about programming.
Tip In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.
Watch a video or try Office 2010.
In this article
What is programming?
In Office Access 2007, programming is the process of adding functionality to your database by using Access macros or Visual Basic for Applications (VBA) code. For example, suppose you have created a form and a report, and you want to add a command button to the form that, when clicked, opens the report. Programming, in this case, is the process of creating a macro or VBA procedure and then setting the command button's OnClick event property (event property: A named attribute of a control, form, report, data access page, or section you use to respond to an associated event. You can run a procedure or macro when an event occurs by setting the related event property.) so that clicking the command button runs the macro or procedure. For a simple operation, such as opening a report, you can use the Command Button Wizard to do all the work, or you can turn off the wizard and do the programming yourself.
Note Many Microsoft Office programs use the term "macro" to refer to VBA code. This can be confusing to Access users because, in Access, the term "macro" refers to a named collection of macro actions that you can assemble by using the Macro Builder. Access macro actions represent only a subset of the commands available in VBA. The Macro Builder gives you a more structured interface than the Visual Basic Editor, enabling you to add programming to controls and objects without having to learn VBA code. You should remember that in Access Help articles, Access macros are referred to as macros. Conversely, VBA code is referred to as either VBA, code, a function, or a procedure. VBA code is contained in class modules (which are part of individual forms or reports and typically contain code just for those objects) and in modules (which are not tied to specific objects and typically contain "global" code that can be used throughout the database).
Objects (such as forms and reports) and controls (such as command buttons and text boxes) have a wide variety of event properties to which you can attach macros or procedures. Each event property is associated with a specific event, such as clicking the mouse, opening a form, or modifying data in a text box. Events can also be triggered by factors outside of Access, such as system events, or by macros or procedures that are attached to other events. Your database can get complex if you add numerous macros or procedures to several event properties of many objects, but in most cases, you can achieve the results you want by using very little programming.
Top of Page
Should I use macros or should I use VBA code?
You should base your decision to use either macros or VBA code on two concerns: security and the functionality that you need. Security is an issue because VBA can be used to create code that either compromises the security of your data or can harm files on your computer. When you use a database that was created by someone other than yourself, you should enable VBA code only if you know the database comes from a trustworthy source. When you create a database that will be used by others, you should try to avoid including programming tools that require the user to specifically grant trusted status to the database. General techniques for avoiding the need for users to trust your database come later in this section.
For more information about enabling or disabling potentially unsafe content, see the article Enable or disable macros in Office documents (as mentioned elsewhere in this article, most other Microsoft Office programs use the term "macro" to refer to VBA code, so don't confuse the term with Access macros).
To help ensure the security of your database, you should try to use macros whenever possible and resort to VBA programming only for operations that cannot be accomplished by using macro actions. Furthermore, you should try to use only macro actions that don't require granting trusted status to the database in order to run. Limiting the use of macro actions in this way allows your users to be confident that the database does not contain any programming that could harm their data or other files on their computers.
Partly to encourage the wider use of macros, Office Access 2007 contains many new macro actions that allow you to build more powerful macros than you can build by using earlier versions of Access. For example, you can now create and use global temporary variables by using macro actions, and you can handle errors more gracefully by using new error-handling macro actions. In earlier versions of Access, these types of features are available only by using VBA. In addition, in Access 2007, you can embed a macro directly into the event property of an object or control. An embedded macro becomes a part of the object or control and stays with the object or control if it is moved or copied. In earlier versions of Access, macros must be maintained as separate objects, which sometimes makes your database more difficult to maintain.
When you add programming to an object or control, you should consider using the following in this order of preference:
- A macro containing only actions that don't require granting trusted status to the database in order to run
- A macro containing actions that do require granting trusted status to the database in order to run
- A VBA procedure
When you build a macro, you can switch between the shorter list of actions that don't require granting trusted status to the database in order to run and the longer list of all macro actions by clicking Show All Actions in the Macro Builder. Clicking Show All Actions also shows the complete list of arguments for the RunCommand macro action. For more information about building macros, see the section Understand macros, or follow the links in the See Also section.
Macros provide an easy way to take care of many programming tasks, such as opening and closing forms and running reports. You can quickly and easily tie together the database objects (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) that you have created because there is little syntax that you need to remember; the arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) for each action are displayed in the Macro Builder.
In addition to the increased security and ease of use that macros provide, you must use macros if you want to:
- Assign an action or set of actions to a key. This requires creating a macro group named AutoKeys.
- Carry out an action or a series of actions when a database first opens. This requires creating a macro named AutoExec.
Note The AutoExec macro runs before any other macros or VBA code, even if you have designated a startup form in the Access Options dialog box and attached a macro or VBA code to that form's OnOpen or OnLoad event.
You should use VBA programming instead of macros if you want to do any of the following:
- Use built-in functions, or create your own functions Access includes many built-in functions, such as the IPmt function, which calculates an interest payment. You can use these built-in functions to perform calculations without having to create complicated expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.). By using VBA code, you can also create your own functions either to perform calculations that exceed the capability of an expression or to replace complex expressions. In addition, you can use the functions that you create in expressions to apply a common operation to more than one object.
- Create or manipulate objects In most cases, you will find that it is easiest to create and modify an object in that object's Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.). In some situations, however, you might want to manipulate the definition of an object in code. By using VBA, you can manipulate all the objects in a database, in addition to the database itself.
- Perform system-level actions You can carry out the RunApp action in a macro to run another program (such as Microsoft Office Excel 2007) from within Access, but you can't use a macro to do much else outside of Access. By using VBA, you can check to see whether a file exists on the computer, use Automation (Automation: A way to work with an application's objects from another application or development tool. Formerly called OLE Automation, Automation is an industry standard and a feature of the Component Object Model (COM).) or Dynamic Data Exchange (DDE) (Dynamic Data Exchange (DDE): An established protocol for exchanging data between Microsoft Windows-based programs.) to communicate with other Microsoft Windows-based programs, such as Office Excel 2007, and call functions in Windows dynamic-link libraries (DLLs) (Dynamic Link Library: A set of routines that can be called from Visual Basic procedures and are loaded and linked into your application at run time.).
- Manipulate records one at a time You can use VBA to step through a set of records, one record at a time, and perform an operation on each record. In contrast, macros work with entire sets of records at one time.
If you do use VBA code in a database that you distribute to others, you should consider packaging and signing the database so that the users can be sure that the code comes from a trustworthy source. For more information about packaging and signing a database, see the article Secure an Access 2007 database.
Top of Page
Use the Command Button Wizard to perform common programming tasks
If you are adding a command button to a form, the Command Button Wizard can help you get started with programming. The wizard walks you through the process of creating a command button that performs a specific task. In an Office Access 2007 (.accdb) file, the wizard creates a macro that is embedded in the OnClick property of the command button. In an .mdb or .adp file, the wizard creates VBA code, because embedded macros are not available in those file formats. In either case, you can then modify or enhance the macro or VBA code to better suit your needs.
- Click the Microsoft Office Button , and then click Open.
- In Open dialog box, select and open the database.
- In the Navigation Pane, right-click the form to which you want to add the command button, and then click Design View.
- On the Design tab, in the Controls group, ensure that Use Control Wizards is selected.
- On the Design tab, in the Controls group, click Button.
- In the form design grid, click where you want the command button to be placed.
The Command Button Wizard starts.
- On the first page of the wizard, click each category in the Categories list to see which actions the wizard can program the command button to perform. In the Actions list, select the action that you want, and then click Next.
- Click either the Text option or the Picture option, depending on whether you want text or a picture to be displayed on the command button.
- If you want text to be displayed, you can edit the text in the box next to the Text option.
- If you want a picture to be displayed, the wizard suggests a picture in the list. If you want to select a different picture, select the Show All Pictures check box to display of all the command button pictures that Office Access 2007 provides, or click Browse to select a picture that is stored elsewhere.
- Enter a meaningful name for the command button. This is an optional step, and this name is not displayed on the command button. However, it is a good idea to enter a meaningful name so that when you need to refer to the command button later (for example, if you are setting the tab order for controls on your form), it will be much easier to differentiate between the command buttons. If the command button closes the form, for example, you might name it cmdClose or CommandClose.
- Click Finish.
Access places the command button on the form.
- If you want to see what the wizard "programmed" for you, follow these optional steps:
- If the property sheet is not already displayed, press F4 to display it.
- Click the Event tab in the property sheet.
- In the On Click property box, click .
Access starts the Macro Builder and displays the macro that the wizard created. You can edit the macro if you want (for more information about editing a macro, see the section Understand macros, or follow the links in the See Also section). When you are finished, on the Design tab, in the Close group, click Close to close the Macro Builder. If Access prompts you to save the changes and update the property, click Yes to save the changes or No to reject the changes.
- On the Design tab, in the Views group, click View, and then click Form View. Click the new command button to confirm that it works as you expected.
Top of Page
A macro is a tool that allows you to automate tasks and add functionality to your forms, reports, and controls. For example, if you add a command button to a form, you associate the button's OnClick event property to a macro that contains the commands that you want the button to perform each time it is clicked.
It is helpful to think of Access macros as a simplified programming language in which you create code by building a list of actions (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.) to perform. When you build a macro, you select each action from a drop-down list and then fill in the required information for each action. Macros enable you to add functionality to forms, reports, and controls without writing code in a VBA module. Macros provide a subset of the commands that are available in VBA, and most people find it easier to build a macro than to write VBA code.
You create a macro by using the Macro Builder, which is shown in the following illustration.
To display the Macro Builder:
- On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.
For more information about creating macros, follow the links in the See Also section of this article.
Top of Page
Understand VBA code
VBA is a programming language that you can use to create powerful applications in Access. VBA includes hundreds of commands that allow you to perform much more complex operations than you can by using Access macros.
You can extend the power of VBA by using third-party controls, and you can write your own functions and procedures for your own specific needs. You can also use VBA to integrate Access with other programs.
A quick way to get started with VBA programming is to first build an Access macro and then convert it to VBA code. Instructions for doing this are included in the section Convert macros to VBA code. This feature creates a new module containing a VBA Function procedure (Function procedure: A procedure that returns a value and that can be used in an expression. You declare a function with the Function statement and end it with the End Function statement.) that performs the equivalent operations in the macro. It also opens the Visual Basic Editor so that you can begin modifying the procedure. When you are working in the Visual Basic Editor, you can click keywords and press F1 to start Access Developer Help and learn more about each keyword. You can then explore Access Developer Help and discover new commands to help you accomplish the programming tasks that you want.
Top of Page
Convert macros to VBA code
You can use Office Access 2007 to automatically convert macros to VBA modules or class modules. You can convert macros that are attached to a form or report, whether they exist as separate objects or as embedded macros. You can also convert global macros that are not attached to a specific form or report.
Convert macros that are attached to a form or report
This process converts to VBA any macros that are referred to by (or embedded in) a form or report (or any of its controls) and adds the VBA code to the form or report's class module. The class module becomes part of the form or report and moves with the form or report if it is moved or copied.
- In the Navigation Pane, right-click the form or report, and then click Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
- On the Database Tools tab, in the Macro group, click either Convert Form's Macros To Visual Basic or Convert Report's Macros To Visual Basic.
- In the Convert form macros or Convert report macros dialog box, select whether you want Access to add error handling code to the functions it generates. Also, if you have any comments in your macros, select whether you want to have them included as comments in the functions. Click Convert to continue.
If no class module exists for the form or report, Access creates one and adds a procedure to the module for each macro that was associated with the form or report. Access also changes the event properties of the form or report so that they run the new VBA procedures instead of the macros.
- To view and edit the VBA code:
- While the form or report is still open in Design view, if the property sheet is not already displayed, press F4 to display it.
- On the Event tab of the property sheet, click in any property box that displays [Event Procedure], and then click . To view the event properties for a specific control, click the control to select it. To view the event properties for the entire form or report, select Form or Report from the drop-down list at the top of the property sheet.
Access opens the Visual Basic Editor and displays the event procedure in its class module. You can scroll up or down to view any other procedures that are in the same class module.
Convert global macros
- In the Navigation Pane, click the name of the macro that you want to convert.
- On the Database Tools tab, in the Macro group, click Convert Macros To Visual Basic.
- In the Convert Macro dialog box, select the options that you want, and then click Convert.
Access converts the macro and opens the Visual Basic Editor.
- To view and edit the VBA code:
- In the Visual Basic Editor, if the Project Explorer pane is not displayed, on the View menu, click Project Explorer.
- Expand the tree under the name of the database in which you are working.
- Under Modules, double-click the module Converted Macro- macro name.
The Visual Basic Editor opens the module.
Attach a VBA function to an event property
When you convert a global macro to VBA, the VBA code is placed in a standard module. Unlike a class module, a standard module is not part of a form or report. You will most likely want to associate the function with an event property on a form, report, or control so that the code runs precisely when and where you want. To do this, either you can copy the VBA code into a class module and then associate it with an event property, or you can make a special call from the event property to the standard module by using the following procedure.
- In the Visual Basic Editor, make a note of the function name. For example, if you converted a macro named MyMacro, the function name will be MyMacro().
- Close the Visual Basic Editor.
- In the Navigation Pane, right-click the form or report with which you want to associate the function, and then click Design View.
- Click the control or section with which you want to associate the function.
- If the property sheet is not already displayed, press F4 to display it.
- On the Event tab of the property sheet, click the event property box with which you want to associate the function.
- In the property box, type an equal sign (=) followed by the name of the function — for example, =MyMacro(). Be sure to include the parentheses.
- Save the form or report by clicking Save on the Quick Access Toolbar.
- In the Navigation Pane, double-click the form or report, and test it to see that the code runs as it should.
You now know the basic steps to add VBA code to your database. This article provides just the basics of how to get started; there are many excellent reference books and online resources that can help you build your programming skills.
Top of Page
Learn how to use the Help system
Office Access 2007 provides a variety of ways to get assistance with your programming. You can get Help at any time by clicking the Help button in the upper-right corner of the Access window.
Access displays the Help window.
Also, when you are building a macro or VBA procedure, you can click a macro action, keyword, or built-in function name and then press F1. In most cases, Access displays the Help topic for the item that you clicked.
After the Help window is open, use the navigation buttons on the toolbar to move between pages.
For example, click Home to view the table of contents, and then click the Back, Forward, Stop, or Refresh button to move between topics or to reload a topic. You can also search the Help system for specific information by entering search terms in the box just below the toolbar and then pressing ENTER or clicking Search.
When you search for information about programming, it is usually helpful to explore Access Developer Help in addition to the Access Help.
Switch to Access Developer Help
- Click the drop-down arrow next to the Search button.
The Help window displays a list of available Help subjects that you can browse through.
- If you are connected to the Internet, under Content from Office Online, click Developer Reference. We recommend using this method, because the content from the Microsoft Office Online Web site is always the most up-to-date. If you are not connected to the Internet, or if your system administrator has disabled the ability of the Help window to go online, you can still view Access Developer Help by clicking Developer Reference under Content from this computer.
- You can browse through Help by using the links in the table of contents or by entering search terms in the box just below the toolbar and then pressing ENTER.
- To switch back to Access Help, click the drop-down arrow next to the Search button, and then click Access Help under Content from Office Online or Content from this computer.
For more information about using offline or online Help, click the Connection Status menu in the lower-right corner of the Help window, and then click Explain these options.
Top of Page