AutoNumber emulation

[Henry]

I would like to stay in control and determin myself what Number to add as
index value. Is there way to add unique values just without using the
AutoNumber feature in my index field? AutoNumber is a problem when you need
to restore data...

 

[Klatuu]

You can emulate Autonumbers, but you have to be careful in a multi user
environment. It is easy enough to determine the highest current number and
add 1 to it:

Dim lngNextNum As Long

lngNextNum = Nz(DMax("[KeyField]", "MyTable"),0) + 1

But in a multi user environment, 2 users could get the same number and the
second to try to save will fail. You would have to use some error trapping
and increment the number until you get a good save.

Out of curiosity, what problems have you had restoring data using autonumbers?

 

[Henry]

when I try to restore an old record the autonumbering logic will give the
next available index number and I cannot force access to accept the original
index value. Therefore I use non-autonumbered index fields. The nice thing
about autonumbers though is that it allocates a unique number and what I want
is to immitate this unique number logic without using autonumbering. This way
when I restore data, records do not get overwritten - which can happen when
the record with the highest number is deleted by the user after the data was
backed up and a new record is allocated with the same index. when you restore
now the record gets overwritten.

I hope this sheds some light into my situation....

thanx for looking at my post.

You can emulate Autonumbers, but you have to be careful in a multi user
environment. It is easy enough to determine the highest current number and
add 1 to it:

Dim lngNextNum As Long

lngNextNum = Nz(DMax("[KeyField]", "MyTable"),0) + 1

But in a multi user environment, 2 users could get the same number and the
second to try to save will fail. You would have to use some error trapping
and increment the number until you get a good save.

Out of curiosity, what problems have you had restoring data using autonumbers?
--
Dave Hargis, Microsoft Access MVP

I would like to stay in control and determin myself what Number to add as
index value. Is there way to add unique values just without using the
AutoNumber feature in my index field? AutoNumber is a problem when you need
to restore data...

 

[Erez Mor]

well, i would say it does (shed some light), and 1 way to go around it is ,
if you're deleting records that can turn out to be returned back to the same
place, it's probably better to just mark them as "deleted" (add a new boolean
field "active" or "deleted" to go the other way), then show/hide them from
the user using filters or queries
that way, theill keep all their original values, including the id.
hope that helps, Erez.

when I try to restore an old record the autonumbering logic will give the
next available index number and I cannot force access to accept the original
index value. Therefore I use non-autonumbered index fields. The nice thing
about autonumbers though is that it allocates a unique number and what I want
is to immitate this unique number logic without using autonumbering. This way
when I restore data, records do not get overwritten - which can happen when
the record with the highest number is deleted by the user after the data was
backed up and a new record is allocated with the same index. when you restore
now the record gets overwritten.

I hope this sheds some light into my situation....

thanx for looking at my post.

You can emulate Autonumbers, but you have to be careful in a multi user
environment. It is easy enough to determine the highest current number and
add 1 to it:

Dim lngNextNum As Long

lngNextNum = Nz(DMax("[KeyField]", "MyTable"),0) + 1

But in a multi user environment, 2 users could get the same number and the
second to try to save will fail. You would have to use some error trapping
and increment the number until you get a good save.

Out of curiosity, what problems have you had restoring data using autonumbers?
--
Dave Hargis, Microsoft Access MVP

I would like to stay in control and determin myself what Number to add as
index value. Is there way to add unique values just without using the
AutoNumber feature in my index field? AutoNumber is a problem when you need
to restore data...