Dlookup range

[Badvoc]

How can I rewrite this code:

If DLookup("[logon]", "Employee", "[id] = 1") = Environ("username") _
        Or DLookup("[logon]", "Employee", "[id] = 2") = Environ("username") _
        Or DLookup("[logon]", "Employee", "[id] = 3") = Environ("username") _
        Or DLookup("[logon]", "Employee", "[id] = 4") = Environ("username") _
        Or DLookup("[logon]", "Employee", "[id] = 5") = Environ("username") _
        Or DLookup("[logon]", "Employee", "[id] = 6") = Environ("username")  then

So the “[id] =any id number” if you see what I mean, I don’t want to copy the code for each id, just any id will do. Example code would be very helpful

Thanks

 

[MarkK]

I don't see what you mean. Any ID? Like a random number? Or this ...

If DLookup("logon", "Employee", "id > 1 and id <= 6") = Environ("username") then

... but that seems pointless. Or do you want to specify the ID using a parameter so the specific ID is not 'hard coded?'
Cheers,

 

[Badvoc]

Hi
Basically Im using a table with users login name for permissions on a form, if the user name is anywhere in the table they have permission.
the id number is not really all that important, just so long as the user name is in the logon field
The Dlookup is in the forms load procedure.
I have upto 30 users and at present I have to wright the code 30 times.
Just thought there might be a better way to code it.

 

[MarkK]

Yeah, I'd use a DCount() where I want to see if something exists in a table. Count the records it appears in...

If DCount("*", "Table", "logon = '" & environ("UserName") & "'") > 0 then 
[COLOR="Green"]  'this username exists in the table[/COLOR]
End If

 

[boblarson]

Just a suggestion but I would use

VBA.Environ("username")

because that may help avoid an error sometime down the line where using Environ by itself can be a problem. I can't remember if it is a later version or on Windows Vista/Windows 7 or something. But I've started using that to keep from having any errors around that.

 

[Badvoc]

Bob sorry but Im not understanding how youd use this VBA.Environ("username")
Im using Environ("username") in the code now.

 

[boblarson]

Bob sorry but Im not understanding how youd use this VBA.Environ("username")
Im using Environ("username") in the code now.

Ummm, just add the letters V B A and a period before what you have now.

 

[Badvoc]

lagbolt
I tried your Dcount code, works just how I want many thanks.

But my company is looking to move from XP to windows 7 soon so I would be interested in what Boblarson mentioned

 

[Badvoc]

oh, "walks away and bangs head on wall"

 

[boblarson]

As long as you are using VBA you can do that with any function (including DATE so that you are less likely to have a reference issue of which Date is common).

 

[smig]

Ummm, just add the letters V B A and a period before what you have now.

you made me lough


I guess he wanted to know the difference