Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Updating Sub-Form Recordset from Main Form


Sub-Form on the Main Form is the common design that we follow to display several related records of the record on the Main Form. Order Detail records related to Orders, Bank Account and its transactions, Mark-List and Student's Id, and in short almost all databases have this kind of one-to-many relationships, and records are displayed in this way to get a quick view of maximum information.

When we open a form with a Table or Query as Record Source the Form when loaded opens a parallel recordset in memory with unique bookmarking of each record. We can search through this virtual recordset in memory without directly touching the actual Table or Query. But, when we add or update a record in this virtual recordset that change is saved in the actual table. We call this virtual recordset as the RecordsetClone of the Form.

Working with Recordset Clone.

The sample VBA Code given below shows how to address the Form's RecordsetClone to find a record based on some criteria.

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
End If

End Sub

We have a Blog Post on Data search and filter on Form through the above code. If you would like to take a look at it then click here.

In the above example, we were using the RecordsetClone Object of the Main Form on the main form module itself. But how do we address the RecordsetClone Object of the Sub-Form, from Main-Form, to update the current recordset on the Sub-Form? Here, what we have to keep in mind is that the records, which appear on the Sub-Form Datasheet View, are related to the current record on the Main-Form and only those records can be accessed for whatever operation we planned to do on them. Not all the records of the Record Source Table/Query.

On the Main Form, all records of the Record Source Table/Query can be accessed through the RecordsetClone object, for search or update operation. But, the RecordsetClone of the sub-form will have only those records displayed on the sub-form, related to the current record on the main form.

Accessing Sub-Form Recordset from Main Form.

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

  1. Import the following two Tables from the Northwind.accdb (or Northwind.mdb) database:
    • Orders
    • Order Details

    Add a New Field for Testing.

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

  3. Add a new field: SaleValue with Data Type Number and Field Size Double.
  4. Save the Order Details Table with the new field
  5. Design the Main form for Orders Table in column format.

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

    Create a Sub-Form.

  6. Design a Datasheet Sub-Form for 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([SaleValue]) in the Control Source property.
  10. Save and close the Form with the name: 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:

    Sub-Form Link with the Main form.

  12. While the Sub-Form is still in the selected state display its Property Sheet (F4).
  13. Change 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 [EventProcedure] 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 Sub-Routine 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
    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![SaleValue] = m_SaleValue
        [Order Details].Form.Bookmark = rst.Bookmark
    Set rst = Nothing
    End Sub
  21. Create a Text box to the right of the Command Button.
  22. Change the Caption of the Child Label to Order Value:
  23. Write the expression =[Order Details].[Form]![totSale]. The idea of this expression is to bring the Summary Value from the Text box, we have created 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 SaleValue column is not appearing in the visible area of the Datasheet then move the bottom scrollbar to the right, highlight the SaleValue column, click and hold the mouse button and drag it to the left and place it into the visible area.

    You can now see the Sub-Form shows some records related to the Order ID on the main Form. The new Text box we have created to the right of the Command Button is empty because we have not updated the [SaleValue] field on the datasheet.

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

Now you will find the Sale Value column of all records on the sub-form updated and the Summary value of all records appearing in the Text box to the right of the Command Button. The records updated are only those Order Details records related to Order Id on the Orders Form. If you move the Orders record forward their related record sale value is not updated. They will be updated only when you click on the Command Button.

The statement in the above code Set rst = [Order Details].Form.RecordsetClone is setting a reference to the RecordsetClone Object of the Sub-Form [Order Details]. Subsequent lines calculate the Sale Value after Discount, if any, and updates the sale value into the new field [SaleValue] we have created in the Order Details Table.

The statement [Order Details].Form.Bookmark = rst.Bookmark overwrites the form's Bookmark with the recordset's current record Bookmark. The result of this action is that the current record processed in the recordset clone becomes the current record on the sub-form. If you have several records on the sub-form you can see some visible action on the sub-form moving the cursor from one record to another very fast, starting from the first record to the last one-by-one as the updating action progress through the records.


No comments:

Post a Comment

Comments subject to moderation before publishing.



MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.



Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.


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 Graph Charts ListView Control Query VBA msaccessQuery Calculation Event 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 Android App 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