Forms with multiple sub_forms - General Question

[Jim Dudley]

I have a form that has two sub-forms in the main form side by side(Pop-ups).

Below I have a series of 10 tabs. 9 of them contain additional sub_forms which are created by up to 4 queries. Query on query etc. Some of them involve Cross Tab queries.

Issue:

When I load the consolidated form with the tabs and sub_forms, I am assuming that each of the sub_forms re-runs the underlying queries. In other words it updates itslef as it loads.

If not what code would I need to assure that the sub-forms and the data(numbers) they display are the most current?

My numbers are not balancing and I am wondering if the forms are not re-calculating each time they load.

Second question, what line of code do I add to force the consolidated spreadsheet to update automatically when the code loads it.
Code Line:
' To open the Excel workbook:
Application.FollowHyperlink "C:\LDP_Database\Seneca_Period_Analysis_Exports\Comparative_Analysis.xlsx"
Exit Function



Thank you.

Jim

 

[Pat Hartman]

The form/report RecordSource query is run between the Current event and the Load event. If you change the criteria for any of the subforms later on, you'll need to requery the subform.
Me.sfrmFormA.Form.Requery
Normally the master/child links are what keep subforms sync'd with their parent form. The initial form events are OPEN, LOAD, CURRENT. The OPEN and LOAD events run only once but the CURRENT event runs every time the form is scrolled forward or backward or otherwised moved to a different record.

 

[missinglinq]

I have a form that has two sub-forms in the main form side by side(Pop-ups).

First off we need to be clear on terminology, here. Subforms, in Access, properly refers to Forms which reside on Subform Controls that have been placed on the Main Form and which are linked thru the 'Link Child Fields/Link Master Fields' Properties of the Subform Control. These are normally visible at all times (although the Subform Control's 'Visible' Property can be set to 'No," and are not 'popups,' as you've indicated your uppermost two are. Are these actually Subforms, as explained above, or are they simply Forms that you've arranged to somehow 'popup' over this area of the Main Form?

...When I load the consolidated form with the tabs and sub_forms, I am assuming that each of the sub_forms re-runs the underlying queries. In other words it updates itslef as it loads.

True Subforms, as explained above, do, in fact, run the underlying Queries when they load during the process of the Main Form loading.

There is a quirk of Access you should be aware of here, and it is quirky, but important if you're going to be dealing with a number of Subforms, as you appear to be.! When you crank up the Main Form in a Main Form/Subform scenario, the Form_Open, Form_Load and Form_Current events of the Form each Subform is based on actually fires before these events do in the Main Form! Told you it was quirky! This can make a big difference if you're trying to do something when the Forms first open.

...My numbers are not balancing and I am wondering if the forms are not re-calculating each time they load.

I think you need to explain this a little more, in plain English, as to what should but isn't happening. And maybe exactly what you mean by "each time they load." The Subforms load once, as indicated above, when the Main Form itself Loads. If you're expecting the Subforms to re-calculate each time you move Focus to them, that's something entirely different from their 'loading.'

...what line of code do I add to force the consolidated spreadsheet to update automatically when the code loads it.

You're actually loading the Excel app at this point, so it is displaying the same data/calculations it would if you simply went to the file in Windows Explorer and opened it by double-clicking on it.

Linq ;0)>

"Bridge Players Still Know All the Tricks!"

And yet they still get 'slammed,' don't they, Pat!