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

 

[Pat Hartman]

The End Select is probably in the wrong place. Move it up above the "with rst". Also, when posting code, please use the code tags

so the indentation is maintained. It makes the code ever so much easier to read.

 

[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 !!!