Access Login Form with user level page direction-GUID issue

J

JohnMichael72

Gold Supporter
Local time
Today, 15:46
Joined
Jul 25, 2019
Messages
11
Hello All,
I am in desperate need of some assistance with modifying some code in VB. I created a database for work some time ago that has VB code written that requires the user to log in. Once they log in, depending upon the security level assigned to that user, they are taken to one of three forms. The database worked perfectly until it was decided to convert the Primary Keys in the tables to GUID's. Now, I need to modify the VB code on the login form so that it can handle the use of GUID's. The issue is coming from the section of the code that directs the user to the correct form when logging in.

The code for the original database that uses an Integer as the primary Key:
Code: 
Private Sub Command1_Click()
Dim UserName, Temppass As String
Dim UserLevel, ID As Integer
Dim TempLogin As TempVar

If IsNull(Me.txtLoginID) Then
    MsgBox "Please enter UserID", vbInformation, "UserID requeired"
    Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please enter Password", vbInformation, "Password requeired"
    Me.txtPassword.SetFocus
Else
    If (IsNull(DLookup("UserID", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "' And password = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Invalid LoginID or Password!"
    Else
            TempVars!TempLogin = Me.txtLoginID.Value
            
            UserName = DLookup("[Username]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            UserLevel = DLookup("[UserSecurity]", "tbluser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            Temppass = DLookup("[password]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            ID = DLookup("[Userid]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            'DoCmd.Close
        If (Temppass = "password") Then
                DoCmd.Close
                MsgBox "Please change Password", vbInformation, "New password requeired"
                DoCmd.OpenForm "ChangePassword", , , "[Userid] = " & ID
        ElseIf IsNull(DLookup("answer1", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Or IsNull(DLookup("answer2", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Or IsNull(DLookup("answer3", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Then
                DoCmd.Close
                msg = "Your security questions have not been set up. " _
                      & vbCr & "Do you want to set it up now?"
                Style = vbYesNo + vbQuestion
                Title = "Set Up Security Question?"
                Response = MsgBox(msg, Style, Title)
                If Response = vbYes Then
                    DoCmd.OpenForm "ChangePassword", , , "UserID =" & ID
                    Exit Sub
                End If
                If Response = vbNo Then
                    'open different form according to user level
                    If UserLevel = 3 Then ' for superadmin
                        DoCmd.ShowToolbar "Ribbon", acToolbarYes
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.SelectObject acTable, , True
                        DoCmd.OpenForm "Super Admin Form"
                    
                    ElseIf UserLevel = 1 Then ' for admin
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Admin Form"
                        
                    Else
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Main Form"
                    End If
                End If
        Else
            DoCmd.Close
            'open different form according to user level
                If UserLevel = 3 Then ' for superadmin
                    DoCmd.ShowToolbar "Ribbon", acToolbarYes
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.SelectObject acTable, , True
                    DoCmd.OpenForm "Super Admin Form"
                    
                ElseIf UserLevel = 1 Then ' for admin
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Admin Form"

                Else
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Main Form"

                End If
        End If
    End If
End If
End Sub


The Admin UserLevel GUID is BE698569-F315-472E-B0E6-814A51B73707
The SuperAdmin UserLevel GUID is D3A01D69-5BF1-4D44-9780-C8ACB033184A

The issue is coming from the "open a different form according to user level" part. I no longer use the 1 or 3 to define the user levels because it is now a GUID. I am thinking it has something to do with needing to convert it to a string or something along those lines. Any help would be immensely appreciated!!
JM
 
Last edited:
Hi John and welcome to AWF.
Your post was moderated which sometimes happens with new users.

I'm intrigued why anyone thought it a good idea to convert the primary key fields to GUIDs.
I'm tempted to suggest that decision be reversed!
Hopefully someone who knows more than me about GUIDs should be along before too long to offer assistance. Good luck
 
Hi. Welcome to the forum! I haven't used GUID in a while but I seem to recall there is a GuidToString function, I think.
 
I was definitely not happy about converting to GUID's...everything works so perfectly on the old database...UGH.
 
theDBguy said:
Hi. Welcome to the forum! I haven't used GUID in a while but I seem to recall there is a GuidToString function, I think.
Click to expand...

There definitely is, I am just having a really difficult time with the placement...the code used on the login form is pretty long and I am very much a beginner. Most of the code I found and modified to fit my database.
 
what is the fieldname that was converted to GUID, is it the UserID?
 
Yes Sir, the UserID was converted. I changed the code at the top "Dim UserLevel, ID As Integer" to "As String" thinking that was part of the problem. I still cannot get the UserLevel part to work with the GUID's now though.
 
This is the part of the code that I believe I am having issues with.

Code: 
'open different form according to user level
                    If UserLevel =  Then  ' for superadmin
                        DoCmd.ShowToolbar "Ribbon", acToolbarYes
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.SelectObject acTable, , True
                        DoCmd.OpenForm "Super Admin Form"
                    
                    ElseIf UserLevel =  Then ' for admin
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Admin Form"
                        
                    Else
                        DoCmd.ShowToolbar "Ribbon", acToolbarYes
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.SelectObject acTable, , True
                        DoCmd.OpenForm "Super Admin Form"
                    End If
                End If
        Else
            DoCmd.Close
            'open different form according to user level
                If UserLevel =  Then ' for superadmin
                    DoCmd.ShowToolbar "Ribbon", acToolbarYes
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.SelectObject acTable, , True
                    DoCmd.OpenForm "Super Admin Form"
                    
                ElseIf UserLevel =  Then ' for admin
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Admin Form"

                Else
                    DoCmd.ShowToolbar "Ribbon", acToolbarYes
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.SelectObject acTable, , True
                        DoCmd.OpenForm "Super Admin Form"

                End If
        End If
    End If
End If
End Sub
 
try this:
Code: 
Private Sub Command1_Click()
Dim UserName, Temppass As String
Dim UserLevel, ID  As String
Dim TempLogin As TempVar

If IsNull(Me.txtLoginID) Then
    MsgBox "Please enter UserID", vbInformation, "UserID requeired"
    Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please enter Password", vbInformation, "Password requeired"
    Me.txtPassword.SetFocus
Else
    If (IsNull(DLookup("StringFromGUID(UserID)", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "' And password = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Invalid LoginID or Password!"
    Else
            TempVars!TempLogin = Me.txtLoginID.Value
            
            UserName = DLookup("[Username]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            UserLevel = DLookup("[UserSecurity]", "tbluser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            Temppass = DLookup("[password]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            ID = DLookup("StringFromGUID([Userid])", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            'DoCmd.Close
        If (Temppass = "password") Then
                DoCmd.Close
                MsgBox "Please change Password", vbInformation, "New password requeired"
                DoCmd.OpenForm "ChangePassword", , , "StringFromGUID([Userid]) = " & Chr(34) ID & Chr(34)
        ElseIf IsNull(DLookup("answer1", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Or IsNull(DLookup("answer2", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Or IsNull(DLookup("answer3", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Then
                DoCmd.Close
                msg = "Your security questions have not been set up. " _
                      & vbCr & "Do you want to set it up now?"
                Style = vbYesNo + vbQuestion
                Title = "Set Up Security Question?"
                Response = MsgBox(msg, Style, Title)
                If Response = vbYes Then
                    DoCmd.OpenForm "ChangePassword", , , "StringFromGUID(UserID) =" & Chr(34) & ID & Chr(34)
                    Exit Sub
                End If
                If Response = vbNo Then
                    'open different form according to user level
                    If UserLevel = 3 Then ' for superadmin
                        DoCmd.ShowToolbar "Ribbon", acToolbarYes
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.SelectObject acTable, , True
                        DoCmd.OpenForm "Super Admin Form"
                    
                    ElseIf UserLevel = 1 Then ' for admin
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Admin Form"
                        
                    Else
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Main Form"
                    End If
                End If
        Else
            DoCmd.Close
            'open different form according to user level
                If UserLevel = 3 Then ' for superadmin
                    DoCmd.ShowToolbar "Ribbon", acToolbarYes
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.SelectObject acTable, , True
                    DoCmd.OpenForm "Super Admin Form"
                    
                ElseIf UserLevel = 1 Then ' for admin
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Admin Form"
                Else
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Main Form"

                End If
        End If
    End If
End If
End Sub
 
Arnelgp,
Thank you very much for helping. So the issue isnt with the UserID's or password changing or anything like that. The issues rears its head with the custom page direction which is based upon the "UserLevel". There is SuperAdmin, Admin and User as three user levels. Before I converted to GUID, this was not an issue as I simply used the integer value of the User Level, 3 for Superadmin, 1 for Admin. Those are no longer integers but GUID's. I can send you a stripped database if that would help.
 
I think you need to supply the password also.
change this:
Code: 
          UserName = DLookup("[Username]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            UserLevel = DLookup("[UserSecurity]", "tbluser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            Temppass = DLookup("[password]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
            ID = DLookup("StringFromGUID([Userid])", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")
to
Code: 
            UserName = DLookup("[Username]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value &  "' And password = '" & Me.txtPassword.Value & "'")
            UserLevel = DLookup("[UserSecurity]", "tbluser", "[UserLogin] = '" & Me.txtLoginID.Value & & "' And password = '" & Me.txtPassword.Value & "'")
            Temppass = DLookup("[password]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & & "' And password = '" & Me.txtPassword.Value & "'")
            ID = DLookup("StringFromGUID([Userid])", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "' And password = '" & Me.txtPassword.Value & "'")
complete code
Code: 
Private Sub Command1_Click()
Dim UserName, Temppass As String
Dim UserLevel, ID Variant
Dim TempLogin As TempVar

If IsNull(Me.txtLoginID) Then
    MsgBox "Please enter UserID", vbInformation, "UserID requeired"
    Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please enter Password", vbInformation, "Password requeired"
    Me.txtPassword.SetFocus
Else
    If (IsNull(DLookup("StringFromGUID(UserID)", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "' And password = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Invalid LoginID or Password!"
    Else
            TempVars!TempLogin = Me.txtLoginID.Value
            
            UserName = DLookup("[Username]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value &  "' And password = '" & Me.txtPassword.Value & "'")
            UserLevel = DLookup("[UserSecurity]", "tbluser", "[UserLogin] = '" & Me.txtLoginID.Value & & "' And password = '" & Me.txtPassword.Value & "'")
            Temppass = DLookup("[password]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & & "' And password = '" & Me.txtPassword.Value & "'")
            ID = DLookup("StringFromGUID([Userid])", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "' And password = '" & Me.txtPassword.Value & "'")
            'DoCmd.Close
        If (Temppass = "password") Then
                DoCmd.Close
                MsgBox "Please change Password", vbInformation, "New password requeired"
                DoCmd.OpenForm "ChangePassword", , , "StringFromGUID([Userid]) = " & Chr(34) ID & Chr(34)
        ElseIf IsNull(DLookup("answer1", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Or IsNull(DLookup("answer2", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Or IsNull(DLookup("answer3", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")) Then
                DoCmd.Close
                msg = "Your security questions have not been set up. " _
                      & vbCr & "Do you want to set it up now?"
                Style = vbYesNo + vbQuestion
                Title = "Set Up Security Question?"
                Response = MsgBox(msg, Style, Title)
                If Response = vbYes Then
                    DoCmd.OpenForm "ChangePassword", , , "StringFromGUID(UserID) =" & Chr(34) & ID & Chr(34)
                    Exit Sub
                End If
                If Response = vbNo Then
                    'open different form according to user level
                    If UserLevel = 3 Then ' for superadmin
                        DoCmd.ShowToolbar "Ribbon", acToolbarYes
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.SelectObject acTable, , True
                        DoCmd.OpenForm "Super Admin Form"
                    
                    ElseIf UserLevel = 1 Then ' for admin
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Admin Form"
                        
                    Else
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Main Form"
                    End If
                End If
        Else
            DoCmd.Close
            'open different form according to user level
                If UserLevel = 3 Then ' for superadmin
                    DoCmd.ShowToolbar "Ribbon", acToolbarYes
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.SelectObject acTable, , True
                    DoCmd.OpenForm "Super Admin Form"
                    
                ElseIf UserLevel = 1 Then ' for admin
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Admin Form"
                Else
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Main Form"

                End If
        End If
    End If
End If
End Sub
 
This is the area that is the issue. Now that we are using GUID's, the "If UserLevel = 3" and "If UserLevel = 1" no longer work as the 1 and 3 used to reference the old autonumber integer values for Admin and SuperAdmin. Those values are now GUID's so I am thinking I need to be able to specify the GUID that refers to the SuperAdmin and Admin levels.

Code: 
'open different form according to user level
                    If UserLevel = 3 Then ' for superadmin
                        DoCmd.ShowToolbar "Ribbon", acToolbarYes
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.SelectObject acTable, , True
                        DoCmd.OpenForm "Super Admin Form"
                    
                    ElseIf UserLevel = 1 Then ' for admin
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Admin Form"
                        
                    Else
                        DoCmd.ShowToolbar "Ribbon", acToolbarNo
                        DoCmd.NavigateTo "acNavigationCategoryObjectType"
                        DoCmd.RunCommand acCmdWindowHide
                        DoCmd.OpenForm "Main Form"
                    End If
                End If
        Else
            DoCmd.Close
            'open different form according to user level
                If UserLevel = 3 Then ' for superadmin
                    DoCmd.ShowToolbar "Ribbon", acToolbarYes
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.SelectObject acTable, , True
                    DoCmd.OpenForm "Super Admin Form"
                    
                ElseIf UserLevel = 1 Then ' for admin
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Admin Form"
                Else
                    DoCmd.ShowToolbar "Ribbon", acToolbarNo
                    DoCmd.NavigateTo "acNavigationCategoryObjectType"
                    DoCmd.RunCommand acCmdWindowHide
                    DoCmd.OpenForm "Main Form"

                End If
        End If
    End If
End If
End Sub
 
copy the code I gave you plus edit your UserLevel and change it to String.
now open the table and copy/paste the guid's on their respective leve, ie:
Code: 
If UserLevel = "{the guid that you copied here}" Then
…
…
End If
'another Userlevel
If UserLevel ="{…..}" Then 
…
…
End If
are all the fields changed to GUID or just UserID and UserLevel?

I made typo error modify to this:
Code: 
          UserLevel = DLookup("StringFromGUID([UserSecurity])", "tbluser", "[UserLogin] = '" & Me.txtLoginID.Value  & "' And password = '" & Me.txtPassword.Value & "'")
            Temppass = DLookup("[password]", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value  &  "' And password = '" & Me.txtPassword.Value & "'")
 
Last edited:
Thank you so much!! Adding the StringFromGUID to the UserLevel worked!! Also, I was not adding {} to the "If UserLevel = ..." code before. You have helped me immensely and I thank you so much!!!!!!!!

JM
 
cheers! we're all good!
 
You must log in or register to reply here.

Similar threads

D
Solved Change password using tempvar not passing to following form
Replies
4
Views
308
Gasman
Gasman
I
Login Form - Check if password reset flag before continuing.
Replies
16
Views
1,492
Jason Lee Hayes
Jason Lee Hayes
Mylton
Solved Difficulty with login and permission
Replies
16
Views
1,365
Mylton
Mylton
M
Solved Hide ribbon code not working
2
Replies
20
Views
2,291
isladogs
isladogs
A
How i make remember password in form login on my pc only !
Replies
7
Views
1,241
KitaYama
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom