Need help with coding

[associates]

Hi,

I've got a table that has fields as follows

studentID, ..., 2005/6, 2006/7, and so on.

My question is if i know the studentID but i don't know whether it'd be 2005/6 or 2006/7 because it's based on the current year and i want to make it flexible enough.

I've got the following code.

rsNew.Open "Select * from [Projects]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

' myyear contains the value of the year
myyear = Me.TX_Year1.Caption

Do Until rsNew.EOF
If rsNew!Student_ID = Me.Student_ID Then
Me.TB_Year1 = rsNew!mystr <------------ error
End If

rsNew.MoveNext
Loop

rsNew.Close

I've got error when i run this code as the access doesn't like rsNew!mystr because mystr is a variable.

Is there any way of getting around this?

Thank you in advance