AutoNumber

[dtaylor]

Hello All -
I have a table that is i call a transit table (for temporary data). I have an autonumber field setup.
What I need to do is have the seq number always start at 1 for each new data set. Using autonumber the count does not refresh, just keeps tallying from the previous count of records.
Is there a way to start autonumber at 1 or possibly a function that will do this.

Thanks

Dan

 

[bat17]

One method of doing this is to use the DMax() function to check the previous number used and then to add one to it.
How you do this will depend on how you are saving the data to the table.

HTh

Peter

 

[SydneyGeek]

If you want to reset AutoNumber to 1, you're out of luck. Access always increments the numbers (essential for insuring referential integrity in teh database -- key field values should never be re-used, and AutoNumber is intended only for use with key fields). Do you have a specific reason for needing a numbering system that starts from 1 with every import? If so, please give more details and we may be able to help

Regards

Denis

 

[Dugantrain]

If you delete all of the records out of your table and then do a Compact and Repair on your database, then your Autonum fields will reset themselves to 1. However, I would strongly discourage this as Autonums are not made to be used for sequential numbering; their sole use is to give a unique identity to each record in your database. Use either the simpler DMAX() function or a subquery (if you want the SQL for the subquery, I'll post) to sequentially number your records.

 

[dtaylor]

Thank you - i will give the DMax() function a go.

bat17 - How you do this will depend on how you are saving the data to the table.
Can you qlarify?

My temp table is being populated and cleared on the fly using vba.

Dugantrain - yes please do post the sql for a seq num subqry

Thanks!
Dan

 

[bat17]

in your VBA code use the DMax function to check the last value used in the table and add 1 to it and use this number when you write the data to the table. If you are repeatedly writing the data using a loop you could just create the number on the fly anyway.

Peter

 

[dtaylor]

bat17 - yes i am using a loop
so instead of an autonumber i can just have the field numbered within my current loop - man thats seems to simple > I am already looping each record - **** didn't even think of that!

thanks for knocking the cobwebs around in my head!

Dan