Create and Increment Numbers with Letter Combinations to make a serial number in VBA

[wayneseymour]

I have a data sheet that I input data from a user form. One aspect of the form is that as you finish adding the data and before you submit it to the data sheet a ID number is generated using the MAX function +1 in the VBA code which just increments 1,2,3,4. The user form is used to enter, add a Vendor (contractor,Owner or Company name), this is done via a combo box with the names and equipment type. some names are two or three words ,ie. "Bob Cat Services" or "Azan Heavy Equipment", "Ron Wilson"), but always a combo of more than one name. There is another combo box that holds the type of equipment owned or listed by the vendors,contractors("Backhoe","Roller","Dozer","Dump Truck"etc.) I would like to have a Label or text box that will, as the names,ie, vendor and equipment are entered it will create a unique ID Number... like for example "Bob Cat Services, uses BCS for the vendor, equipment like "Back-hoe" or Bh for the equipment followed by a number most likely starting out with 0001. Making the serial number; BCSBH0001. THIS WOULD CHANG DEPENDING ON THE NAME AND EQUIPMENT. but always using the first three letters or two of the vendor name like if its more than one word "B" from Bob, "C" from "Cat" and "S" from "services", then add the first two letters from the equipment BH for back-hoe or just the first two letters if its spelt "Backhoe". Then lastly adding 0001, to end up with a unique serial number BCSBH0001 which tells the company,vendor and the equipment type. This makes for each vendor the serial is different "Azan Equipment" would be AEBH0001, "Bob Cat Services" would be BCSBH0001 and so on. After that any addition entry would read the column of the ID in the data sheet and add the next sequential number belonging to that serial group , ie AEBH0001 would become AExx0002,(xx being the first two letters of the equipment type) or BCSxx0002 and so on. Granted something like "Dump Truck" may pose a problem. But thats the jist of what I'm trying to achieve. Hope someone can help. I'm new to VB so I find it hard to figure out the code. Please help. THANKS to everyone.

 

[Z51]

Standardize your data entry and create lookup tables for both vendors and equipment. It will make the task of generating unique ID's much easier.

Your vendor lookup list should include columns for vendor code (that you decide upon), vendor full name, vendor contact info, etc. Your equipment list shouldn't include multiple spellings/variations of the same piece of equipment, e.g. don't let users type "back-hoe", "backhoe" or "Dump Truck" and "dumptruck", etc. Make them pick from a drop-down/data validation list.

Once you've standardized the input, you can create a table of vendors, equipment and generate unique serial #'s.

 

[jimrward]

As you are already using lists however they are stored I would suggest adding an extra column in the data to include the short codes associated with each item it will take a while to setup but some will be sort of fixed list and some will expand
this will allow you to keep control and keep everything clean and eliminate any glitches so joining the letters together will become a simple task and carry on as you are with your numbering
also you could cover when companies amalgamated when bought out by assigning same 3 letter code to two different organisations
when a new item is added to any list just add the new codes
just my thoughts