There are two types of modules: standard modules and class modules. Standard modules are modules that contain procedures that are not associated with any particular object. Class modules are modules that are associated with a particular object. Class modules can be used either for form or report modules or for custom objects accessible throughout your VBA application. For example, form and report modules can contain code that corresponds to a particular form or report. An example of a form module is an event procedure, such as the click event for a control on a form. Since Access 97, class modules can also be created independently of a form or report to create a definition for a custom object. The current chapter will focus on standard modules and class modules that are associated with a form or report. Custom objects are discussed in detail in Chapter 4.
Important | In general, it is a good programming practice to write most or all of your business logic and data access for the application in procedures that are contained in standard modules and keep the presentation logic in class modules associated with the form or report. This separation of code concept was briefly introduced in Chapter 1 in the architecture section. |
One way to create a module is to select the Modules tab in the database window and click the New button. Another way to create a module is to select Insert Module. Let’s now create a sample database with a form and a module that can be used for the examples throughout this chapter. This will also illustrate how to create a new module. I will continue with the discussion on modules and procedures after the database has been created.
Try it Out-Create a Database to Use with Examples in the Chapter
Let’s jump right in by creating the database that can be used with the examples in this chapter.
Create a new database. To do so, the Office Button (where the File menu used to be) New Blank Database. Next, navigate to the path where you want to save the database and specify Ch2CodeExamples.accdb as the File Name field. Click the Create button and the new database will be created. An empty table is displayed on the screen. You can simply close the table view by selecting the X in the right corner, since a new table is not used in this example.
Tip | Note that the default format for databases in Access is 2007 (ACCDB file format), but if you need the compatibility with Access 2002 or 2003 (MDB file format), you should change it to 2002–2003 format. You can change this option by selecting the Office Button Access Options, and on the Personalize Tab changing the Default File Format option from Access 2007 to Access 2002–2003. |
Add a new form to your database. To do so, select the Create ribbon and then select the Blank Form option.
The form opens in Layout View. To switch to Design View, select a blank area of the form, right click, and choose Design View from the pop-up menu that appears. Alternatively, you can change the view by selecting View Design View from the toolbar.
Use the controls shown in the toolbar to select and draw two text boxes and one command button onto the form, as shown in Figure 2-3. You can select Cancel to exit the Button Wizard that appears after you draw the button on the form.
Figure 2-3
Select the Design ribbon, and then Property Sheet in the Tools area of the toolbar to display the Properties dialog box for the form. Alternatively, you can press Alt+ Enter to open the Properties dialog box. Change the Name properties of the first text box, second text box, and command button to txtValue1, txtValue2, and cmdRun, respectively. If you select a particular control, such as the first text box, you can set its properties in the Properties dialog box. Figure 2-4 provides an example of how to change the Name property of the selected control, which in this example is txtValue1.
Figure 2-4
Change the Name property of the first text box label to lblValue1 and its Caption property to Value 1. Change the Name property of second text box label to lblValue2 and the Caption property to Value 2. Change the cmdRun Caption property to Run. After these changes have been made, the finished form should look like that shown in Figure 2-5.
Figure 2-5
Save the form. Select Office Button Save, or click the Save icon on the top right, or click X in the upper-right corner of the form itself. When prompted, specify the name for the form as frmTest. Close the form by clicking the X in the upper-right corner of the form itself, if you have not already done so.
Tip | After frmTest is saved, it appears in the left navigation pane as an “Unrelated Object.” |
Create a new module. Select the Create ribbon from the toolbar, and then select Macro in the area of the toolbar labeled Other, as shown in Figure 2-6. The drop-down with the Create Module option displays Macro, Module, and Class Module as the options in the list.
Figure 2-6
The Visual Basic Editor window appears. Use the Properties window of the Visual Basic Editor to rename the module to modBusinessLogic, as shown in Figure 2-7. If the Properties Window is not already displayed, you can select View Properties Window to display it.
Figure 2-7
Tip | Figure 2-7 contains an Option Explicit statement in the declarations section of the module. If the module you just created does not have the Option Explicit statement, you should select Tools Options and select the Require Variable Declaration option in the Code Settings area of the Editor tab and then reinsert the module. The Option Explicit statement then appears. If it still doesn’t appear, type “Option Explicit” after the line “Option Compare Database.” This Option Explicit option requires variables to be defined before they can be used and promotes better coding habits. |
How It Works
You just created a new Access 2007 database, added a new form with three controls, and added a new module. No real code is written yet, so the database will not really do anything at this point. But you will be writing code in the new database throughout the rest of this chapter to illustrate various coding concepts.