Total in subform

[danc09]

Using A2K, I have two tables, tblCustomer and tblPayments. tblCustomer has an
original amount field. I have a mainform based on a query of tblCustomer with
a subform based on a query of tblPayments. On the subform footer is an
unbound textbox which displays the balance of the account, using the original
amount from the main form minus the sum of payment transactions. The formula
on the mainform is:
=nz([Forms]![frmStartup]![ARAmount])+Sum(nz([PmtAmt]))
This works fine if there are transactions to display on the subform.
However, if I add a new customer with an original amount, no balance is
displayed in the subform footer. It seems that I need to be able to check to
see if there are transactions and display either the original amount or use
the formula above.
Any help will be greatly appreciated.
Thanks very much.
Dan

 

[Arvin Meyer [MVP]]

Just use that expression. If you have an amount it will display the amount.
Otherwise it will display 0 (zero).

 

[danc09]

Sorry, the formula is actually in the textbox in the subform footer. It
actually does show the balance IF there are any payments. If there are no
payments the textbox is blank (no balance information).
So, when I add a new customer, there are no payments, just an original
amount and the textbox on the subform footer is blank.
How can I know there are no payment records so I can just show the original
amount?
Thanks
Dan

Just use that expression. If you have an amount it will display the amount.
Otherwise it will display 0 (zero).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Using A2K, I have two tables, tblCustomer and tblPayments. tblCustomer has
an
original amount field. I have a mainform based on a query of tblCustomer
with
a subform based on a query of tblPayments. On the subform footer is an
unbound textbox which displays the balance of the account, using the
original
amount from the main form minus the sum of payment transactions. The
formula
on the mainform is:
=nz([Forms]![frmStartup]![ARAmount])+Sum(nz([PmtAmt]))
This works fine if there are transactions to display on the subform.
However, if I add a new customer with an original amount, no balance is
displayed in the subform footer. It seems that I need to be able to check
to
see if there are transactions and display either the original amount or
use
the formula above.
Any help will be greatly appreciated.
Thanks very much.
Dan

 

[Arvin Meyer [MVP]]

Try:

=nz([Forms]![frmStartup]![ARAmount],0)+Sum(nz([PmtAmt],0))

Adding the zeros in the expression, shouldn't be necessary, but try it
anyway to force the value.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Sorry, the formula is actually in the textbox in the subform footer. It
actually does show the balance IF there are any payments. If there are no
payments the textbox is blank (no balance information).
So, when I add a new customer, there are no payments, just an original
amount and the textbox on the subform footer is blank.
How can I know there are no payment records so I can just show the
original
amount?
Thanks
Dan

Just use that expression. If you have an amount it will display the
amount.
Otherwise it will display 0 (zero).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Using A2K, I have two tables, tblCustomer and tblPayments. tblCustomer
has
an
original amount field. I have a mainform based on a query of
tblCustomer
with
a subform based on a query of tblPayments. On the subform footer is an
unbound textbox which displays the balance of the account, using the
original
amount from the main form minus the sum of payment transactions. The
formula
on the mainform is:
=nz([Forms]![frmStartup]![ARAmount])+Sum(nz([PmtAmt]))
This works fine if there are transactions to display on the subform.
However, if I add a new customer with an original amount, no balance is
displayed in the subform footer. It seems that I need to be able to
check
to
see if there are transactions and display either the original amount or
use
the formula above.
Any help will be greatly appreciated.
Thanks very much.
Dan

 

[danc09]

I tried it, but still if there are no payments, the textbox in the subform
footer for the total is blank. Maybe if there was a way in the subform to
know if there were no records? Thanks for your help.
Dan

Try:

=nz([Forms]![frmStartup]![ARAmount],0)+Sum(nz([PmtAmt],0))

Adding the zeros in the expression, shouldn't be necessary, but try it
anyway to force the value.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Sorry, the formula is actually in the textbox in the subform footer. It
actually does show the balance IF there are any payments. If there are no
payments the textbox is blank (no balance information).
So, when I add a new customer, there are no payments, just an original
amount and the textbox on the subform footer is blank.
How can I know there are no payment records so I can just show the
original
amount?
Thanks
Dan

Just use that expression. If you have an amount it will display the
amount.
Otherwise it will display 0 (zero).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Using A2K, I have two tables, tblCustomer and tblPayments. tblCustomer
has
an
original amount field. I have a mainform based on a query of
tblCustomer
with
a subform based on a query of tblPayments. On the subform footer is an
unbound textbox which displays the balance of the account, using the
original
amount from the main form minus the sum of payment transactions. The
formula
on the mainform is:
=nz([Forms]![frmStartup]![ARAmount])+Sum(nz([PmtAmt]))
This works fine if there are transactions to display on the subform.
However, if I add a new customer with an original amount, no balance is
displayed in the subform footer. It seems that I need to be able to
check
to
see if there are transactions and display either the original amount or
use
the formula above.
Any help will be greatly appreciated.
Thanks very much.
Dan

 

[Arvin Meyer [MVP]]

If there are no records in the subform, you should be able to see that,
shouldn't you?

There is another possible answer. I've never seen it need in a form/subform,
but it has been necessary for reports:

http://www.mvps.org/access/reports/rpt0007.htm

or possibly:

http://www.mvps.org/access/forms/frm0022.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I tried it, but still if there are no payments, the textbox in the subform
footer for the total is blank. Maybe if there was a way in the subform to
know if there were no records? Thanks for your help.
Dan

Try:

=nz([Forms]![frmStartup]![ARAmount],0)+Sum(nz([PmtAmt],0))

Adding the zeros in the expression, shouldn't be necessary, but try it
anyway to force the value.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Sorry, the formula is actually in the textbox in the subform footer.
It
actually does show the balance IF there are any payments. If there are
no
payments the textbox is blank (no balance information).
So, when I add a new customer, there are no payments, just an original
amount and the textbox on the subform footer is blank.
How can I know there are no payment records so I can just show the
original
amount?
Thanks
Dan

:

Just use that expression. If you have an amount it will display the
amount.
Otherwise it will display 0 (zero).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Using A2K, I have two tables, tblCustomer and tblPayments.
tblCustomer
has
an
original amount field. I have a mainform based on a query of
tblCustomer
with
a subform based on a query of tblPayments. On the subform footer is
an
unbound textbox which displays the balance of the account, using the
original
amount from the main form minus the sum of payment transactions.
The
formula
on the mainform is:
=nz([Forms]![frmStartup]![ARAmount])+Sum(nz([PmtAmt]))
This works fine if there are transactions to display on the subform.
However, if I add a new customer with an original amount, no balance
is
displayed in the subform footer. It seems that I need to be able to
check
to
see if there are transactions and display either the original amount
or
use
the formula above.
Any help will be greatly appreciated.
Thanks very much.
Dan

 

[danc09]

I have gotten around the problem by adding a second subform which only
contains the calculated balance, and it is working fine. However, when I add
a payment I have Me.Requery in the after update of the payment amount. This
works, except I am then at the beginning of the file again. How can I remain

If there are no records in the subform, you should be able to see that,
shouldn't you?

There is another possible answer. I've never seen it need in a form/subform,
but it has been necessary for reports:

http://www.mvps.org/access/reports/rpt0007.htm

or possibly:

http://www.mvps.org/access/forms/frm0022.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I tried it, but still if there are no payments, the textbox in the subform
footer for the total is blank. Maybe if there was a way in the subform to
know if there were no records? Thanks for your help.
Dan

Try:

=nz([Forms]![frmStartup]![ARAmount],0)+Sum(nz([PmtAmt],0))

Adding the zeros in the expression, shouldn't be necessary, but try it
anyway to force the value.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Sorry, the formula is actually in the textbox in the subform footer.
It
actually does show the balance IF there are any payments. If there are
no
payments the textbox is blank (no balance information).
So, when I add a new customer, there are no payments, just an original
amount and the textbox on the subform footer is blank.
How can I know there are no payment records so I can just show the
original
amount?
Thanks
Dan

:

Just use that expression. If you have an amount it will display the
amount.
Otherwise it will display 0 (zero).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Using A2K, I have two tables, tblCustomer and tblPayments.
tblCustomer
has
an
original amount field. I have a mainform based on a query of
tblCustomer
with
a subform based on a query of tblPayments. On the subform footer is
an
unbound textbox which displays the balance of the account, using the
original
amount from the main form minus the sum of payment transactions.
The
formula
on the mainform is:
=nz([Forms]![frmStartup]![ARAmount])+Sum(nz([PmtAmt]))
This works fine if there are transactions to display on the subform.
However, if I add a new customer with an original amount, no balance
is
displayed in the subform footer. It seems that I need to be able to
check
to
see if there are transactions and display either the original amount
or
use
the formula above.
Any help will be greatly appreciated.
Thanks very much.
Dan

 

[Arvin Meyer [MVP]]

I have gotten around the problem by adding a second subform which only
contains the calculated balance, and it is working fine. However, when I
add
a payment I have Me.Requery in the after update of the payment amount.
This
works, except I am then at the beginning of the file again. How can I
remain
on the current record when I requery the form?
Dan

That is doable.

You need to add to your procedure something like (aircode):

Dim lngID As Long
lngID = Me.RecordID ' the PK of that record

Me.Requery ' Now do your requery

Mr.RecordsetClone.Bookmark = lngID

IOW, you capture the record's ID, then requery, then move back to that
record. Moving to the new record is a bit more complex.

 

[danc09]

When I tried that I received the error message Run-time error '3421';
Data
type conversion error.

I am in the subform1 (frmStartupSubform) when I add a payment amount. ARID
is the foreign key in the payment table (based on query of tblPayments), and
the PK in the Customer table (the main form based on a query of tblCustomer).
Here is my code in the afterupdate of the payment amount on the
subform1(frmStartupSubform).
Private Sub PmtAmount_AfterUpdate()
Dim lngID As Long
lngID = Me.ARID
Me.Requery

Me.RecordsetClone.Bookmark = lngID

End Sub

Thanks,
Dan

 

[Arvin Meyer [MVP]]

Are your IDs Long Integers?

Try stepping through the code (F8) after putting a breakpoint at the line:

lngID = Me.ARID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com