Solved How to set up unique customer codes based on customer types

[asteropi]

I'm not sure where to post this, so I hope it works here. If not, feel free to move it around

So I am new to access and I need some help
I have a table with a customer code field and a customer type field.
The customer type is simply 2 options (Retail or Wholesale) and I made it a drop down list.
And I want the customer code to be generated as such: taking the first letter of the customer type and adding the date and a random number at the end. It has to be indexed and it has to be unique values.

The way I want it to work is to start at a blank form, choose the type of customer (found that), take that initial (found that too), and then feed the initial to the customer code, generate the code and feed that back to the table.
So far in my form I have a CustomerType comdo box which gets the values from the table (Retail/Wholesale), then an unbound CustomerT text box which gets the initial of the type with the IIF command (R/W) and after that it's a mess.

I can create an unbound text box to generate the code the way I want it (=CustomerT & Date &random number), but that code is not indexed nor does it feed back to table. Same goes for queries.
On the other hand,if I put the CustomerCode to be bound to the table and have a default value (let's say I inpud default W-Date-RndN, it does not change that value when I alter the customer type, because of course it's bound to the table and not the CustomerT source

I just can't wrap my head around how I should go about it.
Bear in mind that I have minimum knowledge regarding VBA and SQL, so literally ANY help is appreciated.

 

[jdraw]

asteropi,

You seem to be focused on manipulating text box contents. I think it would be helpful to you and readers if you told us more about WHAT you are trying to support with automation. You mention Customer code and Customer type... and that suggests to me some business activity involving Sales/Services. If you could provide an overview of your full requirement in plain English --I'm sure there are other tables and relationships-- then you will likely get focused advice and options.
Consider the things involved and how they relate. Get your tables and relationships identified and tested.

I would caution you on the creation of ad hoc codification. Access and databases generally work very well with atomic data (1 attribute, 1 data element indivisible).

 

[XPS35]

It is a bad idea to record the same information twice in a table. Why would you want to record the customer type both in the code and as a separate field? The code must always remain the same, but it will probably happen from time to time that a customer changes type. What then? Moreover, it is conceivable that in the future you will have a customer who does both types of trading.
Including a date in the code also seems pointless to me. Also include that date only as a field in the table.
To identify customers you use a meaningless number. To record information about customers, use fields in the table.

 

[The_Doc_Man]

First, beware that random numbers CAN repeat in a long enough sequence - even in the same day. That is the nature of randomness. I could see some sequence that generates a duplicate randomized number for the same customer type on the same date. OK, low odds - but not impossible.

Second, if you want a unique identifier and cared so little about its actual value as to make it random, use an autonumber instead. You CAN make that indexed and still feel safe about uniqueness. And yet it is just as inherently meaningless as a random number - but YOU don't have to compute it because Access will handle that for you.

Third, having the client type code tacked onto your identifier is both overkill and kind of unnecessary and maybe obfuscating. Keep your special one-letter code in the record, for sure. If you want a date for whatever you are recording, keep that in the record, certainly. If you want to show the type, date, and number together because for some reason it is important to you, don't store them together, just display them together via concatenation. Keep them separate in the record. That indexed identifier could then be kept "pure" and you could test the other fields directly without having to extract the parts later. Besides that, if your record has child records, having the indexed numerical identifier makes it eligible to be a prime key for the main table and a good foreign key for any child (dependent) tables.

 

[theDBguy]

Hi. Welcome to AWF!

If you can calculate the value by using the data from other fields, you generally don't need to store it in the table. Perhaps the customer code if isn't necessary to be in the table. You can simply add it to your queries or forms by doing the calculations there.

 

[asteropi]

It is a bad idea to record the same information twice in a table. Why would you want to record the customer type both in the code and as a separate field? The code must always remain the same, but it will probably happen from time to time that a customer changes type. What then? Moreover, it is conceivable that in the future you will have a customer who does both types of trading.
Including a date in the code also seems pointless to me. Also include that date only as a field in the table.
To identify customers you use a meaningless number. To record information about customers, use fields in the table

First, beware that random numbers CAN repeat in a long enough sequence - even in the same day. That is the nature of randomness. I could see some sequence that generates a duplicate randomized number for the same customer type on the same date. OK, low odds - but not impossible.

Second, if you want a unique identifier and cared so little about its actual value as to make it random, use an autonumber instead. You CAN make that indexed and still feel safe about uniqueness. And yet it is just as inherently meaningless as a random number - but YOU don't have to compute it because Access will handle that for you.

Third, having the client type code tacked onto your identifier is both overkill and kind of unnecessary and maybe obfuscating. Keep your special one-letter code in the record, for sure. If you want a date for whatever you are recording, keep that in the record, certainly. If you want to show the type, date, and number together because for some reason it is important to you, don't store them together, just display them together via concatenation. Keep them separate in the record. That indexed identifier could then be kept "pure" and you could test the other fields directly without having to extract the parts later. Besides that, if your record has child records, having the indexed numerical identifier makes it eligible to be a prime key for the main table and a good foreign key for any child (dependent) tables.

Thank you all for your input. However,it seems I didn't explain it properly.

This customer code I want is indeed meaningless and only to show up on their invoices. Even if over time they change type, I will be able to cross-reference that with their VAT number and not register them twice or even change their code. Once given it will remain the same.
I have a different autonumber as my primary key set up as their true IDs (customerID) for inside database use, but I don't want that number to show up anywhere they might see it. So I thought to create a fake code they can see.

I thought to set up their code by registering their type of customer, date of registration and a small random number (like 0-100). In this way they will have no idea how many customers I have, only which customer they were on that specific day of registration.
So for example if someone registered today it would be W2406020xx, where W=wholesale and xx pick a number at random. They can give me that number to find them and I can show that number on their invoices. That's all I want.
The date part of the code is not taken from the table field (I have a separate one for private use), but formatted on the code in the default setup or the expression command.

So back to square one. These are my two options so far:
1)Choose Type -> extract initial -> set code with [initial] & Format(Now();"yymmdd") & Format(Int(Rnd()*100);0)
but I can't make the code non-duplicable nor can I send it to the table. I don't know how to do that.
or
1) set the code to be generated immediately on a new record with a default value, indexed non-duplicable, as:
"W" & Format(Now();"yymmdd") & Format(Int(Rnd()*100);0)
but if I change type it won't change the initial W.

How can I connect the 2 settings? Would a query work to form the code and then a second query to append it to the table?

Hi. Welcome to AWF!

If you can calculate the value by using the data from other fields, you generally don't need to store it in the table. Perhaps the customer code if isn't necessary to be in the table. You can simply add it to your queries or forms by doing the calculations there.

I'm afraid I need the same process to show Order sums, but thought not to confuse you guys too much. Let me explain though.

I have an Order_Detail form and an Order List form
When I input a new order I go to order details and I add all the products in a continuous form. Then in the form I get my sums, vats, and total in unbounded textboxes.
Now I have repeat customers making orders and I have an order list. Sometimes those customers call me and I just want to see all their orders at once. I want to have a field on that list form that will show the total sum of that order, but I want it to extract the sums directly from the detail form, insteam of me typing it manually to avoid mistakes.
I think it's the same process.

Should I maybe add pictures for you to see?

 

[Pat Hartman]

It is not unusual to generate a "visible" identifier to use for invoices or reports. Here is an example that generates one. It doesn't use the criteria you specified but you should get the idea of how it is done and you can change the code to implement your own rules. Although storing the generated unique ID technically duplicates data that is stored in discrete columns, I generally do it so that I can index the unique "visible" ID.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

 

[The_Doc_Man]

I have a different autonumber as my primary key set up as their true IDs (customerID) for inside database use, but I don't want that number to show up anywhere they might see it. So I thought to create a fake code they can see.

Why? (That is NOT a frivolous question.) What can they do if they see this customer number that is also an autonumber? Do you not have the user interface secured?

Choose Type -> extract initial -> set code with [initial] & Format(Now();"yymmdd") & Format(Int(Rnd()*100);0)
but I can't make the code non-duplicable nor can I send it to the table. I don't know how to do that.

You can't make it non-duplicable. Because you are using the RND function on a limited range, you DRASTICALLY increase your odds of duplicating a number within the same day. "Random" means you take yourself out of the equatioin.

et the code to be generated immediately on a new record with a default value, indexed non-duplicable, as:
"W" & Format(Now();"yymmdd") & Format(Int(Rnd()*100);0)
but if I change type it won't change the initial W.

Your concerns about duplication of numbers doesn't go away with this approach either. RND() is NOT going to guarantee you any specific value of numbering. It is the nature of random numbers that duplication is possible. For comparison, what are the odds that you will randomly flip a fair coin (without trying to cheat) to show Heads 10 times in a row? (Answer: 1/1024, which is NOT ZERO.)

It is your chosen ID format that is going to eat your lunch if you continue to insist on randomness. You claim limited knowledge of VBA or SQL so it will be hard for you to program your way around this. Letting Access do at least SOME of the heavy lifting will perhaps be better.

If you would take a four-digit generated number instead of a two-digit random one, I can give you a number that would perhaps be helpful.
It would not show your customers how many of them there were but might tell them when they became a client. AND since you have the day represented with that format (yymmdd) string, just make the format for yymmddhhnn (year, month, day, hour, minute) and you have your sequence number. As long as you don't do two new IDs in the same minute, you are fine.

 

[asteropi]

It is not unusual to generate a "visible" identifier to use for invoices or reports. Here is an example that generates one. It doesn't use the criteria you specified but you should get the idea of how it is done and you can change the code to implement your own rules. Although storing the generated unique ID technically duplicates data that is stored in discrete columns, I generally do it so that I can index the unique "visible" ID.

I saw it and it's exactly what I needed but sadly it's too advanced for me.
Is there an easier roundabout?

 

[LarryE]

I'm not sure where to post this, so I hope it works here. If not, feel free to move it around

So I am new to access and I need some help
I have a table with a customer code field and a customer type field.
The customer type is simply 2 options (Retail or Wholesale) and I made it a drop down list.
And I want the customer code to be generated as such: taking the first letter of the customer type and adding the date and a random number at the end. It has to be indexed and it has to be unique values.

The way I want it to work is to start at a blank form, choose the type of customer (found that), take that initial (found that too), and then feed the initial to the customer code, generate the code and feed that back to the table.
So far in my form I have a CustomerType comdo box which gets the values from the table (Retail/Wholesale), then an unbound CustomerT text box which gets the initial of the type with the IIF command (R/W) and after that it's a mess.

I can create an unbound text box to generate the code the way I want it (=CustomerT & Date &random number), but that code is not indexed nor does it feed back to table. Same goes for queries.
On the other hand,if I put the CustomerCode to be bound to the table and have a default value (let's say I inpud default W-Date-RndN, it does not change that value when I alter the customer type, because of course it's bound to the table and not the CustomerT source

I just can't wrap my head around how I should go about it.
Bear in mind that I have minimum knowledge regarding VBA and SQL, so literally ANY help is appreciated.

First, fasten your seat belts because for a real beginner, this is going to get complicated.

It sounds like you want to create a new Customer Code for each customer AFTER you change the customer type. Whether it's a new or existing customer doesn't matter. Also, since you already have a Primary Key when you enter a new customer and it is an AutoNumber Data Type, you already have a unique number you can use in your customer code field.

Let's assume the Customers Primary Key field name is CustomerID. So, let's say customer John Smith is entered and the Customer Type is R (Retail). Say Johns Primary Key 12 for example. Using the After Update Event in the Customer Type combo box, enter the following VBA line of code:

If IsNull(Me.CustomerCode) Then
    Me.CustomerCode=Left(Me.CustomerType,1) & Format(Date,"mmddyyyy") & CustomerID
Else
    Me.CustomerCode=Left(Me.CustomerType,1) & Right(Len(Me.CustomerCode)-1)
End If

R0602202412

If the customer already has a Customer Code, and only the Customer Type changes, then we take the Customer Type R or W and add only the characters AFTER the Customer Type from the old Customer Code. So the date and initial unique number remain intact.

The Left function will get the R or W value, then the date, then the unique CustomerID.

The Customer Code field should change AFTER you change the Customer Type.

Why you want a date there, I have no clue, but there it is.

 

[asteropi]

You can't make it non-duplicable. Because you are using the RND function on a limited range, you DRASTICALLY increase your odds of duplicating a number within the same day. "Random" means you take yourself out of the equatioin.

Honestly the randomness is not the problem so please try to see past it. If it becomes a problem I'll gladly implement the hour-minute module.
My problem currently is to make the code change its first initials based on the customer type. Do you have a suggestion for that?

 

[asteropi]

Let's assume the Customers Primary Key field name is CustomerID. So, let's say customer John Smith is entered and the Customer Type is R (Retail). Say Johns Primary Key 12 for example. Using the After Update Event in the Customer Type combo box, enter the following VBA line of code:

If IsNull(Me.CustomerCode) Then
    Me.CustomerCode=Left(Me.CustomerType,1) & Format(Date,"mmddyyyy") & CustomerID
Else
    Me.CustomerCode=Left(Me.CustomerType,1) & Right(Len(Me.CustomerCode)-1)
End If

Wow Thank you so much!!!
Ok, questions:
I copied it exactly as written and only changed my table names (I use an english version of access but the names of everything is in greek)
However, when I try to input a new record I get the error you see below and it highlights the Right part of your code.

Also, how can I make sure that the customerID part will always be 2 or 3 numbers, for formatting reasons? so 1 should appear as 001 etc

 

[Gasman]

You have already been told in post #10.
Do read the posts.

 

[asteropi]

You have already been told in post #10.
Do read the posts.

I assume you referred to post #11? I wrote that before I refreshed my page so I hadn't yet read #10.
But I can assure you I read everything and follow your instructions carefully

 

[Gasman]

No, post #10

 

[asteropi]

No, post #10

I read #10 top to bottom so many times and I copied the code exactly but I don't understand why I get the error

 

[Gasman]

Wow Thank you so much!!!
Ok, questions:
I copied it exactly as written and only changed my table names (I use an english version of access but the names of everything is in greek)
However, when I try to input a new record I get the error you see below and it highlights the Right part of your code.

Also, how can I make sure that the customerID part will always be 2 or 3 numbers, for formatting reasons? so 1 should appear as 001 etc

The error message is hiding what is there, but the first argument in Right() is the string.
For leading zeroes, you would use the Format() function.
Larry's code was incorrect, you need the whatever you want to use, as the first argument, for the length of whatever it is.

 

[bob fitz]

From post #12

However, when I try to input a new record I get the error you see below and it highlights the Right part of your code.

Can you be more specific

 

[Gasman]

From post #12

Can you be more specific

Bob, the Right() function in post 10 is missing the first argument.

 

[asteropi]

The error message is hiding what is there, but the first argument in Right() is the string.
For leading zeroes, you would use the Format() function.
Larry's code was incorrect, you need the whatever you want to use, as the first argument, for the length of whatever it is.

I understand what you meant. But what should I write as the rest of the argument?
Me.CustomerCode again?