IF statement with array   

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!

date: Tue, 22 Jul 2008 12:51:07 -0700   author:   Pendragon

Re: IF statement with array   

THere is an SQL IN function but I don't think there is one for an array.  I
would think you would keep your names in a table for flexibility and then you
could use a DLookup() instead of hard coding the names in your program.

Pendragon wrote:
>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!

-- 
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200807/1

date: Tue, 22 Jul 2008 20:16:16 GMT   author:   ruralguy via AccessMonster.com u12102@uwe

Re: IF statement with array   

Pendragon wrote:

>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.

-- 
Marsh
MVP [MS Access]

date: Tue, 22 Jul 2008 15:26:29 -0500   author:   Marshall Barton

Re: IF statement with array   

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.

"perry" wrote:

> Other approach straight from the immediate window: (same amount of 
> development typing)
> 
> sUsers = "User1;User2;User3;User4;User5"
> Print InStr(sUsers, "User2")
>  7
> Print InStr(sUsers, "User5")
>  25
> Print InStr(sUsers, "User0")
>  0
> if instr(sUsers, "User2") then print "ok": else print "access denied"
> ok
> if instr(sUsers, "User20") then print "ok": else print "access denied"
> access denied
> 
> Krgrds,
> Perry
> 
> "Pendragon"  schreef in bericht 
> news:27C5E5A7-5B85-4002-9F73-F32C275FF839@microsoft.com...
> > 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! 
>

date: Wed, 23 Jul 2008 09:24:00 -0700   author:   Pendragon