Change autonumber data type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a table that has an ID field as an autonumber

People create new records through a form, but very frequently they end up cancelling the record (deleting it). The problem is that if they go and make a new record again it will be a number higher - with the amount of deletions etc, this has meant eg the IDs go as follows 1,2,3,4,6,7,11,28,33,34,35,55,70 et

Is there a way that when they do delete a record I can include with the code a preocedure to change the field data type from autonumber to number and then back to autonumber - I have noticed that if I do this manually it seems to reset the autonumber in effect. Will this work

I have thought about having a temporary form which is then submitted to the main table... but the idea seems a bit overwhelming to me

What do you think? Any examples on code if changing the data type will work

Mikey
 
If it is important to your application to have a column in
strict sequence then I would advise that you do not use the
autonumber facility but instead have a query that
determines the max value of the column and adds 1 to it.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi,

I have a table that has an ID field as an autonumber.

People create new records through a form, but very
Click to expand...
frequently they end up cancelling the record (deleting it).
The problem is that if they go and make a new record again
it will be a number higher - with the amount of deletions
etc, this has meant eg the IDs go as follows
1,2,3,4,6,7,11,28,33,34,35,55,70 etc
Is there a way that when they do delete a record I can
Click to expand...
include with the code a preocedure to change the field data
type from autonumber to number and then back to autonumber
- I have noticed that if I do this manually it seems to
reset the autonumber in effect. Will this work?
I have thought about having a temporary form which is then
Click to expand...
submitted to the main table... but the idea seems a bit
overwhelming to me.
 
If it is a multi user (Lan application) system, it is pretty tricky to use
the Max+1 trick as I found out. Due to the some delay in the network or
whatever, it is easy to get duplicate value. I suppose you have to lock the
table somehow when you do this kind of stuff.
 
True. I have always coded defensively by delaying the
retrieval of the sequence number until the last possible
moment and then trapping for the duplicate error.

Gerald Stanley MCSD
 
If it is a multi user (Lan application) system, it is pretty tricky to
use
Click to expand...

My strategy for this is to create an empty record using

"INSERT INTO MyTable (MyNumber) VALUES " & strSomeNumber

and incrementing strSomeNumber until dbFailOnError doesn't fail. Usually it
succeeds first time round, obviously. The strSomeNumber is passed back to
the requesting procedure/ form.

Hope that helps


Tim F
 
I coded something like that before, but wrapped within a transaction. When
it fails, i forgot to rollback and bam! the table locked up. Just be
careful, this kind of thing is kinda hard to test/debug.
 
Unfortunately, none of those schemes will guarantee what the OP wants - ie.
no gaps in the numbers - unless the assignment of the next available #, and
the final saving of all new, updated & deleted records pertaining to that
new #, are performed as an atomic transaction - and that ain't possible in
various cases with Access; eg. when using bound forms.

Thus:
- user A hits Save
- code gets next available #123
- user B hits Save
- code gets #124
- A's save fails (for some reason);
- B's save works.

#123 is now MIA!

Cheers,
TC
 
TC said:
Unfortunately, none of those schemes will guarantee what the OP wants
- ie. no gaps in the numbers -
Click to expand...

Fair cop -- in all the excitement about multiple users I forgot the OP!

B Wishes


Tim F
 
Tim Ferguson said:
Fair cop -- in all the excitement about multiple users I forgot the OP!
Click to expand...

As opposed to the normal case, where the OP forgets about the multiple
users!!

Cheers,
TC
 
Fair cop! Thanks all.

----- TC wrote: -----



As opposed to the normal case, where the OP forgets about the multiple
users!!

Cheers,
TC
 
Back
Top