Form Filter Using Login Name

[AccessHawaii]

I hope someone can help me with this. I currently have a function that applies a filter to a form based on a user's login. I'm using a DLookup function in VBA, which looks at a table that contains the user's login and department, which is used as the filter. It looks similiar to below.

Dim strUser as string
dim strFilter as string

strUser=CurrentUser

StrFilter=("[Department]","tblLogin","[Login]= strUser")

It works fine like this but now the users want to be able to be assigned to multiple departments so the DLookup function would need to pull multiple instances and concatenate the department(s) to be used in a filter.

The DLookup function only pulls a single record. Does anyone know a procedure in VBA where I can use the DLookup function and have it look for multiple instances of the records? Like in a For Next type of statement or something?

If this isn't possible is there another way I can accomplish this? Thanks.

 

[MakeItSo]

I have two suggestions:
a) How about setting up a "Groups" table assigning different acces-levels to certain groups and have a group-ID for each user.

b)a binary assignment:
If there aren't too many departments, you could add Yes/No fields to your user table for each department, so your entries would look like this:
user_id user dep1 dep2 dep3 dep4 dep5 dep6 dep7
1 joe TRUE FALSE TRUE TRUE FALSE FALSE FALSE
2 jack FALSE TRUE FALSE TRUE FALSE FALSE FALSE
3 andy FALSE TRUE TRUE FALSE TRUE FALSE FALSE

assigning the values 2^(dep.no.-1), (dep1=1, dep1=2, dep3=4...)
you would receive a value of 13 for joe, 10 for jack and 22 for andy.
Each combination is unique and from the crosssum you can always deduct the combination of departments.
If the department-specific where-clauses are interchangeable, you could think of a function, that will "paste together" the respective Filter-Statement based on the departments.

Hope that helps,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[AccessHawaii]

Thanks Andy. I'm not sure how I'd be able to throw this into a filter though. In the main table, the department is in a single field with a drop down. What basically happens is the record is sent to one of the 10 or so departments for resolution and people may be assigned to different departments for resolution purposes than their actual department.

I think in a nutshell what I need is a DLookUp function that would be able to concatenate like [Dept]='AR' or [Dept]='HR' etc. The problem is, which I'm sure you know is that DLookup only does this for a single record and I need it to to do it for multiple records. If there isn't a way to do this through DLookUp I'm guessing that there must be some type of array that can be performed but I just don't know how to do it with my limited knowledge. If this clarifies more of what I'm trying to do and you know how it can be performed, I'd appreciate any feedback you could offer. Thanks for taking the time to reply.

 

[MakeItSo]

Have a try with a recordset:

Dim rs as DAO.recordset, deps as Variant, i as integer
deps=array(1)
i=0
strSQL="SELECT * from tblLogin where [Login]='" & currentuser & "'"
Set rs=currentdb.openrecordset(strSQL,dbOpenSnapshot)
rs.MoveFirst
Do while not rs.EOF
 redim preserve deps(i)
 deps(i)=rs!Department
 i=i+1
 rs.MoveNext
Loop
rs.Close

Now the array deps() contains all departments of that one user.

Would that help?
Andy

 

[AccessHawaii]

Thanks Andy. This sounds like what I need. I'm gonna try it out and I'll let you know how it goes.

 

[AccessHawaii]

Andy, Thanks. Worked like a charm. It does exactly what I was looking for and I'm even able to get rid of some of the other routines I have and replace it with this. Thanks again.