Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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:

[embed width="500"; height="360"]http://www.youtube.com/watch?v=71tu5LeEgZ8[/embed]

You can create this Form very easily. See the sample image given below:

  1. 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.
  2. 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
  3. Change the Name Property value of the Child Label as HeaderLabel.
  4. Create an Option Group Control above the sub-form with the names of your existing three form names as Labels.
  5. Change the Name Property value of Option Group Control to Frame1.
  6. Change the Default Value property value of Option Group control to 0.
  7. 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.
  8. 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
  9. Modify the above Code to replace the Form Names in quotes with your own form names.
  10. Press ALT+Q to close the VBA Window.
  11. Change the Child-label Caption, of the Option Group control, to Form Selection.
  12. Save and close the Form.
  13. 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:

[embed width="500" height="360"]http://www.youtube.com/watch?v=oem4oFFQCu0[/embed]

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"
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
  'Disable cmdStationary command Button
  Me.cmdStationary.Enabled = False
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"
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
'Disable cmdPayments Command Button
Me.cmdPayments.Enabled = False

End Sub

You may download the sample databases for a quick view of this trick.

Download Demo SubFormTrick2003.zip

Download Demo SubFormTrick2007.zip



  1. 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.

  2. Sorry, scrap my previous message, I see it now in the Students form :-)


Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts