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
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
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 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.

"Gerald Stanley" <(E-Mail Removed)> wrote in message
news:1be4b01c4216a$ad56bf90$(E-Mail Removed)...
> 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
> 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
> 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
> >.
> >

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
>-----Original Message-----
>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.
>
>"Gerald Stanley" <(E-Mail Removed)> wrote in message
>news:1be4b01c4216a$ad56bf90$(E-Mail Removed)...
>> 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
>> 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
>> 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
>> >.
>> >
>
>
>.
>

"Phil Hunt" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> If it is a multi user (Lan application) system, it is pretty tricky to
> use

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.

"Tim Ferguson" <(E-Mail Removed)> wrote in message
news:Xns94CABF83986C4garbleme4455656@207.46.248.16...
> "Phil Hunt" <(E-Mail Removed)> wrote in
> news:(E-Mail Removed):
>
> > If it is a multi user (Lan application) system, it is pretty tricky to
> > use
>
> 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
>

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


"Tim Ferguson" <(E-Mail Removed)> wrote in message
news:Xns94CABF83986C4garbleme4455656@207.46.248.16...
> "Phil Hunt" <(E-Mail Removed)> wrote in
> news:(E-Mail Removed):
>
> > If it is a multi user (Lan application) system, it is pretty tricky to
> > use
>
> 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
>

"=?Utf-8?B?TWlrZXk=?=" <(E-Mail Removed)> wrote in
news:9F7478B6-116D-459D-86FA-(E-Mail Removed):

> Is it actually possible to do what I was saying though - i.e. change
> the data type of the field to number

Yes

> and then back to autonumber?

No


Tim F

"Phil Hunt" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> I coded something like that before, but wrapped within a transaction.

Why?


Tim F

"TC" <(E-Mail Removed)> wrote in news:407cc2a3$(E-Mail Removed):

> Unfortunately, none of those schemes will guarantee what the OP wants
> - ie. no gaps in the numbers -

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

B Wishes


Tim F