Make a present number field that automatically increments by 1

[Chris]

I am converting an excel database to Access, a rather large one, that has
clients assigned with a unique id that start at 1000000001 and currently ends
at 1000005623. I need this number to continue to automatically increment by
one, and always remain in the same digit format (same number of digits b/n
1000000001 and 1999999999.

I am fairly new in setting this up in Access, however, can not figure out
how to do this, and still keep the numbers we already have in place. Can
anyone help please?

Thank you,
Chris

 

[Allen Browne]

Add this line to the BeforeUpdate event procedure of your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[ID] = Nz(DMax("ID", "MyTable"), 1000000000) + 1
End Sub

I've suggested Form_BeforeUpdate because that's the last possible moment
before the record is saved, so it reduces the chance that 2 users adding
records at the same time will be given the same number.

 

[Chris]

A couple of notes, I have not started the form on this yet, although I will
be using the same template type style from the Excel Dbase, I was wondering
if there was a way to do this in the table directly, as this is how the
information will be entered first.

After the form is complete however, there will be multiple users, that may
be entering at the same time, even at the end wouldn't it cause an error on
one of the users end, if it assigned, or tried to assign a same number as is
in the primary key?

Add this line to the BeforeUpdate event procedure of your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[ID] = Nz(DMax("ID", "MyTable"), 1000000000) + 1
End Sub

I've suggested Form_BeforeUpdate because that's the last possible moment
before the record is saved, so it reduces the chance that 2 users adding
records at the same time will be given the same number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am converting an excel database to Access, a rather large one, that has
clients assigned with a unique id that start at 1000000001 and currently
ends
at 1000005623. I need this number to continue to automatically increment
by
one, and always remain in the same digit format (same number of digits b/n
1000000001 and 1999999999.

I am fairly new in setting this up in Access, however, can not figure out
how to do this, and still keep the numbers we already have in place. Can
anyone help please?

Thank you,
Chris

 

[Allen Browne]

You could set up the table so it has an AutoNumber field. You can then use
an Append query to import the data from Excel, and Access will continue
automatically numbering from there.

However, this will not give you a sequential number sequence. It gives
unique numbers, but if someone starts adding a record and aborts the entry,
the aborted number will not be reused. If you just need a unique identifier,
by all means use the AutoNumber.

If you do have a multi-user situation and must roll your own numbering
system, you have some code to write. You will need to create another table
that just stores the highest number assigned so far. In the BeforeUpdate
event procedure of your form, you will need to lock this table, increment
it, grab the new number, assign it to your record, save the record, and
release the lock. You need to add error handling that introduces random
delays to cope with the multi-user clashes, and some way to error out with a
meaningful message if a number cannot be acquired in a specific number of
retries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

A couple of notes, I have not started the form on this yet, although I will
be using the same template type style from the Excel Dbase, I was
wondering
if there was a way to do this in the table directly, as this is how the
information will be entered first.

After the form is complete however, there will be multiple users, that may
be entering at the same time, even at the end wouldn't it cause an error
on
one of the users end, if it assigned, or tried to assign a same number as
is
in the primary key?

Add this line to the BeforeUpdate event procedure of your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[ID] = Nz(DMax("ID", "MyTable"), 1000000000) + 1
End Sub

I've suggested Form_BeforeUpdate because that's the last possible moment
before the record is saved, so it reduces the chance that 2 users adding
records at the same time will be given the same number.

I am converting an excel database to Access, a rather large one, that
has
clients assigned with a unique id that start at 1000000001 and
currently
ends
at 1000005623. I need this number to continue to automatically
increment
by
one, and always remain in the same digit format (same number of digits
b/n
1000000001 and 1999999999.

I am fairly new in setting this up in Access, however, can not figure
out
how to do this, and still keep the numbers we already have in place.
Can
anyone help please?

Thank you,
Chris

 

[Rick Brandt]

A couple of notes, I have not started the form on this yet, although
I will be using the same template type style from the Excel Dbase, I
was wondering if there was a way to do this in the table directly, as
this is how the information will be entered first.

After the form is complete however, there will be multiple users,
that may be entering at the same time, even at the end wouldn't it
cause an error on one of the users end, if it assigned, or tried to
assign a same number as is in the primary key?

The window of time for a multi-user collision is very small when using
BeforeUpdate. Unless you have LOTS of users doing inserts at the same time the
odds are very low.

Note that you need to test for NewRecord when using BeforeUpdate or else the
value will be reassigned every time you edit an existing record.