If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Make a present number field that automatically increments by 1
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 |
#2
|
|||
|
|||
Make a present number field that automatically increments by 1
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Chris" wrote in message news 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 |
#3
|
|||
|
|||
Make a present number field that automatically increments by 1
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? "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Chris" wrote in message news 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 |
#4
|
|||
|
|||
Make a present number field that automatically increments by 1
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Chris" wrote in message ... 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? "Allen Browne" wrote: 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" wrote in message news 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 |
#5
|
|||
|
|||
Make a present number field that automatically increments by 1
Chris wrote:
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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
Thread Tools | |
Display Modes | |
|
|