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

 SubListAllForms() Dimint1AsInteger     'Printthenumberofformsintheproject Debug.PrintCurrentProject.AllForms.Count Debug.Print     'Enumerateeachformintheproject Forint1=0ToCurrentProject.AllForms.Count-1 Debug.PrintCurrentProject.AllForms.Item(int1).Name Debug.PrintCurrentProject.AllForms.Item(int1).IsLoaded Debug.Print Nextint1     EndSub 

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.

 SubListControlsOnOpenForms()  Dimfrm1AsForm,ctl1AsControl     'Enumerateallopenforms ForEachfrm1InForms Debug.Printfrm1.Name 'Enumerateeachcontrolonaspecificopenform ForEachctl1Infrm1.Controls Debug.Print "  " &ctl1.Name& ", " &_ IIf(ctl1.ControlType=acLabel, "label", "notlabel") Nextctl1 Nextfrm1     EndSub 

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.

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:

 SubHideAForm(frmNameAsString)  'Closeformifitisopensothatitcanbehidden IfCurrentProject.AllForms(frmName).IsLoaded=TrueThen DoCmd.CloseacForm,frmName EndIf     'Setform'sHiddenpropertyanddonotshowhidden 'objectsinDatabasewindow Application.SetHiddenAttributeacForm,frmName,True Application.SetOption "ShowHiddenObjects",False EndSub     SubUnhideAForm(frmNameAsString)      'Ifformishidden,setform'shiddenpropertytoFalse 'andopenform IfApplication.GetHiddenAttribute(acForm,frmName)=TrueThen Application.SetHiddenAttributeacForm,frmName,False DoCmd.OpenFormfrmName EndIf     EndSub 

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.

 SubFormToLookFor() Dimstr1AsString Dimstr2AsString     'SearchforformsintheNorthwinddatabase str1= "C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\Northwind_backup.mdb" 'Getthenameoftheformtosearchforfromtheuser. str2=InputBox("Enternameofformtosearchfor: ",_  "ProgrammingMicrosoftAccess2003")     'CallFormExistsInDBtocheckwhethertheformexists FormExistsInDBstr1,str2 EndSub     SubFormExistsInDB(str1AsString,str2AsString) DimappAccessAsAccess.Application,int1AsInteger 'ReturnreferencetoMicrosoftAccessapplication SetappAccess=NewAccess.Application     'Openadatabaseintheotherapplication appAccess.OpenCurrentDatabasestr1     'Checkwhethertheformexists Forint1=0To(appAccess.CurrentProject.AllForms.Count-1) If(appAccess.CurrentProject.AllForms.Item(int1).Name=_ str2)Then MsgBox "Form " &str2& " existsinthe " _ &str1& " database.",_ vbInformation, "ProgrammingMicrosoftAccess2003" GoToFormExistsExit EndIf Nextint1     'Reportthatformdoesnotexist MsgBox "Form " &str2& " doesnotexistinthe " _ &str1& " database."     'CloseotherAccessapplication FormExistsExit: appAccess.Quit SetappAccess=Nothing     EndSub 

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.