Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Wend Withou While Error

Posted on 2013-12-08
2
Medium Priority
?
235 Views
Last Modified: 2013-12-08
I've stared at and checked this code over too many times.  I'm getting the 'Wend without WHile' error when I try to run it but it looks OK to me.  

I do have nested While a payment file making several passes through a detail file but that should be OK.

The first 'Wend' is the one being highlighted.  This one:

                End If ' payment record work balance = 0
                '
                rsFeesIn.MoveNext
                '
            Wend

Here is the code:

Public Function getFeeBalance(passedWriteTmpPmntRecs As Long) As Double
                         
'
getFeeBalance = 0
'
' This routine matches fee payments against fees with a balance due.  Prior to this routine
'
' Property specific Fee payments have been loaded into into 'tblzTmpWk_tblPayments_Fees_Sub_Local'
'
' Property specific Fee records have been loaded into 'tblzTmpWk_tblTaxRecs_Fees_Local'
'
' Prior to processing the record are sorted into order by FeePayment Priority (Court Costs are always last) and Fee Date, oldest first
'
' The fee payments are applied in 3 passes.
'  First Pass matches fee payments against fee records based on GRB Fee Desc, which is the long more specific description.
'  Second Pass matches fee payments against fee records based on COP Fee Desc, which is the short less specific.
'  Third pass just applies any remaining payment balances to the fees in the order they ares sorted until all payments havbe been applied.
'
' Lastly, if we are doing this for payment distribution purposes records are written to the table 'tblzTmpWk_TaxRecFees_BalDue_Local' at the end
'
'
' First every payment record must set the 'PaymentWorkingBalance' to the FeePaymentAmount since it always defaults
' to zero when the table is loaded
'
updateString = "Update tblzTmpWk_tblPayments_Fees_Sub_Local " & _
                    " SET [PaymentWorkingBalance] = [FeePayment] "
DoCmd.SetWarnings False
DoCmd.RunSQL updateString
'
' Also
'
' First every fee record must set the 'FeeWorkingBalance' to the CurrBalanceAmt since it always defaults
' to zero when the table is loaded
'
updateString = "Update tblzTmpWk_tblTaxRecs_Fees_Local " & _
                    " SET [FeeWorkingBalance] = [CurrBalanceAmt] "
DoCmd.SetWarnings False
DoCmd.RunSQL updateString
'
'
'
Dim rsFeesIn As ADODB.Recordset
Set rsFeesIn = New ADODB.Recordset
rsFeesIn.Open "tblzTmpWk_tblTaxRecs_Fees_Local", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
Dim rsPayIn As ADODB.Recordset
Set rsPayIn = New ADODB.Recordset
rsPayIn.Open "tblzTmpWk_tblPayments_Fees_Sub_Local", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
Dim matched As Boolean
Dim loopCnt As Long
'
Dim wkPayBal As Double
Dim wkFeeBal As Double
'Dim wkPayOrigAmt As Double
'Dim wkFeeOrigAmt As Double
'
Dim wkAmountPaid As Double
'
For loopCnt = 1 To 3
        '
        ' Position fee recs to first rec
        '
        If rsFeesIn.EOF Then
        Else
            If rsFeesIn.RecordCount > 0 Then
                rsFeesIn.MoveFirst
            End If
        End If
        '
        ' Position fee payments to first rec
        '
        If rsPayIn.EOF Then
        Else
            If rsPayIn.RecordCount > 0 Then
                rsPayIn.MoveFirst
            End If
        End If
        '
        ' Payments will be the driver
        '
        While Not rsPayIn.EOF   ' each payment record is matched against all of the fees before the next payment is read
        '
        ' First time thru for every payment must move the payment amount to
            '
            ' set payment balance for loop
            '
            wkPayBal = rsPayIn!PaymentWorkingBalance
            '
            ' Have payment record, match it against fee records
            '
            While Not rsFeesIn.EOF      ' lopps thru the fee records looking to match any payments
                '
                ' The fee payments are applied in 3 passes.
                '  First Pass matches fee payments against fee records based on GRB Fee Desc, which is the long more specific description.
                '  Second Pass matches fee payments against fee records based on COP Fee Desc, which is the short less specific.
                '  Third pass just applies any remaining payment balances to the fees in the order they ares sorted until all payments havbe been applied.
                '
                matched = False
                '
                ' make sure there is a balance on the payment
                '
                If rsPayIn!PaymentWorkingBalance > 0 Then
                    '
                    ' If there is a balance see if it matches this tax rec.
                    '
                    If loopCnt = 1 Then
                        '
                        If Nz(rsPayIn!GRBFeeType, "Pay") = Nz(rsFeesIn!GRBFeeType, "Fee") Then
                            matched = True
                        End If
                        '
                    ElseIf loopCnt = 2 Then
                        '
                        If Nz(rsPayIn!COPFeeType, "Pay") = Nz(rsFeesIn!COPFeeType, "Fee") Then
                            matched = True
                        End If
                        '
                    Else
                        matched = True
                    End If
                    '
                    If matched = True Then    ' apply the payment to the fee record.
                        '
                        wkAmountPaid = 0
                        '
                        wkFeeBal = rsFeesIn!FeeWorkingBalance
                        'wkFeeOrigAmt = rsFeesIn!CurrBalanceAmt
                        '
                        If wkFeeBal > 0 Then
                            '
                            If wkPayBal <= wkFeeBal Then
                                wkAmountPaid = wkPayBal
                            Else
                                wkAmountPaid = wkFeeBal
                            End If
                            '
                            wkFeeBal = wkFeeBal - wkAmountPaid
                            wkPayBal = wkPayBal - wkAmountPaid
                            '
                            ' update the working balance on the fee record
                            '
                            rsFeesIn!FeeWorkingBalance = wkFeeBal
                            rsFeesIn.Update
                            '
                            ' update the working balance on the fee record
                            '
                            '
                        End If ' the fee record already has a blance of zero
                        '
                End If ' payment record work balance = 0
                '
                rsFeesIn.MoveNext
                '
            Wend
            '
            ' Any payment appllications have been done and the final balance is in this variable
            ' update the balance on the pay record for any subseqent loops.
            '
            rsPayIn!PaymentWorkingBalance = wkPayBal
            rsPayIn.Update
            '
            rsPayIn.MoveNext
        Wend
        '
        ' reset for next cyear
        '
        rsPayIn.MoveFirst
        '
    End If
'
Next loopCnt
'
rsFeesIn.Close
Set rsFeesIn = Nothing
'
rsPayIn.Close
Set rsPayIn = Nothing
'
On Error Resume Next
'
getFeeBalance = DSum("[FeeWorkingBalance]", "rsFeesIn")
'
On Error GoTo 0
'
End Function

Open in new window

0
Comment
Question by:mlcktmguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 400 total points
ID: 39705219
Those errors can be a little deceiving.  In this case, it looks like the issue was with your IF-then blocks.  See my inline notes.  This compiles, but you'll have to determine if it gives you the results you need:

Public Function getFeeBalance(passedWriteTmpPmntRecs As Long) As Double
                         
'
getFeeBalance = 0
'
' This routine matches fee payments against fees with a balance due.  Prior to this routine
'
' Property specific Fee payments have been loaded into into 'tblzTmpWk_tblPayments_Fees_Sub_Local'
'
' Property specific Fee records have been loaded into 'tblzTmpWk_tblTaxRecs_Fees_Local'
'
' Prior to processing the record are sorted into order by FeePayment Priority (Court Costs are always last) and Fee Date, oldest first
'
' The fee payments are applied in 3 passes.
'  First Pass matches fee payments against fee records based on GRB Fee Desc, which is the long more specific description.
'  Second Pass matches fee payments against fee records based on COP Fee Desc, which is the short less specific.
'  Third pass just applies any remaining payment balances to the fees in the order they ares sorted until all payments havbe been applied.
'
' Lastly, if we are doing this for payment distribution purposes records are written to the table 'tblzTmpWk_TaxRecFees_BalDue_Local' at the end
'
'
' First every payment record must set the 'PaymentWorkingBalance' to the FeePaymentAmount since it always defaults
' to zero when the table is loaded
'
updateString = "Update tblzTmpWk_tblPayments_Fees_Sub_Local " & _
                    " SET [PaymentWorkingBalance] = [FeePayment] "
DoCmd.SetWarnings False
DoCmd.RunSQL updateString
'
' Also
'
' First every fee record must set the 'FeeWorkingBalance' to the CurrBalanceAmt since it always defaults
' to zero when the table is loaded
'
updateString = "Update tblzTmpWk_tblTaxRecs_Fees_Local " & _
                    " SET [FeeWorkingBalance] = [CurrBalanceAmt] "
DoCmd.SetWarnings False
DoCmd.RunSQL updateString
'
'
'
Dim rsFeesIn As ADODB.Recordset
Set rsFeesIn = New ADODB.Recordset
rsFeesIn.Open "tblzTmpWk_tblTaxRecs_Fees_Local", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
Dim rsPayIn As ADODB.Recordset
Set rsPayIn = New ADODB.Recordset
rsPayIn.Open "tblzTmpWk_tblPayments_Fees_Sub_Local", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
Dim matched As Boolean
Dim loopCnt As Long
'
Dim wkPayBal As Double
Dim wkFeeBal As Double
'Dim wkPayOrigAmt As Double
'Dim wkFeeOrigAmt As Double
'
Dim wkAmountPaid As Double
'
For loopCnt = 1 To 3
        '
        ' Position fee recs to first rec
        '
        If rsFeesIn.EOF Then
        Else
            If rsFeesIn.RecordCount > 0 Then
                rsFeesIn.MoveFirst
            End If
        End If
        '
        ' Position fee payments to first rec
        '
        If rsPayIn.EOF Then
        Else
            If rsPayIn.RecordCount > 0 Then
                rsPayIn.MoveFirst
            End If
        End If
        '
        ' Payments will be the driver
        '
        While Not rsPayIn.EOF   ' each payment record is matched against all of the fees before the next payment is read
        '
        ' First time thru for every payment must move the payment amount to
            '
            ' set payment balance for loop
            '
            wkPayBal = rsPayIn!PaymentWorkingBalance
            '
            ' Have payment record, match it against fee records
            '
            While Not rsFeesIn.EOF      ' lopps thru the fee records looking to match any payments
                '
                ' The fee payments are applied in 3 passes.
                '  First Pass matches fee payments against fee records based on GRB Fee Desc, which is the long more specific description.
                '  Second Pass matches fee payments against fee records based on COP Fee Desc, which is the short less specific.
                '  Third pass just applies any remaining payment balances to the fees in the order they ares sorted until all payments havbe been applied.
                '
                matched = False
                '
                ' make sure there is a balance on the payment
                '
                If rsPayIn!PaymentWorkingBalance > 0 Then
                    '
                    ' If there is a balance see if it matches this tax rec.
                    '
                    If loopCnt = 1 Then
                        '
                        If Nz(rsPayIn!GRBFeeType, "Pay") = Nz(rsFeesIn!GRBFeeType, "Fee") Then
                            matched = True
                        End If
                        '
                    ElseIf loopCnt = 2 Then
                        '
                        If Nz(rsPayIn!COPFeeType, "Pay") = Nz(rsFeesIn!COPFeeType, "Fee") Then
                            matched = True
                        End If
                        '
                    Else
                        matched = True
                    End If
                    '
                    If matched = True Then    ' apply the payment to the fee record.
                        '
                        wkAmountPaid = 0
                        '
                        wkFeeBal = rsFeesIn!FeeWorkingBalance
                        'wkFeeOrigAmt = rsFeesIn!CurrBalanceAmt
                        '
                        If wkFeeBal > 0 Then
                            '
                            If wkPayBal <= wkFeeBal Then
                                wkAmountPaid = wkPayBal
                            Else
                                wkAmountPaid = wkFeeBal
                            End If
                            '
                            wkFeeBal = wkFeeBal - wkAmountPaid
                            wkPayBal = wkPayBal - wkAmountPaid
                            '
                            ' update the working balance on the fee record
                            '
                            rsFeesIn!FeeWorkingBalance = wkFeeBal
                            rsFeesIn.Update
                            '
                            ' update the working balance on the fee record
                            '
                            '
                        End If ' the fee record already has a blance of zero

                        '
                    End If ' payment record work balance = 0
                '
                End If  '************* The if at the end should have been HERE
                rsFeesIn.MoveNext
                '
            Wend
            '
            ' Any payment appllications have been done and the final balance is in this variable
            ' update the balance on the pay record for any subseqent loops.
            '
            rsPayIn!PaymentWorkingBalance = wkPayBal
            rsPayIn.Update
            '
            rsPayIn.MoveNext
        Wend
        '
        ' reset for next cyear
        '
        rsPayIn.MoveFirst
        '
    'End If   ************** This should have been earlier, where noted
'
Next loopCnt
'
rsFeesIn.Close
Set rsFeesIn = Nothing
'
rsPayIn.Close
Set rsPayIn = Nothing
'
On Error Resume Next
'
getFeeBalance = DSum("[FeeWorkingBalance]", "rsFeesIn")
'
On Error GoTo 0
'
End Function

Open in new window

0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 39705249
Thank you.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question