File Record Number Field (Like an Odometer)

[captdave311]

I need to add data to a table of 200,000+ records that is essentially an autonumber, however, the needs to start with record 1 in the table as 000000 increase by 1 with every record to 99999 and then reset to 00000.

Let me know if I need to provide more info!

 

[MajP]

You probably can do this in Sql but I am not smart enough in that area. This assumes you have a text field to store your pseudo number, and that the records exist already. Once you fill in your existing values there are several FAQs on creating an autonumber. I also assume that you will not use this number as a primary key, since it is not unique.

Public Sub makeOdometer(strTableName As String, strFieldName As String, intLimit As Long)
  Dim rs As DAO.Recordset
  Dim intCount As Long
  Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
  Do While Not rs.EOF
     rs.Edit
     rs.Fields(strFieldName) = Format(intCount, "000000")
     rs.Update
     rs.MoveNext
     intCount = intCount + 1
     If intCount > intLimit Then
       intCount = 0
     End If
  Loop
End Sub

Public Sub test()
  makeOdometer "table1", "txtOdometer", 99999
End Sub