Open subforms on form load

[boooney]

Hi-
I have a total field on a form that sums a field from each of the subforms.
I shows #Error unless I expand the subforms, and then the total field shows
the correct amount.

How do I get rid of the #Error so that when I load the form, the total shows?

Thanks.

 

[BruceM]

I'm not sure what you mean by "expand the subforms", but if the subforms do
not have a record source when the main form is loaded you may need to find
another way of performing the calculation. How are you performing the
calculation now? That is, how does the total get into the text box on the
main form?

 

[John W. Vinson]

Hi-
I have a total field on a form that sums a field from each of the subforms.
I shows #Error unless I expand the subforms, and then the total field shows
the correct amount.

How do I get rid of the #Error so that when I load the form, the total shows?

Thanks.

The problem is arising because the Subforms are opened *first*, before the
main form gets populated. But it sounds like you're doing something else -
what do you mean by "expand" the subforms? How is the sum being generated?
Could you leave the textbox blank (no control source) on opening the form, and
set its value or its control source in whatever event "expands" the subform?

 

[boooney]

Hi Bruce and John-

Thanks for both of your replies. Sorry I didn't make myself clearer the
first time.

The main form 'project' has a sub-datasheet 'scope' with a list of
applicable scopes of work for each project. within the 'scope' are
continuous subforms of 'Contracts subform' which are tied to the 'scope' by
the 'projectscopeID' field.

The calculated field 'ShowTotalFromSubform' field has =[Contracts
subform].Form![Scope Total] as its control source. In the 'Contracts
subform' footer, the 'Scope Total' has the control source =Sum([Contract
Amount]).

When I open the 'project', I get #Error showing in each of the
'ShowTotalFromSubform' boxes. However, they are filled with the correct
total when I click the + to expand (and show the detail) of each 'Contract
subform'

Thanks much for your help.

 

[John W. Vinson]

When I open the 'project', I get #Error showing in each of the
'ShowTotalFromSubform' boxes. However, they are filled with the correct
total when I click the + to expand (and show the detail) of each 'Contract
subform'

I would consider this to be yet another reason (one of which I was not
previously aware) to never use subdatasheets.

You can accomplish what you want by using a Datasheet or Continuous subform
and turning off subdatasheets.