Reset Autonumber Field 5

[lshields]

Is there a way in code that I can reset an autonumber field back to 1 whenever I clear the table out?

Please advise.

Thanks

 

[ZmrAbdulla]

Do a compact & repiar

TOOLS > DATABASE UTILITIES > ....

________________________________________
Zameer Abdulla
Visit Me
There is only one perfect child in this world. Every mother has it.

 

[flyover789]

you don't have to do the compact/repair thing. you can do it during runtime using the following code:

if your autonumber field (in the code I call it [ID]) is the primary key in yourtable, use:

Function resetautonumber()
DoCmd.RunSQL "ALTER TABLE yourTable DROP CONSTRAINT [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable DROP COLUMN [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable ADD COLUMN [ID] COUNTER CONSTRAINT [ID] PRIMARY KEY;"
End Function

if it's not the primary key, use:

Function resetautonumber()
DoCmd.RunSQL "ALTER TABLE yourTable DROP CONSTRAINT [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable DROP COLUMN [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable ADD COLUMN [ID] COUNTER CONSTRAINT [ID] UNIQUE;"
End Function

if you don't wish to index the ID field at all, use:

Function resetautonumber()
DoCmd.RunSQL "ALTER TABLE yourTable DROP COLUMN [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable ADD COLUMN [ID] COUNTER;"
End Function

hth,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[ZmrAbdulla]

That was a fantastic reminder
Thank you.

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together