Help with recordsets

[CBO]

Trying to access a field in a table using a select case, can anyone tell me what the problem is. I get a "No Current Record Error" when I run the code

Select Case intTMO_RATE
Case 5
strNew_PromoID = rst.Fields("5YR").Value
Case 7
strNew_PromoID = rst.Fields("7YR").Value
Case 10
strNew_PromoID = rst.Fields("10YR").Value
Case 15
strNew_PromoID = rst.Fields("15YR").Value
Case 20
strNew_PromoID = rst.Fields("20YR").Value
End Select

 

[ChrisO]

Probable cause...no records in the recordset.

Would need to see all the code and some data to determine why there are no records.

BTW there is a hard coded reference between the Case number and the field name: -

Select Case intTMO_RATE
    Case [color=red]5[/color]
        strNew_PromoID = rst.Fields("[color=red]5[/color]YR").Value
    Case [color=red]7[/color]
        strNew_PromoID = rst.Fields("[color=red]7[/color]YR").Value
    Case [color=red]10[/color]
        strNew_PromoID = rst.Fields("[color=red]10[/color]YR").Value
    Case [color=red]15[/color]
        strNew_PromoID = rst.Fields("[color=red]15[/color]YR").Value
    Case [color=red]20[/color]
        strNew_PromoID = rst.Fields("[color=red]20[/color]YR").Value
End Select

[color=green]'So it can be broken down to: -[/color]
strNew_PromoID = rst.Fields(CStr(intTMO_RATE) & "YR")

If you can post all the code someone will be able to help.

Regards,
Chris.

 

[Pat Hartman]

If you normalize your table, you will end up writing far less code and have a more robust structure. You have a repeating group that makes your table more of a spreadsheet than a relational table. The many-side of a 1-many relationship should be stored in individual rows of a separate table rather than in individual columns as you have presently.