User Access

[Benwatson]

Hello
i have this vba code that is supposed to resrtict access to users based on their access level what i have is a table that shows the access level and the forms they have access to and a tick box that says whether they have access to that form. now the problem im finding is that if i give them access to one form it will give them access to all forms but if i put in the hasaccess table that they dont have access to a form then they cannot access any forms :/

If Nz(DLookup("hasaccess", "tblhasaccess", "accesslvl=" & TempVars("AccessLevel") & " AND formname='" & Me.Name & "'"), False) = False Then
        MsgBox "You Do Not Have Access"
        DoCmd.close acForm, "Palletinpter"
        DoCmd.OpenForm ("PalletMenu")
    End If

it feels like if its not finding the formname then its looking for the closest match im not sure though could anyone advise me on how i can fix this

 

[sneuberg]

I suggest putting

Debug.Print "accesslvl=" & TempVars("AccessLevel") & " AND formname='" & Me.Name & "'"

before this code so you can see in the Immediate Window if the criteria is really want you think it is.

Where and how is TempVars("AccessLevel") set?

 

[Benwatson]

hello steve i did the debug mate and it did come up with what it should be for the criteria the tempvars is set when the user logs in

so even though the criterea is showing what i thought it would why would it allow access to a user that is not marked to have access :/ its driving me nuts

i do have this code it seems to work

If TempVars("AccessLevel") <> 9 Then
        MsgBox "You Do Not Have Access"
        DoCmd.close acForm, "Shiftleadersmenu"
        DoCmd.OpenForm ("Main Menu")

    End If

but im not sure how to allow mulitple access levels to access the same page

 

[The_Doc_Man]

If Nz(DLookup("hasaccess", "tblhasaccess", "accesslvl=" & TempVars("AccessLevel") & " AND formname='" & Me.Name & "'"), False) = False Then
        MsgBox "You Do Not Have Access"
        DoCmd.close acForm, "Palletinpter"
        DoCmd.OpenForm ("PalletMenu")
    End If

First problem: DLookup("hasaccess", "tblhasaccess", "accesslvl="... is syntactically wrong. It SHOULD read something more like DLookup("[hasaccess]", "tblhasaccess", "accesslvl="...

You forgot the brackets on the field name. For DLookup, the field name needs brackets. The table name does not.

Second comment: If you have a control-panel that your users to launch the forms, put your test in the Button's OnClick routine and just put your message box without opening the selected form in the first place. If you don't open it, you won't have to close it or clean it up.

Third comment: The code involved seems to indicate (or at least I read it this way) that the test is done inside the open form AND it tries to open your main menu again. Does the Main Menu form close a lot and open a lot? If so, you could do yourself a lot of good by not closing the Main Menu every time. You can do a forum search or web search on how to programmatically MINIMIZE and RESTORE the Main Menu without forcing it to close. Hint: Look up the OnEnter and OnExit events, which would be the places where you might wish to do something constructive regarding the main and child forms. You could minimize the Main Menu in the FormExit event and maximize or restore it in the FormEnter event. Just a suggestion, but one that might save you some overhead and CPU response time, since the Main form will Maximize a lot more quickly than it would ever Open.

 

[sneuberg]

For DLookup, the field name needs brackets. The table name does not.

I thought brackets were required when a field name had spaces in it or was a reserved name. Why would they be required here and how would one know that? Microsoft doesn't say anything about brackets on this Web page about DLookup.

 

[Benwatson]

so i tried with the brackets and it didnt make any difference to the outcome of the code

i tried a debug on the dlookup

Debug.Print Nz(DLookup("hasaccess", "tblhasaccess", "accesslvl=" & TempVars("AccessLevel") & " AND formname='" & Me.Name & "'"), False)

and it returns a -1, when it should return a true or false as the hasaccess field is a tick box which indicates true or false so i think this is where the issue lies

 

[MarkK]

You can execute an expression in DLookup()...

Private Sub Test108740192471()
    Debug.Print DLookup("1.12 * (value + sort + dollars)", "tTestData", "ID = 9")
End Sub

 

[The_Doc_Man]

and it returns a -1, when it should return a true or false

Ah, but -1 is TRUE. (Zero would be FALSE.) I don't recall that DLookup returns TRUE/FALSE in the traditional sense. It returns the internal numbers that equal the bit patterns for TRUE and FALSE.

Try this experiment. Since you are expecting a T/F answer anyway,...

If DLookup("[whatever]","whereever", "[flag]=some-value") = TRUE Then 
    {whatever you do for true}
Else
    {whatever you do for false}
End If

I'll bet it will behave correctly.

 

[Benwatson]

Hey all thanks for all your advice i couldn't get the dlookup to work for the life of me so i used this instead

If TempVars("AccessLevel") = 8 Then
    DoCmd.OpenForm ("Palletediter")
    DoCmd.close acForm, ("PalletMenu")
    Exit Sub
    
ElseIf TempVars("AccessLevel") = 2 Then
    DoCmd.OpenForm ("Palletediter")
    DoCmd.close acForm, ("PalletMenu")
    Exit Sub
    
ElseIf TempVars("AccessLevel") = 9 Then
    DoCmd.OpenForm ("Palletediter")
    DoCmd.close acForm, ("PalletMenu")
    Exit Sub
End If
MsgBox "You Do Not Have Access"

May seem a little messy but it works and have no need for a table to do a Dlookup on i used this on the on click event of the button that takes you too the form needed. the only problem with this is that if you were to open the form from the "all access objects" bar then it would open but seeing as this will be completely locked down from the end user it shouldn't be an issue.

Again thank you all it is very much appreciated

 

[MarkK]

To match multiple items you can also use Select Case, like...

Select Case TempVars("AccessLevel")
   Case 2, 8, 9
      DoCmd.OpenForm "Palletediter"
      DoCmd.Close acForm, "PalletMenu"
   Case Else
      MsgBox "You Do Not Have Access"
End Select