I am not that great in Access. I found this code to reset the Autonumber
field in a table. However I am unsure of how to substitute my fields in the
code.

Table Name = IP-1
Autonumber field in Table = "Primary"

Can you tell me how to substitute the Table name and the autonumber field in
the below code?

Thanks in advance.

Private Sub DeleteData()
Dim db As DAO.Database
Dim Rec As DAO.Recordset
Dim a As Long
Set db = CurrentDb()
Set Rec = db.OpenRecordset("YourTable", dbOpenDynaset)
?Make sure there are records to delete
If Not Rec.EOF Then
Rec.MoveFirst
Do Until Rec.EOF
Rec.Delete
Rec.MoveNext
Loop
End If
?Set a to the number before the number you want to start with
?Set it to 0 if you want to start at 1
?This will start the count at 15
a = 14
Rec.AddNew
'Set your autonumber field to a
Rec![ID] = a
?Make sure you use data that matches Field(1)
Rec.Fields(1) = "a"
?Update it then delete it
Rec.Update
Rec.MoveFirst
Rec.Delete
Rec.Close
Set Rec = Nothing
End Sub

Why do you need to do this?
To reset the autonumber, empty the table and do a compact and repair.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"jhicsupt" wrote:

> I am not that great in Access. I found this code to reset the Autonumber
> field in a table. However I am unsure of how to substitute my fields in the
> code.
>
> Table Name = IP-1
> Autonumber field in Table = "Primary"
>
> Can you tell me how to substitute the Table name and the autonumber field in
> the below code?
>
> Thanks in advance.
>
> Private Sub DeleteData()
> Dim db As DAO.Database
> Dim Rec As DAO.Recordset
> Dim a As Long
> Set db = CurrentDb()
> Set Rec = db.OpenRecordset("YourTable", dbOpenDynaset)
> ?Make sure there are records to delete
> If Not Rec.EOF Then
> Rec.MoveFirst
> Do Until Rec.EOF
> Rec.Delete
> Rec.MoveNext
> Loop
> End If
> ?Set a to the number before the number you want to start with
> ?Set it to 0 if you want to start at 1
> ?This will start the count at 15
> a = 14
> Rec.AddNew
> 'Set your autonumber field to a
> Rec![ID] = a
> ?Make sure you use data that matches Field(1)
> Rec.Fields(1) = "a"
> ?Update it then delete it
> Rec.Update
> Rec.MoveFirst
> Rec.Delete
> Rec.Close
> Set Rec = Nothing
> End Sub
>

This is for a multi-user environment. Compacting and repairing will prohibit
users from using database while this is going on.

I need this so that the person that covers for me will not have to go into
Design mode.

Thanks so much.

"Dorian" wrote:

> Why do you need to do this?
> To reset the autonumber, empty the table and do a compact and repair.
> -- Dorian
> "Give someone a fish and they eat for a day; teach someone to fish and they
> eat for a lifetime".
>
>
> "jhicsupt" wrote:
>
> > I am not that great in Access. I found this code to reset the Autonumber
> > field in a table. However I am unsure of how to substitute my fields in the
> > code.
> >
> > Table Name = IP-1
> > Autonumber field in Table = "Primary"
> >
> > Can you tell me how to substitute the Table name and the autonumber field in
> > the below code?
> >
> > Thanks in advance.
> >
> > Private Sub DeleteData()
> > Dim db As DAO.Database
> > Dim Rec As DAO.Recordset
> > Dim a As Long
> > Set db = CurrentDb()
> > Set Rec = db.OpenRecordset("YourTable", dbOpenDynaset)
> > ?Make sure there are records to delete
> > If Not Rec.EOF Then
> > Rec.MoveFirst
> > Do Until Rec.EOF
> > Rec.Delete
> > Rec.MoveNext
> > Loop
> > End If
> > ?Set a to the number before the number you want to start with
> > ?Set it to 0 if you want to start at 1
> > ?This will start the count at 15
> > a = 14
> > Rec.AddNew
> > 'Set your autonumber field to a
> > Rec![ID] = a
> > ?Make sure you use data that matches Field(1)
> > Rec.Fields(1) = "a"
> > ?Update it then delete it
> > Rec.Update
> > Rec.MoveFirst
> > Rec.Delete
> > Rec.Close
> > Set Rec = Nothing
> > End Sub
> >

On Mon, 13 Apr 2009 14:13:01 -0700, jhicsupt
<(E-Mail Removed)> wrote:

> I found this code to reset the Autonumber
>field in a table.

Why do you need to do so?

Autonumbers have one purpose and one purpose ONLY: to provide a guaranteed
unique key. If you're assigning other meaning to them, you're skating on thin
ice.

312271, 312272 and 312295 are *just* as good as unique identifiers for three
rows as are 1, 2 and 3.
--

John W. Vinson [MVP]