Runntime Error 3464

[avca]

I created a database with restriction to forms for users using a module.
I am getting a "runtime error 3464 data type mismatch in criteria expression" I marked in red where I get the error. Can anyone help me please!

This is the code for the module

Public Function Permiso(sFormName As String) As Boolean

Dim sActiveUser As String
sActiveUser = DLookup("IdUser", "tblActiveUser")

Dim bPermisoFor As Boolean
bPermisoFor = DLookup("Access", "tblUsersPermits", "IdUsers= '" & sActiveUser & "' AND NameForm= '" & sFormName & "'")
If bPermisoFor = False Then
Call MsgBox("You don't have access to view this form" _
, " Contact your admin", vbCritical, "Attention")
DoCmd.Close acForm, sFormName
End If

End Function

this is the code to call the module in each form

Private Sub Form_Open(Cancel As Integer)
Call Permiso(Me.Name)
End Sub

:banghead::banghead:

 

[pbaldy]

If it's possible for the value not to be there try

Nz(DLookup("Access", "tblUsersPermits", "IdUsers= '" & sActiveUser & "' AND NameForm= '" & sFormName & "'"), 0)

 

[Ranman256]

put a stop point on the red line,
then when code stops, hover cursor over: sActiveUser
I bet that it came back null.

get the active user ID via:
vUserID = Environ("Username")

then look THAT up in the table by:
sActiveUser = DLookup("IdUser", "tblActiveUser", "IdUser='" & vUserID & "'")

 

[isladogs]

Just to add to the previous comments, datatype mismatch indicates that your expression doesn't match the datatype of one of your fields. Your expression assumes two text fields
If idUsers is a number field, the single quotes should be removed

bPermisoFor = DLookup("Access", "tblUsersPermits", "IdUsers=[COLOR="DarkRed"] [B]" & sActiveUser & "[/B][/COLOR] AND NameForm= '" & sFormName & "'")

Also Access is IMHO a bad choice for a field name. Although it seems not a reserved word, it could cause confusion - UserAccess might be better

 

[Mark_]

To add to the others, when I use a where clause I normally create it in a string variable first before passing it. This lets me make sure I know EXACTLY what I am feeding to Access.

Dim asWhere as String
asWhere = "IdUsers= '" & sActiveUser & "' AND NameForm= '" & sFormName & "'"
If IsNull( DLookup("Access", "tblUsersPermits", asWhere ) ) Then
   Call MsgBox("You don't have access to view this form" _
   , " Contact your admin", vbCritical, "Attention")
   DoCmd.Close acForm, sFormName
End If

For troubleshooting this can be invaluable.

 

[avca]

To add to the others, when I use a where clause I normally create it in a string variable first before passing it. This lets me make sure I know EXACTLY what I am feeding to Access.

Dim asWhere as String
asWhere = "IdUsers= '" & sActiveUser & "' AND NameForm= '" & sFormName & "'"
If IsNull( DLookup("Access", "tblUsersPermits", asWhere ) ) Then
   Call MsgBox("You don't have access to view this form" _
   , " Contact your admin", vbCritical, "Attention")
   DoCmd.Close acForm, sFormName
End If

For troubleshooting this can be invaluable.

I have tried adding the where and I am still getting the same 3464 runtime error. it highlights this line.

If IsNull(DLookup("UserAccess", "tblUsersPermits", asWhere)) Then

any other suggestions would be greatly appreciated.

 

[Mark_]

Errorcode 3464 means you have a type mismatch. You are passing the wrong type of variable to something.

To debug, I would start by removing the asWhere from your DLookup. If it work, then set IdUsers OR NameForm to see if that is the issue.

I'm going to take a rather wild guess, but if IdUsers is NOT a string, you would be setting it to sActiveUser, not 'sActiveUser'.