Jim: You are correct. The way I get around this situation is not to use it with a form in the "ENTER" mode; the user is prompted if he wants a record added, if the answer is "YES", the record is added with the sequential auto number and other columns loaded with default information; the record is then shown to the user in a form in the "EDIT" mode. This way, if the user wants to, he can either "UPDATE" the record with new information, "VOID" the record without creating a vacancy (leaving a trail) or "DELETE" the record creating a vacancy (no trail); by taking the delete option out of the form, you avoid the last case. Again, you have to carefully decide if this method will work for your particular application; as I indicated, I only use this approach for specific situations.
Javier Valencia, PE President Valencia Technology Group, L.L.C. 14315 S. Twilight Ln., Suite #14 Olathe, KS 66062-4571 (913)829-0888 (913)649-2904 FAX -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of James (Jim) Bentley Sent: Saturday, July 13, 2002 7:45 PM To: [EMAIL PROTECTED] Subject: RE: Edited autonumber Javier, You stated: "The only way for the auto number to have vacancies is if a record is deleted" In addition if you are using a form to add a record and abandon the form without saving the data the autonumber will have incremented and you will have a gap in the sequence. -- Jim Bentley American Celiac Society [EMAIL PROTECTED] - email (973) 325-8837 voice (973) 669-8808 Fax ---- "Javier Valencia" <[EMAIL PROTECTED]> wrote: > It depends on what the requirements of your application are; > auto number > columns work best when used to signal entry order, such as checking > account. > The only way for the auto number to have vacancies is if a record > is > deleted; this is true for any numbering scheme unless you have > sliding > (dynamic) numbering scheme that moves the numbers up when one > is deleted. > You can avoid this by making the record "VOID" such as you would > do with a > checking account. > Many numbering schemes that I have used in the past used increments > larger > than 1, so in the future you could add a record in between 2 > existing > records. I have a street inventory in which street segment have > a unique > sequential ID based on the street name and relative order, sometimes, > a > segment is subdivided into 2 or more segments and in order to > maintain the > "chain" we rename the new segments with smaller increments. > For example Main St. can have segments Main05, Main10, Main15, > etc., going > from west to east, at some future time segment Main10 can be > divided in 2 > smaller segments Main08 and Main12. This scheme indicates the > physical > location on the field and allows printing the records in order, > but not the > sequence in which the information was entered. For this type > of numbering, I > do not use an auto number column. I use auto number columns > for information > that is sequentially entered such as Work Order Numbers, Complaints, > Checks, > etc. > > Javier Valencia, PE > President > Valencia Technology Group, L.L.C. > 14315 S. Twilight Ln., Suite #14 > Olathe, KS 66062-4571 > (913)829-0888 > (913)649-2904 FAX > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of MikeB > Sent: Thursday, July 11, 2002 9:55 AM > To: [EMAIL PROTECTED] > Subject: Re: Edited autonumber > > SEQUENTIAL is the reason it should not be used to have real > world meaning. > The type of number you describe can easily be accommodate independent > of the > autonumber generator. Because of the way autonumber is generated, > it is > likely to have vacancies in the sequence. That is the reason > you should > develop your own number generation when the numbers must have > an unending > sequence. With your own system, you can accommodate vacancies > and fill them > up or decrement a number when it fails. Autonumber is best > used as an > 'under the covers' linking column. Vacancies are not very well > received as > a check number, invoice number, work order number, etc. So if > a vacancy > exists or you have to decrement because of an incomplete transaction > that > forces you to fiddle with your autonumber, you would have better > safeguards > in place if you code your own from the start. My experience > dictates I > follow this rule. Your preferences and experience may guide > you > differently. > > > ----- Original Message ----- > From: "Javier Valencia" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, July 10, 2002 9:51 PM > Subject: RE: Edited autonumber > > > > This is not necessarily true. The auto number column can generate > numbers > > that have a user-defined format. I have an application in > which the > computer > > generates sequential numbers that are assigned to work order > numbers with > > format 02-00001 and so on, where the first 2 numbers represent > the last 2 > > digit of the year and the last numbers are sequential (and > unique) number > > assigned by the system. I have housekeeping software that > allows the user > to > > go back and forth between years by changing the format of > the first 2 > digits > > of the auto number. This is particularly handy at the beginning > of a new > > year when users sometimes have to enter old work orders that > have the > > previous year prefix, the system determines what the next > sequential > number > > should be and assigns it to the new record and then goes back > to the next > > sequential number for the current year. This is an ideal application > of > the > > auto number feature. > > > > Javier Valencia, PE > > President > > Valencia Technology Group, L.L.C. > > 14315 S. Twilight Ln., Suite #14 > > Olathe, KS 66062-4571 > > (913)829-0888 > > (913)649-2904 FAX > > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > > Behalf Of MikeB > > Sent: Wednesday, July 10, 2002 7:15 PM > > To: [EMAIL PROTECTED] > > Subject: Re: Edited autonumber > > > > Autonumbering should never be used in the context to have > any real world > > meaning to the user. > > If you need a number that is to hold a meaning, you should > develop your > own > > numbering scheme. > > > > > > ----- Original Message ----- > > From: "Nicky Avery" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, July 10, 2002 5:12 PM > > Subject: Re: Edited autonumber > > > > > > > David Ebert wrote: > > > > > > >What is the effect on autonumbered columns if a value is > manually > edited? > > > > > > > > > > > > > > > David, > > > > > > The autonumbering depends on its own housekeeping to know > the value of > > > the last number it assigned. If you allow renumbering to > a lower number, > > > you may end up with gaps in the run (not necessarily an > issue) and, if > > > someone renumbers the records incorrectly, mismatches. If > the numbers > > > are higher, you can end up with a mess since the autonumbering > will not > > > know a number is already used and will plough on regardless > so you then > > > have plenty of opportunites for mismatches. If the numbers > are higher > > > and you define the autonum column as unique, you will likely > break your > > > app when the attempt to reissue the number is rejected. > > > > > > Nicky > > > > > > > > > ================================================ > > > TO SEE MESSAGE POSTING GUIDELINES: > > > Send a plain text email to [EMAIL PROTECTED] > > > In the message body, put just two words: INTRO rbase-l > > > ================================================ > > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > > ================================================ > > > TO SEARCH ARCHIVES: > > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ > > TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ > > TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
