Solved Auto Configure

[sctb0825]

I have a table that has a field called Customer Code and another field called Customer Name.

The way I assign a Customer Code is to use 1st 4 letters of Customer Name + 2 additional characters.

an example would be "Jones Manufacturing Inc." would get a Customer Code of "JONEMI"

1st 4 letters of first name + 1st letter of each additional name (or random numbers or letters if there are none, as in a company named ICE

Is there a way to get the field Customer Code to automatically do this so that all the records are uniform and eliminate users not following the rules.?

 

[sctb0825]

I have a table that has a field called Customer Code and another field called Customer Name.

The way I assign a Customer Code is to use 1st 4 letters of Customer Name + 2 additional characters.

an example would be "Jones Manufacturing Inc." would get a Customer Code of "JONEMI"

1st 4 letters of first name + 1st letter of each additional name (or random numbers or letters if there are none, as in a company named ICE

Is there a way to get the field Customer Code to automatically do this so that all the records are uniform and eliminate users not following the rules.?

This would be used in the Form

 

[GPGeorge]

Can you articulate a rule about how the 2 additional characters are to be chosen? In words? If so, it should be translatable into code.
Left([Customer Name], 4) will reliably retrieve the first 4 characters of the Customer Name as long at it is at least 4 characters long.

In the case you stated, a Customer Name of ICE, you could pad the name first, to ensure at least four characters:

Left([Customer Name] & "0000", 4) to return ICE0.
The rest depends on the rule about the other two characters being stated.

 

[sctb0825]

Sorry I am a bit of a newbie so I need more information,

The rule on the other two characters is the first digit of the second and third name or in the absence of those a random 2 digits, either letters or numbers.

this would be composed when you type in a customer name while adding a new customer and not allow duplicates, it that customer already exists and error message.

 

[sctb0825]

Sorry I am a bit of a newbie so I need more information,

The rule on the other two characters is the first digit of the second and third name or in the absence of those a random 2 digits, either letters or numbers.

this would be composed when you type in a customer name while adding a new customer and not allow duplicates, it that customer already exists and error message.

I assume this is done by creating an event from the property sheet on the field, using code builder

 

[GPGeorge]

"...the first digit of the second and third name...." Thank you, that could have been inferred from the question, but I prefer to have the rules more explicit, just in case.

You can do this in a few different ways. Not in the "code builder", per se, though.

Give me a few minutes to work on this. I think a VBA function would be my first choice.

I also don't think purely random values are going to be the best route. It's too hard to generate them and then verify that they don't coincidentally duplicate two previously generated random values.

 

[Pat Hartman]

I'll give you some ideas for how you might write the code.
1. if you use split(), you can get all the words in the name into individual elements of an array.
2. You would check the first instance yourarray(0) - remember arrays are zero based so the first subscript is 0, not 1. - to determine its length.
3. After that you could easily pick up the first character of as many words as you need or as there are.
4. If you still need additional characters, you can use random to pick a number between 1 and 36 to get a letter or number as you need each additional random character.

But, you should think about why this is necessary. In the past, it might have made data easier to find but with combos and other search options, you can easily use the entire name to find things.

I'm not saying you shouldn't do this but I am saying, I wouldn't spend a lot of time on it.

And REGARDLESS, the PK of the record should be an autonumber NOT the generated "smart" ID.

 

[sctb0825]

I'll give you some ideas for how you might write the code.
1. if you use split(), you can get all the words in the name into individual elements of an array.
2. You would check the first instance yourarray(0) - remember arrays are zero based so the first subscript is 0, not 1. - to determine its length.
3. After that you could easily pick up the first character of as many words as you need or as there are.
4. If you still need additional characters, you can use random to pick a number between 1 and 36 to get a letter or number as you need each additional random character.

But, you should think about why this is necessary. In the past, it might have made data easier to find but with combos and other search options, you can easily use the entire name to find things.

I'm not saying you shouldn't do this but I am saying, I wouldn't spend a lot of time on it.

And REGARDLESS, the PK of the record should be an autonumber NOT the generated "smart" ID.

 

[sctb0825]

I'll give you some ideas for how you might write the code.
1. if you use split(), you can get all the words in the name into individual elements of an array.
2. You would check the first instance yourarray(0) - remember arrays are zero based so the first subscript is 0, not 1. - to determine its length.
3. After that you could easily pick up the first character of as many words as you need or as there are.
4. If you still need additional characters, you can use random to pick a number between 1 and 36 to get a letter or number as you need each additional random character.

But, you should think about why this is necessary. In the past, it might have made data easier to find but with combos and other search options, you can easily use the entire name to find things.

I'm not saying you shouldn't do this but I am saying, I wouldn't spend a lot of time on it.

And REGARDLESS, the PK of the record should be an autonumber NOT the generated "smart" ID

"...the first digit of the second and third name...." Thank you, that could have been inferred from the question, but I prefer to have the rules more explicit, just in case.

You can do this in a few different ways. Not in the "code builder", per se, though.

Give me a few minutes to work on this. I think a VBA function would be my first choice.

I also don't think purely random values are going to be the best route. It's too hard to generate them and then verify that they don't coincidentally duplicate two previously generated random values.

The random characters can be repeated as long as the complete code is not duplicated, ie you can have JONE12 and FRAK12

 

[sctb0825]

It is just that a structured customer code is easier to enter in the heat of battle so to speak than trying to remember random assigned numbers with thousands of customers in the system. Call it personal preference, or what I am use to.

 

[Pat Hartman]

But that's just it, give them a search box or a combo. I could write the code easily and someone might write it for you but my position is - if I don't think you should be doing it, I'm not going to write code for you that I would never write for myself and not get paid for it.

I've attached a sample database that generates "intelligent" IDs. It is not the method you wanted but you might be able to adapt some of it.

 

[GPGeorge]

I haven't been able to test this really thoroughly, so there may be cases that need to be accounted for which I've not thought of, but it does the basic task.

Public Function CustomerCode(ByVal strCustomerName As String) As String

Dim CustCode() As String
Dim CustCode4 As String
Dim i As Integer

strCustomerName = Replace(strCustomerName, ".", "")
CustCode = Split(strCustomerName, " ")
CustCode4 = UCase(Left(CustCode(0) & "0000", 4))
For i = 1 To UBound(CustCode)
CustCode4 = CustCode4 & UCase(Left(Nz(CustCode(i), 0), 1))
Next i
CustomerCode = Left(CustCode4, 6)
If Len(CustomerCode) < 6 Then
CustomerCode = Left(CustomerCode & "00", 6)
End If
i = 0
Do While DCount("CustomerCode", "tblCustomer", "tblCustomer.CustomerCode = """ & CustomerCode & """") > 0
CustomerCode = Left(CustomerCode, 5) & i
i = i + 1
Loop

End Function

 

[GPGeorge]

It is just that a structured customer code is easier to enter in the heat of battle so to speak than trying to remember random assigned numbers with thousands of customers in the system. Call it personal preference, or what I am use to.

Ah, but Pat's right, though. You're never going to improve on the performance of a combo or list box. The point is this: in order to use this Customer Code, your user has to have the customer name in front of them anyway, in order to derive what they think the code will be. The list or combo box has the actual customer Names or codes in it, so it's a match against that list. This code doesn't really change that task all that much.

I actually was intrigued by the challenge of writing an algorithm to generate new customer codes, otherwise I wouldn't have gone down this path either. As noted, I can't guarantee it will work over thousands of records, especially when similar names are going to start generating similar codes. Chances are pretty good you'll someday end up with a case that I haven't provided for yet.

 

[sctb0825]

But that's just it, give them a search box or a combo. I could write the code easily and someone might write it for you but my position is - if I don't think you should be doing it, I'm not going to write code for you that I would never write for myself and not get paid for it.

I've attached a sample database that generates "intelligent" IDs. It is not the method you wanted but you might be able to adapt some of it.

I think I understand what you are trying to say, and you are right I do not way you to give me something that you would not use yourself. This customer ID I and using is assigned it never changes, stays with the customer forever, even if he leaves and comes back. I enter this code into many different forms and it auto fills information. by having a structured Code it is easy to remember and saves the lookup customer step. I start typing the code and a list to choose from comes up to select. I don't know how to do that with random auto assigned IDs. I am not going to spend a lot of time on this, I just have a system I have been using and have had trouble with data entry not following the rules and once entered it cannot be changed from front end and this links to my accounting so it creates extra data in my accounting that I have to go in and delete.
I just thought I could auto generate a customer code that fit my structure.

Hope you understand more of what I was asking and know I appreciate all your good replys.

 

[sctb0825]

I think I understand what you are trying to say, and you are right I do not way you to give me something that you would not use yourself. This customer ID I and using is assigned it never changes, stays with the customer forever, even if he leaves and comes back. I enter this code into many different forms and it auto fills information. by having a structured Code it is easy to remember and saves the lookup customer step. I start typing the code and a list to choose from comes up to select. I don't know how to do that with random auto assigned IDs. I am not going to spend a lot of time on this, I just have a system I have been using and have had trouble with data entry not following the rules and once entered it cannot be changed from front end and this links to my accounting so it creates extra data in my accounting that I have to go in and delete.
I just thought I could auto generate a customer code that fit my structure.

Hope you understand more of what I was asking and know I appreciate all your good replys.

Set up as 1st 4 letters of customer name with I start typing customer name the list only shows customers with those first 4 letters. or goes directly to those 1st 4 letters

 

[GPGeorge]

I think I understand what you are trying to say, and you are right I do not way you to give me something that you would not use yourself. This customer ID I and using is assigned it never changes, stays with the customer forever, even if he leaves and comes back. I enter this code into many different forms and it auto fills information. by having a structured Code it is easy to remember and saves the lookup customer step. I start typing the code and a list to choose from comes up to select. I don't know how to do that with random auto assigned IDs. I am not going to spend a lot of time on this, I just have a system I have been using and have had trouble with data entry not following the rules and once entered it cannot be changed from front end and this links to my accounting so it creates extra data in my accounting that I have to go in and delete.
I just thought I could auto generate a customer code that fit my structure.

Hope you understand more of what I was asking and know I appreciate all your good replys.

"...I don't know how to do that with random auto assigned IDs."

Normally, NO one does that, actually. Either you have a piece of paper with the ID printed on it to copy (rare), or you use some data that is readily and easily available, such as the Company Name. This Customer Code is really nothing more than a stylized version of that Company name, and you must have some way to identify it. Either it's printed on a piece of paper or you have the company name in front of you, from which to start reconstructing it in your head.

Again, the beauty of a combo or list box is that you don't have to remember ANY customer code, randomly or algorithmically generated. Combo boxes have a property, called "Auto Expand" that starts to match values as you type: For example, typing G - R - O - V will attempt to match on the first four letters of the company name and find "Grover Park Consulting" among a short list of other company names like "Grove Pattern Creators", etc. You are no closer to a match using a made up code, really, since the customer code would be GROVPC and you'd STILL have to decide whether that stands for the first or the second potential match.

Again, I took on the challenge of a partial algorithm because it looked interesting, not because it's the most effective strategy.

 

[sctb0825]

"...I don't know how to do that with random auto assigned IDs."

Normally, NO one does that, actually. Either you have a piece of paper with the ID printed on it to copy (rare), or you use some data that is readily and easily available, such as the Company Name. This Customer Code is really nothing more than a stylized version of that Company name, and you must have some way to identify it. Either it's printed on a piece of paper or you have the company name in front of you, from which to start reconstructing it in your head.

Again, the beauty of a combo or list box is that you don't have to remember ANY customer code, randomly or algorithmically generated. Combo boxes have a property, called "Auto Expand" that starts to match values as you type: For example, typing G - R - O - V will attempt to match on the first four letters of the company name and find "Grover Park Consulting" among a short list of other company names like "Grove Pattern Creators", etc. You are no closer to a match using a made up code, really, since the customer code would be GROVPC and you'd STILL have to decide whether that stands for the first or the second potential match.

Again, I took on the challenge of a partial algorithm because it looked interesting, not because it's the most effective strategy.

You are both right and I appreciate helping me figure this out. The code is assigned at initial company setup when a customer is input into the system. It has been the primary method of looking up a customer, but I can fix that since I am creating a new database, with a properly configured query and combo box. Thanks guys

 

[Pat Hartman]

Change is hard but sometimes change is progress

PS, I knew someone would take the bait and write code for you