Dynamic "IN" criteria in query

[ITistic]

I am in the midst of modifying an existing Access database used by a
single office into one which is used by and stores data for multiple
offices. Data for all offices is stored in the same database. When the
database is initially opened a global variable is set to the value of
the office_id(s) that user is associated with. This can be one number
("1") or multiple numbers ("1", "2", "3"). I need to update all of the
existing queries in the database so that they only show data for
customers who belong to the office_id(s) the current logged in user is
associated with.

What I have tried initially is creating a function in a module called
"UserOfficeIDs" which returns a string of the comma-delimited
office_id(s) the current user has access to. I then modified one of
the queries so the criteria for the "office_id" field was set to "In
(UserOfficeIDs())". This did not work. When I output UserOfficeIDs()
as a field in the query I do see the correct data, so I know the
function is working properly. Does anybody else have any idea how I
can accomplish this?

 

[mscertified]

Make your function return True or False depending on whether there is a match
or notthan insert a clause in the query "AND UserOfficeIDs()=TRUE"

-Dorian

 

[John Spencer]

A method that might work is to create a table that contains the userID and
one record for each office the user is associated with. Then you could add
that table to all your queries where officeid is a factor and apply criteria
using the User id in the new table.

SELECT *
FROM SomeOriginalTable Inner JOIN UserOffices
ON SomeOriginalTable.OfficeID = UserOffices.OfficeID
WHERE UserOffices.UserID = "UserIDYouHave"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..