Need help automatically filling a field

[natemclain]

Hey I need a way to Fill a CustomerID field that uses like the first 4 characters of the company name and adds a random number to the end that would be like 3 digits.
Example:

Company Name: ABC Company
CustomerID: ABCC263

Would this have to be done with VBA code or is there another way to do it??
Right now the user has to manually put in a CustomerID.
This would simplify the process.
Has anyone done this before?

TIA

natemclain

 

[Lightning]

Hi Nate

It would be easiest done with VBA code. Something like

Me.CustomerID = Left((Me.Company), 4) & Int((999 * Rnd) + 1) ' Generate random value between 1 and 999.

Check the "Random Function" topic in the On-Line Help.

HTH
Lightning

 

[natemclain]

Ya it does help. But can you explain what exactly that code is doing? I'm trying to learn VBA but if you explained what it meant it would help some...


TIA

natemclain

 

[Lightning]

Hi Again

Quick and simple explanations:

Me.CustomerId Identifies the field you want to update. Me is an Access keyword
that refers to the "Code Behind Form" module that the code is
running from. CustomerID is the name of the control.

Left((Me.Company),4) This is saying "Get the first 4 Left-hand characters of the text
in the "Company" field.

& This is the Concatenation character for Access VBA

Int((999 * Rnd) + 1) This is saying "generate a random number between 1 and 999.

So, this whole code string simply means

"Get the first 4 characters of the Company field, then generate a random number between 1 and 999, join them together and put the result into the CustomerID field."

On-line Help topics for more information:

"Left Function"
"LTrim Function"
"Random Function"
"Concatenating Text"
"Concatenation Operators"
"Me keyword"

Have Fun!

Lightning

 

[Craig0201]

Nate,

Are you sure this should be a random number?

A unique number should be used as a random number could be duplicated.

This can be done using recordsets to access the table holding the customer data, finding the max and adding one to it. This is then concatenated to your four characters to create the new customer ID.

Craig

 

[damber]

Nate

Craig is correct.. though as you store the number attached to a 3 character code you may want to create an autonumber field in the table which you add to the first three letters of the company code...

me.CustomerID = Left(me.Company, 3) & me.AutoID

This will continue to increase and will be a unique number across all customers..

Just a thought.

Damian
damber@damber.net

A smile is worth a thousand kind words. So smile, it's easy!