How to reset an autonumber after delete query 2

[ffleitas]

Hello programmers,

How do I reset an autonumber field on a table to 1 after I run a delete query for this temporary table without having to delete the field and recreating it on the table? I noticed that if I run the compact and repair utility it automatically resets it to 1. The only problem is that this becomes a problem in terms of automation.

Thanks,
Felix

 

[MasterPO]

I read this technique in MS Access Advisor, but I don't know the author to properly credit him.

Create a table with a single numeric field. Enter the number 1 in the table. This will be the only record in the table.

Create an append query to append this record to the table with the autonumber field.

Run your delete query to clear all the records from the autonumber table, then run the append query. The autonumber will now begin at 1 again. You could put any number you want into the 'seed' table to cause the autonumber to start at a different value.

Apologies and Thanks to the Advisor contributor who came up with this!

 

[ffleitas]

Hi,

I tried your suggestion and it works for the first record only but the other records that come in start at a higher number? Is there something I am doing wrong?


Thanks,
Felix

 

[SgtJarrow]

The only way to "reset" an autonumber field is for the table to be empty and to do a compact on the database....

Otherwise, the highest used number will always be retained....

That being said, here comes the great big WHY???????
An autonumber field should only be used as a unique identifier for your record.....it should never have any bearing on your data......

So if the autonumber starts at 1 or 256,742 should make no difference...... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[ffleitas]

Thanks for responding. The reason for me to place this autonumber is for visual use as a counter 001, 002, etc.

There must be another way for me to place counters on a form's detail section. Let me know?

Thanks,
Felix

 

[SgtJarrow]

So in the form, you have a field that contains a one-up number just for visual purposes???? Simple enough, you don't need the autonumber field reset to zero for this.....

Check out faq701-1155 and faq701-896

The first is an updated version of the second...but I have got both to successfully work....

Let me know if you have any questions... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[ffleitas]

Hi again,

Last question. How do I call the function ResetCounter() from my form when I open it?


Thanks,
Felix