Equivalent Syntax But Only 1 of 3 Works!? 3

[xweyer]

I have a section of code that appears below. It is intended to update the field "trans" in the table "Stran" with sequential numbers (beginning with '100' in this case). The code works fine when the field is referenced as "myset.Fields(2) = mycounter" but fails with the "Item not found in this collection" error when the actual field name is used in the reference aka either...

myset.Fields![trans] = mycounter
myset.Fields("trans") = mycounter

Does anyone know why this would be?

'CODE

Dim MyDB As DAO.Database
Dim myset As DAO.Recordset
Dim mycounter As Integer
Dim criteria As String

mycounter = 100
criteria = "[Item] >= 0"

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set myset = MyDB.OpenRecordset("Stran", DB_OPEN_DYNASET)

myset.FindFirst criteria

Do Until myset.NoMatch

myset.Edit
myset.Fields(2) = mycounter
mycounter = mycounter + 1
myset.Update
myset.FindNext criteria

Loop

 

[JerryKlmns]

Maybe a reserved word????

And why don't you open a recordset at the first place using the --WHERE [Item] >= 0-- clause and loop through EOF instead of FindNext all the time???

 

[CautionMP]

Field caption?

Open the table in design view and confrim that the field is actually called [tt]trans[/tt].

Or you could do it in code:

Debug.Print myset.Fields(2).Name
Stop

Hope this helps,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[MajP]

My bet is that "trans" does not exist like CMP said. Unfortunately, you can use reserved words as field names if you enclose in brackets or quotes. Here is an example.
Private Sub Command12_Click()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
MsgBox rs.Fields("Form").Name
MsgBox rs![Form].Name
MsgBox rs.Fields("Query").Name
MsgBox rs.Fields("Table").Name

 

[xweyer]

Thanks to all for responding. You were all on the right track.

Here's specifically what the problem turned out to be. The database I am working on is replacing an earlier creation that was done in Rbase.

One issue came up when I was exporting data was that due to some quirks in the Rbase version I was unable to successfully export some tables data via the comma delineated method that I prefer, but instead had employ a round about route of exporting from Rbase to 1,2,3 format then import that file into Excel and from there back out to Access. Although I was able to eliminate the import errors this way the process did something else that I was unable to see initially. Probably because Rbase was a Dos based application any field names residing in the tables imported this way, which were less than eight characters, got trailing spaces appended to them to make up the difference. So although it was for the most part invisible "trans" was actually "trans ".

CautionMP's code was particularly helpful in finding this.

JerryKlmns - good suggestion. In this instance the recordset is small enough that it doesn't make much functional difference but your way is cleaner.