Overlaying Sub-Forms in Real-Time

Overlaying Sub-Forms in Real-Time.

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 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, 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 dynamically switching between multiple forms.

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:

Right-click to open Large Image in New Tab/Window.

Sample Form Design.

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

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 existing three form names as Labels.

5. Change the Name Property value of the Options Group Control to Frame1.

6. Change the Default Value property value of the Options Group Control to 0.

7. Select the After Update Event property, select [Event Procedure] from the drop-down list, and click the Build (...) button to open the VBA Editing window.

8. 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 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 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) via the SessionID field. 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, not 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:

Right-click to open Large Image in New Tab/Window.

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,

The simplest solution is to create an unbound TextBox on the main form and set its Control Source to an expression such as:

=[frm_Session].[Form]![SessionID]

This allows the TextBox to display the current record key value from the first subform. You can use the TextBox name 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 CommandButtons Stationary and Payments Click Event Procedures 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

• DOS Commands in VBA
• Accounting Year/Week Calculation
• Deleting Folders with DOS Command
• CHDIR and IN Clause of Access Query
• Microsoft DOS Commands in VBA

Read More

Updating Sub-Form Recordset from Main Form

Updating Sub-Form Recordset from Main Form.

A Subform on a Main Form is a common design pattern used to display multiple related records alongside a single record from the main form. For example, order details linked to orders, bank accounts with their transactions, or student IDs with their mark lists. In short, nearly all databases with one-to-many relationships use this approach, as it provides a convenient way to view a large amount of related information in one place.

When a form is opened with a table or query as its record source, Access loads a parallel Recordset in memory, with each record uniquely bookmarked. This allows us to search and navigate through the virtual recordset without directly interacting with the underlying table or query. However, when a record is added or updated in this virtual recordset, the change is automatically saved back to the actual table. This in-memory recordset is known as the form’s RecordsetClone.

Working with Recordset Clone.

The sample VBA code below demonstrates how to work with a form’s RecordsetClone to locate a record based on specific criteria. In this example, we search for an order using its ProductID and then move the form’s current record pointer to that record:

Private Sub FindPID_Click()
'Find Record matching Product ID
Dim m_find, rst As Recordset

'validation check of search key value
m_find = Me![xFind]
If IsNull(m_find) Then
   Me.FilterOn = False
   Exit Sub
End If

'validation check of search key value
If Val(m_find) = 0 Then
  MsgBox "Give Product ID Number..!"
   Exit Sub
End If

'Find a record that matches the ProductID
'and make that record current using the recordset bookmark.
If Val(m_find) > 0 Then
   Set rst = Me.RecordsetClone 'declare the recordset of the form
   rst.FindFirst "ProductID = " & m_find
   If Not rst.NoMatch Then '<if record found then make that record current
      Me.Bookmark = rst.Bookmark
   End If
   rst.Close
End If

End Sub

We already have a blog post demonstrating how to search and filter data on a form using the RecordsetClone object. You can check that article [here].

In the earlier example, we worked with the RecordsetClone of the main form directly from its own module. But what if we need to access the RecordsetClone of a subform from the main form?

The key point to remember is this:

• The subform displays only records that are related to the current record on the main form.

• Therefore, the subform’s RecordsetClone will contain only this filtered set of records—not all records from the underlying table or query.

By contrast, the RecordsetClone of the main form always includes all records from its record source, to search and find, or update across the entire Recordset. For the subform, however, you can work only with the records currently visible in the subform related to the active main form record.

Accessing Sub-Form Recordset from Main Form.

Let us try an example to learn how to access the sub-form record set from the main form and update records.

1. Import the following two Tables from the Northwind (or Northwind.mdb) database:

   • Orders
   • Order Details
   
   

   Create a New Field for testing purposes.

2. Open the Order Details Table in the design view.

3. Add a new field: Sale Value with Data Type: Number and Field Size: Double.

4. Save the Order Details Table with the new field.

5. Design the Main form for the Orders Table in column format.

   If you have used the Form Wizard to create a Split Form, then delete the Datasheet sub-form or table (Access 2007). Display the Property Sheet of the Form, find the Default View property, and change the Split Form value to Single Form.

   Create a Sub-Form.

6. Design a Datasheet Sub-Form for the Order Details Table.

7. Expand the Footer of the Sub-Form and create a Text box there.

8. Change the Name Property value to TotSale.

9. Write the expression =Sum([Sale Value]) in the Control Source property.

10. Save and close the Form named Order Details.

11. Insert the Order Details sub-form in the Detail Section of the Orders Form below the Orders Form controls. See the image given below:

    

    Right-click to open Large Image in New Tab/Window.

    Sub-Form Link with the Main Form.

12. While the Sub-Form is still in selected state, display its Property Sheet (F4).

13. Set the Link Master Field property value to [Order ID].

14. Change the Link Child Field property value to [Order ID].

15. Add a Command Button above the sub-form as shown on the design above

16. Display the Property Sheet of the Command Button (F4 or Alt+Enter.

17. Change the Name property value to cmdUpdate.

18. Select the On Click Event property and select [Event Procedure] from the drop-down list.

19. Click on the Build (...) button at the right end of the property to open the VBA Module of the Form.

20. Copy and paste the following VBA Code into the VBA Module, overwriting the skeleton lines of the subroutine there.

    Sub-form Module Code.

    Private Sub cmdUpdate_Click()
    Dim rst As dao.Recordset
    Dim m_UnitPrice As Double
    Dim m_Discount As Double
    Dim m_Quantity As Long
    Dim m_SaleValue As Double
    
    'Address the recordset on the Sub-Form [Order Details]
    Set rst = [Order Details].Form.RecordsetClone
    rst.MoveFirst
    Do While Not rst.EOF
        m_UnitPrice = rst![Unit Price]
        m_Discount = rst![Discount]
        m_Quantity = rst![Quantity]
        m_SaleValue = m_Quantity * ((1 - m_Discount) * m_UnitPrice)
        rst.Edit
        rst![SaleValue] = m_SaleValue
        rst.Update
        [Order Details].Form.Bookmark = rst.Bookmark
    rst.MoveNext
    Loop
    rst.Close
    
    Set rst = Nothing
    
    End Sub

21. Create a Textbox to the right of the Command Button.

22. Set the Caption property value of the Child Label to Order Value:

23. Write the expression =[Order Details].[Form]![totSale]. This expression brings the Summary Value from the Text box, in the Footer Section of the sub-form, into the Order Form.

24. Save and close the Orders Form.

    Open the Order Form.

25. Open the Orders Form in Normal View.

26. If the Sale Value column is not visible in the datasheet, use the bottom scroll bar to move to the right. Locate the Sale Value column, then click and hold the mouse button, drag the column to the left, and drop it within the visible area.

    Now you can see that the subform displays the records related to the current Order ID on the main form. However, the new text box we created to the right of the command button is still empty because the [Sale Value] field in the datasheet has not yet been updated.

27. Click on the Command Button to calculate and update the Sale Value of each record on the Datasheet Sub-Form.

Now you will see that the Sale Value column of all records on the subform has been updated, and the summary value of these records is displayed in the text box to the right of the command button. Only the Order Detail records related to the current Order ID on the Orders form are updated. If you move to another record in the Orders table, its related Sale Value records will remain unchanged until you click the command button again.

In the code, the statement:

Set rst = [Order Details].Form.RecordsetClone

creates a reference to the RecordsetClone object of the subform [Order Details]. The following lines calculate the Sale Value (after applying any discount) and update it into the new field [Sale Value] in the Order Details table.

The line:

[Order Details].Form.Bookmark = rst.Bookmark

sets the subform’s Bookmark equal to the current record’s Bookmark in the recordset clone. This ensures that the record being processed in the recordset clone also becomes the current record on the subform.

If the subform contains many records, you may notice a visible cursor movement as it rapidly shifts from one record to the other, starting at the first record and progressing through to the last, while the update operation runs.

• Macro and Temporary Variables
• Easy-read Reports
• Top N Records in Query
• Attachment field in Access 2007
• Embedded Macros in Access 2007

Read More