Reference a field by its ordinal number (1 Viewer)

[Everton]

Hi,
I'm trying to set the values of all the fields in a recordset, and would like to reference each field by number rather than name, so I can create a loop to cycle through all fields eg:

Dim rstpiklst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("MyTable", dbOpenDynaset)

rst.MoveFirst

for i = 1 to 10
With rst
!Fields(i) = "Default"
.Update
End With
next i

I can't get it to work, and suspect it is something to do with the syntax of how I am refering to the field. I am getting nowhere with the help file, so if anyone can help me it would be appreciated.

Paul.

 

[ntp]

Try changing your code to this:

Dim rstpiklst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("MyTable", dbOpenDynaset)

rst.MoveFirst
rst.edit
With rst
for i = 0 to 9
.Fields(i) = "Default"
.Update
next i
End With

You need to add rst.edit to 'open' the recordset for editing or try rst.addnew if you are creating a new record and setting its fields to "default".

Values are indexed from 0 so if you have ten fields in your recordset you need to use a for loop from 0 to 9.

The notation is rst.fields not rst!fields as you have it.

Also you should not use the With..End With statement inside the for loop since on each iteration you will be setting starting a with clause and closing one. Having it outside the for lopp means this is done only once. Only a mater of efficiency both ways will work.

ntp

 

[Everton]

Thanks.

Paul.