Sub Form Total

[katz]

Hello,
I have a Mainform with a Subform with details. In the subform I have a
control name 'Amount ' and in the Form footer I have TotAmt using Sum to get
the total of all the detail Amounts, and on the MainForm I display the
TotAmt field.
The problem is, the Sum(TotAmt) control gets updated only when the user tabs
to the next record.
Many time the user will press a button (for processing) on the Main form,
before going to the next record on the Detail and I don't have the sum of
all detail records yet.
How can I get the correct sum for TotAmt as they press the button on the
Main form, because on the On click event I need the correct Sum(TotAmt). I
tried to add the saveRecord right in the beginning, but it doesn't work.

Sean Bailey told me to try adding a line of code at the beginning of the
main form
buttons OnClick event like;
Me.[NameOfYourSubfomControl].Form.Dirty = False
But it dosent work. Is there something else I can try?

Thanks
Abe

 

[Jeanette Cunningham]

Hi Katz,
Access puts a lower priority on updating calculated controls on forms than
running all the other code that relates to controls on the form.

Sometimes putting the line DoEvents will help

Sometimes you may need to put
DoEvents
DoEvents
DoEvents

You could try this instead of the save suggested in the previous post. I
don't know if it will help you unless you try it.

I am trying to understand your setup. Here is my understanding, correct me
if I am wrong.
The subform is a continuous form.
Each record of the subform has a control named Amount.
On the footer of the subform there is txtbox TotAmt which sums the Amount
for all the records in the subform.

When you say

Many time the user will press a button (for processing) on the Main form,
before going to the next record on the Detail and I don't have the sum of
all detail records yet.

What exactly happens? What is the button they press and what does it do?
What do you need the value of TotAmt for in the MainForm? Is it needed in a
calculation or to decide what to do next?

When you say
The problem is, the Sum(TotAmt) control gets updated only when the user tabs

to the next record.

Do you mean that the user tabs to a new record on the MainForm?

Jeanette Cunningham

Hello,
I have a Mainform with a Subform with details. In the subform I have a
control name 'Amount ' and in the Form footer I have TotAmt using Sum to
get
the total of all the detail Amounts, and on the MainForm I display the
TotAmt field.
The problem is, the Sum(TotAmt) control gets updated only when the user
tabs
to the next record.
Many time the user will press a button (for processing) on the Main form,
before going to the next record on the Detail and I don't have the sum of
all detail records yet.
How can I get the correct sum for TotAmt as they press the button on the
Main form, because on the On click event I need the correct Sum(TotAmt). I
tried to add the saveRecord right in the beginning, but it doesn't work.

Sean Bailey told me to try adding a line of code at the beginning of the
main form
buttons OnClick event like;
Me.[NameOfYourSubfomControl].Form.Dirty = False
But it dosent work. Is there something else I can try?

Thanks
Abe

 

[katz]

Hi Jeanette, Thanks for your response.

You are right, the txtbox TotAmt sums the Amount for all the records in the
subform.
I also have a txtbox TotAmtMain displayed on the main form. e.g..
TotAmtMain=[Invoice Detail].[Form]![TotAmt]
As I enter the detail records the TotAmtMain gets updated when I go the next
record (line). But when I am on the last record entering an amount the
TotAmtMain is not updated yet, and I press the Button on the Main form
before going to the next line.
Here is the problem, when I click the button on the main form I need the
correct Total for all the detail records including the last one.
The DoEvents doesn't work
Is there an other solution?
Thanks again
Abe

Hi Katz,
Access puts a lower priority on updating calculated controls on forms than
running all the other code that relates to controls on the form.

Sometimes putting the line DoEvents will help

Sometimes you may need to put
DoEvents
DoEvents
DoEvents

You could try this instead of the save suggested in the previous post. I
don't know if it will help you unless you try it.

I am trying to understand your setup. Here is my understanding, correct me
if I am wrong.
The subform is a continuous form.
Each record of the subform has a control named Amount.
On the footer of the subform there is txtbox TotAmt which sums the Amount
for all the records in the subform.

When you say

Many time the user will press a button (for processing) on the Main form,
before going to the next record on the Detail and I don't have the sum of
all detail records yet.

What exactly happens? What is the button they press and what does it do?
What do you need the value of TotAmt for in the MainForm? Is it needed in
a
calculation or to decide what to do next?

When you say
The problem is, the Sum(TotAmt) control gets updated only when the user
tabs

to the next record.

Do you mean that the user tabs to a new record on the MainForm?

Jeanette Cunningham

Hello,
I have a Mainform with a Subform with details. In the subform I have a
control name 'Amount ' and in the Form footer I have TotAmt using Sum to
get
the total of all the detail Amounts, and on the MainForm I display the
TotAmt field.
The problem is, the Sum(TotAmt) control gets updated only when the user
tabs
to the next record.
Many time the user will press a button (for processing) on the Main form,
before going to the next record on the Detail and I don't have the sum of
all detail records yet.
How can I get the correct sum for TotAmt as they press the button on the
Main form, because on the On click event I need the correct Sum(TotAmt).
I
tried to add the saveRecord right in the beginning, but it doesn't work.

Sean Bailey told me to try adding a line of code at the beginning of the
main form
buttons OnClick event like;
Me.[NameOfYourSubfomControl].Form.Dirty = False
But it dosent work. Is there something else I can try?

Thanks
Abe