get sum from subform on button(on main for)click

  • Thread starter Thread starter Jahanzaib
  • Start date Start date
J

Jahanzaib

Hi

In the footer of a continuous subform, I have a text box named 'SumDebit',
with control sources=Sum(nz([DebitAmount],0)).
I then use a button on the main form to compare the another textbox value
on main form with subform's 'SumDebit'.
This works, but with one big problem. When I use the mouse to move out of
the subform and click on the mainform button, the last subform record is not
included in the sums.
I have tried requery and recalc.
These do update the subform totals in the subform footer, but AFTER my code
to compare values.
Please help

Thanks in advance!

JB
 
In your button's click event, Recalc the subform before reading the amount,
e.g.:
Me.[Sub1].Form.Recalc
Debug.Print Me.[Sub1].form!SumDebit

Substitute the name of your subform control for Sub1.

If you moved focus to the main form programmatically, you may also need to
save the subform record before this will work i.e.:
Me.[Sub1].Form.Dirty = False
 
Thanks for reply, Mr. Brown, glad to read your post, you are very popular on
ms access db forums.

Unfortunately problem did not solve, Lets see in a sequence;

1-I insert a new record/update an old record in subform1
2-Click the button on 'Main Form' (while the subform1 record is in edit
mode(dirty=-1))
3-button Click event has the following code

subform1.form.recalc
msgbox me.Subform1.form!SumDebit
'Comapare values

4-I get the blank msgbox.....

hope you would help.
JB


Allen Browne said:
In your button's click event, Recalc the subform before reading the amount,
e.g.:
Me.[Sub1].Form.Recalc
Debug.Print Me.[Sub1].form!SumDebit

Substitute the name of your subform control for Sub1.

If you moved focus to the main form programmatically, you may also need to
save the subform record before this will work i.e.:
Me.[Sub1].Form.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Jahanzaib said:
Hi

In the footer of a continuous subform, I have a text box named 'SumDebit',
with control sources=Sum(nz([DebitAmount],0)).
I then use a button on the main form to compare the another textbox value
on main form with subform's 'SumDebit'.
This works, but with one big problem. When I use the mouse to move out of
the subform and click on the mainform button, the last subform record is
not
included in the sums.
I have tried requery and recalc.
These do update the subform totals in the subform footer, but AFTER my
code
to compare values.
Please help

Thanks in advance!

JB
Click to expand...
Click to expand...
 
You say it does update afterwards, so presumably this is a timing issue.
(You could test that by adding a Stop before the MsgBox. Then when it
pauses, press F5 to continue. See if you get the updated total after the
pause.)

A DoEvents may help, but how much pause do you need?

Display the Record Selector so you can see whether the subform record is
dirty or not. It should automatically save *before* the main form's button's
Click event runs, but you may have some code that dirties it again.

In any case, why do you need this total at this time? (Hopefully you're not
doing anything unnormalized such as storing it.)

If you can't get it working, could you use a DSum() on the subform's table
to get the desired total?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Jahanzaib said:
Thanks for reply, Mr. Brown, glad to read your post, you are very popular
on
ms access db forums.

Unfortunately problem did not solve, Lets see in a sequence;

1-I insert a new record/update an old record in subform1
2-Click the button on 'Main Form' (while the subform1 record is in edit
mode(dirty=-1))
3-button Click event has the following code

subform1.form.recalc
msgbox me.Subform1.form!SumDebit
'Comapare values

4-I get the blank msgbox.....

hope you would help.
JB


Allen Browne said:
In your button's click event, Recalc the subform before reading the
amount,
e.g.:
Me.[Sub1].Form.Recalc
Debug.Print Me.[Sub1].form!SumDebit

Substitute the name of your subform control for Sub1.

If you moved focus to the main form programmatically, you may also need
to
save the subform record before this will work i.e.:
Me.[Sub1].Form.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Jahanzaib said:
Hi

In the footer of a continuous subform, I have a text box named
'SumDebit',
with control sources=Sum(nz([DebitAmount],0)).
I then use a button on the main form to compare the another textbox
value
on main form with subform's 'SumDebit'.
This works, but with one big problem. When I use the mouse to move out
of
the subform and click on the mainform button, the last subform record
is
not
included in the sums.
I have tried requery and recalc.
These do update the subform totals in the subform footer, but AFTER my
code
to compare values.
Please help

Thanks in advance!

JB
Click to expand...
Click to expand...
Click to expand...
 
Back
Top