Invoice Payments Subform / Reset Balance Due?

B

Brook

Good Day All,

I have an invoice form that I use for orders and within that invoice form,
I have a invoicepayments subform that is set up to accept multiple payments,
assign a payment number, accept a payment amount, and deduct the amount from
the invoice total and therefore keep a running total.

I have two types of invoices, consignment (CON-0001, CON-0002, etc) and
Sold (SLD-0001, SLD-0002, etc).

What I need is to have the payments running total "reset" or calculated
for each invoice number, right now, if I enter a payment on invoice CON-0001
then go to invoice SLD-0001, the CON-0001 payment is deducted from the
invoice SLD-0001 payment total.


Thanks in advance....

Brook

Here is the code that I use on my payments form.

Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function
 
J

Joshua A. Booker

Brook,

The criteria in your DSum call needs to include the InvoiceNumber. Not
knowing your filed names, I can't get it right, but it should look something
like this:

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", "[PaymentDate] <= #" &[paymentdate] & "# AND
InvoiceID = " & Me!InvoiceID)

As you have it, all the payments in the payments table are being deducted
from the current invoice. Also, you may want to try an approach that only
deducts the current payment. Because when you run this a second time, it
will deduct the preexisting payment amounts again. Lastly, it's always best
to calculate values that rely on other data at the time you need to report
them. This way you don't have to worry about all the implications of
updates like this. Even so, many accounting databases store the calculated
open amount on invoices for faster reporting.

HTH,
Josh
 
B

Brook

joshua..

Thanks for the post...

well, the reason that I am trying to set it up this way is so that I can
create A/R reporting for taxes.

Brook


Joshua A. Booker said:
Brook,

The criteria in your DSum call needs to include the InvoiceNumber. Not
knowing your filed names, I can't get it right, but it should look something
like this:

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", "[PaymentDate] <= #" &[paymentdate] & "# AND
InvoiceID = " & Me!InvoiceID)

As you have it, all the payments in the payments table are being deducted
from the current invoice. Also, you may want to try an approach that only
deducts the current payment. Because when you run this a second time, it
will deduct the preexisting payment amounts again. Lastly, it's always best
to calculate values that rely on other data at the time you need to report
them. This way you don't have to worry about all the implications of
updates like this. Even so, many accounting databases store the calculated
open amount on invoices for faster reporting.

HTH,
Josh

Brook said:
Good Day All,

I have an invoice form that I use for orders and within that invoice form,
I have a invoicepayments subform that is set up to accept multiple payments,
assign a payment number, accept a payment amount, and deduct the amount from
the invoice total and therefore keep a running total.

I have two types of invoices, consignment (CON-0001, CON-0002, etc) and
Sold (SLD-0001, SLD-0002, etc).

What I need is to have the payments running total "reset" or calculated
for each invoice number, right now, if I enter a payment on invoice CON-0001
then go to invoice SLD-0001, the CON-0001 payment is deducted from the
invoice SLD-0001 payment total.


Thanks in advance....

Brook

Here is the code that I use on my payments form.

Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function
Click to expand...
Click to expand...
 
B

Brook

Joshua,

When I added the code to include the invoiceid, I am getting an error. I
will point out where I'm getting the error, but first thing... When I open my
payment entry subform, in the Balance Due column that is calculated by the
"=MyNewBalance" I get the following before I enter any information: "#Error"

Begin Code:
Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "# AND> Invoicenumber = " &
Me!invoicenumber)
End If
End Function
End Code:

Begin Error Debug:

Private Sub paymentamount_AfterUpdate()
Me.Recalc
Me.InvTotal = Me.invoicetotal
Me.AmtDue = Me.Balance_Due ---- this is where the error is happening
End Sub
End Error Debug:


Do you have any suggestions?

Brook
Joshua A. Booker said:
Brook,

The criteria in your DSum call needs to include the InvoiceNumber. Not
knowing your filed names, I can't get it right, but it should look something
like this:

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", "[PaymentDate] <= #" &[paymentdate] & "# AND
InvoiceID = " & Me!InvoiceID)

As you have it, all the payments in the payments table are being deducted
from the current invoice. Also, you may want to try an approach that only
deducts the current payment. Because when you run this a second time, it
will deduct the preexisting payment amounts again. Lastly, it's always best
to calculate values that rely on other data at the time you need to report
them. This way you don't have to worry about all the implications of
updates like this. Even so, many accounting databases store the calculated
open amount on invoices for faster reporting.

HTH,
Josh

Brook said:
Good Day All,

I have an invoice form that I use for orders and within that invoice form,
I have a invoicepayments subform that is set up to accept multiple payments,
assign a payment number, accept a payment amount, and deduct the amount from
the invoice total and therefore keep a running total.

I have two types of invoices, consignment (CON-0001, CON-0002, etc) and
Sold (SLD-0001, SLD-0002, etc).

What I need is to have the payments running total "reset" or calculated
for each invoice number, right now, if I enter a payment on invoice CON-0001
then go to invoice SLD-0001, the CON-0001 payment is deducted from the
invoice SLD-0001 payment total.


Thanks in advance....

Brook

Here is the code that I use on my payments form.

Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function
Click to expand...
Click to expand...
 
J

Joshua A. Booker

Brook,

It looks like you have an extra > in your code:
Try this:
"[PaymentDate] <= #" & [paymentdate] & "# AND Invoicenumber = " &
Me!invoicenumber

HTH,
Josh

Brook said:
Joshua,

When I added the code to include the invoiceid, I am getting an error. I
will point out where I'm getting the error, but first thing... When I open my
payment entry subform, in the Balance Due column that is calculated by the
"=MyNewBalance" I get the following before I enter any information: "#Error"

Begin Code:
Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "# AND> Invoicenumber = " &
Me!invoicenumber)
End If
End Function
End Code:

Begin Error Debug:

Private Sub paymentamount_AfterUpdate()
Me.Recalc
Me.InvTotal = Me.invoicetotal
Me.AmtDue = Me.Balance_Due ---- this is where the error is happening
End Sub
End Error Debug:


Do you have any suggestions?

Brook
Joshua A. Booker said:
Brook,

The criteria in your DSum call needs to include the InvoiceNumber. Not
knowing your filed names, I can't get it right, but it should look something
like this:

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", "[PaymentDate] <= #" &[paymentdate] & "# AND
InvoiceID = " & Me!InvoiceID)

As you have it, all the payments in the payments table are being deducted
from the current invoice. Also, you may want to try an approach that only
deducts the current payment. Because when you run this a second time, it
will deduct the preexisting payment amounts again. Lastly, it's always best
to calculate values that rely on other data at the time you need to report
them. This way you don't have to worry about all the implications of
updates like this. Even so, many accounting databases store the calculated
open amount on invoices for faster reporting.

HTH,
Josh

Brook said:
Good Day All,

I have an invoice form that I use for orders and within that invoice form,
I have a invoicepayments subform that is set up to accept multiple payments,
assign a payment number, accept a payment amount, and deduct the
Click to expand...
amount
from
the invoice total and therefore keep a running total.

I have two types of invoices, consignment (CON-0001, CON-0002, etc) and
Sold (SLD-0001, SLD-0002, etc).

What I need is to have the payments running total "reset" or calculated
for each invoice number, right now, if I enter a payment on invoice CON-0001
then go to invoice SLD-0001, the CON-0001 payment is deducted from the
invoice SLD-0001 payment total.


Thanks in advance....

Brook

Here is the code that I use on my payments form.

Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function
Click to expand...
Click to expand...
Click to expand...
 
B

Brook

I changed the code and am now getting an error in the BalanceDue Column, I
don't know at this point. Is there something more that I can send you for
help?

Brook

Joshua A. Booker said:
Brook,

It looks like you have an extra > in your code:
Try this:
"[PaymentDate] <= #" & [paymentdate] & "# AND Invoicenumber = " &
Me!invoicenumber

HTH,
Josh

Brook said:
Joshua,

When I added the code to include the invoiceid, I am getting an error. I
will point out where I'm getting the error, but first thing... When I open my
payment entry subform, in the Balance Due column that is calculated by the
"=MyNewBalance" I get the following before I enter any information: "#Error"

Begin Code:
Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "# AND> Invoicenumber = " &
Me!invoicenumber)
End If
End Function
End Code:

Begin Error Debug:

Private Sub paymentamount_AfterUpdate()
Me.Recalc
Me.InvTotal = Me.invoicetotal
Me.AmtDue = Me.Balance_Due ---- this is where the error is happening
End Sub
End Error Debug:


Do you have any suggestions?

Brook
Joshua A. Booker said:
Brook,

The criteria in your DSum call needs to include the InvoiceNumber. Not
knowing your filed names, I can't get it right, but it should look something
like this:

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", "[PaymentDate] <= #" &[paymentdate] & "# AND
InvoiceID = " & Me!InvoiceID)

As you have it, all the payments in the payments table are being deducted
from the current invoice. Also, you may want to try an approach that only
deducts the current payment. Because when you run this a second time, it
will deduct the preexisting payment amounts again. Lastly, it's always best
to calculate values that rely on other data at the time you need to report
them. This way you don't have to worry about all the implications of
updates like this. Even so, many accounting databases store the calculated
open amount on invoices for faster reporting.

HTH,
Josh

Good Day All,

I have an invoice form that I use for orders and within that invoice
form,
I have a invoicepayments subform that is set up to accept multiple
payments,
assign a payment number, accept a payment amount, and deduct the amount
from
the invoice total and therefore keep a running total.

I have two types of invoices, consignment (CON-0001, CON-0002, etc) and
Sold (SLD-0001, SLD-0002, etc).

What I need is to have the payments running total "reset" or calculated
for each invoice number, right now, if I enter a payment on invoice
CON-0001
then go to invoice SLD-0001, the CON-0001 payment is deducted from the
invoice SLD-0001 payment total.


Thanks in advance....

Brook

Here is the code that I use on my payments form.

Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function
Click to expand...
Click to expand...
Click to expand...
 
J

Joshua A. Booker

Brook,

What about having an apply button that reduces the balance and marks the
payment as being applied. That way, a payment is certain to be appied to
the correct invoice and only once.

To do this, add a yes/no field to the payments table called applied. Then
add a button labeled apply to the payment subform. In the onclick event of
the button, if the payment hasn't been applied, have your code reduce the
balance of the invoice by the amount of the payment and flag the payment as
being applied.

HTH,
Josh

Brook said:
I changed the code and am now getting an error in the BalanceDue Column, I
don't know at this point. Is there something more that I can send you for
help?

Brook

Joshua A. Booker said:
Brook,

It looks like you have an extra > in your code:
Try this:
"[PaymentDate] <= #" & [paymentdate] & "# AND Invoicenumber = " &
Me!invoicenumber

HTH,
Josh

Brook said:
Joshua,

When I added the code to include the invoiceid, I am getting an error. I
will point out where I'm getting the error, but first thing... When I
Click to expand...
open
my
payment entry subform, in the Balance Due column that is calculated by the
"=MyNewBalance" I get the following before I enter any information: "#Error"

Begin Code:
Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "# AND> Invoicenumber
Click to expand...
= "
&
Me!invoicenumber)
End If
End Function
End Code:

Begin Error Debug:

Private Sub paymentamount_AfterUpdate()
Me.Recalc
Me.InvTotal = Me.invoicetotal
Me.AmtDue = Me.Balance_Due ---- this is where the error is happening
End Sub
End Error Debug:


Do you have any suggestions?

Brook
:

Brook,

The criteria in your DSum call needs to include the InvoiceNumber. Not
knowing your filed names, I can't get it right, but it should look something
like this:

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", "[PaymentDate] <= #" &[paymentdate] & "# AND
InvoiceID = " & Me!InvoiceID)

As you have it, all the payments in the payments table are being deducted
from the current invoice. Also, you may want to try an approach
Click to expand...
that
only
deducts the current payment. Because when you run this a second
Click to expand...
time,
it
will deduct the preexisting payment amounts again. Lastly, it's
Click to expand...
always
best
to calculate values that rely on other data at the time you need to report
them. This way you don't have to worry about all the implications of
updates like this. Even so, many accounting databases store the calculated
open amount on invoices for faster reporting.

HTH,
Josh

Good Day All,

I have an invoice form that I use for orders and within that invoice
form,
I have a invoicepayments subform that is set up to accept multiple
payments,
assign a payment number, accept a payment amount, and deduct the amount
from
the invoice total and therefore keep a running total.

I have two types of invoices, consignment (CON-0001, CON-0002,
Click to expand...
etc)
and
Sold (SLD-0001, SLD-0002, etc).

What I need is to have the payments running total "reset" or calculated
for each invoice number, right now, if I enter a payment on invoice
CON-0001
then go to invoice SLD-0001, the CON-0001 payment is deducted from the
invoice SLD-0001 payment total.


Thanks in advance....

Brook

Here is the code that I use on my payments form.

Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate], 0) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function
Click to expand...
Click to expand...
Click to expand...
 

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

Invoice Payments subForm - help with running balance 0
help with "BalanceDue" on Invoicepayment form? 2
DMAX 5
help with a subform calculation... 0
SQL Query Double Counts on Multi-Table Query 3
Invoice Amt. - Payments = Balance 2
Complex problem with subform? 0

Top