S
sa
I was wondering if you have or have seen a routine to generate unique
username from first, middle and last names. Here is my requirement -
by default first initial and last name of a user is their username.
But if the expected or default username is already taken then if the
user have middle initial, then the username for this user would be
first initial, middle initial and lastname. If the user does not have
middle name then the username for the user would be first two letters
of first name and lastname and if that's already taken then first
three letters of first name and last name and so on.
I already have Active Directory link to my access dabase. I have new student
from our ERP system.
This is what i have so far. It works if there is already a username on the
network next will take first two character of firstname and lastname as
design, but if there no user with that username and if there are two users
with the same name in new
users table then my code won't work or assigns same user names.
I think my problem is right after i INSERT the newly created username is
somehow failed to get into rst.recordset. I tried rst.requery but still
no luck.
Private Sub Command0_Click()
Dim strSql As String
Dim StrSql1 As String
Dim strSqlUpdate As String
Dim rst As DAO.Recordset
Dim rsn As DAO.Recordset
strSql = "SELECT SamAccountName as UserName from Network UNION Select
UserName From temp_user; "
strsql2 = "SELECT id_num, last_name, first_name, middle_name FROM
Admission;"
Set rsn = CurrentDb.OpenRecordset(strsql2)
rsn.MoveFirst
Do While Not rsn.EOF
Dim lngStep As Integer
lngStep = 1
Set rst = CurrentDb.OpenRecordset(strSql)
rst.MoveFirst
Dim TempUserName As String
Do While Not rst.EOF
TempUserName = Left(rsn!First_name, lngStep) + rsn!Last_Name
If rst!UserName = RTrim(TempUserName) Then
lngStep = lngStep + 1
rst.MoveFirst
Else
rst.MoveNext
End If
Loop
strSqlUpdate = "INSERT into temp_user (id_num, last_name, First_name,
Middle_name, UserName) VALUES ( " & rsn!ID_num & ", '" & rsn!Last_Name &
" ', '" & rsn!First_name & "', '" & rsn!Middle_Name & "' , ' " &
TempUserName & "');"
CurrentDb.Execute strSqlUpdate
rsn.MoveNext
Loop
Set rst = Nothing
Set rsn = Nothing
DoCmd.OpenTable "temp_user"
End Sub
Any help or pointer is greatly appriciated.
username from first, middle and last names. Here is my requirement -
by default first initial and last name of a user is their username.
But if the expected or default username is already taken then if the
user have middle initial, then the username for this user would be
first initial, middle initial and lastname. If the user does not have
middle name then the username for the user would be first two letters
of first name and lastname and if that's already taken then first
three letters of first name and last name and so on.
I already have Active Directory link to my access dabase. I have new student
from our ERP system.
This is what i have so far. It works if there is already a username on the
network next will take first two character of firstname and lastname as
design, but if there no user with that username and if there are two users
with the same name in new
users table then my code won't work or assigns same user names.
I think my problem is right after i INSERT the newly created username is
somehow failed to get into rst.recordset. I tried rst.requery but still
no luck.
Private Sub Command0_Click()
Dim strSql As String
Dim StrSql1 As String
Dim strSqlUpdate As String
Dim rst As DAO.Recordset
Dim rsn As DAO.Recordset
strSql = "SELECT SamAccountName as UserName from Network UNION Select
UserName From temp_user; "
strsql2 = "SELECT id_num, last_name, first_name, middle_name FROM
Admission;"
Set rsn = CurrentDb.OpenRecordset(strsql2)
rsn.MoveFirst
Do While Not rsn.EOF
Dim lngStep As Integer
lngStep = 1
Set rst = CurrentDb.OpenRecordset(strSql)
rst.MoveFirst
Dim TempUserName As String
Do While Not rst.EOF
TempUserName = Left(rsn!First_name, lngStep) + rsn!Last_Name
If rst!UserName = RTrim(TempUserName) Then
lngStep = lngStep + 1
rst.MoveFirst
Else
rst.MoveNext
End If
Loop
strSqlUpdate = "INSERT into temp_user (id_num, last_name, First_name,
Middle_name, UserName) VALUES ( " & rsn!ID_num & ", '" & rsn!Last_Name &
" ', '" & rsn!First_name & "', '" & rsn!Middle_Name & "' , ' " &
TempUserName & "');"
CurrentDb.Execute strSqlUpdate
rsn.MoveNext
Loop
Set rst = Nothing
Set rsn = Nothing
DoCmd.OpenTable "temp_user"
End Sub
Any help or pointer is greatly appriciated.