Is it possible to append a letter to the front of an autonumber (which is also my primary key). eg: E568
:rolleyes:

No.

Sorry. Can you do a seperate fld?

Ken

Is it not possible because it is a primary key or just because it is an autonumber?

I suspect it's because it appears to really be an integer data type...

kh

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.

Is it possible to append a letter to the front of an autonumber - Visually, yes. In your table, the letter and autonumber must be separate fields and the autonumber remains as your primary key since it is unique without the addition of a "letter". Obviously the "letter" means something to you so define a field to hold it. You should also define a value list that can be used in a combo to restrict the values and display a meaningful description of what "E" means. If you want to show it next to the autonumber so that it looks like they are the same field, you can concatenate them in your query.

Select LetterField & AutonumberField As ComboField, ....

FYI - you cannot store text characters in a numeric field and you cannot do arithmetic on a text field unless it contains ONLY numeric characters.

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 :)

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

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.


Me.NewNumber = Me.Project & Nz(DMax("[FieldID]", "YourTable", "[Project] = '" & Me.Project & "'"), 0) + 1


But, I would still keep the two fields split ... Project & SequenceNumber

Wayne

The only update query you should need to run is the one that puts the "E" in the existing records. The ProjectType should be a field on your project form and it will be chosen by the user when the project data is entered. Of course as you import each new set of data, you'll need to assign the correct projectType value for them also.

I don't think that anyone else pointed out the obvious. All the data for the different project types should be in the same table. Wayne showed how to use DMax() to find the current maximum value for each project type so there is no problem keeping the numbering sets separate even when all the data is in the same table.

And finally, use an autonumber as your primary key but make a unique index for the ProjectType + ProjectSeq fields so the generated projecNum is always unique. Use the autonumber pk for all relationships.