VBA Userform - Setting unique identifiers automatically

[snuffnchess]

I am in the beginning stages of a fairly complex form and file... and one of the things that I am going to need to create is

When the person entering data into the form clicks "add" that not just is the info added to a spreadsheet, but that an identifier (that will later be turned into a barcode) is created as well.

My thoughts are to use a 6 digit date (that the info was added to the file) and the first 4 characters of first name and first 4 characters of last name


so... YYMMDDFFFFLLLL

how do I go about programming this into the VBA? I would have it placed in a column 1 to the right of the LAST data entered to the file

[MarvinP]

Hi,

Building a unique ID is always hard. What happens when you have a 3 letter first or last name. What happens when the customer only gives his/her first initial. I'd just use the Max() of all the previous IDs plus 1 for the next unique ID.

[Logit]

.
Simply based off your description this is an example :

Please Login or Register  to view this content.

[FDibbins]

snuffnchess, it appears that you "reported" this thread, instead of "replying"? below is the comment you included with your report.

Got it. So then what about creating a number like YYMMDD###

How would I go about that?

There will never be more than 100 entries added in a day

I can use the date field as one done in the VBA form so it has to be filled in and can pull from there (the date used will be the date a person first came in for services rather than the date their info was entered to the sheet)

[jmac1947]

Hi,
Marvin was correct, there will always be a Madonna or Prince or short names such as Ng that will complicate any attempt to rely on names.

Solutions may be influenced by the ultimate use of the barcode, does the barcode simply become a reference or does it "have meaning"?

have you considered using date & time to give a greater degree of uniqueness or is it likely that multiple entries could be created in the same say second?

I often have a spare tab (called strangely enough "Constants") where I keep "workbook wide" information and have often used that to hold the "last number used" value (of course the subsequent add function if successful needs to update that value)

Just a few observations to consider when looking at the sample from Logit
Cheers

[snuffnchess]

They do not have meaning, and items will not be entered at the same second.

The one thing that would not work based on the info

Please Login or Register  to view this content.

listed above, is I would NOT want the date to be "now" I would want to be based on a manual entry of a date field on the form

(Basically the date that somebody came in initially to obtain services, may not necessarily be the date that information is input onto the spreadsheet. We want to capture the date that somebody first came in).

Attached is just what I have as a start. I've watched a couple youtube videos to get here, so I realize it is not perfect yet.. just trying to get a basis.

Once complete, it will be a multipage form. Just wanted to get a feeling for it first.

Edit1: I realize i have not created a place for this info yet... for the sake of argument would have it go in column H
Edit2: So the unique id could be YYMMDD01 for example, then next YYMMDD02, etc etc etc

[jmac1947]

Good morning snuffnchess,

Take a look at the attached version and see if it could work for you.

A couple of minor observations about the result so far.

1. The tab order in the user form needs adjusting so that you go from "Zip Code" to "Date of Service" rather than the "add" button as it does presently.
2. I took the easy way out to deal with clearing the user form by simply unloading it rather than clearing all the userform text boxes
3. You have telephone number in Sheet 1 but don't ask for it on the user form
4. I set the initial sequence number so that excel doesn't do the usual trick of removing leading zeros - although as there is no meaning to the bar code I could have just as easily started at 1
5. you will see I have currently stored the next sequence number in row 1 and it is visible, you could move the command button to cover it or to be really sneaky set the text colour of cells (1,K) to be white which would make it invisible to users

Hope this helps you get further along the journey.

Cheers

[snuffnchess]

Thank you! I am just in the playing stages now and learning a lot. I will play with this in a bit and see if I have more questions

[torachan]

@snuffnchess
Attached extract from one of my apps.
Within the code I have highlighted the unique 'name code' generator.
In this app it was necessary to generate a MEANINGFUL & MEMORABLE code.
Hence first name initial + lastname initial + unique two digit number.(this is limited in code to 99 * "Bill Smiths"
Because most people can remember their own name this cut their memory overload to a minimum.
Using the attached to create 'NEW' entry first 'CLEAR' the form.
Extra food for thought ?
torachan.