[AccessD] Building a Control Scanner

I had intended to implement Stuart’s change log using his code however it is pretty specific to his 
application so we will (eventually) try to do the same thing with a more generic log.  In order to 
set this up however I am going to build a control scanner and show you how that works.

A form has a collection called the controls collection.  In that collection will be a pointer to 
each control on the form.  The basic structure of the control scanner is as follows:

Private Function CtlScanner()
Dim ctl As Control
     For Each ctl In mfrm.Controls
         Select Case ctl.ControlType
             Case acCheckBox
             Case acComboBox
             Case acCommandButton
             Case acListBox
             Case acOptionButton
             Case acOptionGroup
             Case acPage
             Case acSubform  'subform controls
             Case acTabCtl   'tab pages are handled in the tab control
             Case acTextBox  'Find all text boxes and load class to change backcolor
             Case acToggleButton
         End Select
     Next ctl
End Function

•	Cut and paste that into the clsFrm down at the bottom.  Next we need to cause the control scanner 
to be called when mInit runs so add a new line to the mInit function as below:

Function mInit(lfrm As Form)
     Set mfrm = lfrm
     mfrm.BeforeUpdate = cstrEvProc
     mfrm.OnClose = cstrEvProc
     CtlScanner
End Function

Now, when mInit executes it drops into CtlScanner and runs through all of the controls in the form’s 
Control collection.  At the moment it doesn’t do anything but the scanner runs.

If you place your cursor over the acCheckBox (or any of the constants) and hit Shift-F2, you will be 
taken to a list of the constants.  Notice that I did not place every constant in the case statement, 
though you are free to do so.  The ones I left out are not data aware controls, nor controls that 
you normally write code for.  So these are the major players and you now have a way to find out that 
they exist in your form class and do something.  That something will be to load a class and save a 
pointer to that class.

In preparation for doing more with the form class we will add a private colCtls variable in the form 
header, and start to USE the Initialize and Terminate events of the class.

•	In the header of the class insert the following line:

Private colCtls As Collection

•	Also replace the message box code in the Class_Initialize and Class_Terminate with code that 
initializes and cleans up a collection pointer for us:

Private Sub Class_Initialize()
     Set colCtls = New Collection
End Sub

Private Sub Class_Terminate()
     Set colCtls = Nothing
End Sub

In this lecture we have created a method to iterate the form’s control collection.  We have 
dimensioned a collection to hold a class for each control type sometime in the future, and we have 
modified the Initialize and Terminate events to set up and tear down that collection automatically 
as the class opens and closes.

-- 
John W. Colby
www.ColbyConsulting.com