Using Access Collections and Objects | Beginning Access 2007 VBA

Earlier in this chapter, you briefly explored the difference between an object and a collection. To recap, collections can contain multiple objects. For example, you might use a forms collection to loop through all forms in the project, whereas you would refer to a particular form object when you wanted to deal with that specific form only.

Now that you have mastered the basic concepts of object-oriented programming and understand that objects are different from collections, let’s look at some of the most useful collections and objects that come with Access. The examples in this section are by no means an exhaustive list, but they provide you with the most common collections. A complete list can be obtained in the Visual Basic help documentation from the Visual Basic Editor.

The Application object is at the top of the Access object model and refers to the active Access application. It contains all other objects and collections. The Application object contains several child objects that are very useful. Several Application object examples will now be described.

The Forms Collection and Form Object

The Forms collection of the Application object contains all forms in the application. A Form object refers to a specific form in the Forms collection. Let’s look at an example.

Try It Out-Using the Forms Collection and Form Object

You can use the Forms collection and Form object to obtain a list of all the open forms in the application.

Create a new form for your database and switch to design view. Use the ToolBox shown in the Controls area of the Design ribbon to drag a list box control and a command button control onto the new form. You can select Cancel to any wizards that appear when you draw the controls.
Change the Name property of the list box control to lstForms.

Select the label for the list box. Set the Name property of the list box to lblForms and the Caption property to All forms in project:. Set the RowSourceType property of the list box to Value List.
Next, select the command button control. Set the Name property to cmdGetForms and the Caption property to Get List of Forms.
At this point, the form should look similar to the one shown in Figure 3-18.

Figure 3-18
Make sure to save the form by clicking the Save button and, when prompted, give the form the name frmTestForms.
Select the cmdGetForms command button and view the Properties dialog box. Select the Design ribbon Property Sheet if the Properties dialog box is not already displayed. Select the Events tab of the Properties dialog box, and select the Code Builder option to add the new On Click event. Add the code below to the newly created On Click event procedure.
```
  Private Sub cmdGetForms_Click() Dim frmForms As Form 'loop through forms collection and 'add name of each open form to list box For Each frmForms In Forms     Me.lstForms.AddItem (frmForms.Name) Next frmForms End Sub 
```
Save the code in the Visual Basic Editor and return to the design view of the form.
Run the form by selecting View from the toolbar. Click the Get List of Forms command button. You should see the names of all open forms displayed in the list box control, as shown in Figure 3-19.

image from book
Figure 3-19

How It Works

First, you created a new form and added a list box object and a command button object to the form. You then set several properties of the list box and command button objects using the Properties dialog box. Next, you created an event procedure to run whenever the user clicks the cmdGetForms command button. The event procedure looped through the Forms collection to obtain a list of all open forms in the application and added the name of any open form to the list box.

 Private Sub cmdGetForms_Click() Dim frmForms As Form 'loop through forms collection and 'add name of each open form to list box For Each frmForms In Forms     Me.lstForms.AddItem (frmForms.Name) Next frmForms End Sub

More forms are included in the database than the one displayed. Is this an error? No, it is not. The reason you see the name of only one form in the list box is that it is the only form that is open. You can use the other collections and objects, whether or not they are open, as you will see in a later example.

The Reports Collection and Report Object

The Reports collection contains all the open reports in the application, and a Report object refers to a specific report. Look at another example. The modBusinessLogic standard module can be added to the database to store the sub procedures used in the remaining examples. Suppose that you have the following TestReports procedure in the modBusinessLogic standard module in your database.

  Sub TestReports() Dim rptReport As Report 'loop through each open report in the reports collection 'and display the name in the Immediate window For Each rptReport In Reports     Debug.Print rptReport.Name Next rptReport End Sub

Notice how a local variable called rptReport is first declared as a Report object, and then the program loops through each Report object in the Reports collection to print the name of the report to the Immediate Window. If any report is currently open in your database, you see the report name in the Immediate Window (after you run the preceding code) if you type TestReports and press Enter. Otherwise, you get a blank response to indicate that no reports are currently open.

The CurrentProject Object

The CurrentProject object of the Application object returns a reference to the current project. The CurrentProject object has various properties, such as FullName and ProjectType, as shown here:

  Debug.Print Application.CurrentProject.FullName Debug.Print Application.CurrentProject.ProjectType

These two lines of code will print the FullName and ProjectType properties of the current project to the Immediate Window. The CurrentProject object also has several collections, such as: AllForms, AllReports, AllMacros, and others. When you want to obtain a list of all forms in the project, regardless whether the form is open, you can use the AllForms collection. Here is an example:

  Sub TestAllForms() Dim objAccObj As AccessObject Dim objTest As Object 'set the object equal to the Current Project Set objTest = Application.CurrentProject 'loop through each form in the AllForms collection 'and print information about each Form to the 'Immediate Window. For Each objAccObj In objTest.AllForms     Debug.Print objAccObj.Name Next objAccObj End Sub

The preceding code declares a new instance of the CurrentProject object called objTest. Then, the name of each object that is a form in the AllForms collection of the objTest object is written to the Immediate Window.

The DoCmd Object

The DoCmd object can be used to perform nearly any macro command or menu option from VBA code. You have seen the DoCmd object in earlier chapters in examples that opened forms, such as the one that follows:

  DoCmd.OpenForm "frmTestObjects"

The DoCmd object can also be used to open reports, go to a particular record, and obey numerous other commands that you issue from a menu option or a macro.

The Screen Object

The Screen object is used to refer to the form, datasheet, report, control, or data access page that currently has the focus. For example, to refer to the active control, you could use the following:

  Screen.ActiveControl.Name

Let’s look at another example.

Try It Out-Using the Screen Object

In this example, use the Screen object to obtain the name of the currently active form.

Open the frmTestForms form that was created earlier. Switch to the Visual Basic Editor and in the Immediate Window type the following:
```
  ? Screen.ActiveForm.Name 
```
The name of the currently active form is displayed in the Immediate Window (see Figure 3-20).

Figure 3-20

How It Works

In the current example, you used the Immediate Window to find the name of the active form on the screen, using the ? Screen.ActiveForm.Name command.

The Printers Collection and Printer Object

Another useful collection in the Application object is the Printers collection. The Printers collection is a collection of Printer objects and contains all printers available to an application. You can walk through an example to see how this works.

Try It Out-Using the Printers Collection and Printer Object

In this example, you use the Printers collection and Printer object to display the name of each printer device in the Debug window.

Add the following procedure to the modBusinessLogic standard module. If you have not yet created modBusinessLogic, do so by inserting a new module. Then add the following code to the new module:

  Sub TestPrinter() Dim prtAvailPrinters as Printer For Each prtAvailPrinters In Application.Printers     With prtAvailPrinters         Debug.Print "Printer name: " & .DeviceName & "Printer driver: " & _          .DriverName     End With Next prtAvailPrinters End Sub

Run the TestPrinter procedure from the Immediate Window, and you will see a list of printers available on your computer, similar to Figure 3-21.

Figure 3-21

How It Works

In this example, you created a new procedure to output a list of all printers on your computer. You started by creating a TestPrinter sub procedure in modBusinessLogic module.

  Sub TestPrinter()

Next, you declared a local variable as a Printer object.

  Dim prtAvailPrinters as Printer

Then, you used a For Each loop to iterate through all the Printer objects in the Printers collection and printed the device name and driver name of each Printer object to the Immediate Window.

 For Each prtAvailPrinters In Application.Printers     With prtAvailPrinters         Debug.Print "Printer name: " & .DeviceName & "Printer driver: " & _          .DriverName     End With     Next prtAvailPrinters

The results of all printers on your computer were then displayed in the Immediate Window when you executed the procedure from the Immediate Window. It may take a few seconds for the complete list of printers to appear.

Other Objects

At this point, you should have an idea of the various ways you can use objects in your Access applications to perform various tasks. The Application objects that you are most likely to use as a beginning VBA programmer were illustrated. However, other Application objects in Access were not described in this chapter. Consult the Visual Basic help documentation from the Visual Basic Editor for more information.

Furthermore, numerous other libraries that contain useful objects are available for use in your Access applications. One example in Chapter 10 includes using Microsoft Office objects (such as the CommandBar object) that are common to all Microsoft Office programs. Chapter 10’s example uses the Microsoft Office CommandBar object to customize the command bar you see in your applications. If you want to learn more about the objects that are available for use in your applications, consult the Object Browser to view them or review the Help documentation.