VBA Form to enter data to table and create a unique nonrepeating id number based on date

[dkmiller16]

I am looking to create a "simple" data entry form with a function I have not been able to find. Listed below are the columns, column name, and data type that I am trying to fill. My biggest challenge is creating a unique ID/Number based on the current year/month/day but not repeating. However I will create multiple entries in a day therefore the unique ID/Number can't only be yy:mm:dd, it should be yymmdd1 followed by yymmdd2 if the second entry is created that day. Ideally this unique ID/Number would be showing on the data entry form as soon I go to enter the next dataset or have a clickable "create new" button that would clear form and generate next ID number.

A, Timestamp, yy:mm:dd:24hr time
B, PO, Text
C, Supplier, Text
D, Type, Text
E, Grade, Text with Multi Select drop down if possible (1c, 2c, 1&2c, FAS)
F, Cost, Accounting
G, KD/GRN, Text with Multi Select drop down if possible (KD,GRN)
H, Quantity, General
I, Pickup Date, Date (mm:dd)
J, Notes, Long Text

 

[HighAndWilder]

What of this are you able to do?

Are you able to create the Userform to enter the data into?

You could use the date and time to create the unique ID.

Format(Date, "yymmdd") & Format(Time, "hhmmss")

This doesn't include a suffix indicating the number of entries made so far in the day, how important is it to know this from the unique ID.

I recommend having a column in the worksheet to indicate the date and the one for the time of entry.

 

[dkmiller16]

What of this are you able to do?

Are you able to create the Userform to enter the data into?

You could use the date and time to create the unique ID.

Format(Date, "yymmdd") & Format(Time, "hhmmss")

This doesn't include a suffix indicating the number of entries made so far in the day, how important is it to know this from the unique ID.

I recommend having a column in the worksheet to indicate the date and the one for the time of entry.

I am thinking I can get the Userform created to enter the data into. I have not taken the time to really dig in but I new I needed a unique identifier number, biggest challenge I was seeing was how to show that unique id# on the Userform. I appreciate the reply.

 

[HighAndWilder]

You could have a textbox on the Userform to contain the UniqueId and a Command Button with code to generate the UniqueID.

If you use my idea then the code behind the Command Button could be as below.

Private Sub cmdUniqueID_Click()

    Me.txtUniqueID = Format(Date, "yymmdd") & Format(Time, "hhmmss")

End Sub

Or you could just use my formula and generate the UniqueID when you submit the data to the worksheet.

Is there a reason why it needs to be seen on the Userform?