We mentioned earlier in the book that all forms and reports are able to have class modules associated with them. In fact, forms and reports do not have associated class modules by default. The class module is only created when you first attempt to view or enter code in the form's class module. You can actually tell whether a form or report has an associated module by inspecting its HasModule property. This returns True or False to indicate whether the object has an associated class module. This property is read-only at run-time but can be written to at design time.
You can also tell whether a form or report has an associated class module by looking in the Project Explorer w indow in VBA. To open the explorer window from the code editor, tap Ctrl-R or click View Project Explorer from the menu. If the form or report has a class module it will be listed as a Microsoft Access Class Object .
We create custom properties for forms and reports in just the same manner as we do for other classes. The easiest way to see this is to try it out for yourself - so let's do it! In the following example, we will create a Maximized property for the form frmSales and define what happens when the property is set.
Open the Chapter 13 Code module that we have been using in this chapter and type the following declaration in the Declarations section of the form's module:
Public Declare Function IsZoomed Lib "User32" (ByVal hWnd As Long) As Integer
Now open the code module for the form frmSales and type in the two new procedures listed below:
Public Property Get Maximized() As Boolean If IsZoomed(Me.hWnd) Then Maximized = True Else Maximized = False End If End Property Public Property Let Maximized(blnMax As Boolean) If blnMax Then Me.SetFocus DoCmd.Maximize Else Me.SetFocus DoCmd.Restore End If End Property
Close frmSales , saving the changes that you have made. Then open it up in Form view. Make sure it isn't maximized and it should look something like this:
The important thing to notice on this form is that the control buttons in the top right corner indicate that the form is not maximized.
Now switch to the Immediate window by hitting Ctrl+G .
Inspect the form's Maximized property by typing the following in the Immediate window and hitting the Enter key.
It should return False , indicating that the form is not maximized.
Now switch back to Access and maximize frmSales . Then inspect its Maximized property again in the Immediate window. This time it should return True , indicating that the form is maximized.
Finally set the form's Maximized property to False in the Immediate window with the following statement.
If you switch back to Access, you should see the form has returned to its normal non-maximized state.
How It Works
In order to create a custom form property, we use the now-familiar Property Let and Property Get procedures. The Property Let procedure allows us to set the property's value and the Property Get procedure allows us to interrogate its value. The first procedure we wrote was the Property Get procedure.
Public Property Get Maximized() As Boolean
The procedure creates a property called Maximized , which can be either True or False, and which is Public , that is, visible to all procedures.
If IsZoomed(Me.hWnd) Then Maximized = True Else Maximized = False End If
These next lines are responsible for determining the value returned to anyone interrogating the value of the Maximized property. If IsZoomed(Me.hWnd) returns a non-zero value, then the Maximized property is returned as True , otherwise it is returned as False .
IsZoomed() is simply an API function, a procedure in an external DLL. The DLL, User32 , contains procedures that handle interaction of Windows programs with user interfaces, and so is responsible for tasks such as window management.
The IsZoomed() procedure takes the handle of a window as an argument. It returns False if the window is not maximized and a non-zero value if it is maximized. A handle is simply a unique long integer identifier generated by Windows and used to allow it to keep track of individual windows and controls. We get the handle of the form's window by using the form's hWnd property. You probably won't come across this property very much, and when you do it will almost invariably be when you want to pass the handle to an API function.
To set the property we use the Property Let statement.
Public Property Let Maximized(blnMax As Boolean)
Again, we can see from the opening line of this procedure that the property's name is Maximized and that it has a Boolean datatype.
As for the rest of the procedure, it is fairly straightforward.
If blnMax Then Me.SetFocus DoCmd.Maximize Else Me.SetFocus DoCmd.Restore End If
If the value to which Maximized is being set is non-zero, we need to maximize the form. If the value is being set to False , we need to restore the form.
As you can see, creating custom form properties is just the same as creating properties for other class modules and is a fairly simple task once you have got your mind around the syntax of the Property Let and Property Get statements.
As well as custom form and report properties, you can also create custom form and report methods. To create a custom method, you simply write a procedure within the form (or report) module and expose it outside the form by making it Public .
So, to create a Maximize method that increases the size of the form in the manner described above, simply type this code into the form module of frmSales :
Public Sub Maximize() Me.Maximized = True End Sub
Because this procedure has been made Public , it can be invoked from outside the form in the following manner:
and there you have a custom form method! Now that wasn't too hard, was it?
Now we'll move on to another feature that is exposed to us through the object-oriented nature of Access and VBA - the ability to create multiple instances of a single form. When you open a form, you are creating an instance of that form. The first instance is called the default instance . Most of the time, that is the only instance you will need, but there may be occasions when you want to have multiple instances of the same form open at the same time.
Typically, you will create multiple instances of forms when you want to view two records alongside each other. We will try that out now by creating a pop-up form to give details of the ingredients of ice creams that appear in the frmSales form.
In the IceCream.mdb database, make a copy of the frmsubIceCreamIngredients form, call it frmIceCreamPopup, and open it up in design view.
Open the Properties window and change the form's Pop Up and Has Module properties to Yes .
Next change the form's RecordSource property to the following SQL string.
SELECT * FROM tblIceCreamIngredient WHERE fkIceCreamID=[forms]![frmSales]![fkIceCreamID]
Now close the form, saving changes when prompted to do so.
Next, switch to VBA by hitting Alt+F11 and open Form_frmSales , the class module for the frmSales form.
Add the following code to the Declarations section of the class module.
Private colForms As New Collection
Now add the following code to the DblClick event handler for the fkIceCreamID control.
Private Sub fkIceCreamID_DblClick(Cancel As Integer) Dim frmPopup As Form_frmIceCreamPopup Set frmPopup = New Form_frmIceCreamPopup frmPopup.Caption = fkIceCreamID.Column(1) frmPopup.Visible = True colForms.Add frmPopup End Sub
Now switch to Access and close the frmSales form, saving changes when prompted to do so.
Next, open the frmSales form and double-click on the combo box containing the name of an ice cream for one of the sales records. This should cause a form to appear detailing the ingredients of that ice cream.
Move the pop-up form to one side and double-click on the name of a different ice cream on the frmSales form. This should cause a second pop-up form to appear.
Now close the frmSales form. This should automatically close both of the pop-up forms as well.
How It Works
This is not as confusing as it might look at first glance. After creating the frmIcecreamPopup form, the first thing we have to do is to make sure that the new form has a class module. The reason for that is that we can only create instances of (as opposed to simply open) forms with a class module behind them. That is why we set the form's HasModule property to True . We also change the form's Popup property to True to ensure that the form will always remain topmost on the screen.
We then modify the pop-up form's RecordSource property to ensure that it will only display details of the selected ice cream.
Next, we add the code to the frmSales form to create a new instance of the pop-up form every time the ice cream combo box is double-clicked. Most of this code should seem straightforward. The only unusual line is the one in which we add the variable referring to the pop-up form to a collection declared at the form level.
Private Sub fkIceCreamID_DblClick(Cancel As Integer) Dim frmPopup As Form_frmIceCreamPopup
Set frmPopup = New Form_frmIceCreamPopup frmPopup.Caption = fkIceCreamID.Column(1) frmPopup.Visible = True colForms.Add frmPopup End Sub
Form instances perish when the variables referencing them go out of scope. The variable frmPopup was declared at the procedure level, and so will go out of scope when the procedure exits. In other words, the fkIceCreamID_DblClick procedure creates a form instance that dies immediately as the procedure ends.
In order to prolong the lifetime of the form instance, we add it to a collection that was declared at the form level (that is, in the Declarations section of the form module for frmSales ).
Option Compare Database Option Explicit Dim colForms As New Collection
We can then add the form instance to the collection.
This variable will only go out of scope when the form frmSales is closed. Any form instances that are added to the collection survive beyond the end of the procedure that created them, but perish when frmSales is closed because, at that point, the collection variable colForms goes out of scope.
One thing to be careful of is that when you create multiple instances of a form, the syntax for referring to the form by name - forms("frmIceCreamPopup").SomeProperty is no longer valid since there are two instances of the form in memory. Trying to refer to a form opened multiple times using this syntax will result in a run-time error.
In fact you can "see" the form by using the syntax forms(Y) (if you know what Y is) but since the index Y changes as forms open and close you cannot reliably refer to forms using this syntax. In the example above we stored a pointer to the forms in our own collection. If you need to reference one of these instances, you will need to do so by going through that collection. In fact you could use the Key property of the collection to "name" the pointer to the form as you add it to the collection, for easy reference later:
colForms.Add frmPopup, frmPopup.Name & 1