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 |
#11
|
|||
|
|||
To Autonumber or not to Autonumber...
I would recommend against making this the default value unless you are in a
single user environment. If in a multi-user environment, if more than one user creates a record at the same time, they will likely both get the same number. Instead, use the forms BeforeUpdate event to set the value of that field. This way, that number will not be determined until just before the record is saved, virtually eliminating the chance of duplication. HTH Dale ellsanto wrote: Perfect. Thanks, Dave. I modified the line you gave me and inserted it into the data default value property. Works perfectly. I spent more time than I want to admit sifting through suggestions from a bunch of sites. Thanks for the quick easy solution. I strongly disagree with using a table of numbers for this process. Why have to keep up with such a table and why slow the process with an extra disc [quoted text clipped - 47 lines] Thanks in advance for the help! -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#12
|
|||
|
|||
To Autonumber or not to Autonumber...
Klatuu wrote:
That is an entirely different circumstance, Tony. Even for that, I would probably not use a table of available numbers. I would probably use a boolean field to denote the record as deleted and when I wanted a new record to use a previously used number, I would do a DMin filtered for only deleted records. In hindsight that would've been a better solution. We had occasional problems with it. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
#13
|
|||
|
|||
To Autonumber or not to Autonumber...
IMHO the likelihood of a collision between two users (both users
finding the same value using DMAX()) is exactly the same no matter which Form event is chosen to fire the method. What Dale described is the best event to use to fire the method to assure the fewest holes in the generated sequence. Abandoning a record before saving it will not generate a new value. HTH -- -Larry- -- "Dale_Fye via AccessMonster.com" u43991@uwe wrote in message news:97a53b0387439@uwe... I would recommend against making this the default value unless you are in a single user environment. If in a multi-user environment, if more than one user creates a record at the same time, they will likely both get the same number. Instead, use the forms BeforeUpdate event to set the value of that field. This way, that number will not be determined until just before the record is saved, virtually eliminating the chance of duplication. HTH Dale ellsanto wrote: Perfect. Thanks, Dave. I modified the line you gave me and inserted it into the data default value property. Works perfectly. I spent more time than I want to admit sifting through suggestions from a bunch of sites. Thanks for the quick easy solution. I strongly disagree with using a table of numbers for this process. Why have to keep up with such a table and why slow the process with an extra disc [quoted text clipped - 47 lines] Thanks in advance for the help! -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#14
|
|||
|
|||
To Autonumber or not to Autonumber...
Larry,
I disagree. If you make this the default value for that field, it will get computed as soon as the user gets to a new record. If they sit on that record for 10-20 minutes, without saving the record, you have 10-20 minutes for someone else to come in and generate the exact same number. OTOH, if you do it in the BeforeUpdate event, you have the benefit of only seconds (maybe mili-seconds) of time between when you generate it and save the record (almost guaranteeing that you won't get duplicates). Dale Larry Daugherty wrote: IMHO the likelihood of a collision between two users (both users finding the same value using DMAX()) is exactly the same no matter which Form event is chosen to fire the method. What Dale described is the best event to use to fire the method to assure the fewest holes in the generated sequence. Abandoning a record before saving it will not generate a new value. HTH I would recommend against making this the default value unless you are in a single user environment. [quoted text clipped - 17 lines] Thanks in advance for the help! http://www.accessmonster.com/Uwe/For...esign/200906/1 -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#15
|
|||
|
|||
To Autonumber or not to Autonumber...
On Tue, 16 Jun 2009 17:57:32 GMT, "Dale_Fye via AccessMonster.com"
u43991@uwe wrote: I disagree. If you make this the default value for that field, it will get computed as soon as the user gets to a new record. If they sit on that record for 10-20 minutes, without saving the record, you have 10-20 minutes for someone else to come in and generate the exact same number. OTOH, if you do it in the BeforeUpdate event, you have the benefit of only seconds (maybe mili-seconds) of time between when you generate it and save the record (almost guaranteeing that you won't get duplicates). Alternatively, *IF* there are no required fields other than the primary key, you can assign the value in the form's BeforeInsert event and immediately save the record. This should leave only a millisecond or two gap, and has the advantage that the user can actually see the new ID onscreen. -- John W. Vinson [MVP] |
#16
|
|||
|
|||
To Autonumber or not to Autonumber...
Agree, am actually doing that now with one of my applications.
The down side to this is that if another user creates a record immediately after this, and the original user decides to cancel his record (in this case he would have to actually delete it since it has already been created), then you have a hole in your numbering scheme (which for some users is totally inexcusable). Dale John W. Vinson wrote: I disagree. If you make this the default value for that field, it will get computed as soon as the user gets to a new record. If they sit on that [quoted text clipped - 4 lines] seconds (maybe mili-seconds) of time between when you generate it and save the record (almost guaranteeing that you won't get duplicates). Alternatively, *IF* there are no required fields other than the primary key, you can assign the value in the form's BeforeInsert event and immediately save the record. This should leave only a millisecond or two gap, and has the advantage that the user can actually see the new ID onscreen. -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#17
|
|||
|
|||
To Autonumber or not to Autonumber...
Isn't that assessment ("inexcusable") a result of the user trying to "use a
chainsaw to drive nails?" Autonumbers are ... autonumbersg! Regards Jeff Boyce Microsoft Office/Access MVP "Dale_Fye via AccessMonster.com" u43991@uwe wrote in message news:97bbbbd46c81b@uwe... Agree, am actually doing that now with one of my applications. The down side to this is that if another user creates a record immediately after this, and the original user decides to cancel his record (in this case he would have to actually delete it since it has already been created), then you have a hole in your numbering scheme (which for some users is totally inexcusable). Dale John W. Vinson wrote: I disagree. If you make this the default value for that field, it will get computed as soon as the user gets to a new record. If they sit on that [quoted text clipped - 4 lines] seconds (maybe mili-seconds) of time between when you generate it and save the record (almost guaranteeing that you won't get duplicates). Alternatively, *IF* there are no required fields other than the primary key, you can assign the value in the form's BeforeInsert event and immediately save the record. This should leave only a millisecond or two gap, and has the advantage that the user can actually see the new ID onscreen. -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#18
|
|||
|
|||
To Autonumber or not to Autonumber...
On Wed, 17 Jun 2009 11:48:31 GMT, "Dale_Fye via AccessMonster.com"
u43991@uwe wrote: Agree, am actually doing that now with one of my applications. The down side to this is that if another user creates a record immediately after this, and the original user decides to cancel his record (in this case he would have to actually delete it since it has already been created), then you have a hole in your numbering scheme (which for some users is totally inexcusable). Yep. It has the same disadvantage as the builtin autonumber in this regard. The only solution I've seen is simply to disallow deletion. Once a record is created it's there for good, perhaps with a VOID flag and perhaps even a field to explain why the record was voided. -- John W. Vinson [MVP] |
#19
|
|||
|
|||
To Autonumber or not to Autonumber...
Oops, my bad! I didn't follow closely enough and thought that Autonumbers
were the topic... Regards Jeff Boyce Microsoft Office/Access MVP "Jeff Boyce" wrote in message ... Isn't that assessment ("inexcusable") a result of the user trying to "use a chainsaw to drive nails?" Autonumbers are ... autonumbersg! Regards Jeff Boyce Microsoft Office/Access MVP "Dale_Fye via AccessMonster.com" u43991@uwe wrote in message news:97bbbbd46c81b@uwe... Agree, am actually doing that now with one of my applications. The down side to this is that if another user creates a record immediately after this, and the original user decides to cancel his record (in this case he would have to actually delete it since it has already been created), then you have a hole in your numbering scheme (which for some users is totally inexcusable). Dale John W. Vinson wrote: I disagree. If you make this the default value for that field, it will get computed as soon as the user gets to a new record. If they sit on that [quoted text clipped - 4 lines] seconds (maybe mili-seconds) of time between when you generate it and save the record (almost guaranteeing that you won't get duplicates). Alternatively, *IF* there are no required fields other than the primary key, you can assign the value in the form's BeforeInsert event and immediately save the record. This should leave only a millisecond or two gap, and has the advantage that the user can actually see the new ID onscreen. -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#20
|
|||
|
|||
To Autonumber or not to Autonumber...
Dale, you're right, of course. Thanks for catching and correcting my
blunder. I made an illogical assumption and fired from the hip. I actually use the BeforeUupdate or BeforeInsert event. A medication I take has a reputed side effect of impairing presence of mind. At least the side effect is working. -- -Larry- -- "Dale_Fye via AccessMonster.com" u43991@uwe wrote in message news:97b26205f8f2f@uwe... Larry, I disagree. If you make this the default value for that field, it will get computed as soon as the user gets to a new record. If they sit on that record for 10-20 minutes, without saving the record, you have 10-20 minutes for someone else to come in and generate the exact same number. OTOH, if you do it in the BeforeUpdate event, you have the benefit of only seconds (maybe mili-seconds) of time between when you generate it and save the record (almost guaranteeing that you won't get duplicates). Dale Larry Daugherty wrote: IMHO the likelihood of a collision between two users (both users finding the same value using DMAX()) is exactly the same no matter which Form event is chosen to fire the method. What Dale described is the best event to use to fire the method to assure the fewest holes in the generated sequence. Abandoning a record before saving it will not generate a new value. HTH I would recommend against making this the default value unless you are in a single user environment. [quoted text clipped - 17 lines] Thanks in advance for the help! http://www.accessmonster.com/Uwe/For...lesdbdesign/20 0906/1 -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
Thread Tools | |
Display Modes | |
|
|