Hello everyone and Happy Friday!
I have a function that posts payments to accounts (oldest to newest). My users also need to post payments when there is a zero balance and I need it to show the account balance as -100.00 or whatever. Here is the whole code:
Code:
Private Sub mnthlyCheckNum_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String
Dim CN As String
Dim BD As Variant
Dim BL As Variant
Dim CR As Currency
Dim P As Currency
Dim mbrid As Long
Dim dp As Date
Dim u As String
'get values from sub form
dp = Me.DatePaid
u = Forms![memberpayments]![dbuser]
CN = Me.mnthlyCheckNum
mbrid = Forms![memberpayments]![MemberID_PK]
P = Me.payment
BL = P '<<-- payment amount
'open recordset
strsql = "SELECT MonthlyQueryforPayments.MemberID_FK, MonthlyQueryforPayments.DBAmount, MonthlyQueryforPayments.CRAmount,"
strsql = strsql & " MonthlyQueryforPayments.dbdate, MonthlyQueryforPayments.AsmtType, MonthlyQueryforPayments.Details,"
strsql = strsql & " MonthlyQueryforPayments.LotNumber, MonthlyQueryforPayments.EnteredBy, MonthlyQueryforPayments.crdate,"
strsql = strsql & " MonthlyQueryforPayments.CheckNumber, MonthlyQueryforPayments.comments, [dbamount]-[cramount] AS baldue"
strsql = strsql & " FROM MonthlyQueryforPayments"
strsql = strsql & " WHERE MonthlyQueryforPayments.MemberID_FK = " & [Forms]![memberpayments]![MemberID_PK]
'strsql = strsql & " And ([DBAmount] - [CRAmount]) > 0"
strsql = strsql & " ORDER BY MonthlyQueryforPayments.dbdate;"
'check for records in recordset
If Not rst.BOF And Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
'get values from each record in recordset
BD = rst!baldue
CR = rst!CRAmount
Debug.Print "Bal Due " & BD
Debug.Print "BAlleft1 " & BL
'edit or add a payment to the record set
If BL < BD Then 'less than
rst.Edit
rst!CRDATE = dp
rst!CRAmount = BL + CR
rst!CheckNumber = CN
rst!EnteredBy = u
rst!Comments = dp & " - " & CN
rst.Update
BL = 0
Debug.Print "Bal Due1 (bl less than bd) " & BD
Debug.Print "BAlleft1 " & BL
ElseIf BL > BD Then 'greater than
rst.Edit
rst!CRDATE = dp
rst!CRAmount = BD + CR
rst!CheckNumber = CN
rst!EnteredBy = u
rst!Comments = dp & " - " & CN
rst.Update
BL = BL - BD
Debug.Print "Bal Due2 (bl greater than bd) " & BD
Debug.Print "BAlleft2 " & BL
ElseIf BL = BD Then 'equal to
rst.Edit
rst!CRDATE = dp
rst!CRAmount = BD + CR
rst!CheckNumber = CN
rst!EnteredBy = u
rst!Comments = dp & " - " & CN
rst.Update
BL = 0
End If
Debug.Print "Bal Due3 (bl equals bd) " & BD
Debug.Print "BAlleft3 " & BL
rst.MoveNext
Loop
'---- extra left over after making payments ----
'save remaining amt to additional principal in last records of record set
If BD <= 0 And BL > 0 Then 'no balance due, yet payment was made, creating a credit
rst.AddNew
rst!CRDATE = dp
rst!AsmtType = "Monthly"
rst!CRAmount = BL
rst!Comments = dp & " - " & CN
rst!EnteredBy = u
rst!MemberID_FK = Me.MemberID_FK
rst.Update
BL = 0
End If
Debug.Print "Bal Due4 (bd is less than or equal to bl and bl is greater than zero) " & BD
Debug.Print "BAlleft4 " & BL
If BL > 0 Then
rst.MoveLast
rst.Edit
rst!CRAmount = BL
rst!Comments = dp & " - " & CN
rst!EnteredBy = u
rst.Update
BL = 0
Debug.Print "Bal Due5 (bl greater than zero) " & BD
Debug.Print "BAlleft5 " & BL
ElseIf BD = 0 Then
rst.MoveLast
rst.Edit
rst!CRAmount = BL
rst!Comments = dp & " - " & CN
rst!EnteredBy = u
rst.Update
BL = 0
Debug.Print "Bal Due6 (bd = zero) " & BD
Debug.Print "BAlleft6 " & BL
End If
Debug.Print "end of the line"
'add payment to payments table
strsql = "INSERT INTO AsmtPayments ( MemberID_FK, PaymentAmount, PaymentDate, CheckNumber, EnteredBy )"
strsql = strsql & " VALUES ( " & mbrid & ", " & P & ", #" & dp & "#," & CN & ", '" & u & "')"
Forms![memberpayments]![AsmtPayments].Form.Refresh
Me.Refresh
CurrentDb.Execute strsql, dbFailOnError
rst.Close
Set rst = Nothing
DoCmd.OpenQuery "UpdateCRDATEStoNull"
Forms![memberpayments]![AsmtPayments].Form.Requery
End If
End Sub
With any luck I will probably figure this out as soon as I post it. But need another set of eyes. TIA!