Access Login Form with user level page direction-GUID issue (1 Viewer)

JohnMichael72

Gold Supporter
Local time
Yesterday, 22:31
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:

isladogs

MVP / VIP
Local time
Today, 05:31
Joined
Jan 14, 2017
Messages
18,186
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
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:31
Joined
Oct 29, 2018
Messages
21,358
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.
 

JohnMichael72

Gold Supporter
Local time
Yesterday, 22:31
Joined
Jul 25, 2019
Messages
11
I was definitely not happy about converting to GUID's...everything works so perfectly on the old database...UGH.
 

JohnMichael72

Gold Supporter
Local time
Yesterday, 22:31
Joined
Jul 25, 2019
Messages
11
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.

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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:31
Joined
May 7, 2009
Messages
19,169
what is the fieldname that was converted to GUID, is it the UserID?
 

JohnMichael72

Gold Supporter
Local time
Yesterday, 22:31
Joined
Jul 25, 2019
Messages
11
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.
 

JohnMichael72

Gold Supporter
Local time
Yesterday, 22:31
Joined
Jul 25, 2019
Messages
11
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:31
Joined
May 7, 2009
Messages
19,169
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
 

JohnMichael72

Gold Supporter
Local time
Yesterday, 22:31
Joined
Jul 25, 2019
Messages
11
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:31
Joined
May 7, 2009
Messages
19,169
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
 

JohnMichael72

Gold Supporter
Local time
Yesterday, 22:31
Joined
Jul 25, 2019
Messages
11
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:31
Joined
May 7, 2009
Messages
19,169
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:

JohnMichael72

Gold Supporter
Local time
Yesterday, 22:31
Joined
Jul 25, 2019
Messages
11
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:31
Joined
May 7, 2009
Messages
19,169
cheers! we're all good!
 

Users who are viewing this thread

Top Bottom