MS Access中rst.MoveNext无法切换至下一条记录的问题求助

我写了以下VBA代码，用于遍历支付表并将支付金额抵扣至可用余额，但始终无法正常切换至下一条支付记录。偶尔能完成正常循环，多数情况下会重复处理同一条支付记录，最终因支付已被使用而报错。

Public Sub applyPaymentsFunction(ByVal PaymentDate As String)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim qry As String
Dim printedList As DAO.Recordset
Set dbs = CurrentDb

Set printedList = dbs.OpenRecordset("000AppliedPayments")
qry = "SELECT * FROM ReceivePayment WHERE (UnusedPayment > 0) AND TxnDate = #" + PaymentDate + "#"

Set rst = CurrentDb.OpenRecordset(qry)
If rst.RecordCount > 0 Then
rst.MoveFirst
    Do
        Debug.Print (rst("TxnID"))
        
        printedList.AddNew
        printedList!accountNumber = rst("CustomerRefFullName")
        printedList!Date = rst("TxnDate")
        printedList!Payment = rst("TotalAmount")
        printedList!AmountLeft = rst("UnusedPayment")
        printedList.Update
    
    Call applyPaymentsFunction2(rst("CustomerRefListID"), rst("TxnId"), rst("UnusedPayment"))

    rst.MoveNext
    Loop Until rst.EOF = True
rst.Close
End If
End Sub

Public Sub applyPaymentsFunction2(ByVal CustomerRefListID As String, ByVal PaymentTxnID As String, ByVal thisPayment As Integer)

Dim dbs As Database
Dim custrst As DAO.Recordset
Dim getCharges As String
Dim availPayment As Integer
Set dbs = CurrentDb

        availPayment = thisPayment
        getCharges = "SELECT TxnDate, TxnID, BalanceRemaining, Desc AS thisThing FROM Charge WHERE (CustomerRefListID = '" + CustomerRefListID + "'"
        getInvoices = "SELECT TxnDate, TxnID, BalanceRemaining, InvoiceLineDesc as thisThing FROM InvoiceLine WHERE (CustomerRefListID = '" + CustomerRefListID + "'"
        
        getCust = "SELECT * FROM Customer Where ParentRefListID = '" + CustomerRefListID + "'"
        Set custrst = CurrentDb.OpenRecordset(getCust)
        If custrst.RecordCount > 0 Then
            custrst.MoveFirst
                Do
                getCharges = getCharges + " OR CustomerRefListID = '" + custrst("ListId") + "'"
                getInvoices = getInvoices + " OR CustomerRefListID = '" + custrst("ListId") + "'"
                custrst.MoveNext
                Loop Until custrst.EOF = True
            custrst.Close
        End If
            
        getCharges = getCharges + ") AND (BalanceRemaining > 0)"
        getInvoices = getInvoices + ") AND (BalanceRemaining > 0)"
        joinedChargeInvoice = getCharges + "UNION ALL " + getInvoices + "ORDER BY TxnDate ASC"
        Set chargesrst = CurrentDb.OpenRecordset(joinedChargeInvoice)

        If chargesrst.RecordCount > 0 Then
        chargesrst.MoveFirst
            Do
                If availPayment = 0 Then
                    Exit Sub
                    
                ElseIf availPayment = chargesrst("BalanceRemaining") Then
                    Debug.Print ("INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('" + PaymentTxnID + "', '" + chargesrst("TxnID") + "', " + Format(availPayment, "0.00") + " )")
                    DoCmd.RunSQL ("INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('" + PaymentTxnID + "', '" + chargesrst("TxnID") + "', " + Format(availPayment, "0.00") + " )")
                    availPayment = 0
                    Exit Sub
                    
                ElseIf availPayment < chargesrst("BalanceRemaining") Then
                    Debug.Print ("INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('" + PaymentTxnID + "', '" + chargesrst("TxnID") + "', " + Format(availPayment, "0.00") + " )")
                    DoCmd.RunSQL ("INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('" + PaymentTxnID + "', '" + chargesrst("TxnID") + "', " + Format(availPayment, "0.00") + " )")
                    availPayment = 0
                    Exit Sub
                
                ElseIf availPayment > chargesrst("BalanceRemaining") Then
                    Debug.Print ("INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('" + PaymentTxnID + "', '" + chargesrst("TxnID") + "', " + Format(chargesrst("BalanceRemaining"), "0.00") + " )")
                    DoCmd.RunSQL ("INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('" + PaymentTxnID + "', '" + chargesrst("TxnID") + "', " + Format(chargesrst("BalanceRemaining"), "0.00") + " )")
                    availPayment = availPayment - chargesrst("BalanceRemaining")
                        
                End If
                    
            chargesrst.MoveNext
            Loop Until chargesrst.EOF = True Or availPayment = 0
        chargesrst.Close
        End If

End Sub

我已经尝试过以下方法，但问题仍未解决：

• 使用rst.MoveNext和rst.Move(1)
• 保留或移除Exit Sub语句
• 将代码合并为单个查询或拆分为两个子过程

内容的提问来源于stack exchange，提问作者Kode