Question Auto generate unique customer ID's

[jjarman]

Hello, hope someone can help me this problem.

I am in the process of creating a DB for customer names, job numbers and document numbers, but have run into a problem right from the start.

We are using a naming convention of the first 3 letters of the customer name, and 3 numbers after that. Eg. Customer names Allstar, Allpro would be All001 and All002 respectively.

The job numbers would then be sequential numbers, for each customer. So the first job for Allstar would be All001-001 and so on.

How do I get Access to check/create the customer ID's? And then how do I get Access to check for the next job number for each customer?

I was able to do this in Excel with the following;

=LEFT(B12,3)&COUNTIF($B$3:B12,LEFT(B12,3)&"*") in one cell
=IF(B12="","",LEFT(D12,3)&TEXT(RIGHT(D12,1),"00")) in another to add the preceding zero's.
Obviosuly , Excel is not the best way of tracking customers, jobs, documents etc.

Any help? I'm not completely new to access, but can't get my head wrapped around this?

Thanks

 

[Sketchin]

I'm not expert, but this might help you get started:
http://www.techonthenet.com/access/modules/sequential_nbr2.php

 

[jjarman]

This is great! This solves the second part of my problem without any extra work.... I still need to generate the customer code to begin with.
I'm not an expert either and I'm assuming/hoping that I can figure out how to use the same code to generate customer code (commodity type in the sample).
Thanks for your help.

 

[Sketchin]

to use the first 3 letters of a company name, create a field called something like "NumberPrefix" and make an expression that looks like :
=Left([Companyname],3)

And then in the "CustomerNumber" Field, just add this field to the other field.

 

[jjarman]

I was able to do this, unfortunately the people that will be using the DB want it automatic. As our customers come on board, they don't want to have to remember, or look up, if the 3 letters have already been used, and if so what the current number was.
As an example, an initial customer was Allstar, so customer code was ALL01. A month later, Allpro joined up, so customer code was ALL02. And 6 months later Alleviate joined up, code was ALL03.

I think the original code you sent me the link for will work.... I think I just need to basically duplicate the orginal tables, and make a copy of the coding to reference the new tables.

What I need to end up with in the end is a code that looks like;

ALL01-001-0001JJ
customer-jobnumber-documentnumber and author initials

 

[Sketchin]

This is getting to be over my head! You will need some sort of VBA to check if the 3 letters have been used, and then use DMAX somehow.

This is what I used for my reservation #'s

If Me.ReservationNumber = 0 Or IsNull(Me.ReservationNumber) Then
        Me.ReservationNumber = Nz(DMax("[ReservationNumber]", "tblReservations"), 0) + 1
    End If

But all this does is check if RES-001, RES-002 etc exists, and if it doesn't, create RES-003. (does this make any sense??) lol.

So somehow, you will have to do the same, but say:

If Me.CustomerPrefix exists, then DMAX +1, otherwise do this.....

I know this is super vague, but I'm hoping someone that knows wtf they are doing will chime in!

Any chance you can change your naming convention? Could you have the number ALWAYS increment? Is there a reason it has to go back to one for new letters?

 

[jdraw]

You might benefit from this article on relational database principles.
http://forums.aspfree.com/attachment.php?attachmentid=4712

 

[JLCantara]

You will find in the linked DB the solution to your problem. Of course, the query will be run from VBA (using a command button or the afterupdate event) and the ID number appended to the prefix entered...

JLCantara

 

[RainLover]

Try this sample.

It may be a little less complicated than the others.

 

[JLCantara]

Hi RainLover,

In your solution, part of the job is done through table design (isolation of prefix) while mine uses full customer ID...

Nonetheless, using DMax is a good idea but should use LEFT to fetch the 3-car. prefix.

JLCantara.

 

[jdraw]

According to the OP, this is what he "needs"

What I need to end up with in the end is a code that looks like;

ALL01-001-0001JJ
customer-jobnumber-documentnumber and author initials

Any other options/comments?

 

[jjarman]

Thanks Everyone. The DMAX solution seems to be on the right track, but needs to be comparing/incrementing only the first 3 letters. The actual full name needs to be unique.

Energy - ENE01
Enertry - ENE02
Enertime - ENE03

etc.
I added a field (myLetters) to the DMAX Plus One solution, and changed myLetter to a calculated field (Left$, myletters,3) and left the VBA intact.
It prevented me from having duplicate customer names, but for some reason the line of code that compares myLetter (now only 3 letters), is still refering to the entire myLetters, hence the concantented field is the entire name with the number 1. Do calculated fields not work properly with the coding??

 

[jjarman]

Got it!!
Added a field to capture the entire customer name, no duplicates.

Added a line in the code (after it checks for data) to copy the entire name into the new field.
Then added a line before the DMax line to grab the first 3 letters.

Thank you everyone for your contribution!!

 

[RainLover]

jjarman

Glad to see you went to the trouble to find out how my code works and then how to adjust it for your needs.

Other Advice.

Think about weather or not you should store the Concatenated value or each separately.

Strictly speaking some of this is anti Normalisation rules, however I would ignore that rule in this situation. The broken rule is storing calculated values.

This solution can fail in a multi-user environment. It can allocate the same number to two users at the same time if they both happen to run at exactly the same time. This is highly unlikely to happen even with a dozen of more users. If it does I have another version which is rather complicated.

If you want a copy go to my Sky Drive (Link in my signature) and download a copy under the DMax folder. I doubt that anyone else has written a similar thing. Feel free to use it, just don't forget to acknowledge the Author.

 

[jjarman]

Thanks Rainlover, fortunately, or unfortunately, I will be the only one using the DB

 

[RainLover]

Hi RainLover,

In your solution, part of the job is done through table design (isolation of prefix) while mine uses full customer ID...

Nonetheless, using DMax is a good idea but should use LEFT to fetch the 3-car. prefix.

JLCantara.

Can you explain the part about "isolation of prefix"

 

[jjarman]

Seem to be stuck again

I have added a field called Author which needs to be added to the end of the string. I assumed I could use the following;

LNewItemCode = pComID & "-" & Format(1, "0000") & "-" & txt.Author

Even though text has been entered on the form prior to the code running, it doesn't add it to the string.
Can you tell me what I missed?

Thanks

 

[JLCantara]

Hi RainLover,

Splitting the Customer ID in 2 parts + the ID itself is surprising since it violates normalisation rules. Indeed, it grately simplifies the job but then whats the use of the CustID?

Regards,
JLCantara.

 

[JLCantara]

Hi JJarman,

txt.Author -> txt dot Author? Are you sure??????

Bet you a buck it should be txtAuthor...

JLCantara.

 

[RainLover]

Hi RainLover,

Splitting the Customer ID in 2 parts + the ID itself is surprising since it violates normalisation rules. Indeed, it grately simplifies the job but then whats the use of the CustID?

Regards,
JLCantara.

JL

I posted a Sample. That is all it is. It has been demonstrated this way to help the poster understand what is going on.

It does need adaption to each circumstance.