Combining fields in different tables

[Warrl]

I'm creating a database which would automatically assign a unique workorder number in the "WorkorderNumber" field of the "Workorder" table. Note: this will not be the Primary number for the work order.

The WorkorderNumber will be developed by combining fields from the "System" table. Fields used to create the WorkorderNumber from the "System" table are:

1) Location [currently in the field is "MAX"]
2) CalendarYear [currently in the field is "2014"]
3) NextWorkorderNumber [currently in the field is "1"]

I need the following to happen to the "WorkorderNumber" field of the "Workorder" table:

a. I need the field to read as followed: Max-2014-00001
b. I need the number 00001 to autonumber to 00002 on the next entry of a new request. [MAX-2014-00002].
c. I need to be able to control how the "WorkorderNumber" field populates by changing the "CalendarYear" and "NextWorkorderNumber" fields within the "System" table without messing up prior workorder numbers already populated.

I hope this is clear enough to follow.

thanks!

 

[Cronk]

It is clear enough what you want but messy enough to say you should have posted this to the VBA forum.

I personally detest users who want to include a year in the reference number with an incrementing number being reset at the start of a new year. This is a hang over from the paper based systems.

Are you comfortable with VBA coding? In any case, this is not a tables issue and I suggest you re-post in that forum.