MS-Access / Getting Started

---

Visual Basic Development Environment

In Access for Windows 95 (version 7.0), Visual Basic replaced the Access Basic programming language included with versions 1 and 2 of Access. The two languages are very similar because both Visual Basic and Access Basic evolved from a common design created before either product existed. (It's called Visual Basic because it was the first version of Basic designed specifically for the Windows graphical environment.) In recent years, Visual Basic has become the common programming language for Microsoft Office applications, including Access, Microsoft Excel, Microsoft Word, and Microsoft PowerPoint. Some of the Office 2010 system products (including Word 2010 and Excel 2010) can work with an even newer variant of Visual Basic-VB.NET-but Access 2010 does not.

Having a common programming language across applications provides several advantages. You have to learn only one programming language, and you can easily share objects across applications by using Visual Basic with object automation. Access 2010 uses the VBE common to all Office applications and to the Visual Basic programming product. The VBE provides color-coded syntax, an Object Browser, and other features. It also provides excellent tools for testing and confirming the proper execution of the code you write.

Modules

You save all Visual Basic code in your database in modules. Access 2010 provides two ways to create a module: as a module object or as part of a client form or client report object.

Note:
You cannot create a module as part of a web form or web report object in a web database. You can, however, create module objects and modules as part of client forms and client reports in a web database. Web objects do not support using Visual Basic so for the purposes of the discussions in this tutorial, you can assume we are always referring to client forms and client reports.

Module Objects

You can view the module objects in your database by clicking the top of the Navigation pane and then clicking Object Type under Navigate To Category. Click the Navigation Pane menu again, and click Modules under Filter By Group. (We also right-clicked the top of the Navigation pane, clicked View By on the shortcut menu, and then Details on the submenu so you can see the descriptions we've attached to all the modules.) You should use module objects to define procedures that you need to call from queries or from several forms or reports in your application. You can call a public procedure defined in a module from anywhere in your application.

To see all the modules in your database, click Modules under Filter By Group on the Navigation Pane menu when you have Navigate To Category set to Object Type. On the Create tab, in the Macros & Code group, click the Module command to create a new standard module.

To create a new module, click the Module or Class Module command in the Macros & Code group on the Create tab. When you click Module, Access creates a new standard module. You use a standard module to define procedures that you can call from anywhere in your application. It's a good idea to name modules based on their purpose. For example, you might name a module that contains procedures to perform custom calculations for queries modQueryFunctions, and you might name a module containing procedures to work directly with Windows functions modWindowsAPIFunctions.

Advanced developers might want to create a special type of module object called a class module. A class module is a specification for a user-defined object in your application, and the Visual Basic procedures you create in a class module define the properties and methods that your object supports. You create a new class module by clicking Class Module. You'll learn more about objects, methods, properties, and class modules later in this tutorial.

Form and Report Modules

To make it easy to create Visual Basic procedures that respond to events on client forms or client reports, Access 2010 supports a class module associated with each form or report. (You can design forms and reports that do not have a related class module.) A module associated with a form or report is also a class module that allows you to respond to events defined for the form or report as well as define extended properties and methods of the form or report. Within a form or report class module, you can create specially named event procedures to respond to Access-defined events, private procedures that you can call only from within the scope of the class module, and public procedures that you can call as methods of the class. For more information about objects and methods see the section "Collections, Objects, Properties, and Methods". You can edit the module for a form or a report by opening the form or report in Design view and then clicking the View Code button in the Tools group on the Design contextual tab (located under Form Design Tools). As you'll learn later, you can also open a form or a report by setting an object equal to a new instance of the form or report's class module.

Using form and report modules offers three main advantages over module objects:

• All the code you need to automate a form or a report resides with that form or report. You don't have to remember the name of a separate form-related or reportrelated module object.
• Access loads module objects into memory when you first reference any procedure or variable in the module and leaves them loaded so long as the database is open. Access loads the code for a form or a report only when the form or the report is opened. Access unloads a form or a report class module when the object is closed; therefore, form and report modules consume memory only when you're using the form or the report to which they are attached.
• If you export a form or report, all the code in the form or report module is exported with it.

However, form and report modules have one disadvantage: Because the code must be loaded each time you open the form or report, a form or report with a large supporting module opens noticeably more slowly than one that has little or no code. In addition, saving a form or report design can take longer if you have also opened the associated module and changed any of the code.

One enhancement that first appeared in Access 97 (version 8.0)-the addition of the Has- Module property-helps Access load forms and reports that have no code more rapidly. Access automatically sets this property to Yes if you try to view the code for a form or report, even if you don't define any event procedures. If HasModule is No, Access doesn't bother to look for an associated Visual Basic module, so the form or report loads more quickly.

Remember:
If you set the HasModule property to No in the property window, Access deletes the code module associated with the form or report. However, Access warns you and gives you a chance to change your mind if you set the HasModule property to No in error.