Need help with vba for posting payments to zero balance accounts

**Gina Maylone** · 08-10-2018, 06:08 AM

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!

**ranman256** · 08-10-2018, 07:31 AM

There is no need to write code to do this.
you can make a query that does it. (no looping neede)

Q1 to get the clients with zero accts, and Q2 to update the accts in Q1.

**Gina Maylone** · 08-10-2018, 10:57 AM

Originally Posted by ranman256

There is no need to write code to do this.
you can make a query that does it. (no looping neede)

Q1 to get the clients with zero accts, and Q2 to update the accts in Q1.

Thanks RanMan, I needed to post payments latest to newest with various options of amounts (if that makes any sense). A query wasn't going to cut it. I will try the query, running it before the loop starts. Thanks!

**ssanfu** · 08-10-2018, 05:11 PM

Hi Gina.....
Boy, this code looks vaguely familiar...

Originally Posted by Gina Maylone

<snip>
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.
<snip>

Its familiar, but different. I don't have the query "MonthlyQueryforPayments", so I don't know what it is doing (Select query, Totals query), but it looks like if there is a zero balance and a payment is made, a credit (?) record is/should be created.
I see you commented out the criteria for the balance to be GT zero.

After thinking about it for a while, I would rearrange the code a little, but it sure looks like if the balance is 0 and a payment is made, a credit record is created.

Code:

<snip>
        '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

<snip>

Maybe I'm misunderstanding???

**Gina Maylone** · 08-12-2018, 07:23 AM

Originally Posted by ssanfu

Hi Gina.....
Boy, this code looks vaguely familiar...

Its familiar, but different. I don't have the query "MonthlyQueryforPayments", so I don't know what it is doing (Select query, Totals query), but it looks like if there is a zero balance and a payment is made, a credit (?) record is/should be created.
I see you commented out the criteria for the balance to be GT zero.

After thinking about it for a while, I would rearrange the code a little, but it sure looks like if the balance is 0 and a payment is made, a credit record is created.

Code:

<snip>
        '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

<snip>

Maybe I'm misunderstanding???

Hi Steve!

Yes, it SHOULD post the credit but, sadly, it is not, it does post to the payment table, but doesn't create the credit, it doesn't do anything to the members' account. I've moved it to the top, the bottom and in between....totally baffled.

**ssanfu** · 08-13-2018, 05:01 PM

Originally Posted by Gina Maylone

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
   ' < snip >
    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;"

   Set rst = CurrentdB.OpenRecordset(strsql)  '<<--  you are missing this line in the code you posted in Post #1..........  

    '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

< snip >

I can't trace the code because the copy I have is missing the query "MonthlyQueryforPayments". Also, I see that the posted sub is using the after update event of "mnthlyCheckNum", so it must be a combo box/list box or text box and not a button.

Could/would you send me/post a current copy of the dB with a couple of member's records like before?

Need help with vba for posting payments to zero balance accounts

Thread Tools

Need help with vba for posting payments to zero balance accounts

Posting to accounts

Need help with vba for posting payments to zero balance accounts Your Message

Similar Threads

Opening balance worked into running balance.

How To Get Running Balance Of Customer with last balance

General Ledger Query With Opening Balance and Running Balance

how to build query to give daily balance across bank accounts? (to then plot in a graph)

client ledger report that show his last month balance as new month opening balance.

Tags for this Thread

Posting Permissions