I've spent some time researching a solution for this and I have encountered a number of helpful posts however I have not been able to produce a solution for my very specific issue.

I have been tasked with creating a multiuser management database using access. To update from the existing system (multiple spreadsheets). Although I am developing in 2007 I will need to provide the finished article in an .mdb format because most users within the company are using 2003.

The problem I am concerned about involves the databases ID generating capabilities. The company uses an ID code which follows the following structure.

Site Code(2 characters)/Year(yy)/CaseID(5 digits)/JobID(2 digits)

eg DN/10/00001/01

What I need to incorporate into a form is the ability to intially generate the site/year/case ID in a manner which will prevent user duplication issues. I believe this can be solved using Dmax +1 form coding and a count table. I am trying to get this sorted inspite of my limited vba knowledge.

The problems really start when I need to do something similar for the jobID. For each caseID there can be multiple jobID so again I will need a form which generates the jobID for each caseID. The table counting solution seems to fall down here as I would have to have thousands of little tables to account for each caseID with multiple jobIDs.

I dont want to go any further with the development without having a solution for this as the solution may dictate how the related tables are structured.

I can't help but feel I am overcomplicating this for myself and a simple solution is staring me in the face but I can't seem to get it sorted. My most recent idea was to create a query which shows all the jobs for the case where I am going to generate the new job and again look at the Dmax +1 solution from there.

Am I barking up the wrong tree?

Any help/advice/amusing anecdotal testimony/nudges in the right direction would be greatfully received. Heck if you've read through all my issues I'm already greatful for your time!

Thanks

Felix

There are some general thoughts here:

http://www.baldyweb.com/CustomAutonumber.htm

In most situations the DMax + 1 will work fine, the trick is to commit the record as soon as you grab that number, to prevent other users from getting the same number. One method of doing that is to use the update event of the form so it grabs/saves the number at the same time. As to your query, you wouldn't really need it, as your job DMax could simply have a criteria for Case ID.

The table method is probably the most bulletproof when it comes to avoiding multiple users getting the same number, but a little harder to set up. You can still use that method, but rather than many tables I'd try having a single table with a record for each case ID.

Thank you at least now I know I'm heading in the right direction even if I can't get the code working. I'm going to head out today and get a vba for dummies book in the hopes of cracking this.

Thanks for the help!

Felx

No problemo; post back if you get stuck.