How do I refer to a table field name in vba code during update process?

[Jeo0o7]

Here is my code:

Public Function fnGeneral

     Dim rs As DAO.Recordset
     Dim ss As String
    Dim aLetter(1 To 14) As String
    Dim lGrid As Long
    Dim iLetter As Long
    Dim sField As String
   '  Set up array of 14 letters
    aLetter(1) = "A": aLetter(2) = "B": aLetter(3) = "C": aLetter(4) = "D"
    aLetter(5) = "E": aLetter(6) = "F": aLetter(7) = "G": aLetter(8) = "H"
    aLetter(9) = "I": aLetter(10) = "J": aLetter(11) = "K": aLetter(12) = "L"
    aLetter(13) = "M": aLetter(14) = "N"

    ' Open grid table for entry
    ss = "Select * from tGrid;"
    Set rs = CurrentDb.OpenRecordset(ss, dbOpenDynaset)
    
    For iLetter = 1 To 14
        lGrid = lGrid + 1
        With rs
            .AddNew
          !sField = aLetter(iLetter) & lGrid
            .Update
        End With
Stop
    Next iLetter
    MsgBox "Finished updating table tGrid!"
End Function

'  Problem is: Although sField is correctly set to a field name in table tGrid called   A1,

'  I get the runtime error "Item not found in this collection" on the line  !sField = aLetter(iLetter) & lGrid

'  when attempting to update field A1.    What can I do to fix the field reference?

[#2]

@Jeo0o7 

To be honest, this is pretty opaque to me. What is the goal?

That said, I can see at least one potential place where it goes wrong.

You define a string called "SS" and assign a SQL Statement to it. Then use it to open a recordset. All standard stuff.

However, you do NOT specific which fields to retrieve from the table called tGrid. By using the wildcard, your recordset opens with as many fields are are already in tGrid, whether 1 or 26 or any number in between.

ss = "Select * from tGrid;"
    Set rs = CurrentDb.OpenRecordset(ss, dbOpenDynaset)

Later you try to add a new record and assign a specific value to one of those fields:

!sField = aLetter(iLetter) & lGrid

That means your table tGrid has to have field "sField" in it, because you are trying to put the specific value--resuling from the concatenation of a value from the array and the current value of the LGrid variable--into the field called "sField". 

Is that really the goal? If not what is the goal?

Thanks.

[#3]

@George Hepworth 

Thanks George. You have it correct about my goal.  If you run my code you get a "Not in collection" error at the place you discussed.  The variable has the correct field name , A1, as its value, but produces the error when you use that sField variable fot updating.  It seems to me the local variable is being taken literally instead of being able to represent the value of the FieldName in the updating process.

[#4]

My goal is to dynamically create with vba code the left (assignment) side to name table fields that accept data on the right side. That is why the tGrid table actual field names are A1, A2, B1, B2, etc. -- to facilitate vba code

[#5]

@Jeo0o7 

I see. Thanks for the clarification. 
If you want to address variable fields, you have to use the Recordset.Fields(x) collection, where x is either the index for the field in question, or the name of that field.

[#6]

Thanks so much for the replies, George. I know you are correct! I understand, and will work on it that way!

[#7]

All I needed was the correct way of referencing:
Also, wild card in sql statement was ok to use.
Thanks for everything, George!
sField = "A1"
With rs
.Edit
.Fields(sField) = "B"
.Update
End With

[#8]

Congratulations on solving the problem.
Continued success with the project.