Append a letter to autonumber?
- Thread starter LLB
- Start date
- Local time
- Today, 12:16
- Joined
- Feb 28, 2001
- Messages
- 30,568
Ken's answer is right but doesn't tell you the why of the why.
An autonumber is a special case of a LONG in Access. It is generated by touching the recordset of the underlying table with an AddNew function (whether YOU do it in VBA directly on the recordset or a form or query does it behind the scenes.) If you abort the operation before you do the Update, it is too late for the thing in the TableDef that tracks the next autonumber.
Therefore, autonumbers are not continuously numbered. They can have gaps. They are, essentially, unpredictable.
When you ask to put a letter before, after, or in the middle of an autonumber sequence, it implies that the autonumber has some other meaning than just a record identifier. Even if the letter is constant, the fact that you think you need it means you have a use for it somewhere.
When that is the case, you usually want predictability for the number. And therein lies your difficulty. Autonumbers are inherently not predictable. Now, here is where you have a couple of choices, neither of which is necessarily nice.
First, if the letter is a constant (i.e. ALWAYS 'E') then why do you care? Adjust your attitude.
Second, if the letter is NOT a constant then you cannot use autonumbering to define this item at all. Because an autonumber MUST be independent of any and all other fields in the record. More technically, the other fields of the record are identified by the autonumber, not the other way around. This is a normalization thing, when examined at its most basic level. Fields in a record either depend on their prime keys or shouldn't be in the record. THAT rule...
In the second case, you need to consider a way to determine something like the maximum number assigned among all records with the same letter. Then you have to recognize that numbers and letters are interpreted differently, so having a key based on letters and numbers means at least a two-part compound primary key. Legal - but trickier to manage because of issues in destructive interference. Like, if two users attempt to add a record with the same letter key at the same time, only one of them will correctly add the record. The other adds a duplicate.
Search this forum for non-autonumber keys.
An autonumber is a special case of a LONG in Access. It is generated by touching the recordset of the underlying table with an AddNew function (whether YOU do it in VBA directly on the recordset or a form or query does it behind the scenes.) If you abort the operation before you do the Update, it is too late for the thing in the TableDef that tracks the next autonumber.
Therefore, autonumbers are not continuously numbered. They can have gaps. They are, essentially, unpredictable.
When you ask to put a letter before, after, or in the middle of an autonumber sequence, it implies that the autonumber has some other meaning than just a record identifier. Even if the letter is constant, the fact that you think you need it means you have a use for it somewhere.
When that is the case, you usually want predictability for the number. And therein lies your difficulty. Autonumbers are inherently not predictable. Now, here is where you have a couple of choices, neither of which is necessarily nice.
First, if the letter is a constant (i.e. ALWAYS 'E') then why do you care? Adjust your attitude.
Second, if the letter is NOT a constant then you cannot use autonumbering to define this item at all. Because an autonumber MUST be independent of any and all other fields in the record. More technically, the other fields of the record are identified by the autonumber, not the other way around. This is a normalization thing, when examined at its most basic level. Fields in a record either depend on their prime keys or shouldn't be in the record. THAT rule...
In the second case, you need to consider a way to determine something like the maximum number assigned among all records with the same letter. Then you have to recognize that numbers and letters are interpreted differently, so having a key based on letters and numbers means at least a two-part compound primary key. Legal - but trickier to manage because of issues in destructive interference. Like, if two users attempt to add a record with the same letter key at the same time, only one of them will correctly add the record. The other adds a duplicate.
Search this forum for non-autonumber keys.
Thank you very much for your help. I do now understand why the AutoNumber is the way it is. I was able to concatenate the "E" to the AutoNumber field with a query, and even update the table with a second field with the new lettered number. But that means having to run an update query. I played around with a field on the form, trying to get it to concatenate them as a default value into the second field on the table, but with no luck. Maybe if I explain my purpose you can suggest a better option.
Simply put, my office takes in 3 types of project work. I currently have in place a project database that tracks all the Type "A" projects but nothing for the Type "B" and Type "C" work. I have been asked to expand the database to include all the project work. Which would be fairly simple by adding a Type table to separate the projects, except... the project leads for the 3 types of projects would like to keep the project numbers for their type of project sequential. i.e.: there would be an "A1000", "B1000" and "C1000" project. The reason I want to keep them in one database is that all the data collected for each type of project is the same and there is common reporting for planning and scheduling of work hours that need to come from all 3 types onto one report.
I hope this is enough to give you an idea of what I'm trying to do. Any suggestions would be welcome.
Thanks, Lori
Simply put, my office takes in 3 types of project work. I currently have in place a project database that tracks all the Type "A" projects but nothing for the Type "B" and Type "C" work. I have been asked to expand the database to include all the project work. Which would be fairly simple by adding a Type table to separate the projects, except... the project leads for the 3 types of projects would like to keep the project numbers for their type of project sequential. i.e.: there would be an "A1000", "B1000" and "C1000" project. The reason I want to keep them in one database is that all the data collected for each type of project is the same and there is common reporting for planning and scheduling of work hours that need to come from all 3 types onto one report.
I hope this is enough to give you an idea of what I'm trying to do. Any suggestions would be welcome.
Thanks, Lori
KenHigg
Registered User
- Local time
- Today, 13:16
- Joined
- Jun 9, 2004
- Messages
- 13,291
What you're trying to do can be done, but it will be a nightmare...
I would make an old project number fld in the new table, move the existing project numbrs there, append all the new stuff over, putting A, b & C in where appropriate... Then let the new project number use the new project id autonumber fld...
kh
I would make an old project number fld in the new table, move the existing project numbrs there, append all the new stuff over, putting A, b & C in where appropriate... Then let the new project number use the new project id autonumber fld...
kh
Lori,
Use the BeforeInsert event of your form. Since you know the Project Prefix,
you can use the DMax + 1 method to generate your number.
But, I would still keep the two fields split ... Project & SequenceNumber
Wayne
Use the BeforeInsert event of your form. Since you know the Project Prefix,
you can use the DMax + 1 method to generate your number.
Code:
Me.NewNumber = Me.Project & Nz(DMax("[FieldID]", "YourTable", "[Project] = '" & Me.Project & "'"), 0) + 1But, I would still keep the two fields split ... Project & SequenceNumber
Wayne
