Update Parent Form with Sub Form References

I was about to write an article on using sub form events in the main form to update data displayed in the main form based on data from the sub form.

In the process it turned out there is an alternative approach, which is simpler and still produces similar results for most scenarios. While my original intention and approach still has its merits, I want to also introduce you to my findings.

This content is also available as a video on YouTube:

Starting Point

You may have a main form with a sub form to display data and allow the user to edit data in the sub form. The main form displays some data, potentially aggregated, from the sub form. The (sub form) data in the main form must be updated once a record in the sub form is selected or updated.

Control References in the ControlSource

Disclaimer: I very much dislike the use of these long, chained references to controls in forms or even multi-layer deep nested sub forms. This is poking around deeply in the bowels of the form objects, which rather should be encapsulated and left alone. Nonetheless, I must concede, this approach allows us to quickly implement solutions, as described in this text, which would be more complicated to implement using better design principles.

One of the major features of Access is that you can reference any control that is displayed in your database application to retrieve the control’s value.

A simple example to display the value of one control in a completely unrelated another control:

From within the parent form of the sub form this can be reduced to just include the reference in the context of the parent form:

All three above samples will automatically retrieve the Value property of the control. This approach cannot be used to access any other control property.

The value retrieved will be updated once the value in the source control changes. This may happen either because another record in the source form was made the current record, or the record was updated by the user with new values. This is very convenient as the parent form control referencing a sub form control in this way will automatically display the current value from the sub form control.

This automatic updating of control content not only works with a direct reference to the update control but also along a chain of multiple controls referencing with one referencing another and only the control at end of that chain is update.

The content of AnotherControl will also be updated if the control ControlNameInSubForm in the sub form is updated with a new value.

If you’ve got multiple controls in the parent form depending on one and the same control in the sub form, it is helpful to create such a “proxy control” to consolidate the ugly reference into the sub form in just one control, while all the other controls referencing the value of the “proxy control” and are isolated from the sub form reference.

Updating Calculation Results without Direct Control References

What about controls depending on sub form data indirectly. I.e., without a direct reference to a control in the sub form.

E.g., if we wanted to display the sum of a column in the RecordSource of the sub form, we would use this expression in the parent form control’s ControlSource property:

The expression works by querying the sub form’s RecordSource, but it is not updated automatically if data in the sub form changes. E.g., if we add a new record or update the value of Amount in an existing record. You would need to explicitly Requery the control to get the updated value.

But there is a dirty trick to make Access believe the content of this control depends on the Value of a control in the sub form. I discovered this trick while using a slightly different example. I wanted to query the sum of the Amount column excluding the amount from the current record of the sub form. So, I used this ControlSource:

RecordId is the primary key of the data displayed in the sub form and [txtRecordId] is a control in the current form, which contains a reference to the corresponding RecordId control in the sub form, as described in the previous paragraph.

With this ControlSource the sum control is automatically updated whenever the current record is changed or updated, or a record is added or deleted. This works because Access knows that the sub form value RecordId is relevant for the result of the expression, and it must be recalculated once the sub form record changes.

What if we trick Access into believing the sub form record is relevant input for the expression, even if it isn’t? With that thought in mind, let’s revise the ControlSource for the total sum from above:

We included txtRecordId in the criteria of the call to DSum. However, as this criterion will always be true, it will never have any effect on the result of the DSum query.

The crucial point is: Now the total sum will be automatically updated once the current record in the sub form is changed or updated, or a record is added or deleted.

Generic Use of the “Dirty Trick”

If the expression involves a function, it isn’t required that the value from the sub form record is an actual argument to the function call. It is already enough if it is just any part of the expression in the ControlSource.

So, we can use the “dirty trick” with basically every imaginable expression, even with VBA functions being invoked from the ControlSource.

Here is an example for an VBA function call of the function ComplexCalculation to which the result of an Iif expression is appended that will always be an empty string:

However, the above use of appending and empty string (the result of the Iif expression) will cause the whole expression to become text data, even if the actual value does not change. So, you might need to add an explicit conversion back to the original type of the expression before the hack was applied. Otherwise control formatting and the text alignment in the control might not work as intended anymore.

Here is an example of such a data type conversion using the CDate function for the date value retrieved with the Now function:

You’ll find suitable conversion function for every intrinsic VBA data type in the built-in module Conversion of the VBA standard library.

Conclusion

Even though I very much dislike the use of control references beyond form boundaries as described in this text, they are a fairly simple and yet powerful tool to implement automatically updated calculated controls.

This approach is limited to the change of the current record and after any operation adding, deleting, or updating a record in the sub form. These are, without doubt, the most frequently needed.

If you need to respond to other sub form events in the parent form to update the screen or run VBA code, you must resort to the more complicated solution I originally intended to propose in this text. (A link will be added here once it is published.)

Download

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

Benefits of the newsletter subscription

Using Sub Form Control References to Control the Parent Form