Generate unique code based on user form entry and put in table

[edgarmalroy]

Hello all!

I hope you can help. I have created a userform for data entry and is working well. I am hoping that I can add something that generates a unique code for each entry and add that.

So I have a table like below and a userform that populates everything from name onwards. I would like each submission on the form to also generate a unique client code, ideally based on the first letter +1 of the name and the number of row, as per below - is this possible? Many thanks

Client Code	Name	Start Date	Client Type	Psychotherapist
N001	Martin Stevenson	34/23/4234	x	x
C002	Bob Mortimer	34/23/123	x	x

Current code:

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Clients")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1



With ws

.Cells(iRow, 2).Value = Me.txtname.Value
.Cells(iRow, 3).Value = Me.txtdate.Value
.Cells(iRow, 4).Value = Me.cbtype.Value
.Cells(iRow, 5).Value = Me.cbtherapist.Value
.Cells(iRow, 6).Value = Me.txtfee.Value
.Cells(iRow, 7).Value = Me.txtphone.Value
.Cells(iRow, 8).Value = Me.txtemail.Value
.Cells(iRow, 9).Value = Me.txtadd1.Value
.Cells(iRow, 10).Value = Me.txtadd2.Value
.Cells(iRow, 11).Value = Me.txtcity.Value
.Cells(iRow, 12).Value = Me.txtpostcode.Value
.Cells(iRow, 13).Value = Me.txtgp.Value
.Cells(iRow, 14).Value = Me.cbhealth.Value
.Cells(iRow, 15).Value = Me.txthdetails.Value
.Cells(iRow, 16).Value = Me.txtemerg.Value
.Cells(iRow, 17).Value = Me.cbmeds.Value
.Cells(iRow, 18).Value = Me.txtmdetails.Value



End With

'clear the data
Me.txtname.Value = ""
Me.txtdate.Value = ""
Me.cbtype.Value = ""
Me.cbtherapist.Value = ""
Me.txtfee.Value = ""
Me.txtphone.Value = ""
Me.txtemail.Value = ""
Me.txtadd1.Value = ""
Me.txtadd2.Value = ""
Me.txtcity.Value = ""
Me.txtpostcode.Value = ""
Me.txtgp.Value = ""
Me.cbhealth.Value = ""
Me.txthdetails.Value = ""
Me.txtemerg.Value = ""
Me.cbmeds.Value = ""
Me.txtmdetails.Value = ""

End Sub

 

[jimrward]

Few observations
Bob, Billy and Barry will all have a UC starting C and what happens should Zeberdee Smith ever order anything this will stop your +1 concept working
What is the reason for the unique code

 

[Micron]

How is N or C the first letter in either of those names? I guess you would be OK using your idea because of the row number, but if the same client can be on another row, you would have the same beginning letter, but one might be D010 and the other D020 yet be the same client? Also, what if the client had the same name? Then you could have E015 and E022 but now E is not the same client.

You would use string functions, like Left(.Cells(iRow,1)) to get M from Martin. If you want S from Stevenson instead then you'd also need InstrRev() to find the space preceding the last name, assuming you can rely on that. You'd also need Format function to precede row 5 with zeros, or convert row number (long data type) to a string and concatenate zeros and row numbers along with the letter code you extract.

Please post code within code tags (use vba button on posting toolbar) to maintain indentation and readability.

 

[edgarmalroy]

Few observations
Bob, Billy and Barry will all have a UC starting C and what happens should Zeberdee Smith ever order anything this will stop your +1 concept working
What is the reason for the unique code

Ah yes, good point.

Okay, so taking that on board, what if it produced a unique code unrelated to the name. So a two random letters followed by three digits that represent their number in the table. eg.

FT001
TY002

it does not matter if the letters would repeat. Is that more reasonable?

 

[jimrward]

How is N or C the first letter in either of those names? I guess you would be OK using your idea because of the row number, but if the same client can be on another row, you would have the same beginning letter, but one might be D010 and the other D020 yet be the same client? Also, what if the client had the same name? Then you could have E015 and E022 but now E is not the same client.

You would use string functions, like Left(.Cells(iRow,1)) to get M from Martin. If you want S from Stevenson instead then you'd also need InstrRev() to find the space preceding the last name, assuming you can rely on that. You'd also need Format function to precede row 5 with zeros, or convert row number (long data type) to a string and concatenate zeros and row numbers along with the letter code you extract.

Please post code within code tags (use vba button on posting toolbar) to maintain indentation and readability.

N and C are the initial letters but next in the alphabet hence the +1

 

[jimrward]

More observations
If Bob Mortimer orders twice do you generate the 2 letter combo first and check to see if it exists already if yes then use old code if no then generate code

 

[edgarmalroy]

More observations
If Bob Mortimer orders twice do you generate the 2 letter combo first and check to see if it exists already if yes then use old code if no then generate code

There will never be a double entry, each one will be unique.

 

[Micron]

untested, but perhaps
.cells(iRow, 1) = Left(Me.txtname,1) & format(.Cells(iRow,"000")

 

[edgarmalroy]

untested, but perhaps
.cells(iRow, 1) = Left(Me.txtname,1) & format(.Cells(iRow,"000")

This is giving syntax error

 

[Micron]

Show how you used it? Using vbe immediate window this works for me
LEFT(sheets("009").range("B2"),1) & format(sheets("009").range("C2"),"000")

Obviously I had to insert sheet and range references because I don't have your form.