Auto Number Reset 1

[brahmabull525]

I have a table called "Active" with a AutoNumber field in it called EntryID. At a certain point, data in "Active" is moved to a table called "Archive".

Is there a way to have the EntryID field start again at 1 (and so on) when new data is entered into Active?

Any help would be most appreciated!!

 

[dhookom]

Why? Normally the autonumber field values are never shown to the user so it makes no difference what value is being stored.

If all records are removed from the table, compacting the database may reset the autonumber back to 1.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[brahmabull525]

In my case, the user needs to know the entry sequence/order to match with a paper form hardcopy.

Based on the user, I don't think compacting would be practical.

Any other suggestions?

 

[brahmabull525]

On second thought.....can code be added to a command button to perform Compacting???????

 

[dhookom]

Depending on the version of Access, you might be able to set the mdb to compact on close. I still don't think this is a good idea.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZmrAbdulla]

You may read these FAQs for better understanding of Autonumber
[ul]
[li] Use of Autonumber faq702-5106 [/li]
[li] How can I auto-prefix my autonumber field? faq181-1725 [/li]
[li] Create a Autonumber with a Suffix faq181-1179[/li]
[li] How to autonumber date values in a control. faq181-1129 [/li]
[li] What can I use besides AutoNumber faq181-1023 [/li]
[li] Make an intelligent autonumber field? faq181-159 [/li]
[li] can I make a dynamic autonumber? faq181-158 [/li]
[/ul]

Reset Autonumber Field thread702-1050735

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[brahmabull525]

The thread702-1050735 was particularly helpful. However, how can/do I apply this correctly to a form command button?

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

I tried using it, and it's just not working for me. Any help would be most appreciated!!

Thanks.

 

[ZmrAbdulla]

Can you post your code that you are using?

________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".

 

[brahmabull525]

That's my problem Zameer, I'm not sure how to use the code.

I'd like to put a command button on a form and have the code executed when the command button is clicked.

Not sure how I can do this.

Any help would be appreciated!