Auto build name from 2 fields

[NigelShaw]

Hi,

Is this achievable? if so, roughly how?

ok

3 fields "FirstName" & "LastName" & "UserName".

FirstName = Nigel ( or any other contact )
LastName = Shaw

i would like to try and have a username auto build based on the first 3 letters of the firstname & lastname & a random 2 figure number

UserName = NigSha34

i could build it myself only if i knew how to get the first 3 letters of a string and generate a random number.......

im still looking this up but thought i would ask the question



regs,

Nigel

 

[pbaldy]

The Left function for the first 3 letters, the Rnd function for the random number. More info on both in VBA help.

 

[Swillsy]

In Excel I'd use something like this

=CONCATENATE(LEFT(A1,3),LEFT(A2,3),RAND())

The Rand Function is used differently in VB though (rnd)

 

[Pat Hartman]

Nigel,
It looks like you're trying to build a "smart" key. There are lots of posts suggesting against this. Eventually these types of keys fall apart either because they do not allow enough variation or the rules changed. One problem right off the bat is that when women get married, convention has them change their surnames. How will this impact your "smart" key?

Also, before you can use the Rnd() function, you need to use the randomize statement to seed the randomizer. Generally using the Now() value is sufficient to avoid duplicates.

Swillsy,
There is no concatenate function in VBA. You use the concatenation operator as Nigel did in his question.

 

[NigelShaw]

Hi Pat,

i was just trying to add a way of creating generic usernames. these can be changed quite easily by the administrator in way of picking the user, updating the surname ( if applicable ) and re-generating the username.

in a way, this could be automated by selecting the user, updating the surname and then running the processes.

my code works fine and generates what i need. the names are entered in a ribbon which get passed to hidden textboxes for data collection.



Public Sub GenUsrNme()

Dim strUserBuildFirst As String
Dim strUserBuildLast As String
Dim intUserRand As Integer
Dim strString1 As String
Dim strString2 As String
Dim strUsrNme As String
Dim strUserStringFst As String
Dim strUserStringLst As String
Dim strFieldChecks As String

strFieldChecks = strFieldChecks & Switch(Nz(Forms![mainform]![MainFormSubForm]![AddFirstName], "") = "", "First Name" & vbCrLf, True, "")
strFieldChecks = strFieldChecks & Switch(Nz(Forms![mainform]![MainFormSubForm]![AddLastName], "") = "", "Last Name" & vbCrLf, True, "")
If strFieldChecks <> "" Then
MsgBox "You need the following before you can generate a username:" & vbCrLf & vbCrLf & strFieldChecks & vbCrLf & "Username Error!", vbExclamation, "Missing Data"
Exit Sub

Else

'get the FirstName textbox data from the form
strUserBuildFirst = Forms![mainform]![MainFormSubForm]![AddFirstName].Value
'create the first part of the user name
strUserStringFst = Left(strUserBuildFirst, 4)

'get the lastname textbox data from the form
strUserBuildLast = Forms![mainform]![MainFormSubForm]![AddLastName].Value

'create the last part of the user name
strUserStringLst = Left(strUserBuildLast, 4)

'create the random number to go on the end of the username
intUserRand = Int(999 * Rnd)

strUsrNme = strUserStringFst & strUserStringLst & intUserRand

Forms![mainform]![MainFormSubForm]![AddUserName].Value = strUsrNme
End If

'refresh the ribbon

InvalidateUserName

End Sub

so, based on my name and a random 3 number, i would get NigeShaw692

regards,

Nigel

 

[Pat Hartman]

Primary keys should never change. You can have an internal key - autonumber and an external key - your smart key. Use the autonumber as the PK and for all relationships. Add a unique index on your smart key to prevent duplicates. As long as the smart key is simply data, it doesn't matter if it changes.

 

[NigelShaw]

Hi Pat,

im not changing the PK in the record. the PK is created after i apply the data. it works like-

Enter first name
enter last name
generate username
enter password
confirm password

apply.

if firstname, lastname or password are not present, the prompt and exit
if password and confirm differ, then prompt and exit

if all is good, the open the recordset, add the data, close the recordset.

the data is entered into a table that is related to the "YourCompanyDetails" by UserID on a one to many .

i even added a button to call the windows login name if the user preferred.

it works fine for me. my only one gripe is the ribbon side.

i enter the data via the ribbon and it gets passed to a hidden txtbox with the GetPressed procedure which is all fine except the password & confirm.

there doesnt seem to be a way of using a mask on these boxes in the ribbon so you can see the password until it is applied. i tried an OnChange & AfterUpdate on the textboxes to pass a masked value back to the ribbon and re-validate it but it created a loop that changed the ribbon that changed the txtbox that changed the ribbon and so forth so im still looking at that



regs

NS

 

[Pat Hartman]

Windows logins ALSO change. Creating a "meaningful" key that can change is a mistake. That's my position and I'm stickin' to it. Believe it or not, sometimes even men change their names when they marry!

 

[boblarson]

I have to go with Pat on this one. Use an autonumber for the PK and you can also have your other "username" as what is being used. You will find that the risk is almost non-existent in that case, whereas there is always a level of risk if you go the other way.

 

[NigelShaw]

im not changing the PK in the record. the PK is created after i apply the data. it works like-

Enter first name
enter last name
generate username
enter password
confirm password

apply.

NS

Hi,

i am using an autonumber for the PK and have not suggested that i'm Not.

the ONLY purpose for the rnd number is to stick on the end of the password ie NigelShaw123

this is just a format i have always used when asking for password changes on a server. i have always stipulated that they use a name that is relative to the user and a 3 digit number at the end. the PK has nothing to do with it, the PK is created whe nthe data is passed to the table as a new record. i use the PK to relate the tables " yourDetailsTbl" & "YourUserDetails" both related by the UserID which is the PK for the added users.

you cannot add a username without first adding a user and the users cannot have duplicates in the field. you can change a username & update a password via the admin control & admin password. this will bring up a form to select the user, change the details and save. the change is recorded to the tracking log with date, action and who changed it.

regs,

Nigel