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
|
|||
|
|||
Thank You
Ken, Keith & Arvin,
I can't thank you enough for all the help you provided below. I'll be working on this in the next day or two and may have additional posts to this one. For now you guys are my Hero's! Tara KenSheridan via AccessMonster.com wrote: Of the approaches which have been suggested Roger Carlson's is simple 19-Jan-10 Of the approaches which have been suggested Roger Carlson's is simple and reliable, but has the drawback that if there is a conflict the number is incremented only when an attempt to save the record is made. This would seem to go against your requirement that 'it needs to show the user the Registration # they are currently working on'. Keith's approach overcomes this by saving the new record immediately before other data is entered, but this means that Nulls cannot be disallowed in the non-key fields by setting their Required property to True, unless each is also given a DefaultValue property, so this could be a problem. Arvin's solution, while seeding the start number, has the disadvantage, as Keith has pointed out, of not guaranteeing an unbroken sequence (or a sequence at all for that matter), for which an autonumber should never be used, an autonumber being intended to ensure arbitrary unique values and nothing else (which is presumably why Microsoft changed it from 'counter' after version 2). A solution which covers all of these points, and also allows the next number used to be reseeded at any time can be found at: http://community.netscape.com/n/pfx/...g=ws-msdevapps It is a little more complex than the other solutions as it stores the latest number in a separate database which is transparently opened and updated exclusively in code to get the next number. Consequently only one user can get the same number, but without the need to save the current record immediately, so non-key fields can have their Required property as True in the table's design. Ken Sheridan Stafford, England Tara Metzger wrote: -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201001/1 Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Dynamic ASP.NET Excel Workbooks In C# http://www.eggheadcafe.com/tutorials...excel-wor.aspx |
#12
|
|||
|
|||
Custom Sequential Numbering
"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:a25b114e44fa5@uwe... Arvin's solution, while seeding the start number, has the disadvantage, as Keith has pointed out, of not guaranteeing an unbroken sequence (or a sequence at all for that matter), for which an autonumber should never be used, an autonumber being intended to ensure arbitrary unique values and nothing else Thanks for confirming that Ken, I thought I was going a bit doo-lally on that one for a while Keith. |
#13
|
|||
|
|||
Custom Sequential Numbering -- Why Bother?
I couldn't agree more, Roger, but "need" can in some cases be a legal
requirement. Things might have changed since I retired, though knowing how slowly the wheels of legislation grind I doubt it, but in my own field of work I believe it was a legal requirement that all applications made to the authority in our quasi-judicial capacity must be numbered sequentially and immutably in order of date of receipt, and registered as such. The law may be behind the technology, but its still the law until changed, I'm afraid. Ken Sheridan Stafford, England Roger Carlson wrote: This is perhaps slightly off-topic, but since your question has been thoroughly answered (with 4 separate solutions, no less) I'd like to challenge the necessity of having an *unbroken* sequential number -- or a sequential number at all. Sequential numbering is really a paper-based security system. Sales Order, Purchase Order, Checks, and the like are numbered sequentially to make sure that someone doesn't steal one from the middle of a pile of them. Sequential numbers highlight this immediately because it's easy to see when a number has been skipped. However, when your system is electronic, paper-based security is as useful as a screen door in a submarine. It does no good, causes other design complications, and might possibly cause harm. I know there are customers who believe they "need" sequential numbering for some reason, but I always try to dissuade them. Sometimes it works sometimes it doesn't, but I try. Even though my sample was suggested as a good solution, to my mind Arvin's suggestion of an Autonumber field is really the best solution and forget the sequential gaps. They aren't worth worrying over. Hello, [quoted text clipped - 22 lines] Putting Twitter Realtime Search to Work http://www.eggheadcafe.com/tutorials...-realtime.aspx -- Message posted via http://www.accessmonster.com |
#14
|
|||
|
|||
Custom Sequential Numbering
"Keith Wilby" wrote in message ... OK I may well be being a bit thick here but the OP stated "This is a 7-digit number (no alphas) where 8146614 is my first Registration #. Each time a Registration # is entered it is to increase by 1 ..." Exactly. I understand your method of creating the *first* number, 8146614, but subsequent numbers risk being lost using AutoNumber don't they? Not unless a number is deleted. That can always happen. There is never a 100% guarantee that a record will not be deleted. Remember what I said. The largest an autonumber can be is 2,147,483,647. that 10 digits. 8,146,614 is a seed, there won't be any holes unless they are created, by adding a higher seed sometime later. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
#15
|
|||
|
|||
Custom Sequential Numbering
"KenSheridan via AccessMonster.com" u51882@uwe wrote in message news:a25b114e44fa5@uwe... Arvin's solution, while seeding the start number, has the disadvantage, as Keith has pointed out, of not guaranteeing an unbroken sequence (or a sequence at all for that matter), for which an autonumber should never be used, an autonumber being intended to ensure arbitrary unique values and nothing else (which is presumably why Microsoft changed it from 'counter' after version 2). If an incremental autonumber is used, unless a number is deleted, or someone reseeds the field again with a higher number, there will not be an unbroken sequence. If you mean that a number can be "lost" by starting and discarding a record, yes that can happen, but that's the same as deleting a record. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
#16
|
|||
|
|||
Custom Sequential Numbering
On Thu, 21 Jan 2010 00:04:17 -0500, "Arvin Meyer [MVP]"
wrote: Not unless a number is deleted. That can always happen. There is never a 100% guarantee that a record will not be deleted. Remember what I said. The largest an autonumber can be is 2,147,483,647. that 10 digits. 8,146,614 is a seed, there won't be any holes unless they are created, by adding a higher seed sometime later. Is that correct, Arvin? IME if you even *start* adding a new record manually (on a form, or directly in a table), an autonumber is generated; if you hit ESC or otherwise cancel the addition before it's saved to disk, the autonumber gets used up and skipped... leaving a gap. Has this changed without my noticing? -- John W. Vinson [MVP] |
#17
|
|||
|
|||
Custom Sequential Numbering
"Arvin Meyer [MVP]" wrote in message
... "KenSheridan via AccessMonster.com" u51882@uwe wrote in message news:a25b114e44fa5@uwe... Arvin's solution, while seeding the start number, has the disadvantage, as Keith has pointed out, of not guaranteeing an unbroken sequence (or a sequence at all for that matter), for which an autonumber should never be used, an autonumber being intended to ensure arbitrary unique values and nothing else (which is presumably why Microsoft changed it from 'counter' after version 2). If an incremental autonumber is used, unless a number is deleted, or someone reseeds the field again with a higher number, there will not be an unbroken sequence. If you mean that a number can be "lost" by starting and discarding a record, yes that can happen, but that's the same as deleting a record. IMHO dirtying a record but not saving it is not the same as deleting a saved one, and users are going to wonder why the sequence is broken when they haven't deleted anything. In fact, they're not just going to wonder but they're going to complain quite loudly! I also seem to remember reading somewhere, although I can't provide any evidence, that even if you don't force Access to discard an AutoNumber, it is still possible to have a broken sequence in an incremental AutoNumber. FWIW I don't allow deletions in systems that require sequential numbering, rather I give the option to mark a record as "deleted" and then query accordingly. Keith. |
#18
|
|||
|
|||
Custom Sequential Numbering
"John W. Vinson" wrote in message
news On Thu, 21 Jan 2010 00:04:17 -0500, "Arvin Meyer [MVP]" wrote: Not unless a number is deleted. That can always happen. There is never a 100% guarantee that a record will not be deleted. Remember what I said. The largest an autonumber can be is 2,147,483,647. that 10 digits. 8,146,614 is a seed, there won't be any holes unless they are created, by adding a higher seed sometime later. Is that correct, Arvin? IME if you even *start* adding a new record manually (on a form, or directly in a table), an autonumber is generated; if you hit ESC or otherwise cancel the addition before it's saved to disk, the autonumber gets used up and skipped... leaving a gap. Has this changed without my noticing? I think that Arvin is recommending this method with the caveat that the user may create gaps by either deletion or by discarding a new but unsaved record. To me that does not satisfy the OP's requirement. Even if you disallow deletions you'll never stop users from creating new records and then changing their minds, and why should you? Keith. |
#19
|
|||
|
|||
Custom Sequential Numbering
"John W. Vinson" wrote in message news On Thu, 21 Jan 2010 00:04:17 -0500, "Arvin Meyer [MVP]" wrote: Not unless a number is deleted. That can always happen. There is never a 100% guarantee that a record will not be deleted. Remember what I said. The largest an autonumber can be is 2,147,483,647. that 10 digits. 8,146,614 is a seed, there won't be any holes unless they are created, by adding a higher seed sometime later. Is that correct, Arvin? IME if you even *start* adding a new record manually (on a form, or directly in a table), an autonumber is generated; if you hit ESC or otherwise cancel the addition before it's saved to disk, the autonumber gets used up and skipped... leaving a gap. Has this changed without my noticing? Is hitting escape, not akin to deleting a record? With an Access form, or any bound form, the first character typed creates a record, does it not? ESC is what one would do to delete that record. I think we are saying the same thing in different ways. Autonumbers cannot be reused, whether the record is started or deleted. Once used, it's gone. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
#20
|
|||
|
|||
Custom Sequential Numbering
"Keith Wilby" wrote in message ... IMHO dirtying a record but not saving it is not the same as deleting a saved one, and users are going to wonder why the sequence is broken when they haven't deleted anything. In fact, they're not just going to wonder but they're going to complain quite loudly! Dirtying a record has the identical effect as deleting it. To use the paper analogy, once a restaurant check has been written on, it's dirtied, you can: 1. Throw it away 2. Leave it the way it is. 3. Change the data (to some degree) In all of those cases, the number is used. Only the first leaves a visible gap. I also seem to remember reading somewhere, although I can't provide any evidence, that even if you don't force Access to discard an AutoNumber, it is still possible to have a broken sequence in an incremental AutoNumber. I don't think so except for the now fixed bug that allowed autonumbers to attempt reuse. FWIW I don't allow deletions in systems that require sequential numbering, rather I give the option to mark a record as "deleted" and then query accordingly. Good move. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
Thread Tools | |
Display Modes | |
|
|