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 form 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.

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 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, it 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 = 100, "label", "not label")         Next ctl1     Next frm1 End Sub 

Notice that the procedure decodes the value of the ControlType property. When this value is 100, the control is a label. In a practical decoding exercise, you are more likely to use a Select Case statement instead of the Immediate If function in the preceding sample. The Immediate If function, however, works adequately for decoding a single value. (You can use the Object Browser to find the ControlType values.)

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 SetHiddenAtrribute 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, SetHiddenAtrribute 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 form with the target database in it, and then you use the current project of that instance as the source for your AllForms collection. This subtle refinement lets you process database objects in another database file.

The following two procedures implement this 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 form from the first one.

Sub FormToLookFor() Dim strDB As String Dim strFormName As String 'Search for forms in the Northwind database.     strDB = "C:\Program Files\Microsoft " & _         "Office\Office\Samples\Northwind.mdb" 'Get the name of the form to search for from the user.     strFormName = InputBox("Enter the name of the form to search for: ", _         "Programming Microsoft Access 2000")      'Call FormExistsInDB to check whether the form exists.     FormExistsInDB strDB, strFormName End Sub Sub FormExistsInDB(strDB As String, strFormName 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 strDB      'Check whether the form exists.     For int1 = 0 To (appAccess.CurrentProject.AllForms.Count - 1)         If (appAccess.CurrentProject.AllForms.Item(int1).Name = _             strFormName) Then             MsgBox "Form " & strFormName & " exists in the.", _                 & strDB & " database.", _                 vbInformation, "Programming Microsoft Access 2000"             GoTo FormExistsExit         End If     Next int1 'Report that form does not exist.     MsgBox "Form " & strFormName & " does not exist in the.", _         & strDB & " database."      'Close other Access application. FormExistsExit:     appAccess.CloseCurrentDatabase     Set appAccess = Nothing End Sub 

The first procedure sets strDB equal to a typical path for the Northwind database. If you have your copy of Northwind elsewhere, you should update this path. 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.