multiple criteria in dlookup

[Song Su]

I already have a function fOSUserName() setup.

On the form open event:

If userid is in the tblUsers AND field L has value R Then
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
Else ' Field L has value E
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True
End If

I did the first part (evaluate if userid is in the table) but I don't know
how to add 2nd part (AND L = R)

DLookup("[userid]", "tblUsers", "[userid] = " & Chr(34) &
Left(fOSUserName(), 8) & Chr(34))

 

[John W. Vinson]

I did the first part (evaluate if userid is in the table) but I don't know
how to add 2nd part (AND L = R)

The third argument to a domain function is a valid SQL WHERE clause without
the word WHERE. One way to build it is to create a query in the grid that gets
you the desired results, and view that query's SQL.

In this case, try

DLookup("[userid]", "tblUsers", "[userid] = " & Chr(34) &
Left(fOSUserName(), 8) & Chr(34) & " AND [L] = " & Chr(34) & "R" & Chr(34))

or, a bit more readably since a userid isn't likely to contain apostrophes,
just use ' as the string delimiter:

DLookup("[userid]", "tblUsers", "[userid] = '" & Left(fOSUserName(), 8) &
"' AND [L] = 'R'")

 

[Tom van Stiphout]

DLookup("[userid]", "tblUsers", "[userid] = " & Chr(34) &
Left(fOSUserName(), 8) & Chr(34) & " AND L='R')

(instead of the Chr(34) you can also use single-quotes)

-Tom.
Microsoft Access MVP

 

[Song Su]

This is my complete code. There is no error in compiling. But when I open
the form, error message says " Run-time error 13, type mismatch" and it
points to If line. What did I do wrong?

Private Sub Form_Open(Cancel As Integer)
If DLookup("[userid]", "tblUsers", "[userid] = '" & Left(fOSUserName(),
8) & "' AND [L] = 'VIEW'") Then
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
Else
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True
End If

End Sub

I did the first part (evaluate if userid is in the table) but I don't know
how to add 2nd part (AND L = R)

The third argument to a domain function is a valid SQL WHERE clause
without
the word WHERE. One way to build it is to create a query in the grid that
gets
you the desired results, and view that query's SQL.

In this case, try

DLookup("[userid]", "tblUsers", "[userid] = " & Chr(34) &
Left(fOSUserName(), 8) & Chr(34) & " AND [L] = " & Chr(34) & "R" &
Chr(34))

or, a bit more readably since a userid isn't likely to contain
apostrophes,
just use ' as the string delimiter:

DLookup("[userid]", "tblUsers", "[userid] = '" & Left(fOSUserName(), 8)
&
"' AND [L] = 'R'")

 

[John W. Vinson]

This is my complete code. There is no error in compiling. But when I open
the form, error message says " Run-time error 13, type mismatch" and it
points to If line. What did I do wrong?

Private Sub Form_Open(Cancel As Integer)
If DLookup("[userid]", "tblUsers", "[userid] = '" & Left(fOSUserName(),
8) & "' AND [L] = 'VIEW'") Then

ok... you're looking up a UserID. What are you doing with it?? Treating it as
either TRUE or FALSE it would seem...

Perhaps this should be

If IsNull(DLookup("[userid]", "tblUsers",
"[userid] = '" & Left(fOSUserName(), 8) & "' AND [L] = 'VIEW'")) Then

The DLookUp will be the UserID if it exists, and NULL if it doesn't.

 

[Song Su]

Following code is fine but userID with VIEW value in L field still can edit
record. In the tblUsers, we have 2 kind of record. In field [L], View is
view only and EDIT can edit. Please help.

Private Sub Form_Open(Cancel As Integer)
If IsNull(DLookup("[userid]", "tblUsers", "[userid] = '" &
Left(fOSUserName(), 8) & "' AND [L] = 'VIEW'")) Then
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
Else
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True
End If

End Sub

This is my complete code. There is no error in compiling. But when I open
the form, error message says " Run-time error 13, type mismatch" and it
points to If line. What did I do wrong?

Private Sub Form_Open(Cancel As Integer)
If DLookup("[userid]", "tblUsers", "[userid] = '" &
Left(fOSUserName(),
8) & "' AND [L] = 'VIEW'") Then

ok... you're looking up a UserID. What are you doing with it?? Treating it
as
either TRUE or FALSE it would seem...

Perhaps this should be

If IsNull(DLookup("[userid]", "tblUsers",
"[userid] = '" & Left(fOSUserName(), 8) & "' AND [L] = 'VIEW'")) Then

The DLookUp will be the UserID if it exists, and NULL if it doesn't.

 

[Andy Hull]

Hi

Although the code should be fine I would change the logic slightly so that
if, for some reason, a userid isn't found the default will be to set their
access to View.

At the moment, if the dlookup fails it will aloow the user to Edit.

I would use following code...

---------------------------------------------
If dlookup("[L]", "tblUsers", "[userid] = '" & left(fOSUserName(), 8) & "'")
= "EDIT" then

me.AllowAddittions = True
me.AllowEdits = True
me.AllowDeletions = True
Else
me.AllowAddittions = False
me.AllowEdits = False
me.AllowDeletions = False
End If
---------------------------------------------

If this is still failing you should verify that the function fOSUserName is
returning the value you are expecting (ie userid) and that this can match
exactly with the entries in tblUsers. You may need to turn both into CAPS if
the test is being performed as case sensitive.

hth

Andy Hull

Following code is fine but userID with VIEW value in L field still can edit
record. In the tblUsers, we have 2 kind of record. In field [L], View is
view only and EDIT can edit. Please help.

Private Sub Form_Open(Cancel As Integer)
If IsNull(DLookup("[userid]", "tblUsers", "[userid] = '" &
Left(fOSUserName(), 8) & "' AND [L] = 'VIEW'")) Then
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
Else
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True
End If

End Sub

This is my complete code. There is no error in compiling. But when I open
the form, error message says " Run-time error 13, type mismatch" and it
points to If line. What did I do wrong?

Private Sub Form_Open(Cancel As Integer)
If DLookup("[userid]", "tblUsers", "[userid] = '" &
Left(fOSUserName(),
8) & "' AND [L] = 'VIEW'") Then

ok... you're looking up a UserID. What are you doing with it?? Treating it
as
either TRUE or FALSE it would seem...

Perhaps this should be

If IsNull(DLookup("[userid]", "tblUsers",
"[userid] = '" & Left(fOSUserName(), 8) & "' AND [L] = 'VIEW'")) Then

The DLookUp will be the UserID if it exists, and NULL if it doesn't.