Autonumber Woes

[Joseppi]

Hello all,
I have a database that tracks Work Orders submitted by 4 different sections in my organization. What I am trying to do is create a TrackingNo field that is based on each of these 4 sections. For example, if the workorder is raised by my Admin section, I need to generate a TrackingNo that would look like:
ADM4001
ADM4002 etc

If it was my Transportation Section, It would be:
TN4001
TN4002 etc

To further complicate matters, I need to be able to generate these numbers on a yearly basis. i.e. On Jan 01 2005, I need new Work Orders to be numbered;
ADM5001
TN5001
ADM5002 etc

I would like to keep all of the entries in a single table if possible but am also open to 4 separate tables and then using a Union Query to comile and report the data. I have searched high and low and can not find a solution to this dilemna. I would appreciate any help anyone could offer.

 

[John.Woody]

What happens if you have more than 999 orders in a year? You want a prefix on the numbers but do they have to run consecutively for each section? or could you have

TM4001
ADM4002
ADM4003
TM4004

 

[Joseppi]

The odds are miniscule that they will even come close to 999 in a given year. As each section operates completely independently from the others, I would prefer it if they could keep their section TrackingNo in sequence; i.e. TN4001,TN4002, ADM4001,ADM4002.

I curently have each Section in it's own database and I'm using the Format Control ("ADM4"000) to trick the users into seeing the above TrackingNo. The problem with that option in when I need the data outside of the form. When I do a Union Query to complile all 4 sections I only get the raw number ie, 1, 2, 3. The other problem is that I can't have the number switch to a new set of numbers on Jan 01 2005.

 

[Joseppi]

Thanks Pat,

Being a rookie at this sort of thing, maybe you could take a look at what I have and let me know where I'm going off the rails?

 

[Joseppi]

Pat,

Thanks for the recommendation on removing the Year column. I would love to use the [DateRaised] Field to help generate this TrackingNo if someone can assist me in building the needed values.

 

[Joseppi]

Now that I've stripped my project and feel completly overwhelmed for trying to improve it's functionality , can someone please tell me why my Lookup on my frmMain is not working properly. When I select Admin from the combo box, it does what it is supposed to for the first record but then it holds that data throughout the entire table? It also means that when I first enter the table I get a blank field even though there is existing data in the table.

 

[Joseppi]

Thanks Pat!

I figured out the ControlSource part but could not find why the remainder was causing me such grief. You've been a real life-saver!

I've convinced those that use this database that that they don't need a section specific tracking number. A Unit level one will be quite sufficient. All I have to do now is figure out how to use DMax() so that I can use the data already in my table to create a incremental tracking number [TrackingNo]that will start on 01 Jan of each year. and look like:

GSBN-04-0001.

GSBN is our Unit Name
04 is the year (table has a [DateRaised] field {format dd-MMM-yy})
0001 is the incident number for this calendar year

I've been going through all of the posts but I haven't quite found what I think I'm looking for.