IF statement with array

[Pendragon]

I'm sure I've seen something in these threads but my searches are coming up
empty. Is there an IN or NOT IN statement, and if so, is it tied to an array?

Here's what I want to do - perform the same function(s) if the user is one
of five authorized; if not, everyone gets an "Access denied" message. What
is below could be way off, but hopefully you can interpret and correct.

Dim A as variant
Dim Empl as string
Empl = CurrentUser()

A = Array("John","Jane","Mary","Bob","Bill")

IF Empl Not in A THEN
msgbox "Access Denied." etc etc
ELSE
DoCmd.OpenForm etc etc
END IF

Just thought there was something quicker/more efficient than typing out the
same function to perform for the five individuals using IF/ELSEIF statements
or a SELECT CASE statement.

Thanks!

 

[Marshall Barton]

I'm sure I've seen something in these threads but my searches are coming up
empty. Is there an IN or NOT IN statement, and if so, is it tied to an array?

Here's what I want to do - perform the same function(s) if the user is one
of five authorized; if not, everyone gets an "Access denied" message. What
is below could be way off, but hopefully you can interpret and correct.

Dim A as variant
Dim Empl as string
Empl = CurrentUser()

A = Array("John","Jane","Mary","Bob","Bill")

IF Empl Not in A THEN
msgbox "Access Denied." etc etc
ELSE
DoCmd.OpenForm etc etc
END IF

Just thought there was something quicker/more efficient than typing out the
same function to perform for the five individuals using IF/ELSEIF statements
or a SELECT CASE statement.

The IN operator is not available in VBA code.

If you feel compelled to use an array, then the code could
be like:

For k = LBound(A) To UBound(A)
If Empl = A(k) Then
DoCmd.OpenForm etc
Exit Sub
End If
Next K
msgbox "Access Denied." etc

A more common way to do this kind of thing would be to
create a table with the user names and check if the name is
in the table:

If DCount("*", "thetable", "Empl-""" & Empl & """") > 0 Then
DoCmd.OpenForm etc
Else
msgbox "Access Denied." etc
End If

Note that it would be a simple exercise for anyone to
circumvent this kind of authorization.

 

[Pendragon]

Thanks. I used this approach setting A as String = "{list of names}" and
then an If statement to determine if Instr() >0.

Marshall's comment was notable - while these users are already in a table,
the security on this specific form (and potentially other forms for other
users without any pattern or consistency) necessitated a "quick-security"
code. Most users understand how to get to the database window and do what
they need to if a form doesn't work properly, but amazingly seem deterred
from doing such if a msgbox appears telling them they don't have access.
Apparently they seem to think if they get the message box, then they also
have been locked out of accessing the specific data by other means.