Auto text based on drop menu selection

[mandykoonts]

My database houses four types of key documents: bylaws, resolutions, policies & procedures. We want each document's number to be its "ID" in the database however, some of them have the same number (policy 001, bylaw 001, etc.). There are hundreds of them so it is too late to change the document numbering convention. Therefore I would like the ID field to insert text before the ID number (but in the ID number field) to define a new ID number. For instance:

If the ID number was 001 and the type selected from the list box was Bylaw, I want BYL to be inserted before the ID number, creating a new ID number of BYL-001 (or if it were a policy POL-001 or a resolution RES-001, etc).

Does this make sense and is it possible?

Thanks.

[June7]

These documents are all in the same table? How many different types?

The alpha prefix can be concatenated with the ID value in an expression.

[rpeare]

are you using a bound form or an unbound form?

is there ANY possibility that any of these bylaw, polciy, resolution numbers can change over time? if there is you do not want to use it as your primary key, better to use an autonumber but also have this generated, internal, identifier as well.

[mandykoonts]

Yes, the form is bound to a table. No, the numbers will never change. Expressions are new to me - what would that look like? Thanks so much. I'm trying to avoid creating 4 separate databases.

[June7]

I should have read OP more carefully. Only 4 document types is not so bad. An expression could be:

Switch([Type]="bylaws","BYL", [Type]="resolutions","RES", [Type]="policies","POL", [Type]="procedures","PRO") & "-" & [ID]

Use your actual field name in place of Type.

[rpeare]

I don't like doing this sort of thing on bound forms but here's an example:

mandykoonts.zip

I do not like displaying data in tables other than exactly how it is, it's just my preference. June's suggestion of doing it at the table level is just as valid, it's really a matter of personal choice. In this example it's displaying the modified document number on the form, I've also left the field it's being built form visible so you can see the numbering system at work.

Note, there aren't any real protections or error traps on this form which you will have to write to prevent bad things from happening, like once a document is put in as a bylaw, if you change it to a procedure, the internal number will not change so in essence you will end up, if you already have a procedure 1, two procedure 1's in your database.

[June7]

I did not mean to imply doing calc in table (possible with Access 2010 up). I doubt it can be done with the Switch function anyway. Do calcs in query or textbox.