Order of Events for Sum?

G

Guest

Access03/WinXP

While it's not generally recommended, I have had to take a calculated sum
from a popup form and store it in a field on a main form (there are many
issues involved here; suffice it to say too many people have access to
information and the queries involved in arriving at some of the totals are
too complicated for them - they only need to query the main form's table and
get to the total).

In the subform footer, I have an unbound text box, txtTotalPayments set to
=Sum([PmtAmt]). In the records of the subform is a command button which
automatically inserts a default amount (based on an invoice amount and
payment schedules), payment date (as current date), etc. In the OnClick on
this command button I have the following:

Me.PaymentDate = Date()
Me.PaymentAmount = {a function to calculate is referenced here}
Me.EnteredBy = CurrentUser()
Me.Refresh
Forms!frmSales.txtAllPayments = Me.txtTotalPayments

(txtAllPayments is a bound field)

When the main form's field txtAllPayments was not updating properly when a
new payment record was input, I added MsgBox me.txtTotalPayments after the
Refresh to see what total was being represented. The msgbox displayed the
total before the new record was added, even though I have Me.Refresh before
the msgbox.

What do I need to do to save the record and have txtTotalPayments
recalculate appropriately BEFORE the main form's txtAllPayments is updated?
I've tried requerying and repainting the subform and have not had the correct
result.

Thanks!
 
S

Steve Schapel

Pendragon,

Try it like this:

Me.PaymentDate = Date()
Me.PaymentAmount = {a function to calculate is referenced here}
Me.EnteredBy = CurrentUser()
DoCmd.RunCommand acCmdSaveRecord
Me.Recalc
Forms!frmSales!txtAllPayments = Me.txtTotalPayments
 
G

Guest

F A N T A S T I C !!!!!!!!!! I'm so happy I could cry - I've spent days
going through so many permutations of this and that.

Bless you!

Steve Schapel said:
Pendragon,

Try it like this:

Me.PaymentDate = Date()
Me.PaymentAmount = {a function to calculate is referenced here}
Me.EnteredBy = CurrentUser()
DoCmd.RunCommand acCmdSaveRecord
Me.Recalc
Forms!frmSales!txtAllPayments = Me.txtTotalPayments

--
Steve Schapel, Microsoft Access MVP
Access03/WinXP

While it's not generally recommended, I have had to take a calculated sum
from a popup form and store it in a field on a main form (there are many
issues involved here; suffice it to say too many people have access to
information and the queries involved in arriving at some of the totals are
too complicated for them - they only need to query the main form's table and
get to the total).

In the subform footer, I have an unbound text box, txtTotalPayments set to
=Sum([PmtAmt]). In the records of the subform is a command button which
automatically inserts a default amount (based on an invoice amount and
payment schedules), payment date (as current date), etc. In the OnClick on
this command button I have the following:

Me.PaymentDate = Date()
Me.PaymentAmount = {a function to calculate is referenced here}
Me.EnteredBy = CurrentUser()
Me.Refresh
Forms!frmSales.txtAllPayments = Me.txtTotalPayments

(txtAllPayments is a bound field)

When the main form's field txtAllPayments was not updating properly when a
new payment record was input, I added MsgBox me.txtTotalPayments after the
Refresh to see what total was being represented. The msgbox displayed the
total before the new record was added, even though I have Me.Refresh before
the msgbox.

What do I need to do to save the record and have txtTotalPayments
recalculate appropriately BEFORE the main form's txtAllPayments is updated?
I've tried requerying and repainting the subform and have not had the correct
result.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reducing Balance on Invoice 9
Duplicate Form, Subfrom 1
subform total on Main 3
Record sum from subform 1
Conditional Sum 4
Sum data under special condition 1
text box update problem 6
Very slow textbox expression 3

Top