Access won't set the first index to 1

[togo]

This is a frustrating one.

Set db3 = CurrentDb
Set db4 = db3.OpenRecordset("ItemAcceptance")
Set fldColumns = db4.Fields
curRec = 1
db4.MoveFirst

While Not db4.EOF
For Each fldEnumerator In db4.Fields
If fldEnumerator.Name = "ID" Then
                    db4.Edit: fldEnumerator.Value = curRec: db4.Update
                    curRec = curRec + 1
End If

// various other routines //

db4.MoveNext
Wend

I want the first entry to have an index of 1, each entry following that to increment by one. It will increment by one but gives some other number to the first entry. I tried this

ALTER TABLE temptextdelimit ALTER COLUMN ID COUNTER(1,1)

query, which doesn't work at all. Thanks for looking at this problem.

edit: so at the moment, the ID starts at 6463. If there is any way to change this back to 1 that would be great. The order of these items is critical at this stage of the d-base.

[alansidman]

Is this a case of having built the db and then deleted all the records. So now the db is empty and you are starting to enter new records? If this is the situation. Make sure the db is empty of records and do a compact and repair. See if this returns the count to start at 1.

[ssanfu]

What are the declarations"?

Dim db3 as ???
Dim db4 as ???
Dim fldColumns as ???
Dim curRec as ???


It looks like you are trying to renumber a group of records. If that is the case, try this (on a copy of your database):

   Dim db3 As DAO.Database
   Dim rst As DAO.Recordset
   Dim curRec As Long

   curRec = 1
   Set db3 = CurrentDb
   Set rst = db3.OpenRecordset("ItemAcceptance")

   rst.MoveFirst

   While Not rst.EOF

      rst.Edit
      rst("ID") = curRec
      rst.Update
      curRec = curRec + 1


      '// various other routines //

      rst.MoveNext
   Wend

Note: The field "ID" cannot be an "Autonumber" type field....

[togo]

can't close/reopen/compact database, since this isn't a one time thing. A CSV file is imported through a spec, one of those fields being autonumbered. Then an append query is used to put it into item accepted table as noted above. Declarations have been made in code as suggested in the last post, but were omitted in my post. I will change the spec from autonumber to number and hopefully that will force it to number my way.

then again it isn't an autonumber in the itemacceptance table.

[togo]

so nobody really knows why it does this?

[ssanfu]

change the spec from autonumber to number and hopefully that will force it to number my way.

If the field is a long integer (or a number type), you can number any way you want. An autonumber type is controlled by access.

then again it isn't an autonumber in the itemacceptance table.

Don't know what this is: "itemacceptance table"

Would you care to post your database? It might be easier to understand your problem.