 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / August 2005Invoice Payments Subform / Reset Balance Due?
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 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, > [quoted text clipped - 31 lines] > End If > End Function 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 > Brook, > [quoted text clipped - 57 lines] > > End If > > End Function 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 > Brook, > [quoted text clipped - 57 lines] > > End If > > End Function Brook, It looks like you have an extra > in your code: Try this: "[PaymentDate] <= #" & [paymentdate] & "# AND Invoicenumber = " & Me!invoicenumber HTH, Josh > Joshua, > [quoted text clipped - 95 lines] > > > End If > > > End Function 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 > Brook, > [quoted text clipped - 118 lines] > > > > End If > > > > End Function 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 > 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 [quoted text clipped - 124 lines] > > > > > End If > > > > > End Function |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.