Please help to number records in a database 4

[vamoose]

I have what I think is a simple request but I am stummped by the solution. I am using Access 2000 and I need to assign a number, not Autonumber because I need to edit it, to each record in the database. The database has about 30 records in it and a column named Auto which is empty. I would like to start at the first record and add the number 1 in the Auto field and move to the next and add a 2 to the Auto field and so on. Can anyone please help me with a solution or tell me how to go about doing this ? I would like to use the DoCmd.RunSql command if possible. Thank you so very much.

 

[AccessGuruCarl]

Not sure if this will help, but here is an idea...

Dim db as Database
Dim rst as Recordset
Dim intCnt as Integer
Dim x as Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("YourTable")

DoCmd.MoveLast  'May not be needed to get count, test...
intCnt = rst.RecordCount
DoCmd.MoveFirst  'Make sure your at the first record in the table.

For x = 1 to intCnt
  Me.Auto = x
Next x

I didn't type this in the VB Editor so there my be errors.
Not sure what your trying to attempt here, that auto-number wouldn't do for you unless your trying to 'insert' records, then re-number them for sorting. If this is the case, and your having problems place another post... I have code that allows you to insert new records, then renumbers the lines.

Good Luck

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[MajP]

You can do this in Sql or using recordsets, but the question is what do you need it for? If you just want to number items in a report you can do that just on the report. If you need an artificial sort order then just add another number field. If you want to make your own auto number there are ways to do that.

If you want this to be your be a primary field, then this is a very bad idea. A primary key must be unique and never changing, not sequential.

If you want to do it by code, I do not think the above code will work. Try this. Again this assumes that the field Auto is not an autonumber field (which seems like a bad name).

Public Sub subRecordCount()
  Dim strTableName As String
  Dim strFieldName As String
  Dim rs As DAO.Recordset
  Dim intCount As Integer
  'Put your names here for table and field
  strTableName = "Table1"
  strFieldName = "Auto"
  Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
  Do While Not rs.EOF
    intCount = intCount + 1
    rs.Edit
    rs.Fields(strFieldName) = intCount
    rs.Update
    rs.MoveNext
  Loop
End Sub

All this does is number the records in the order they appear in the table, not sure why you need that. You may want to sort the table first.

 

[TheAceMan1]

Howdy MajP . . .

I was going to post nearly the same thing with the following differences:

[blue]  Do While Not rs.EOF
    rs.Edit
    rs[purple][b](strFieldName)[/b][/purple] = [purple][b]rs.AbsolutePosition + 1[/b][/purple]
    rs.Update
    rs.MoveNext
  Loop[/blue]

See Ya! . . . . . .

 

[Zion7]

I think MajP, has the most efficient & straight forward
solution (do pay heed to his final statement).

Since you asked for RunSQL, (?), here's my version,
*****(A ROUGH, ROUGH IDEA)******

x = DCount("pkiD","tblYourTable")

Docmd.SetWarnings False

For y = 1 To x
SQL = "UPDATE tblYourTable SET " & _
" AUTO =" & y & _
" WHERE AUTO = " & (x-y)

Docmd.RunSQL SQL
Next y

Docmd.SetWarnings True