Two collections that we haven't discussed might seem a bit confusing. Remember how we said that AllForms and AllReports give us a list of forms and reports. Well, what about the Forms and Reports collections? What do they contain?
The Forms and Reports collections store details about the open forms and reports. So these are the ones that the user currently has open. This allows you to perform actions on the forms that the user currently has open, rather than the forms that are not being used. This is an important point, because you can use the Forms collection to change an open form, but any changes you make are not saved when the form is closed. The changes only exist while the form is open. If, however, the form is open in design view and you make changes, then these changes will be saved when you close the form, as long as you don't abandon the changes.
Let's just see this in action:
Close any open forms.
Create a new module, open the Immediate window, and type:
Press Enter and you should see the following error message:
This is because there are no forms open, so the Forms collection is empty. The Forms collection, like an array, starts at 0, so trying to look at this entry when there are no forms in the collection causes an error.
Now open a form, perhaps frmCompany , and try this line again:
The Forms collection is very useful when you need to perform tasks on open forms. For example, you may want to change the color scheme or current font. This is really simple because each form has another collection, Controls . This contains an entry for each control on a form. So you could easily build a control that changes the current font for all controls, just by looping through this collection.
Create a new subroutine called FormFonts , like so:
Sub FormFonts (strFont As String) Dim frmCurrent As Form Dim ctlControl As Control For Each frmCurrent In Forms For each ctlControl In frmCurrent.Controls ctlControl.FontName = strFont Next Next End Sub
Make sure that only the form frmCompany is open.
Run the new procedure from the Immediate window, passing in Rockwell as the font name, like so:
If you don't have this font, substitute the name of one you do, preferably one that is obviously different. Why not try Wingdings (if you don't mind not being able to read it)?
Press Return to run the procedure, and you should see another error message:
Now don't panic; this is expected. What we're doing is changing the font name for all of the controls. But what you may not realize is that not all controls have a font.
Switch back to Access and you'll see this:
Notice that all of the text boxes have had their font changed, but we seemed to have stalled at the Supplier . That's because this is a check box field and it doesn't have a font, therefore it has no FontName property. Its associated label does, but the field itself doesn't. So we get the error. Notice that the command buttons haven't been changed either - they do have a FontName property, but since the Supplier field is before them in the collection they haven't been reached yet.
So how do we get around this problem? How do we check for controls that don't have an associated font? Well, there are two ways to tackle this:
The first way is to check each control in the loop to see if it is one of the controls that has a font. We use code similar to this:
For Each frmCurrent In Forms For Each ctlControl In frmCurrent.Controls If ctlControl.Type <> acCheckBox Then ctlControl.Font = strFont End If Next Next
This uses the ControlType property of each control to see if the control is a checkbox ( acCheckBox is an intrinsic constant), and only changes the font if it isn't a checkbox. But again we run into a problem here, because there are several control types that don't have fonts: images, lines, etc. Should we check each one? Yes, we could, but this would make our code hard to read and maintain.
The second way is to use the error handling of VBA and Access. We're going to discuss error handling in detail later on in the book, but you've already seen we've got an error - Run-time error '438' . This is a fixed error number telling us the property is not supported. So what it's really saying is that the property we are trying to use doesn't exist. Now wouldn't it be nice if we could just say 'OK, I know it doesn't exist, so just skip trying to set the font for this control, and move onto the next control'? Well, with error handling we can.
Whenever a VBA error is generated, a special object is used to hold the error details - this is the Err object. We'll look at this in more detail later in the book, but for the moment all you have to know is that one of the properties of the Err object is the Number property - this is the error number that we were shown in the error dialog.
Using error handling allows us to tell VBA that when a given error occurs, it shouldn't just display an error message, but should run a special bit of code, which we can supply.
If you are in Access, switch back to the VBE.
Now change the code for the FormFonts procedure, so that it looks like this:
Sub FormFonts (strFont As String) On Error GoTo FormFonts_Err Dim frmCurrent As Form Dim ctlControl As Control For Each frmCurrent In Forms For Each ctlControl In frmCurrent.Controls ctlControl.FontName = strFont Next Next FormFonts_Exit: Exit Sub FormFonts_Err: If Err.Number = 438 Then Resume Next Else MsgBox Err.Description Resume FormFonts_Exit End If End Sub
Switch back to Access, and close and reopen the form. This makes sure that the font is reset to its default - remember our changes aren't saved.
Switch back to the VBE and run the procedure again.
Now if you switch back to Access you'll see that all of the text has changed to our new font.
How It Works
As we've said, we won't go into detail about the error handling, as this is covered fully in the debugging chapter. But let's briefly look at the collections a little more:
Every form has a collection of controls. This comprises everything on the form, and since it is held in a collection we can use the For Each statement to iterate through it. This sets the variable ctlControl to point to successive controls, and then tries to set the FontName property of this control. If the control doesn't support this property, then an error is generated. However, we are using On Error to trap errors, so our special bit of code is run. This is the bit of code after FormFonts_Err . In this section of code we check to see if the error number is 438 - remember this error number identifies that the FontName property doesn't exist. In this case we don't care, so we just tell the program to Resume at the Next statement. If the error number is anything other than 438 , then the error message is displayed, and the function exits.
Don't worry too much about this error handling code, as we will cover it in detail later. The thing to remember is that we now have a procedure that works on all open forms, irrespective of the controls they have on them.
Another important thing to remember is that changes you make to an open form are not saved when you close the form. Later in the book we'll see ways of making permanent changes to forms and reports by opening the form in design mode and making changes there.