With the help form a this forum member i was able to get my Access VB working.
http://www.access-programmers.co.uk/forums/showthread.php?t=109875
I would really like to convert this to MS SQL SP. What is best way to approach this? I can convert recordset to cursor, but how do i deal with dlookup?
Help
http://www.access-programmers.co.uk/forums/showthread.php?t=109875
I would really like to convert this to MS SQL SP. What is best way to approach this? I can convert recordset to cursor, but how do i deal with dlookup?
Help
Dim StrSql2 As String
Dim strSqlUpdate As String
Dim rsn As DAO.Recordset
Dim TempUserName As String
Dim lngStep As Integer
StrSql2 = "SELECT id_num, last_name, first_name, middle_name FROM Admission;"
Set rsn = CurrentDb.OpenRecordset(StrSql2)
rsn.MoveFirst
Do While Not rsn.EOF
lngStep = 1
Dim mnCheck As Boolean
mnCheck = False
'Propose the first username
TempUserName = Left(rsn!First_name, lngStep) + rsn!Last_Name
'Check to see if the user name exists
Do While Not IsNull(DLookup("UserName", "qryUserName", "UserName = '" & TempUserName & "'"))
'If so, try the next one
If Not IsNull(rsn!middle_name) And mnCheck = False Then
TempUserName = Left(rsn!First_name, 1) + Left(rsn!middle_name, 1) + rsn!Last_Name
mnCheck = True
Else
lngStep = lngStep + 1
TempUserName = Left(rsn!First_name, lngStep) + rsn!Last_Name
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 rsn = Nothing