Solved

Wend Withou While Error

Posted on 2013-12-08
2
231 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 100 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Datamismatch when doing a VBA Event in Access 6 39
Access Query: Text to Date 10 28
access query - field that substitutes characters 1 21
Access #Deleted data 20 39
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

740 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