recalculating total on main form when record in subform is changed

[Guest]

My main form 'total' field is not recalculating the sum whenever a record in
the subform is changed. The subform calculation field changes correctly and
the mainform calculation field will update when the form is closed and then
reopened.

Is the problem that I need to save something? Do I save the query of the
subform, the mainform or the form itself?

Thanks
Laura

 

[Van T. Dinh]

Are you using an intermediate calculated Control in the Form Footer of the
Subform?

 

[Guest]

Hello,

Sorry, I don't understand what you mean by intermediate. I created an
unbound text box in the footer of the main form with the following as the
source

=sum([funded])

It provides me with the correct sum, but does not change when a record to
the subform is changed, it only changes when the form is closed and reopened.
I'm thinking I need to somehow refresh the query that the main form is based
off of. But I'm not sure in which event to make this happen or what macro to
build. Should I create a save button with a macro that runs the query...Will
this refresh the data? But it would then cause the user to hit the 'save'
button for each change to the subform, which could be tedious.

Thanks
Laura

 

[Van T. Dinh]

The TextBox in the Form Footer of the Subform is the intermediate step to
show the Total in the Main Form. In fact, I usually set the Form Footer
invisible so that the users don't see the Form Footer or the TextBox in the
Form Footer.

I think the Sum does not include the new Record being entered or the edited
amount until you move the Focus to another rowRecord in the Subform
(assuming you are using the Form in CtsFormView).

Try moving the Focus to another row / Record ...

If you still have problem, check your set-up against the Form "Orders" in
the sample database "NorthWind" that is usually installed with your Access
software.

 

[Guest]

Yes, I'm using Continuous form view and moving focus doesn't solve the
problem...When I move to a new record on the subform, the subform total will
change, but not the total on the main form. Access is loaded at work on our
server so I don't think they have loaded the NW db, but I'll ask.

But you have led me to something, I do not have the main form textbox total
source set to the subform total textbox...I have it set to the same field in
the mainform query, as I do in the subform query, but not the actual subform
itself.

Laura

The TextBox in the Form Footer of the Subform is the intermediate step to
show the Total in the Main Form. In fact, I usually set the Form Footer
invisible so that the users don't see the Form Footer or the TextBox in the
Form Footer.

I think the Sum does not include the new Record being entered or the edited
amount until you move the Focus to another rowRecord in the Subform
(assuming you are using the Form in CtsFormView).

Try moving the Focus to another row / Record ...

If you still have problem, check your set-up against the Form "Orders" in
the sample database "NorthWind" that is usually installed with your Access
software.

--
HTH
Van T. Dinh
MVP (Access)

Hello,

Sorry, I don't understand what you mean by intermediate. I created an
unbound text box in the footer of the main form with the following as the
source

=sum([funded])

It provides me with the correct sum, but does not change when a record to
the subform is changed, it only changes when the form is closed and
reopened.
I'm thinking I need to somehow refresh the query that the main form is
based
off of. But I'm not sure in which event to make this happen or what macro
to
build. Should I create a save button with a macro that runs the
query...Will
this refresh the data? But it would then cause the user to hit the 'save'
button for each change to the subform, which could be tedious.

Thanks
Laura

 

[Van T. Dinh]

OK, that the problem.

In NorthWind (and my databases), the ControlSource for the Total Control on
the MainForm is set to:

= [SubformControlName].Form![TotalTextBoxInFooter]

(including the equal sign.

--
HTH
Van T. Dinh
MVP (Access)

Yes, I'm using Continuous form view and moving focus doesn't solve the
problem...When I move to a new record on the subform, the subform total
will
change, but not the total on the main form. Access is loaded at work on
our
server so I don't think they have loaded the NW db, but I'll ask.

But you have led me to something, I do not have the main form textbox
total
source set to the subform total textbox...I have it set to the same field
in
the mainform query, as I do in the subform query, but not the actual
subform
itself.

Laura

The TextBox in the Form Footer of the Subform is the intermediate step to
show the Total in the Main Form. In fact, I usually set the Form Footer
invisible so that the users don't see the Form Footer or the TextBox in
the
Form Footer.

I think the Sum does not include the new Record being entered or the
edited
amount until you move the Focus to another rowRecord in the Subform
(assuming you are using the Form in CtsFormView).

Try moving the Focus to another row / Record ...

If you still have problem, check your set-up against the Form "Orders" in
the sample database "NorthWind" that is usually installed with your
Access
software.

--
HTH
Van T. Dinh
MVP (Access)

Hello,

Sorry, I don't understand what you mean by intermediate. I created an
unbound text box in the footer of the main form with the following as
the
source

=sum([funded])

It provides me with the correct sum, but does not change when a record
to
the subform is changed, it only changes when the form is closed and
reopened.
I'm thinking I need to somehow refresh the query that the main form is
based
off of. But I'm not sure in which event to make this happen or what
macro
to
build. Should I create a save button with a macro that runs the
query...Will
this refresh the data? But it would then cause the user to hit the
'save'
button for each change to the subform, which could be tedious.

Thanks
Laura

 

[Guest]

Well, this works yes and no, and forgive me for not being clear, I posted
before I was caffinated.

The solution below gives me the same total in the main form that is showing
up in the subform, which isn't quite what I'm trying to accomplish.

For example, my subform gives me a total for all amounts in the operations
office
but I need the main form to give me the grand total for the entire company,
and recalculate corresponding to what changes the user makes in the subform.
I still think I need to do a requery somehow, just not sure where/what/how .

Laura

OK, that the problem.

In NorthWind (and my databases), the ControlSource for the Total Control on
the MainForm is set to:

= [SubformControlName].Form![TotalTextBoxInFooter]

(including the equal sign.

--
HTH
Van T. Dinh
MVP (Access)

Yes, I'm using Continuous form view and moving focus doesn't solve the
problem...When I move to a new record on the subform, the subform total
will
change, but not the total on the main form. Access is loaded at work on
our
server so I don't think they have loaded the NW db, but I'll ask.

But you have led me to something, I do not have the main form textbox
total
source set to the subform total textbox...I have it set to the same field
in
the mainform query, as I do in the subform query, but not the actual
subform
itself.

Laura

The TextBox in the Form Footer of the Subform is the intermediate step to
show the Total in the Main Form. In fact, I usually set the Form Footer
invisible so that the users don't see the Form Footer or the TextBox in
the
Form Footer.

I think the Sum does not include the new Record being entered or the
edited
amount until you move the Focus to another rowRecord in the Subform
(assuming you are using the Form in CtsFormView).

Try moving the Focus to another row / Record ...

If you still have problem, check your set-up against the Form "Orders" in
the sample database "NorthWind" that is usually installed with your
Access
software.

--
HTH
Van T. Dinh
MVP (Access)



Hello,

Sorry, I don't understand what you mean by intermediate. I created an
unbound text box in the footer of the main form with the following as
the
source

=sum([funded])

It provides me with the correct sum, but does not change when a record
to
the subform is changed, it only changes when the form is closed and
reopened.
I'm thinking I need to somehow refresh the query that the main form is
based
off of. But I'm not sure in which event to make this happen or what
macro
to
build. Should I create a save button with a macro that runs the
query...Will
this refresh the data? But it would then cause the user to hit the
'save'
button for each change to the subform, which could be tedious.

Thanks
Laura

 

[Van T. Dinh]

OK ... I hought you wanted the Total in the Subform to be displayed on the
Main Form like "NorthWind" but your set-up is different.

Try the Recalc method on the main Form which will recalculate all calculated
Controls on the Form.

--
HTH
Van T. Dinh
MVP (Access)

Well, this works yes and no, and forgive me for not being clear, I posted
before I was caffinated.

The solution below gives me the same total in the main form that is
showing
up in the subform, which isn't quite what I'm trying to accomplish.

For example, my subform gives me a total for all amounts in the operations
office
but I need the main form to give me the grand total for the entire
company,
and recalculate corresponding to what changes the user makes in the
subform.
I still think I need to do a requery somehow, just not sure where/what/how
.

Laura

OK, that the problem.

In NorthWind (and my databases), the ControlSource for the Total Control
on
the MainForm is set to:

= [SubformControlName].Form![TotalTextBoxInFooter]

(including the equal sign.

--
HTH
Van T. Dinh
MVP (Access)

Yes, I'm using Continuous form view and moving focus doesn't solve the
problem...When I move to a new record on the subform, the subform total
will
change, but not the total on the main form. Access is loaded at work
on
our
server so I don't think they have loaded the NW db, but I'll ask.

But you have led me to something, I do not have the main form textbox
total
source set to the subform total textbox...I have it set to the same
field
in
the mainform query, as I do in the subform query, but not the actual
subform
itself.

Laura

:

The TextBox in the Form Footer of the Subform is the intermediate step
to
show the Total in the Main Form. In fact, I usually set the Form
Footer
invisible so that the users don't see the Form Footer or the TextBox
in
the
Form Footer.

I think the Sum does not include the new Record being entered or the
edited
amount until you move the Focus to another rowRecord in the Subform
(assuming you are using the Form in CtsFormView).

Try moving the Focus to another row / Record ...

If you still have problem, check your set-up against the Form "Orders"
in
the sample database "NorthWind" that is usually installed with your
Access
software.

--
HTH
Van T. Dinh
MVP (Access)



Hello,

Sorry, I don't understand what you mean by intermediate. I created
an
unbound text box in the footer of the main form with the following
as
the
source

=sum([funded])

It provides me with the correct sum, but does not change when a
record
to
the subform is changed, it only changes when the form is closed and
reopened.
I'm thinking I need to somehow refresh the query that the main form
is
based
off of. But I'm not sure in which event to make this happen or what
macro
to
build. Should I create a save button with a macro that runs the
query...Will
this refresh the data? But it would then cause the user to hit the
'save'
button for each change to the subform, which could be tedious.

Thanks
Laura

 

[Guest]

I ended up puting a button on the main form footer to refresh the underlying
query and it recalculates all the changes I've made.

Works!!

Thanks
Laura

OK, that the problem.

In NorthWind (and my databases), the ControlSource for the Total Control on
the MainForm is set to:

= [SubformControlName].Form![TotalTextBoxInFooter]

(including the equal sign.

--
HTH
Van T. Dinh
MVP (Access)

Yes, I'm using Continuous form view and moving focus doesn't solve the
problem...When I move to a new record on the subform, the subform total
will
change, but not the total on the main form. Access is loaded at work on
our
server so I don't think they have loaded the NW db, but I'll ask.

But you have led me to something, I do not have the main form textbox
total
source set to the subform total textbox...I have it set to the same field
in
the mainform query, as I do in the subform query, but not the actual
subform
itself.

Laura

The TextBox in the Form Footer of the Subform is the intermediate step to
show the Total in the Main Form. In fact, I usually set the Form Footer
invisible so that the users don't see the Form Footer or the TextBox in
the
Form Footer.

I think the Sum does not include the new Record being entered or the
edited
amount until you move the Focus to another rowRecord in the Subform
(assuming you are using the Form in CtsFormView).

Try moving the Focus to another row / Record ...

If you still have problem, check your set-up against the Form "Orders" in
the sample database "NorthWind" that is usually installed with your
Access
software.

--
HTH
Van T. Dinh
MVP (Access)



Hello,

Sorry, I don't understand what you mean by intermediate. I created an
unbound text box in the footer of the main form with the following as
the
source

=sum([funded])

It provides me with the correct sum, but does not change when a record
to
the subform is changed, it only changes when the form is closed and
reopened.
I'm thinking I need to somehow refresh the query that the main form is
based
off of. But I'm not sure in which event to make this happen or what
macro
to
build. Should I create a save button with a macro that runs the
query...Will
this refresh the data? But it would then cause the user to hit the
'save'
button for each change to the subform, which could be tedious.

Thanks
Laura