Refresh a subtotal please leaving record

[Nigel]

I am trying to do a running balance on individual records, on the form footer
I have total of all the payments made on an invoice, I need the to refresh so
I can calculate the running balance. Currently the only way I can get the
running total to refresh is to move to a new record, anyway I can do this
the end result is a running total of payments and balances. I do have a total
at the bottom of the form for the invoice amount also and I am using a check
box in the record to mark it as a payment

here is the code I am using

If Me!paymentyn = True Then
Me!linetotal = 0
Me!payment = Me!quantity * Me!price
Me!balance = Me!subtotal - Me!paytotal
Me!price.SetFocus
End If

paytotal is the filed name that I need to refresh before exiting the
record,I tried a save record but it didn't like that

thanks

 

[Allen Browne]

Nigel, are you storing the balance in the main form's table, or the
subform's table?

If so, that's the problem. Just leave it out of the table, and get Access to
calculate it when needed. For example, if your subform is in Continuous Form
view, you could put a text box in the Form Footer section (of the subform),
and set its ControlSource to:
=Sum([quantity] * [price])

That's for the total. Do something similar for the payments, and then the
difference is the balance.

It really is important not to store these values in the table if you want
your application to be reliable.

 

[Nigel]

I have the subtotal and I have the total payments but until I leave the
record those figures do not update, I am trying to get the balance to update
for the record where the payment is made

for example:

record 1 is an invoice amount for 100
record 2 is for an amount of 300

the total is 400 and on record 1 balance should read 100 record 2 it should
read 400

record 3 is a payment of 150 the balance on that line should be 250, but
until I move to the next record the totals at the bottom of the form do not
update

this is becaue they want to see seperate billing and payment lines on an
invoice, hope it makes sense

Nigel, are you storing the balance in the main form's table, or the
subform's table?

If so, that's the problem. Just leave it out of the table, and get Access to
calculate it when needed. For example, if your subform is in Continuous Form
view, you could put a text box in the Form Footer section (of the subform),
and set its ControlSource to:
=Sum([quantity] * [price])

That's for the total. Do something similar for the payments, and then the
difference is the balance.

It really is important not to store these values in the table if you want
your application to be reliable.

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

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

I am trying to do a running balance on individual records, on the form
footer
I have total of all the payments made on an invoice, I need the to refresh
so
I can calculate the running balance. Currently the only way I can get the
running total to refresh is to move to a new record, anyway I can do this
the end result is a running total of payments and balances. I do have a
total
at the bottom of the form for the invoice amount also and I am using a
check
box in the record to mark it as a payment

here is the code I am using

If Me!paymentyn = True Then
Me!linetotal = 0
Me!payment = Me!quantity * Me!price
Me!balance = Me!subtotal - Me!paytotal
Me!price.SetFocus
End If

paytotal is the filed name that I need to refresh before exiting the
record,I tried a save record but it didn't like that

thanks

 

[Allen Browne]

You did not answer the question about whether you are storing the balance in
your table.

If it is a calculated total (not a stored value), you can update it just by
forcing the record to save. If you want to do that in code:
If Me.Dirty Then Me.Dirty = False

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

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

I have the subtotal and I have the total payments but until I leave the
record those figures do not update, I am trying to get the balance to
update
for the record where the payment is made

for example:

record 1 is an invoice amount for 100
record 2 is for an amount of 300

the total is 400 and on record 1 balance should read 100 record 2 it
should
read 400

record 3 is a payment of 150 the balance on that line should be 250, but
until I move to the next record the totals at the bottom of the form do
not
update

this is becaue they want to see seperate billing and payment lines on an
invoice, hope it makes sense

Nigel, are you storing the balance in the main form's table, or the
subform's table?

If so, that's the problem. Just leave it out of the table, and get Access
to
calculate it when needed. For example, if your subform is in Continuous
Form
view, you could put a text box in the Form Footer section (of the
subform),
and set its ControlSource to:
=Sum([quantity] * [price])

That's for the total. Do something similar for the payments, and then the
difference is the balance.

It really is important not to store these values in the table if you want
your application to be reliable.

I am trying to do a running balance on individual records, on the form
footer
I have total of all the payments made on an invoice, I need the to
refresh
so
I can calculate the running balance. Currently the only way I can get
the
running total to refresh is to move to a new record, anyway I can do
this
the end result is a running total of payments and balances. I do have a
total
at the bottom of the form for the invoice amount also and I am using a
check
box in the record to mark it as a payment

here is the code I am using

If Me!paymentyn = True Then
Me!linetotal = 0
Me!payment = Me!quantity * Me!price
Me!balance = Me!subtotal - Me!paytotal
Me!price.SetFocus
End If

paytotal is the filed name that I need to refresh before exiting the
record,I tried a save record but it didn't like that

 

[Nigel]

yes I am wanting to store the value within the record

You did not answer the question about whether you are storing the balance in
your table.

If it is a calculated total (not a stored value), you can update it just by
forcing the record to save. If you want to do that in code:
If Me.Dirty Then Me.Dirty = False

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

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

I have the subtotal and I have the total payments but until I leave the
record those figures do not update, I am trying to get the balance to
update
for the record where the payment is made

for example:

record 1 is an invoice amount for 100
record 2 is for an amount of 300

the total is 400 and on record 1 balance should read 100 record 2 it
should
read 400

record 3 is a payment of 150 the balance on that line should be 250, but
until I move to the next record the totals at the bottom of the form do
not
update

this is becaue they want to see seperate billing and payment lines on an
invoice, hope it makes sense

Nigel, are you storing the balance in the main form's table, or the
subform's table?

If so, that's the problem. Just leave it out of the table, and get Access
to
calculate it when needed. For example, if your subform is in Continuous
Form
view, you could put a text box in the Form Footer section (of the
subform),
and set its ControlSource to:
=Sum([quantity] * [price])

That's for the total. Do something similar for the payments, and then the
difference is the balance.

It really is important not to store these values in the table if you want
your application to be reliable.

I am trying to do a running balance on individual records, on the form
footer
I have total of all the payments made on an invoice, I need the to
refresh
so
I can calculate the running balance. Currently the only way I can get
the
running total to refresh is to move to a new record, anyway I can do
this
the end result is a running total of payments and balances. I do have a
total
at the bottom of the form for the invoice amount also and I am using a
check
box in the record to mark it as a payment

here is the code I am using

If Me!paymentyn = True Then
Me!linetotal = 0
Me!payment = Me!quantity * Me!price
Me!balance = Me!subtotal - Me!paytotal
Me!price.SetFocus
End If

paytotal is the filed name that I need to refresh before exiting the
record,I tried a save record but it didn't like that

 

[Allen Browne]

Okay: that's the problem, Nigel.

Normalize the design, and you solve the problem.

If that comment doesn't make any sense, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

If you are absolutely determined not to use a good design, you will need to
use a DSum() expression to get the total directly from the table for *other*
rows, and then add the value of the current row.

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

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

yes I am wanting to store the value within the record

You did not answer the question about whether you are storing the balance
in
your table.

If it is a calculated total (not a stored value), you can update it just
by
forcing the record to save. If you want to do that in code:
If Me.Dirty Then Me.Dirty = False

I have the subtotal and I have the total payments but until I leave the
record those figures do not update, I am trying to get the balance to
update
for the record where the payment is made

for example:

record 1 is an invoice amount for 100
record 2 is for an amount of 300

the total is 400 and on record 1 balance should read 100 record 2 it
should
read 400

record 3 is a payment of 150 the balance on that line should be 250,
but
until I move to the next record the totals at the bottom of the form do
not
update

this is becaue they want to see seperate billing and payment lines on
an
invoice, hope it makes sense


:

Nigel, are you storing the balance in the main form's table, or the
subform's table?

If so, that's the problem. Just leave it out of the table, and get
Access
to
calculate it when needed. For example, if your subform is in
Continuous
Form
view, you could put a text box in the Form Footer section (of the
subform),
and set its ControlSource to:
=Sum([quantity] * [price])

That's for the total. Do something similar for the payments, and then
the
difference is the balance.

It really is important not to store these values in the table if you
want
your application to be reliable.

I am trying to do a running balance on individual records, on the
form
footer
I have total of all the payments made on an invoice, I need the to
refresh
so
I can calculate the running balance. Currently the only way I can
get
the
running total to refresh is to move to a new record, anyway I can
do
this
the end result is a running total of payments and balances. I do
have a
total
at the bottom of the form for the invoice amount also and I am using
a
check
box in the record to mark it as a payment

here is the code I am using

If Me!paymentyn = True Then
Me!linetotal = 0
Me!payment = Me!quantity * Me!price
Me!balance = Me!subtotal - Me!paytotal
Me!price.SetFocus
End If

paytotal is the filed name that I need to refresh before exiting the
record,I tried a save record but it didn't like that