Adressing a field with code 1

[billheath]

I am writing a routine to populate the fields in a table. The fields are named Field1, Field2, etc. I used the following code:

For i = 3 to 153
with rst1
.edit
!Field(i) = i
.update

I keep getting an item not found in database errow. I also tried assigning Field(i) to a variable called FieldName and used "FieldName" in place of Field(i). Same result. Please help and thanks
Bill

 

[Steve101]

Here's a simple example that I've put together which should help:

Dim DB As database
Set DB = CurrentDb
Dim rst1 As Recordset
Set rst1 = DB.openrecordset("table1&quot

For i = 0 To rst1.Fields.Count - 1
With rst1
.edit
.Fields(i) = i
.Update
End With
Next i

rst1.Close
DB.Close

In particular, note the following:

(a) Its .Fields(i), not !Fields. Fields is a property of the rst1 recordset, not a fieldname

(b) The first field in the recordset is referenced as the zero'th element; hense the way the example specifies the For loop parameters.

Hope this helps. Assuming your example has 154 fields, then your problem would be caused by using the ! instead of a full stop.

Hope this helps, Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[jebry]

Hi Bill!

What you need to do is use the Fields collection:

FieldName = "Field" & Format(i)
rst1.Fields(FieldName) = i

Alternatively you can use:

rst1.Fields(i) = i

This assumes that you created the fields in order and left them that way since I think Access numbers the fields in the order it finds them.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[billheath]

Steve, Thanks. Your method worked. I was trying to call the field by its name with Field(i). The fields are named field1, field2, field3, etc. I assume that there is no way to adress the fields using a variable, correct?
Bill

 

[jebry]

Hi again!

Take another look at my post for using a variable to access a field.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Steve101]

Bill,
Yes there is. See Jeff's post above. Another example:

ThisField = "CustomerName"
rst1.Fields(ThisField) = "Joe Bloggs&quot

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au