Over-riding autonum

[Jen LaForge]

Thanks for the help before I even start...

I'm working in Access and I have right now 1024 records. We have a field
set to Autonum that gives each new record a new file number. The
problem? We have had a number of records deleted and even though our
file number is up to 1033, we only have 1024 records.

I need to know how to fill in the gaps (have 1033 records and 1033 files
(data can be filled in later)) without screwing up the order of the file
numbers. I know this is a long and complicated process and there is no
real reason to do so but I have a boss who doesn't understand this and
just wants things to look pretty.

I'm almost there but I think I'm doing it wrong, so any help would be
appreciated. Thanks again.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[PC Datasheet]

I don't think you will succeed. Autonumber is designed not to reuse numbers. Why
not add another field called FileNum to your table and make it consecutive. When
you add a new record increment FileNum By:
Me!FileNum = DMax("[FileNum]","NameOfTable") + 1

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Jen LaForge" <je**************@fairmont.com> wrote in message
news:40**********************@news.newsgroups.ws.. .

Thanks for the help before I even start...

I'm working in Access and I have right now 1024 records. We have a field
set to Autonum that gives each new record a new file number. The
problem? We have had a number of records deleted and even though our
file number is up to 1033, we only have 1024 records.

I need to know how to fill in the gaps (have 1033 records and 1033 files
(data can be filled in later)) without screwing up the order of the file
numbers. I know this is a long and complicated process and there is no
real reason to do so but I have a boss who doesn't understand this and
just wants things to look pretty.

I'm almost there but I think I'm doing it wrong, so any help would be
appreciated. Thanks again.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Salad]

Jen LaForge wrote:

Thanks for the help before I even start...

I'm working in Access and I have right now 1024 records. We have a field
set to Autonum that gives each new record a new file number. The
problem? We have had a number of records deleted and even though our
file number is up to 1033, we only have 1024 records.

Autonumbers are very useful as keys for a table. I would never use them
for a sequential number. In fact, most people make autonumber fields
invisible. Why give something to a user to confuse them further. Do
you go through and count each record and look for gaps? If you have a
gap, do you REALLY care? Do you search/access your records by
autonumbers? Do you have a REAL need to show users an autonumber? If
not, open up the property sheet for it and hide it by setting the
visible property to NO under the Format tab.

[Reggie]

Jen, I agree with the other post whole heartedly, however if you really,
really, really feel the need to do this there is a way. Open your table in
design view. Select File/Save As from the main menu. Rename the table. Now
design a new query using the original table as the record source for the
query. Add all the fields to the design grid. Select Query/Append Query
from the main menu. Select the new table you just created as the
destination table. Now all of the Append To fields should match each other
in the design grid. Under the Autonumber field delete the Append To field
so that it is blank. Sort your records Ascending for the Autonumber field.
Run the query. Check the new table to ensure the results are what you want.
If so you can delete the original table and rename this table to the
original tables name. Keep in mind the next time you delete a single
record, your numbers will be off again. I still think you should re-think
your reasoning and/or convince your boss to see the light. Hope it helps.

--
Reggie

www.smittysinet.com
----------
"Salad" <oi*@vinegar.com> wrote in message
news:hb*******************@newsread1.news.pas.eart hlink.net...

Jen LaForge wrote:

Thanks for the help before I even start...

I'm working in Access and I have right now 1024 records. We have a field
set to Autonum that gives each new record a new file number. The
problem? We have had a number of records deleted and even though our
file number is up to 1033, we only have 1024 records.

Autonumbers are very useful as keys for a table. I would never use them
for a sequential number. In fact, most people make autonumber fields
invisible. Why give something to a user to confuse them further. Do
you go through and count each record and look for gaps? If you have a
gap, do you REALLY care? Do you search/access your records by
autonumbers? Do you have a REAL need to show users an autonumber? If
not, open up the property sheet for it and hide it by setting the
visible property to NO under the Format tab.