Team LiB
Previous Section Next Section

Manipulating Forms with VBA

This section describes some techniques for automating the use of forms. In particular, it illustrates how to enumerate forms and controls, techniques for programmatically hiding and showing forms, and methods for listing the forms in another project.

Enumerating Forms and Controls

The AllForms collection, which belongs to the CurrentProject object, contains an item for each form in a project. An application can enumerate the AccessObject objects in the AllForms collection to find all the forms in a project. The AccessObject object's Name and IsLoaded properties are particularly convenient: the Name property represents the name of each form in a project, and the IsLoaded property indicates whether the form is open. If you need to know whether a form with a certain name is in a project, manipulating the AllForms collection is the way to do it—whether or not the form is open.

The following procedure uses the Count property of the AllForms collection to determine how many forms are in the current project. Then, it prints the name and loaded status of each form in the project. (The AllForms collection is indexed beginning with 0; therefore, the For loop runs from 0 to one less than the total number of forms in the project.)

Sub ListAllForms()

Dim int1 As Integer
   
'Print the number of forms in the project
Debug.Print CurrentProject.AllForms.Count
Debug.Print
   
'Enumerate each form in the project
For int1 = 0 To CurrentProject.AllForms.Count - 1
    Debug.Print CurrentProject.AllForms.Item(int1).Name
    Debug.Print CurrentProject.AllForms.Item(int1).IsLoaded
    Debug.Print
Next int1
   
End Sub

The Forms collection contains the set of all open forms in a project, and the Controls collection of the Form object contains the set of controls on a form. Your applications can use these collections to find a specific form and an individual control on that form.

The following procedure enumerates all open forms in a project. For each open form, the procedure lists the form's controls by name and type. The Control object's ControlType property indicates the type of control. You can use the TypeOf keyword in a similar way.

Sub ListControlsOnOpenForms() 
Dim frm1 As Form, ctl1 As Control
   
'Enumerate all open forms
For Each frm1 In Forms
    Debug.Print frm1.Name
    
'Enumerate each control on a specific open form
    For Each ctl1 In frm1.Controls
        Debug.Print "     " & ctl1.Name & ", " & _
            IIf(ctl1.ControlType = acLabel, "label", "not label")
    Next ctl1
Next frm1
   
End Sub

Notice that the procedure decodes the value of the ControlType property. When this value is the intrinsic constant acLabel, the control is a label. In a practical decoding exercise, you are more likely to use a Select Case statement than the Immediate If (IIf) function in the preceding sample. The Immediate If function, however, works adequately for decoding a single value. You can view the complete list of ControlType intrinsic constants in the Object Browser, as shown in Figure 5-24. From this window, you can get additional help about any form's control type.

Click To expand
Figure 5.24: The complete list of Access form control types from the Object Browser.

Hiding and Showing Forms

You can use VBA and the AllForms collection along with some other objects to make forms invisible in the Database window. If you also make the form invisible in your application, the user might think that you removed the form. Hidden forms can still expose values for use by the other objects in an application.

The following pair of procedures hide and unhide an Access form:

Sub HideAForm(frmName As String)  

'Close form if it is open so that it can be hidden
If CurrentProject.AllForms(frmName).IsLoaded = True Then
    DoCmd.Close acForm, frmName
End If
   
'Set form's Hidden property and do not show hidden
'objects in Database window
Application.SetHiddenAttribute acForm, frmName, True
Application.SetOption "Show Hidden Objects", False
End Sub
   
Sub UnhideAForm(frmName As String)  
   
'If form is hidden, set form's hidden property to False
'and open form
If Application.GetHiddenAttribute(acForm, frmName) = True Then
    Application.SetHiddenAttribute acForm, frmName, False
    DoCmd.OpenForm frmName
End If
   
End Sub

The SetHiddenAttribute method sets or clears the Hidden attribute from the Database window for database objects, such as forms, reports, and queries. This method takes two arguments, an AccessObject object and a Boolean argument that indicates whether the object is to be hidden. Calling this method with an object and the value True is the same as setting the object's Hidden property in the Database window.

By itself, SetHiddenAttribute just grays the object; users can still select and use it. To make hidden objects invisible to the user, choose Options from the Tools menu, click Hidden Objects, and then click OK.

Before invoking SetHiddenAttribute, you should check the AccessObject object's IsLoaded property. If the object is loaded, you should close it before attempting to invoke SetHiddenAttribute; calling the method with an open object generates an error.

Enumerating Forms in Another Project

VBA does not restrict you to working with database objects in the current project. For example, you can test for the existence of forms in another instance of an Access application. One essential step in this process is to compare the Name property of AllForms members to the name of the target form. There is also a new trick to learn: You open a new instance of an Access Application with the target database in it, and then you use the CurrentProject object of that instance as the source for an AllForms collection. This subtle refinement lets you process database objects in another database file—namely, the one at which the Access Application instance points.

The following two procedures implement this technique with VBA. FormToLookFor sets the database path to the other database file and gathers the name of the target form. The second procedure, FormExistsInDB, searches for a target form. You call the second procedure from the first one.

Sub FormToLookFor()
Dim str1 As String
Dim str2 As String
   
'Search for forms in the Northwind database
str1 = "C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind_backup.mdb"
'Get the name of the form to search for from the user.
str2 = InputBox("Enter name of form to search for: ", _
    "Programming Microsoft Access 2003")
   
'Call FormExistsInDB to check whether the form exists
FormExistsInDB str1, str2
End Sub
   
Sub FormExistsInDB(str1 As String, str2 As String)
Dim appAccess As Access.Application, int1 As Integer
 
'Return reference to Microsoft Access application
Set appAccess = New Access.Application
   
'Open a database in the other application
appAccess.OpenCurrentDatabase str1
   
'Check whether the form exists
For int1 = 0 To (appAccess.CurrentProject.AllForms.Count - 1)
    If (appAccess.CurrentProject.AllForms.Item(int1).Name = _
        str2) Then
        MsgBox "Form " & str2 & " exists in the " _
            & str1 & " database.", _
            vbInformation, "Programming Microsoft Access 2003"
        GoTo FormExistsExit
    End If
Next int1
   
'Report that form does not exist
MsgBox "Form " & str2 & " does not exist in the " _
    & str1 & " database."
   
'Close other Access application
FormExistsExit:
appAccess.Quit
Set appAccess = Nothing
   
End Sub

The first procedure sets str1 equal to the path for the Northwind database. An InputBox function prompts the user to input the name of the form to search for, and then the first procedure calls the second procedure.

The second procedure sets and opens a reference for the new instance of the Access application, and then enters a loop that checks whether any of the forms in the new database match the target form name. The procedure reports whether it found the target form and frees its resources before returning.


Team LiB
Previous Section Next Section