Hello all, this question is about a database which I have posted many questions on this forum. It is running nearly perfectly, however, this code which posts payments is over writing the previous check number. For example if a payment was made yesterday and the check number was 1644, and another payment is made today with check # 1647, 1644 will become 1647 on all records. I cannot tell where, or how to stop it from doing that. Any help is always greatly appreciated!
Code:
Private Sub monthlyCheckNum_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
Dim C As String
' Dim RC As Long
'get values from sub form
dp = Me.MonthlyDatePaid
u = Forms![memberpayments]![dbuser]
CN = Forms![memberpayments]![MonthlyPayments].Form![monthlyCheckNum]
'debug.print u
mbrid = Forms![memberpayments]![MemberID_PK]
P = Me.Monthlypayment
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;"
Set rst = CurrentDb.OpenRecordset(strsql)
'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
'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 = Forms![memberpayments]![dbuser]
rst!Comments = dp & " - " & CN
rst.Update
BL = 0
ElseIf BL > BD Then 'greater than
rst.Edit
rst!CRDATE = dp
rst!CRAmount = BD + CR
rst!CheckNumber = CN
rst!EnteredBy = Forms![memberpayments]![dbuser]
rst!Comments = dp & " - " & CN
rst.Update
BL = BL - BD
ElseIf BL = BD Then 'equal to
rst.Edit
rst!CRDATE = dp
rst!CRAmount = BD + CR
rst!CheckNumber = CN
rst!EnteredBy = Forms![memberpayments]![dbuser]
rst!Comments = dp & " - " & CN
rst.Update
BL = 0
End If
rst.MoveNext
Loop
'---- extra left over after making payments ----
'save remaining amt to additional principal in last records of record set
If BL > 0 Then
rst.MoveLast
rst.Edit
rst!CRAmount = rst!CRAmount + BL
rst!CheckNumber = CN
rst!Comments = dp & " - " & CN
rst!EnteredBy = Forms![memberpayments]![dbuser]
rst.Update
BL = 0
End If
'add payment to payments table
strsql = "INSERT INTO AsmtPayments ( MemberID_FK, [asmttype], PaymentAmount, PaymentDate, CheckNumber, enteredby )"
strsql = strsql & " VALUES ( " & mbrid & ", '" & AsmtType & "'," & P & ", #" & dp & "#, '" & CN & "', '" & u & "');"
DoCmd.OpenQuery "UpdateCRDATEStoNull"
DoCmd.OpenQuery "balfwd"
DoCmd.OpenQuery "cleanupoverpayments"
DoCmd.OpenQuery "DeleteZeroLines"
Forms![memberpayments]![AsmtPayments].Form.Requery
Forms![memberpayments]![AsmtPayments].Form.Refresh
Me.Refresh
CurrentDb.Execute strsql, dbFailOnError
End If
rst.Close
Set rst = Nothing
Forms![memberpayments]![MonthlyPayments].Form.Requery
Forms![memberpayments]![AsmtPayments].Form.Requery
'MsgBox "End of process"
End Sub