November 2013 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

This is all about loading two or more Forms into a single Sub-Form Control, interchangably. 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 have got the idea as to 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:

Sample Form Design.

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 the 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 Container control and places the sub-form within this control.
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 Options Group Control to Frame1.
Change the Default Value property value of 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 over-writing 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 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 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 Linked Together.

Assume that you have the 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 the main form of the 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:

View the Demo Video of two sub-forms in action.

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.

The VBA Code.

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