Results 1 to 15 of 15
  1. #1
    krc9127 is offline Novice
    Windows 11 Access 2007
    Join Date
    Jul 2023
    Posts
    26

    Variable not defined

    I have set my code on my logon button, it keeps telling me that the Security Level has not been defined as a variable.

    VBA Code:
    [COLOR=#0000FF !important]Private
    [COLOR=#0000FF !important]Sub[/COLOR] LogonBtn_Click()
    [COLOR=#0000FF !important]Dim[/COLOR] ID [COLOR=#0000FF !important]As[/COLOR] [COLOR=#0000FF !important]Long[/COLOR], PW [COLOR=#0000FF !important]As[/COLOR] [COLOR=#0000FF !important]String[/COLOR], SL [COLOR=#0000FF !important]As[/COLOR] [COLOR=#0000FF !important]String[/COLOR]

    ID = Nz(DLookup([COLOR=#E29232 !important]"UserID"[/COLOR], [COLOR=#E29232 !important]"UserT"[/COLOR], [COLOR=#E29232 !important]"Username="""[/COLOR] & Username & [COLOR=#E29232 !important]""""[/COLOR]), [COLOR=#C92C2C !important]0[/COLOR])
    [COLOR=#0000FF !important]If[/COLOR] ID = [COLOR=#C92C2C !important]0[/COLOR] [COLOR=#0000FF !important]Then[/COLOR]
    MsgBox [COLOR=#E29232 !important]"User not found"[/COLOR]


    Quit
    [COLOR=#0000FF !important]End[/COLOR] [COLOR=#0000FF !important]If[/COLOR]

    PW = Nz(DLookup([COLOR=#E29232 !important]"Password"[/COLOR], [COLOR=#E29232 !important]"UserT"[/COLOR], [COLOR=#E29232 !important]"UserID="[/COLOR] & ID), [COLOR=#E29232 !important]""[/COLOR])
    [COLOR=#0000FF !important]If[/COLOR] PW <> Password [COLOR=#0000FF !important]Then[/COLOR]
    MsgBox [COLOR=#E29232 !important]"Incorrect Password"[/COLOR]
    Quit
    [COLOR=#0000FF !important]End[/COLOR] [COLOR=#0000FF !important]If[/COLOR]

    SL = Nz(DLookup([COLOR=#E29232 !important]"SecurityLevel"[/COLOR], [COLOR=#E29232 !important]"UserT"[/COLOR], [COLOR=#E29232 !important]"UserID="[/COLOR] & ID), [COLOR=#E29232 !important]""[/COLOR])
    [COLOR=#0000FF !important]If[/COLOR] SL <> SecurityLevel [COLOR=#0000FF !important]Then[/COLOR]
    MsgBox [COLOR=#E29232 !important]"Access Denied"[/COLOR]
    Quit
    [COLOR=#0000FF !important]End[/COLOR] [COLOR=#0000FF !important]If[/COLOR]

    TempVars([COLOR=#E29232 !important]"SecurityLevel"[/COLOR]) = SecurityLevel.Value
    DoCmd.OpenForm [COLOR=#E29232 !important]"MainMenuF"[/COLOR]
    DoCmd.Close acForm, [COLOR=#0000FF !important]Me[/COLOR].Name, acSaveYes

    [/COLOR]

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Crossposted at https://www.mrexcel.com/board/thread.../#post-6087472

    with at least readable code.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Don't know where all that color stuff comes from. Here's the code with that stripped out.
    The VBA as written would expect SecurityLevel to be a control on the form or perhaps a global variable.

    Code:
    Private Sub LogonBtn_Click()
        Dim ID As Long, PW As String, SL As String
        ID = Nz(DLookup("UserID", "UserT", "Username=""" & UserName & """"), 0)
        If ID = 0 Then
            MsgBox "User not found"
            Quit
        End If
    
    
        PW = Nz(DLookup("Password", "UserT", "UserID=" & ID), "")
        If PW <> Password Then
            MsgBox "Incorrect Password"
            Quit
        End If
    
    
        SL = Nz(DLookup("SecurityLevel", "UserT", "UserID=" & ID), 0)
        If SL <> SecurityLevel Then
            MsgBox "Access Denied"
            Quit
        End If
    
    
        TempVars "SecurityLevel" = SecurityLevel.Value
        DoCmd.OpenForm "MainMenuF"
        DoCmd.Close acForm, Me.Name, acSaveYes
    End Sub

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by davegri View Post
    Don't know where all that color stuff comes from. Here's the code with that stripped out.
    The VBA as written would expect SecurityLevel to be a control on the form or perhaps a global variable.

    Code:
    Private Sub LogonBtn_Click()
        Dim ID As Long, PW As String, SL As String
        ID = Nz(DLookup("UserID", "UserT", "Username=""" & UserName & """"), 0)
        If ID = 0 Then
            MsgBox "User not found"
            Quit
        End If
    
    
        PW = Nz(DLookup("Password", "UserT", "UserID=" & ID), "")
        If PW <> Password Then
            MsgBox "Incorrect Password"
            Quit
        End If
    
    
        SL = Nz(DLookup("SecurityLevel", "UserT", "UserID=" & ID), 0)
        If SL <> SecurityLevel Then
            MsgBox "Access Denied"
            Quit
        End If
    
    
        TempVars "SecurityLevel" = SecurityLevel.Value
        DoCmd.OpenForm "MainMenuF"
        DoCmd.Close acForm, Me.Name, acSaveYes
    End Sub
    Dave,
    Code was coloured over in Mr Excel. Probably just copied and pasted without even looking at it?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by Welshgasman View Post
    Dave,
    Code was coloured over in Mr Excel. Probably just copied and pasted without even looking at it?
    OK, good to know. I'm not an Excel coder, just an occasional casual user.

  6. #6
    krc9127 is offline Novice
    Windows 11 Access 2007
    Join Date
    Jul 2023
    Posts
    26
    I am new to trying to create this level of database. It has always been just simple data collection, nothing with needing security level. I have a User Table set up with Username, Password and Security Level. I need when the Main Menu Form is loaded only users with certain security levels to be able to do certain things. For example level 1 will be able to use Transit For and TODD Form, level 2 will be able to do that and run all the reports on the menu. Level 3 is the admin.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by davegri View Post
    OK, good to know. I'm not an Excel coder, just an occasional casual user.
    It is just the site. It can show different types in colour like the editor can.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Might be a bit too much for your needs but please feel free to us my free utility:
    http://forestbyte.com/ms-access-util...access-levels/

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    krc9127 is offline Novice
    Windows 11 Access 2007
    Join Date
    Jul 2023
    Posts
    26
    Thank you Gicu

  10. #10
    krc9127 is offline Novice
    Windows 11 Access 2007
    Join Date
    Jul 2023
    Posts
    26
    Click image for larger version. 

Name:	Screenshot 2023-07-26 140949.png 
Views:	21 
Size:	25.2 KB 
ID:	50541Gicu, it says I can't use it on my system.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, yes, I haven't updated it for 64 bit, give me some time and I'll post an update.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is a 64 bit version, I will update my website later.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    krc9127 is offline Novice
    Windows 11 Access 2007
    Join Date
    Jul 2023
    Posts
    26
    Thank you Gicu

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @Gicu


    Sorry but whilst it compiles due to PtrSafe, your API conversions contain several errors
    1. Pointers such as hWnd need to be LongPtr for 64-bit e.g. See ShellExecute API in module Utility Functions which should be:
    Code:
    Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias _
            "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, lpParameters As String, _
            ByVal lpDirectory As String, ByVal nShowCMd As Long) As LongPtr
    Also for some reason, that API is listed twice


    Also this should be
    Code:
    Private Declare PtrSafe Function apiNetAPIBufferFree _
            Lib "netapi32.dll" Alias "NetApiBufferFree" _
            (ByVal buffer As LongPtr) _
            As Long
    NOTE: I haven't checked all the APIs


    2. Using Win64 is almost always superfluous in conditional compilation. Just use:
    Code:
     
    #If VBA7 Then 'for A2010 or later, 32 / 64-bit
            ....
    #Else 'for A2007 or earlier
            ....
    #End If

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Thanks Colin, I'll have a look. The API's are not really needed for the access level control code, I brought the modules in the demo file because they had some functions I was using but most are not needed. But thank you very much for the heads up, I thought I tested them in a 64 bit installation and they all seemed to work but I'll make those changes soon.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-23-2022, 03:33 PM
  2. Variable not defined
    By Greg in forum Forms
    Replies: 8
    Last Post: 08-10-2020, 07:18 PM
  3. Object variable or With block variable not defined
    By PorscheMan in forum Programming
    Replies: 3
    Last Post: 01-16-2013, 01:53 PM
  4. Compile error: Variable not defined
    By HarryScofs in forum Access
    Replies: 8
    Last Post: 07-25-2011, 09:06 AM
  5. DSum criteria using a variable that has been defined
    By beanhead0321 in forum Programming
    Replies: 5
    Last Post: 07-24-2011, 09:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums