Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Overlaying Sub-Forms in Real-Time

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.

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

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

  3. Change the Name Property Value of the Child Label to 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 Options Group Control to Frame1.

  6. Change the Default Value property value of Options 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:

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

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

Download the Demo Database.

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


Download Demo SubFormTrick2007.zip

Download Demo SubFormTrick2003.zip

Share:

2 comments:

  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.

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

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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