Using a variable to identify a field.

[tehgreatmg]

Here is My code for starters:

Select Case varMonth
        Case Is = 1
            varLongMonth = "January"
        Case Is = 2
            varLongMonth = "Feburary"
        Case Is = 3
            varLongMonth = "March"
        Case Is = 4
            varLongMonth = "April"
        Case Is = 5
            varLongMonth = "May"
        Case Is = 6
            varLongMonth = "June"
        Case Is = 7
            varLongMonth = "July"
        Case Is = 8
            varLongMonth = "August"
        Case Is = 9
            varLongMonth = "September"
        Case Is = 10
            varLongMonth = "October"
        Case Is = 11
            varLongMonth = "November"
        Case Else
            varLongMonth = "December"
    End Select
    txttest = varLongMonth

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("Query_2")
    rs1.MoveFirst
  
    Do Until rs1.EOF
        If rs1![ID] = Val(ID) Then
            NumOrdTotal = rs1!varLongMonth + Val(NumOrd)
            rs1.Edit
            rs1!varLongMonth = NumOrdTotal
            rs1.Update
        End If
        rs1.MoveNext
    Loop

  rs1.Close
  Set rs1 = Nothing
  Set db = Nothing

At the line, NumOrdTotal = rs1!varLongMont h + Val(NumOrd) , I get an error saying: Item not found in Collection.

I believe I am getting this error because where the field belongs at rs1!"fieldName" I have a variable. I need to be able to use this variable to identify the field depending on certain criteria. How can I use this variable to do that?

[NeoPa]

Try :

rs1.Fields(varLongMonth)

[NeoPa]

Try using this to convert month number to string :

varLongMonth = Format(CDate("25/" & varMonth & "/1999"),"mmmm")

[tehgreatmg]

Try :

rs1.Fields(varLongMonth)

Thanks NeoPa that did the trick, but now I got another probelm, WOOHOO. I can not get it to enter data into other records, just the first record. It is not giving any errors it runs it like its working. So its just some little thing.

Updated Code:

    Select Case varMonth
        Case Is = 1
            varLongMonth = "January"
        Case Is = 2
            varLongMonth = "Feburary"
        Case Is = 3
            varLongMonth = "March"
        Case Is = 4
            varLongMonth = "April"
        Case Is = 5
            varLongMonth = "May"
        Case Is = 6
            varLongMonth = "June"
        Case Is = 7
            varLongMonth = "July"
        Case Is = 8
            varLongMonth = "August"
        Case Is = 9
            varLongMonth = "September"
        Case Is = 10
            varLongMonth = "October"
        Case Is = 11
            varLongMonth = "November"
        Case Else
            varLongMonth = "December"
    End Select
    txttest = varLongMonth
'========================================================================================
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("CartUse")
    rs1.MoveFirst
  
    Do Until rs1.EOF
        If rs1.Fields(ID) = Val(ID) Then
            NumOrdTotal = Val(rs1.Fields(varLongMonth)) + Val(varNumOrd)
            txttest2 = Val(NumOrdTotal)
            rs1.Edit
            rs1.Fields(varLongMonth) = Val(NumOrdTotal)
            rs1.Update
        End If
        rs1.MoveNext
    Loop

  rs1.Close
  Set rs1 = Nothing
  Set db = Nothing

[NeoPa]

You need to post this as a new question as this is unrelated to this one.
Try to explain exactly what you're after a little more clearly though. If I'd understood the question I would have tried to answer it anyway.

[NeoPa]

Try using this to convert month number to string :

varLongMonth = Format(CDate("25/" & varMonth & "/1999"),"mmmm")

BTW. I'm pleased that helped and thanks for the feedback - always valuable.
Did you notice this post (#3 quoted)? It should help you to get rid of a whole hunk of code.

[tehgreatmg]

Alright I moved it here: Using a Do Until Loop to search for and modify data in a table And also I will probably change that case statement to the code you gave me after I get it writing data and working better first.

[NeoPa]

Cool - Nicely done :)