wayneseymour
New Member
- Joined
- Dec 7, 2016
- Messages
- 7
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.
Last edited: