When compiling the code I get the error Next without for. (1 Viewer)

[debbiedoobie10]

I need fresh eyes.
When compiling the code I get the error Next without For.

Here is the Code.

Function RevisedTEST_CALCFEES()



Dim MyDb As Database

Dim mytable As Recordset

Dim MYDYNA As Recordset

Dim TOTAL As Long

Dim FM As Form



Dim Ra1 As Variant

Dim Rb1 As Variant

Dim Rc1 As Variant

Dim Rd1 As Variant

Dim Da1 As Variant

Dim Db1 As Variant

Dim Dc1 As Variant

Dim Dd1 As Variant

Dim Ba1 As Variant

Dim Bb1 As Variant

Dim Bc1 As Variant

Dim Bd1 As Variant



Dim daydiff As Integer



''''''''''''''''''''''''''''''''END DECLARATION SECTION '''''''''''''''''''''''''''''''''''''''''

Set FM = Screen.ActiveForm

Set MyDb = DBEngine.Workspaces(0).Databases(0)





Set FM = Forms![refunds Form]





FM.[TOTINV] = 0

FM.[TOTCASHPMT] = 0

FM.[TOTDEPOSIT] = 0

FM.[INVDEPVAR] = 0

FM.[AVGDAYS] = 0

FM.[TOTFEES] = 0

FM.[TOTRESERVE] = 0

FM.[TOTREFUND] = 0



If FM.FormName = "REFUNDS FORM" Then

Da1 = DLookup("[daily rate days]", "daily rate table", "[id number] = forms![refunds form]![id number] and [daily rate num]=1 and [daily rate level]=1")

Db1 = DLookup("[daily rate days]", "daily rate table", "[id number] = forms![refunds form]![id number] and [daily rate num]=2 and [daily rate level]=1")

Dc1 = DLookup("[daily rate days]", "daily rate table", "[id number] = forms![refunds form]![id number] and [daily rate num]=3 and [daily rate level]=1")

Dd1 = DLookup("[daily rate days]", "daily rate table", "[id number] = forms![refunds form]![id number] and [daily rate num]=4 and [daily rate level]=1")

Ra1 = DLookup("[daily rate]", "daily rate table", "[id number] = forms![refunds form]![id number] and [daily rate num]=1 and [daily rate level]=1")

Rb1 = DLookup("[daily rate]", "daily rate table", "[id number] = forms![refunds form]![id number] and [daily rate num]=2 and [daily rate level]=1")

Rc1 = DLookup("[daily rate]", "daily rate table", "[id number] = forms![refunds form]![id number] and [daily rate num]=3 and [daily rate level]=1")

Rd1 = DLookup("[daily rate]", "daily rate table", "[id number] = forms![refunds form]![id number] and [daily rate num]=4 and [daily rate level]=1")

Ba1 = DLookup("[base rate 1]", "client information", "[id number] = forms![refunds form]![id number]")

Bb1 = DLookup("[base rate 2]", "client information", "[id number] = forms![refunds form]![id number]")

Bc1 = DLookup("[base rate 3]", "client information", "[id number] = forms![refunds form]![id number]")

Bd1 = DLookup("[base rate 4]", "client information", "[id number] = forms![refunds form]![id number]")

End If





'''''''''''''''''''''''''''''''' END INITIALIZATION SECTION '''''''''''''''''''''''''''''''''''''''''''''''''''''



Set mytable = MyDb.OpenRecordset("TEMP-REFUND-TABLE")



TOTAL = mytable.RecordCount

If TOTAL <> 0 Then

mytable.MoveFirst



'''''''''''''''''''''''''''''''' BEGIN MAIN LOOP ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''



For Count = 1 To TOTAL



FM.[FACTOR FEES CHARGE] = 0



If FM.[Factor Cash Payment] = 0 Then

If FM.[DATE STAMP] >= FM.[ZeroEffDate1] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate2]) And IsNull(FM.[ZeroAdvRate2])) Then

FM.[FACTOR FEES CHARGE] = FM.[INV AMOUNT] * IIf(IsNull(FM.[ZeroAdvRate1]), 0, FM.[ZeroAdvRate1])

Else

If FM.[DATE STAMP] >= FM.[ZeroEffDate2] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate3]) And IsNull(FM.[ZeroAdvRate3])) Then

FM.[FACTOR FEES CHARGE] = FM.[INV AMOUNT] * IIf(IsNull(FM.[ZeroAdvRate2]), 0, FM.[ZeroAdvRate2])

Else

If FM.[DATE STAMP] >= FM.[ZeroEffDate3] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate4]) And IsNull(FM.[ZeroAdvRate4])) Then

FM.[FACTOR FEES CHARGE] = FM.[INV AMOUNT] * IIf(IsNull(FM.[ZeroAdvRate3]), 0, FM.[ZeroAdvRate3])

Else

If FM.[DATE STAMP] >= FM.[ZeroEffDate4] Then

FM.[FACTOR FEES CHARGE] = FM.[INV AMOUNT] * IIf(IsNull(FM.[ZeroAdvRate4]), 0, FM.[ZeroAdvRate4])

End If

End If

If FM.[INV MIN AMOUNT] - FM.[INV AMOUNT] > 0 And (FM.[CLIENT 3 ABR] <> "KIE" And FM.[CLIENT 3 ABR] <> "CBF") Then

FM.[FACTOR FEES CHARGE] = FM.[FACTOR FEES CHARGE] + (FM.[INV MIN AMOUNT] - FM.[INV AMOUNT]) * FM.[INV MIN AMOUNT CHARGE]

End If



If FM.[DATE STAMP] >= FM.[Base Date 1] Or ((FM.[DATE STAMP] < FM.[Base Date 2]) And IsNull(FM.[Base Rate 2])) Then

daydiff = (FM.[NUMDAYS] - FM.[Base Days 1])

FM.[FACTOR FEES CHARGE] = (FM.[INV AMOUNT] * Ra1 * daydiff) + (FM.[INV AMOUNT] * Ba1)

Else

If FM.[DATE STAMP] >= FM.[Base Date 2] Or ((FM.[DATE STAMP] < FM.[Base Date 3]) And IsNull(FM.[Base Rate 3])) Then

daydiff = (FM.[NUMDAYS] - FM.[Base Days 2])

FM.[FACTOR FEES CHARGE] = (FM.[INV AMOUNT] * Ra2 * daydiff) + (FM.[INV AMOUNT] * Bb1)

Else

If FM.[DATE STAMP] >= FM.[Base Date 3] Or ((FM.[DATE STAMP] < FM.[Base Date 4]) And IsNull(FM.[Base Rate 4])) Then

daydiff = (FM.[NUMDAYS] - FM.[Base Days 3])

FM.[FACTOR FEES CHARGE] = (FM.[INV AMOUNT] * Ra3 * daydiff) + (FM.[INV AMOUNT] * Bc1)

Else

If FM.[DATE STAMP] >= FM.[Base Date 4] Then

daydiff = (FM.[NUMDAYS] - FM.[Base Days 4])

FM.[FACTOR FEES CHARGE] = (FM.[INV AMOUNT] * Ra4 * daydiff) + (FM.[INV AMOUNT] * Bd1)

End If

End If


''''''''''''''''''''''''''''''''''''''''''



''''''''''''''''''''''''''''''''''''''''''

FM.[Number of Days] = FM.[NUMDAYS]

FM.[REFUND DUE CLIENT] = (FM.[PAYMENT AMOUNT] - FM.[Factor Cash Payment]) - FM.[FACTOR FEES CHARGE]

FM.[TOTINV] = FM.[TOTINV] + FM.[INV AMOUNT]

FM.[TOTCASHPMT] = FM.[TOTCASHPMT] + FM.[Factor Cash Payment]

FM.[TOTRESERVE] = FM.[TOTRESERVE] + FM.[Reserve Amount]

FM.[TOTDEPOSIT] = FM.[TOTDEPOSIT] + FM.[PAYMENT AMOUNT]

FM.[AVGDAYS] = FM.[AVGDAYS] + FM.[NUMDAYS]

FM.[TOTFEES] = FM.[TOTFEES] + FM.[FACTOR FEES CHARGE]

FM.[TOTREFUND] = FM.[TOTREFUND] + FM.[REFUND DUE CLIENT]



If Count = TOTAL Then

GoTo DONELOOP

End If


If FM.FormName = "REFUNDS FORM" Then

DoCmd.GoToRecord A_FORM, "refunds form", A_NEXT

End If

Next

‘ Shouldn’t this be Next Count When I change it I still get the error.

'''''''''''''''''''''''''''''''' END MAIN LOOP '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

DONELOOP:
FM.[AVGDAYS] = FM.[AVGDAYS] / TOTAL

FM.[INVDEPVAR] = FM.[TOTDEPOSIT] - FM.[TOTINV]
End If
mytable.Close
End Function

 

[Micron]

Please go back and enclose your code in code tags - see insert button on menubar.
Somewhere you have an incorrectly nested block but I cannot look through that tome to find it. Sorry.

 

[plog]

Agree with Micron. However, I'd comment out code until you get it to work. Then add back code until it breaks. Then you've found the offending code.

 

[MajP]

Sorry to be harsh but that code is a total disaster. There are so many IF then structures that do not close, I would completely start over. You need to build your structures from outside in. Complete them before putting in the code so you ensure everything is getting closed out

If
elseif
else
endif

Now if you need to fill it in build the next structure

If
   If
   end if
elseif
else
   If
   elseif
   end if
end if

Taking out the code here are your structures.

If TOTAL <> 0 Then
  For Count = 1 To TOTAL
    If FM.[Factor Cash Payment] = 0 Then
        If FM.[DATE STAMP] >= FM.[ZeroEffDate1] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate2]) And IsNull(FM.[ZeroAdvRate2])) Then
        Else
          If FM.[DATE STAMP] >= FM.[ZeroEffDate2] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate3]) And IsNull(FM.[ZeroAdvRate3])) Then
          Else
            If FM.[DATE STAMP] >= FM.[ZeroEffDate3] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate4]) And IsNull(FM.[ZeroAdvRate4])) Then
            Else
             If FM.[DATE STAMP] >= FM.[ZeroEffDate4] Then
             End If
          End If
        If FM.[INV MIN AMOUNT] - FM.[INV AMOUNT] > 0 And (FM.[CLIENT 3 ABR] <> "KIE" And FM.[CLIENT 3 ABR] <> "CBF") Then
          'closed
         End If
         If FM.[DATE STAMP] >= FM.[Base Date 1] Or ((FM.[DATE STAMP] < FM.[Base Date 2]) And IsNull(FM.[Base Rate 2])) Then
         Else
           If FM.[DATE STAMP] >= FM.[Base Date 2] Or ((FM.[DATE STAMP] < FM.[Base Date 3]) And IsNull(FM.[Base Rate 3])) Then
           Else
             If FM.[DATE STAMP] >= FM.[Base Date 3] Or ((FM.[DATE STAMP] < FM.[Base Date 4]) And IsNull(FM.[Base Rate 4])) Then
             Else
               If FM.[DATE STAMP] >= FM.[Base Date 4] Then
               End If

             End If

         If Count = TOTAL Then
         End If
         If FM.FormName = "REFUNDS FORM" Then
         End If

So I count about 13 ifs and 7 end ifs.

Also clearly you database is not normalized. When you see dates in a table date 1, 2, 3, 4 that is a clear sign. I would normalize you data first then you would not have to wrestle with this unmanageable code.

 

[Micron]

I felt guilty so I dropped it all into Notepad++
After this block

If FM.FormName = "REFUNDS FORM" Then

I counted 11 If's and 7 End If's. AFAIK you need an End If for each If that has an Else. I agree that the code is indicative of problems.

 

[vba_php]

If FM.[Factor Cash Payment] = 0 Then 'THIS HAS NO CLOSER


   If FM.[DATE STAMP] >= FM.[ZeroEffDate1] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate2]) And IsNull(FM.[ZeroAdvRate2])) Then
      FM.[FACTOR FEES CHARGE] = FM.[INV AMOUNT] * IIf(IsNull(FM.[ZeroAdvRate1]), 0, FM.[ZeroAdvRate1])
   Else 'THIS HAS NO CLOSER


      If FM.[DATE STAMP] >= FM.[ZeroEffDate2] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate3]) And IsNull(FM.[ZeroAdvRate3])) Then
         FM.[FACTOR FEES CHARGE] = FM.[INV AMOUNT] * IIf(IsNull(FM.[ZeroAdvRate2]), 0, FM.[ZeroAdvRate2])
      Else 'THIS HAS NO CLOSER


         If FM.[DATE STAMP] >= FM.[ZeroEffDate3] Or ((FM.[DATE STAMP] < FM.[ZeroEffDate4]) And IsNull(FM.[ZeroAdvRate4])) Then
            FM.[FACTOR FEES CHARGE] = FM.[INV AMOUNT] * IIf(IsNull(FM.[ZeroAdvRate3]), 0, FM.[ZeroAdvRate3])
         Else
            If FM.[DATE STAMP] >= FM.[ZeroEffDate4] Then
               FM.[FACTOR FEES CHARGE] = FM.[INV AMOUNT] * IIf(IsNull(FM.[ZeroAdvRate4]), 0, FM.[ZeroAdvRate4])
            End If
         End If
      If FM.[INV MIN AMOUNT] - FM.[INV AMOUNT] > 0 And (FM.[CLIENT 3 ABR] <> "KIE" And FM.[CLIENT 3 ABR] <> "CBF") Then
         FM.[FACTOR FEES CHARGE] = FM.[FACTOR FEES CHARGE] + (FM.[INV MIN AMOUNT] - FM.[INV AMOUNT]) * FM.[INV MIN AMOUNT CHARGE]
      End If

If FM.[DATE STAMP] >= FM.[Base Date 1] Or ((FM.[DATE STAMP] < FM.[Base Date 2]) And IsNull(FM.[Base Rate 2])) Then
   daydiff = (FM.[NUMDAYS] - FM.[Base Days 1])
   FM.[FACTOR FEES CHARGE] = (FM.[INV AMOUNT] * Ra1 * daydiff) + (FM.[INV AMOUNT] * Ba1)
Else 'THIS HAS NO CLOSER


   If FM.[DATE STAMP] >= FM.[Base Date 2] Or ((FM.[DATE STAMP] < FM.[Base Date 3]) And IsNull(FM.[Base Rate 3])) Then
      daydiff = (FM.[NUMDAYS] - FM.[Base Days 2])
      FM.[FACTOR FEES CHARGE] = (FM.[INV AMOUNT] * Ra2 * daydiff) + (FM.[INV AMOUNT] * Bb1)
  
   Else 'THIS HAS NO CLOSER
     

      If FM.[DATE STAMP] >= FM.[Base Date 3] Or ((FM.[DATE STAMP] < FM.[Base Date 4]) And IsNull(FM.[Base Rate 4])) Then
         daydiff = (FM.[NUMDAYS] - FM.[Base Days 3])
         FM.[FACTOR FEES CHARGE] = (FM.[INV AMOUNT] * Ra3 * daydiff) + (FM.[INV AMOUNT] * Bc1)
      Else
         If FM.[DATE STAMP] >= FM.[Base Date 4] Then
            daydiff = (FM.[NUMDAYS] - FM.[Base Days 4])
            FM.[FACTOR FEES CHARGE] = (FM.[INV AMOUNT] * Ra4 * daydiff) + (FM.[INV AMOUNT] * Bd1)
         End If
      End If

as you can see, there are many things wrong. you're missing numerous closing statements (END IF's).