Recordsets blowing my mind

[Gina Maylone]

Hello all,



I am working on an amortization schedule in which I am attempting to create a function that will apply extra payments to any past due amounts before they are applied to the principal. I have muddled through this so far (it's ugly, so please no criticisms, I'm TRYING):

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
 
strsql = "SELECT ScheduleT.PaymentNumber, ScheduleT.PaidDate, ScheduleT.LoanID, ScheduleT.RegularPayment, ScheduleT.DueDate, ScheduleT.Pay, ScheduleT.AmountPaid, ScheduleT.AmountDue " & vbCrLf & _
"FROM ScheduleT " & vbCrLf & _
"WHERE (((ScheduleT.PaymentNumber) Is Not Null) AND ((ScheduleT.PaidDate) Is Null) AND ('(ScheduleT.LoanID)=[Forms]![PaymentForm]![LoanID]') AND ((ScheduleT.RegularPayment)=0) AND ((ScheduleT.DueDate)<=Date())) " & vbCrLf & _
"ORDER BY ScheduleT.DueDate;"
Recordset.Filter = "loanid = me.loanid and rs.paymentnumber<>null"
Set db = CurrentDb
Set rs = db.OpenRecordset(strsql)
 
rs.MoveFirst
 
Do While ((LoanID) = [Forms]![paymentform]![LoanID]) And ((RegularPayment) = 0) And ((DueDate) <= Date)and([Forms]![paymentform]![amountleft]>0)
 
Me.PaidDate = Forms!paymentform!datepaid
Me.RegularPayment = Forms!paymentform!PaymentAmount
Me.AmountPaid = Forms!paymentform!PaymentAmount
Me.Requery
addtopaymenttable
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
 
End Sub

This will apply a payment to ALL past due amounts, regardless of the actual amount paid. IE. Say someone is behind 4 months with a total of $348.96 due to date, and they pay $300, my function posts a payment to every month, totaling 348.96 instead of just until the $300 is used up. I just can't get my head around it, to make it stop when $300 is gone, or how to apply the extra if they pay $400. This is my first real attempt at using recordsets, please be gentle.

TIA! Have a great day!

[Micron]

Difficult to visualize what's going on without being able to monitor it. Suggest you step through the code and check what happens to the parameters for a small recordset. Maybe after the Do While add
Debug.Print "RegPayment: " & [RegularPayment] & "; " DueDte: " & [DueDate] & "AmtLeft: " & [Forms]![paymentform]![amountleft]
and watch the immediate window while stepping through the loop. Maybe a condition you don't expect causes a termination of the loop, and you have 4 of them, all of which have to be satisfied in order to continue the loop. If all looks good there, I can only guess about the fact that I don't see a means of handling if the balance < 0.

Suggestions (not criticism):
- You don't need vbcrlf in a sql statement. I dislike line continuation characters for this, so this is what I do:

strsql = "SELECT ScheduleT.PaymentNumber, ScheduleT.PaidDate, ScheduleT.LoanID, ScheduleT.RegularPayment "
strsql = strsql & "ScheduleT.DueDate, ScheduleT.Pay, ScheduleT.AmountPaid, ScheduleT.AmountDue " etc. etc.

Makes it easier to read. I always end on a space to be consistent.
- Why apply a filter to a query recordset instead of just having the query do the filtering?
- you should terminate all objects (db) not just the rs.
- If you wrote most of that yourself, you did really well.

[Gina Maylone]

Thanks Micron! The extra filter is because before I put that in there, the payments were being applied to everyone's account. AND the sql kept erroring Too few parameters expected 1. UGH! And thanks for the tip on continuing lines, I didn't like that either. I will try additional debug.printing and see what happens. Thanks again!

[Micron]

Thanks Micron! The extra filter is because before I put that in there, the payments were being applied to everyone's account. AND the sql kept erroring Too few parameters expected 1. UGH! And thanks for the tip on continuing lines, I didn't like that either. I will try additional debug.printing and see what happens. Thanks again!

You need to pass parameters from vba to a parametized query if you're not invoking one of the simpler methods such as Docmd.OpenQuery. Not real hard to learn, although there is probably more than one approach. I declare param(n) as DAO.Parameter and qdf as DAO.Querydef (n is for number 1 or 2 or...). I assign the sql statement to the querydef object (query definition object) and then With qdf each parameter gets a reference as needed: param1 = Forms!frmSales.txtSaleDate. Then you open the recordset.
Post back when you've had a chance to follow the loop execution.

[Gina Maylone]

HAHILLS (2).zip

Ok, I'm getting more confused by the minute. I've stripped the db of information and most of the tables and forms to make it small enough. I am working with the Payment Form (button on main menu) and Schedulet, Paymentt, Loant. The whole thing is Dam Assessment (appropriately named!). "All" I want to do is determine if someone is past due on their monthly payments, and if they are, apply any extra payment to the past due amounts first, then if there any left over, apply it to current charges and/or the principal. I don't know how to tell Access to look at the first past due amount and pay it, then look at the second past due amount and pay it (if there's enough money) etc. I don't know when I should requery. In my fantasy world I would like this to all happen on the form LoanF (Ind. Dam assessments), instead of on a separate payment form if anyone has any great ideas for that!

Again, thank you in advance for any input!

[ssanfu]

Can you post a BE to go with the FE???

[Gina Maylone]

I could've sworn I posted the one with local tables - but like I said my mind is blown. Posting it again. Good to see you Steve! :-)HAHILLS (2).zip

[ssanfu]

Hi Gina

I can open the forms now.

Question: For loan id 7, there is an amount due of $344.61. If there is a $300 payment made on 4/1/2016, that will cover Jan and Feb with $70.26 left over.
Where should the $70.26 go?
Apply it to Mar, but not enter a date paid? Or .....??

[ssanfu]

I made changes (lots) to the code for Apply Payments code.

In the strSQL lines, you had

FROM ScheduleT " & vbCrLf & _

The vbCrLf shouldn't be there.

You almost had the loanID limiter in the SQL, but you made it a string so it didn't filter the record set. This is fixed.

So the code adds the amount due per payment, and if there is a remaining amt that is not enough for a regular payment, it adds the amt as additional principal.

If the amt due is $344.26, and the payment maid is $400, the remaining amount after paying all past due is added to the last date as additional principal.

(You're going to hate me..... I added Option Explicit to all of the modules)


I was using Loan ID 7 as my test case.

[Gina Maylone]

MIND BLOWN AGAIN! WOW....You never cease to amaze me Steve. THANK YOU!