Case Select problem

[WineSnob]

I get an error " Case without Case Select" on the Case 0 line in the following code. If I change the Case 0 to Case Else I get the error "Case Else Outside Select Case". Is it because of the End If's ? ? nIntType will only be 1 or 0.

Sub FillAnnuity(nProposalID As Long, nAnnuityYearsLife As Integer, cInitial As Currency, nRORGB As Double, _
nAnnuityID As Integer, nIntType As Integer)
Dim rst As Recordset
Dim qdf As QueryDef
Dim GB As Currency
Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblAnnuityIncome] WHERE [ProposalID] = " & nProposalID)
qdf.Execute
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAnnuityIncome")

Select Case nIntType

Case 1
nextStarting = cInitial * (1 + nRORGB) ' calulate the starting value
For nYear = 1 To nAnnuityYearsLife

Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting

If nYear = 1 Then

GB = nextStarting

End If

If nYear > 1 Then

GB = nextStarting * (1 + nRORGB)

nextStarting = nextStarting * (1 + nRORGB)

End If

Case Else
nextStarting = cInitial * nRORGB ' calulate the starting value
For nYear = 1 To nAnnuityYearsLife

Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting

If nYear = 1 Then

GB = nextStarting

End If

If nYear > 1 Then

GB = nextStarting * nRORGB

nextStarting = nextStarting * nRORGB

End If



With rst
.AddNew


![ProposalID] = nProposalID
![AnnuityID] = nAnnuityID
![AnnuityYear] = nYear
![RORofGB] = nRORGB
![GuaranteedBase] = GB



.Update
End With




Next nYear
End Select
End Sub




Public Function testFillannuityTable()
FillAnnuity 1003, 5, 292248.92, 0.06, 1, 1
End Function

 

[WineSnob]

The End Select is at the very bottom just before End Sub. Thanks for the tip on the # for the code. I never knew that.

Sub FillAnnuity(nProposalID As Long, nAnnuityYearsLife As Integer, cInitial As Currency, nRORGB As Double, _
nAnnuityID As Integer, nIntType As Integer)
Dim rst As Recordset
Dim qdf As QueryDef
Dim GB As Currency
Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblAnnuityIncome] WHERE [ProposalID] = " & nProposalID)
qdf.Execute
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAnnuityIncome")
Select Case nIntType
Case 1
nextStarting = cInitial * (1 + nRORGB) ' calulate the starting value
    For nYear = 1 To nAnnuityYearsLife
 
        Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting
 
        If nYear = 1 Then
 
            GB = nextStarting
 
              End If
 
        If nYear > 1 Then
 
            GB = nextStarting * (1 + nRORGB)
 
            nextStarting = nextStarting * (1 + nRORGB)
 
        End If
 
  Case Else
        nextStarting = cInitial * nRORGB ' calulate the starting value
    For nYear = 1 To nAnnuityYearsLife
 
        Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting
 
        If nYear = 1 Then
 
            GB = nextStarting
 
              End If
 
        If nYear > 1 Then
 
            GB = nextStarting * nRORGB
 
            nextStarting = nextStarting * nRORGB
 
        End If
 
 
 
            With rst
        .AddNew
 
 
        ![ProposalID] = nProposalID
        ![AnnuityID] = nAnnuityID
        ![AnnuityYear] = nYear
        ![RORofGB] = nRORGB
        ![GuaranteedBase] = GB
 
 
 
        .Update
    End With
 
 
 
 
Next nYear
End Select
End Sub

 

[VilaRestal]

You first For loop didn't have a Next. I've added it below:

Sub FillAnnuity(nProposalID As Long, nAnnuityYearsLife As Integer, 
                                             cInitial As Currency, nRORGB As Double, _
                                             nAnnuityID As Integer, nIntType As Integer)
    Dim rst As Recordset
    Dim qdf As QueryDef
    Dim GB As Currency
    Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblAnnuityIncome] WHERE [ProposalID] = " & nProposalID)
    qdf.Execute
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAnnuityIncome")

    Select Case nIntType

        Case 1
            nextStarting = cInitial * (1 + nRORGB) ' calulate the starting value
            For nYear = 1 To nAnnuityYearsLife
                Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting
                If nYear = 1 Then
                    GB = nextStarting
                ElseIf nYear > 1 Then
                    GB = nextStarting * (1 + nRORGB)
                    nextStarting = nextStarting * (1 + nRORGB)
                End If
            Next nYear

        Case Else
            nextStarting = cInitial * nRORGB ' calulate the starting value
            For nYear = 1 To nAnnuityYearsLife
                Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting
                If nYear = 1 Then
                    GB = nextStarting
                ElseIf nYear > 1 Then
                    GB = nextStarting * nRORGB
                    nextStarting = nextStarting * nRORGB
                End If

                With rst
                    .AddNew
                    ![ProposalID] = nProposalID
                    ![AnnuityID] = nAnnuityID
                    ![AnnuityYear] = nYear
                    ![RORofGB] = nRORGB
                    ![GuaranteedBase] = GB
                    .Update
                End With
            Next nYear
    End Select
End Sub

Public Function testFillannuityTable()
    FillAnnuity 1003, 5, 292248.92, 0.06, 1, 1
End Function

 

[VilaRestal]

PS: You should close the recordset and set it to Nothing too before the end of the sub.

 

[WineSnob]

Thanks VilaRestal !!!