Syntax error

[hinchi1]

I have been messing around with my code when the user logs in. I was watching a video by Steve Bishop and found this very informative. I followed the video to the letter and created the code for my login form. All worked well until i shut down Access and started it again and got the following error message.


I wrote the module code as instructed, see below

Module code

UserAccess = Nz(DLookup("HasAccess", "tblEmployeeAccess", "AccessType=" & TempVars("AccessType") & " AND FormName='" & FormName & "'"), False)
And now when I login the error message points to this line of code

Code behind login button

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("Login", dbOpenSnapshot, dbReadOnly)
rs.FindFirst "UserLogin ='" & Me.txtLoginID & "'"

If rs.NoMatch = True Then
Me.lblWrongUserName.Visible = True
Me.txtLoginID.SetFocus
Exit Sub
End If
Me.lblWrongUserName.Visible = False

If rs!Password <> Nz(Me.txtPassword, "") Then
Me.lblWrongPassword.Visible = True
Me.txtPassword.SetFocus
Exit Sub
End If
Me.lblWrongPassword.Visible = False
DoCmd.OpenForm "frmMenu"
DoCmd.Close acForm, "frmLogin"

TempVars("AccessType") = rs!AccessType.Value

[June7]

FormName and AccessType are fields in table? How do you populate the variable FormName? Maybe:

" AND FormName = '" & Me.Name & "'"

I've never used TempVars. Where are setting the variable AccessType?

Want to provide link to that video?

[Micron]

If I enlarge the tiny picture, I see the expression has a syntax error, which looks like you're losing the tempVars value. The expression seems to evaluate to
"AccessType = AND formName = ..." so something's missing before the AND.

[ssanfu]

I have been messing around with my code when the user logs in. I was watching a video by Steve Bishop and found this very informative. I followed the video to the letter and created the code for my login form.

Actually you didn't "follow the video to the letter" or you changed the code. You changed some object names.

Steve Bishop Video



Since you modified the code, what's up with

Module code

    UserAccess = Nz(DLookup("HasAccess", "tblEmployeeAccess",   "AccessType=" & TempVars("AccessType") & " AND FormName='"  &  FormName & "'"), False)

and

TempVars("AccessType") = rs!AccessType.Value

Where/how are they used in your dB???




--------------------------------------------------------------------------------------
(and I have issues with the SB code - the record set was not closed and the rs object was not destroyed - see my code modifications)
How I modified the code:

Private Sub btnLogin_Click()
    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("tbl1Employees", dbOpenSnapshot, dbReadOnly)

    rs.FindFirst "UserName='" & Me.txtUserName & "'"

    If rs.NoMatch = True Then
        Me.lblWrongUser.Visible = True
        Me.txtUserName.SetFocus

    Else
        Me.lblWrongUser.Visible = False

        If rs!Password <> Nz(Me.txtPassword, "") Then
            Me.lblWrongPass.Visible = True
            Me.txtPassword.SetFocus

        Else
            Me.lblWrongPass.Visible = False
            DoCmd.OpenForm "frmMain"
            DoCmd.Close acForm, "frmLogin"
        End If
    End If

    rs.Close
    Set rs = Nothing
End Sub

[hinchi1]

Actually you didn't "follow the video to the letter" or you changed the code. You changed some object names.

Steve Bishop Video



Since you modified the code, what's up with

Module code

    UserAccess = Nz(DLookup("HasAccess", "tblEmployeeAccess",   "AccessType=" & TempVars("AccessType") & " AND FormName='"  &  FormName & "'"), False)

and

TempVars("AccessType") = rs!AccessType.Value

Where/how are they used in your dB???






--------------------------------------------------------------------------------------
(and I have issues with the SB code - the record set was not closed and the rs object was not destroyed - see my code modifications)
How I modified the code:

Private Sub btnLogin_Click()
    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("tbl1Employees", dbOpenSnapshot, dbReadOnly)

    rs.FindFirst "UserName='" & Me.txtUserName & "'"

    If rs.NoMatch = True Then
        Me.lblWrongUser.Visible = True
        Me.txtUserName.SetFocus

    Else
        Me.lblWrongUser.Visible = False

        If rs!Password <> Nz(Me.txtPassword, "") Then
            Me.lblWrongPass.Visible = True
            Me.txtPassword.SetFocus

        Else
            Me.lblWrongPass.Visible = False
            DoCmd.OpenForm "frmMain"
            DoCmd.Close acForm, "frmLogin"
        End If
    End If

    rs.Close
    Set rs = Nothing
End Sub

I will go through the video again today. i am using this code to try and disable certain buttons for users who will not have permissions to edit or add new data. The can only view the data. Thanks for all you feedback, as ever greatly appreciated.

[hinchi1]

I believe the error is caused by this line in the form load event of my frmMenu as i am trying to either disable or hide command buttons to limit user access.

Code:

Me.cmdOpenPGR.Visible = modGlobal.UserAccess("frmPGRDeskDataEntry1")

if i comment this code out and the user click on the button to open "frmPGRDeskDataEntry1" they do get a message saying they don't have access to the form. So this part works. it is just when i want to hide or disable the command button is where the problem lies. i will go through the video again. For reference i have changed object names to match the names of the objects in my database. Thanks again.

[ssanfu]

Steve Bishop's video on creating a login screen has nothing to do with setting the visible property of buttons.


It would be a lot easier to make suggestions if the dB was available.

Me.cmdOpenPGR.Visible = modGlobal.UserAccess("frmPGRDeskDataEntry1")

If modGlobal is the name of a standard module, the syntax of the line of code above is incorrect. Should not be referencing the module name.
Not sure what "UserAccess" is..... is it a function? A variable?
If a function, what is the code?
If a (global) variable, how is it filled?


What is the code for the form load event of the form "frmMenu"?

[hinchi1]

I watched his video on managing user access. This involves code behind the login screen and then he demonstrates 3 ways to restrict user access to a form.
1. Prevent user from opening form in the objects pane.
2. Code behind a button to prevent user access to a form and 3
Hide the command button so the user has no way what so ever to access a restricted form.

I will watch the video again today and see what I have missed. Thanks again for your feedback.

[hinchi1]

I have been through the video again and cannot see what i am doing wrong, very frustrating.

[orange]

Post a copy of your database as ssanfu suggested.

[hinchi1]

Post a copy of your database as ssanfu suggested.

I found that i could not apply code to my main form but i could to other subsequent forms. not ideal but it works for me. Thanks for all your inputs.