Introduction.
This section is about loading two or more forms into a single Subform Control, interchangeably. Normally, we place only one form in a Subform Control, and this is usually done during design time. A main Form can host one or more Subforms, typically linked through the Link Master Fields and Link Child Fields properties.
Remember this: a Subform control is essentially a Container that holds a Form object reference in its Source Object property. If you clear the form name from this property, you will notice that the container remains empty on the main form. By changing the Source Object value at runtime, you can load any form you want into the same subform control. This is the key to switching between multiple forms dynamically.
Let us look at a quick example of loading three different forms into a single subform control, one after the other, replacing the previously loaded form. Check the video below for a demonstration:
You can create this Form very easily. See the sample image given below:
Sample Form Design.
You can either use a copy of an existing form with enough space below the existing data fields or create a blank form in Design View. In both cases, you can drag another form from the Navigation Pane and drop it onto the Detail Section. This action automatically creates a Subform Container Control and places the dragged form inside it. If you choose to create a new blank form, leave enough space above the subform to add an Option Group Control later.
Display the Property Sheet (F4) of the sub-form, while the sub-form container is in the selected state, and change the Name Property value to subFrm.
Change the Name Property Value of the Child Label to HeaderLabel.
Create an Option Group Control above the sub-form with the names of your existing three form names as Labels.
Change the Name Property value of the Options Group Control to Frame1.
Change the Default Value property value of the Options Group control to 0.
Select the After Update Event property, select [Event Procedure] from the drop-down list, and click on the build (...) button to open the VBA Editing window.
Copy and paste the following VBA Code into the Module, overwriting the Private Sub Frame1_AfterUpdate() ... End Sub lines:
Frame1_AfterUpdate() Event Procedure.
Private Sub Frame1_AfterUpdate() Dim i, sub_Form As SubForm Set sub_Form = Me.subfrm i = Me![Frame1] Select Case i Case 1 sub_Form.SourceObject = "frm_Session" Me.headerLabel.Caption = "frm_Session:" Case 2 sub_Form.SourceObject = "frm_Payments" Me.headerLabel.Caption = "frm_Payments:" Case 3 sub_Form.SourceObject = "Stationary" Me.headerLabel.Caption = "Stationary:" End Select End SubModify the above Code to replace the Form Names in quotes with your own form names.
Press ALT+Q to close the VBA Window.
Change the Child-label Caption of the Options Group control to Form Selection.
Save and close the Form.
- Open the Form in normal view and try out the Option Group Radio buttons to load your forms into the sub-form control, in any order you like.
When Two Sub-Forms Are Linked Together.
Assume you have a Main Form (e.g., Students) that contains two subforms: frm_Session and frm_Payments. The first subform (frm_Session) is linked to the main form through a common field, StudentID—though this is not the key point here.
The second subform (frm_Payments) is not linked directly to the main form. Instead, it is linked to the first subform (frm_Session) through the field SessionID. To ensure the second subform displays only the related records, you must configure its Link Master Fields and Link Child Fields properties. The critical detail is that the Master Field reference must come from the first subform control, rather than from the main form. This is the main challenge in setting up this type of subform relationship.
The image of the sample form is given below:
View the Demo Video of two sub-forms in action.
The limitation with the Link Master Fields property of a subform control is that it can only be set to reference field or control names at design time. It does not accept expressions or fully qualified references, and it always expects those references to come from the main form, not from another subform.
The simplest solution is to create an unbound TextBox on the main form and set its Control Source to an expression such as:
This allows the TextBox to display the current record key value from the first subform. You can then use the name of this TextBox as the Link Master Field for the second subform. To keep the form uncluttered, set the TextBox’s Visible property to No.
With this setup, the second subform will correctly filter its data based on the SessionID from the first subform. If you load any form that doesn't have the SessionID field into the second sub-form control, Microsoft Access will prompt for the field value set in the Link Child Fields property.
The VBA Code.
The VBA Routines that run on the Command Button Click Event Procedures, of Command Buttons: Stationary and Payments, are given below:
Stationary Command Button Click event procedure:
Private Sub cmdStationary_Click()
Dim frm As SubForm
Set frm = Me![frm_Payments]
With frm
'load Stationary Form into the control
.SourceObject = "Stationary"
.Requery
End With
'second Sub-Form Child Label Caption Change
Me.Label7.Caption = "Stationary"
'Enable Payments Command Button
Me.cmdPayments.Enabled = True
'Shift the Focus to cmdPayments command button
Me.cmdPayments.SetFocus
'Disable cmdStationary command Button
Me.cmdStationary.Enabled = False
Me.Refresh
End SubPayments Command Button Click event procedure:
Private Sub cmdPayments_Click()
Dim frm As SubForm
Set frm = Me![frm_Payments]
With frm
.SourceObject = "frm_payments"
.Requery
End With
'Change Header Label Caption
Me.Label7.Caption = "frm_payments"
'Enable Stationary Command Button
Me.cmdStationary.Enabled = True
'Change focus from cmdPayments
'in preparation to disable the Command Button
Me.cmdStationary.SetFocus
'Disable cmdPayments Command Button
Me.cmdPayments.Enabled = False
Me.Refresh
End Sub
Download the Demo Database.
You may download the sample databases for a quick view of this trick.
Download Demo SubFormTrick2007.zipDownload Demo SubFormTrick2003.zip
The image of the sample form talks of a text box with the expression:=[frm_Session].[Form]![SessionID] but I don't see such text box when I open subFormsTrick2007.accdb in design view.
ReplyDeleteSorry, scrap my previous message, I see it now in the Students form :-)
ReplyDelete