How to specify Microsoft Access auto number prefix value based on user selection

Question

I am currently experimenting with Microsoft Access and was curious how one would accomplish this.

I know that one can add a prefix to an autonumber in the format option such as "EMP"000, and each autonumber would fill as EMP001, EMP002, and so on.

What I would like to do is have the "EMP" change dynamically so if my personnel table has them as a Manager for example, it would be listed as MGR003, MGR004, etc.

My thought was to have something like the following in the format section but again am still new to indepth access so please excuse my crude write up.

"SELECT FROM [PersonnelTable].[PersonnelAbbreviation] if [Add Task].[AddTaskPersonnelType]==[PersonnelTable].[PersonnelType];"000

So to recap, I have two tables one "Add Task" the other "Personnel". Would like prefix on Add Task AutoNumber to be based off the abbreviation I have in table Personnel.

Thank you Stack Overflow users!

If you are using Access 2010 or later then you could do something like [this](http://stackoverflow.com/q/35947449/2144390). — Gord Thompson, Sep 09 '16 at 19:06

score 0 · Answer 1 · edited Sep 12 '16 at 16:04

0

Simply use a query (air code)

SELECT anydesiredfields, PersonellTable.PersonellType & Format(PersonellTable.PersonellID, "000") as FormattedID    
FROM PersonellTable   
INNER JOIN AddTask ON PersonellTable.PersonellID = AddTask.PersonellID

edited Sep 12 '16 at 16:04

dbmitch

5,361
4
24
38

answered Sep 10 '16 at 12:01

AVG

1,317
8
12

How to specify Microsoft Access auto number prefix value based on user selection

1 Answers1