|
Free Resources from FMS |
|
|
|
|
"Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities." - Darren D. |
|
|
|
|
Microsoft
Access Resource Center
Easily Synchronize Two Related Subforms on a Form
Provided by: FMS Development Team
Subforms in Access provide a powerful way to easily see one-to-many relationships without writing code, using the LinkMasterFields and LinkChildFields properties. This is often seen when viewing a form with a recordsource, and using a subform to show the related records in a subform.
But what if you want to show a list of the "master" records and the corresponding detail records of the current master record? For example, on an unbound (no recordsource) main form, add two subforms, where subform1 could be based on Orders, and subform2 on Order Details. Clicking on an Orders row should display all of the related Order Details in the second subform. Fortunately, there's an easy way to do this without code too!
Typically, to synchronize a subform, the LinkChildFields and LinkMasterFields are used. In a regular main to subform setup, the LinkMasterFields would be based on a field from the main form. In the two subforms scenario, the second subform must be based on a value from the first subform. Unfortunately, the second subform's LinkMasterFields property cannot directly reference a field in the first subform.
To work around this problem, use an intermediate text box to retrieve the LinkMaster value and expose it on the main form. Once exposed, the second subform can utilize and react appropriately to it.
To retrieve a value from a subform, use the following syntax:
Forms![SubFormName].Form![ControlName]
If the first subform is called 'sfrmOrders' and the desired data value is displayed in a textbox called 'txtOrderID', then the following syntax could be the controlsource of a textbox on the main form, and would expose the subform value on the main form:
=Forms!sfrmOrders.Form!txtOrderID
Now that the master value is exposed (let's call it 'txtMasterID'), this textbox can be used as the MasterField for the second subform (sfrmOrderDetails). The LinkChildFields for sfrmOrderDetails (the second subform) would be txtOrderID.
In the demo application, there is a sample form that utilizes each of these concepts. For the example, txtMasterID is visible so you can see how it changes as you move through the orders. To hide the textbox from confusing users, set its visible property to No.
