Auto-ID based on Users logged in and date.

Hi All,

I apologise if this is a simple question, but i did a quick search and really could not find what I was looking for.

Essentially I work for a company who does consulting, and we currently work through Excel for generating JobID's and then have a seperate paper system for generating sampleID's and the likes and I want to roll it all into one.

We have a melbourne, and a Sydney office, and our ProjectID's are generated based on that, for example if the project is a Melbourne Job, it gets CV at the front of it, whereas if it is a Sydney job it gets CN at the front of it.

In addition to the site prefix, there is also a year month part of the date, for example if it was the fifth job started in September of this month in melbourne the job ID would be CV100905, whereas if it was the second job in Sydney for January 2011 it would be CN110102.

How can I get it to generate an autoID based on those, If i need to I can get it to combine a few fields, as long as the end result is that the combined field is searchable, and I can link that as most of the samples and the likes then reference that number.

The other example, which might be easier is the lab always has a FYYMM-ID numbering system, regardless of site, so how can I get the autoID to show like that, but also reset the ID at the start of each month, and roll-over the YYMM numbering.

Sorry for the longwinded question, I am keen to keep that numbering system as for the company it makes sense and if I can minimise the number of changes required it would make life a bit easier for everyone else.

OK, a little-appreciated fact about this kind of identifier is that if you want it as a single string but the parts have meaning, you are adding overhead and work to ID generation.

Having said that, it looks to me like a VBA function might be one way to do it. You would need the function to look up something for the CV, CN, C-whatever part - perhaps using a DLOOKUP or a sequence of IF statements. The datepart is easy, you could make a string using something like FORMAT$(Now(),"yymm") or something similar. The part that would become tricky is the last part because you need to count the number of records that already has the same first part. If the resultant string of 8 characters does not have punctuation (i.e. CV100902 ) then you could perhaps do a count of matching records with DCount( "[IDString]", "myTable", "Left$([IDString],4) """ & PartString & """")

Then add one to that count, format the count with FORMAT$(thecount,"##") and concatenate that as the final string.

Now... the EASIER way to do that is to keep those parts as separate fields. Then when you need the ID concatenated, use a query that concatenates the string parts. You should never directly open a table anyway for ugly searches like that. I would also recommend a synthetic or autonumber prime key rather than using the ugly long key.

Is it possible to have both, run the autoID in access to make it easier in that aspect, and then just have another field which becomes combinationIDfield for example

Prime Key: AutoID
Date Created: YYMM code
JobID: Job Number This Month
CombinedIDfield = DateCreated.value&JobID.value

That way i can search the CombinedID field, but access also has its own internalID which might make it easier in searching for a client by name etc

The next question, is if a field has a defaultvalue of the combination of two fields, how do I set it to enter a value when both of the other fields are filled out, not when they are empty.

Here's a small sample that uses the Dmax() function +1 to create the sort of order number system you are after.

Thanks for the small sample, i am trying to fit that example into another database that i have which is a bit simplier. Instead of CV or CN the prefix is always F, then YYMM - ID

How do i get this in a form to work based on the projects table with ProjectID as id, and DateCreated as the date the project was created.

Is there a way to get it to work so that it inserts the number into the table, but then if that record is deleted it won't throw the numbers out of whack. I was planning to put a project cancelled, or projectclosed checkbox and remove the delete function, but just trying to plan around worst case scenaro's where a user generates 40 jobs, and a record gets deleted and all jobs then get mixed up, or would the autoID prevent that?

OK have a look at the Control Source for the Compound Order Number (Text10). Currently it is;
To get the schema you now want change it to;

...And you may also want to change the way the auto-increment in the Combo's After Update event works, to better reflect your current requirements.