Concatenate & Update Table

[Cogivina2004]

I have a table(TrFac) in access with 20 cols as F1, F2...., F20 and some ID fields. The # of rows (Range("TrCounter") is varied by state. I create a listbox in userform so that the user can input their changes for these fields. Range("TrChck").value is the total number of Fs changed by the user. I'd like to simplify the code below using For-Next loop.
-----------------------------------
Sub UpdateGrps_To_Database()
Dim dbl As Database, rst As Recordset

Dim lngRows As Integer, lngGrpChck As Integer

wsTerr.select

Set dbl = OpenDatabase(GetDataBase())
Set rsTerr = dbt.OpenRecordset("TrFac", dbOpenTable)

STR = " SELECT StateTerrID, " _
& " F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20" _
& " FROM [TrFac]" _
& " WHERE StateID = '" & range("State")& "'"

Set rst = dbt.OpenRecordset(STR, dbOpenDynaset)
rst.MoveFirst

lngGrpChng = 0 'initial value
For T = 1 To Range("TrCounter") '# Rows-known
If lngGrpChng >= Range("TrChck") Then GoTo DONE
'range("TrChck") is
If Cells(T + 7, 49) = 0 Then GoTo NextT 'no change for that row
With rst
.Edit

!F1 = Cells(T + 7, 7)
lngGrpChng = lngGrpChng + Cells(T + 7, 49)
!F2 = Cells(T + 7, 8)
lngGrpChng = lngGrpChng + Cells(T + 7, 49)
.........
!F19 = Cells(T + 7, 25)
lngGrpChng = lngGrpChng + Cells(T + 7, 49)
!F20 = Cells(T + 7, 26)
lngGrpChng = lngGrpChng + Cells(T + 7, 49)
.Update
End With
NextT:
rst.MoveNext
Next T
End If

Set rst = Nothing
End Sub
---------------------------------

I like to write something like this but I have a problem of concatenate the ![F} and value I:

=======================================
For T = 1 To Range("TrCounter") '# Rows-known
If lngGrpChng >= Range("TrChck") Then GoTo DONE
If Cells(T + 7, 49) = 0 Then GoTo NextT
With rsTerrData
.Edit
For I = 1 to 20
![F] & I = Cells(T + 7, 6 + I)
lngGrpChng = lngGrpChng + Cells(T + 7, 49)
.Update
End With
NextT:
rst.MoveNext
Next T
End If
=================================

Thanks for your help.

 

[HermanvLimbeek]

What you want to do is access the recordset fields like an array. It can be done but you have to create the array first. See

http://www.vb-helper.com/howto_ado_load_array.html.