loop variable in field name

[Guest]

I want to assign a value to a field depending on the value of the number in a
for loop. For example, I have fields named [BL 1], [BL 2],...[BL 9] and if i
(the loop counter) = 5, I want to assign the value of a variable dbLength to
[BL 5]. How do I accomplish this? Thanks.

 

[Guest]

you will need to use the Fields collection of your recordset.

Set rst = CurrentDb.OpenRecordset("MyTableName")
With rst
For intCtr = 1 to 5
.Edit
.Fields(format(intCtr, "\[BL 0\]") = SomeValue
.Update
Next intCtr
End With

 

[Guest]

Me.Controls("BL " & i).Value = dbLength