Overlaying Sub-Forms in Real-Time
This is all about loading two or more forms into a single Sub-Form Control, one after the other at will. Normally we place only one form into a Sub-form control and this will be done during design time. One Main Form can have one or more sub-forms, normally linked to the main form through Link Master Fields and Link Child Fields references.
By the way, a sub-form control is a container that holds a form object reference in the Source Object Property. If you remove the form’s name from this property you can see that an empty control remains on the main form. You can re-write the Source Object Property value with any form name in real-time to open that form into the sub-form control. Now, you got the idea how it works.
Let us look at a quick example by loading three different forms into a single sub-form control, one after the other, replacing the earlier form. Check the video given below:
You can create this Form very easily. See the sample image given below:
- You can use a copy of an existing form with enough space below the existing data fields, drag another form from the navigation pane and drop it in the detail section as a sub-form. Or you may create a blank form in design view, drag another form and drop it on Detail Section leaving enough space above the sub-form to create an Option Group Control. The drag drop action will automatically create a sub-form control and place the form within the control.
- Display the Property Sheet (F4) of the sub-form, while the sub-form is in selected state and change the Name Property value to subFrm.
- Change the Name Property value of the Child Label as 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 Option Group Control to Frame1.
- Change the Default Value property value of Option 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 over-writing the Private Sub Frame1_AfterUpdate(). . . End Sub lines:
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 Sub
- Modify 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 Option 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.
Assume that you have a Main-Form (say Students) with two sub-forms (frm_Session & frm_Payments). The first sub-form is linked to the main form using a common field StudentID, but that is not so important here.
The second sub-form control is linked to the first sub-form control (not linked to main form field value, as we do normally) using the common field SessionID . It is important to know that to filter data in the second sub-form related to the data in the first sub-form control we must set up Link Master Fields and Link Child Field property values in the second sub-form control. The Mater Field value should come from the first sub-form control and this is the main challenge we face.
The image of the sample form is given below:
The problem with the Link Master Fields property of a sub-form control is that it should be set with Field/Textbox name(s) at design time (will not accept any expressions or qualified names) and it will always expect these references to come from the Main Form only. Not from within any other sub-forms on the same main form. The simplest solution to this issue is to create an Unbound textbox on the main form and write an expression (like =[frm_Session].[Form]![SessionID]) in the textbox to obtain the current record key value from the first sub-form and use the textbox name for the Link Master Fields property value. You can set the Visible property value to No to keep this textbox control hidden.
We established the links between both sub-forms to filter the data in the second sub-form control, corresponding to the SessionID value in the first sub-form. 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.
View the following Youtube Video for a preview of the trick:
The VBA Routines that runs 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 Sub
Payments 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
You may download the sample databases for a quick view of this trick.