Record Level Filtering

[Jody]

Hi,

I have a form in my database that folks have been utilizing for the past
couple of years. All users see the same recordset and can insert and update
using an instance of the same form in each of their front ends. I would like
to keep the process of inserting, viewing and updating in the single form
since they are fully trained and comfortable with doing so.

The company now has a privacy concern and would like user 1 to see a
different set of records than user 2. Therefore, I am trying to uniquely
filter the records that each user can access through the form. The goal is
to allow one user to view, update, and insert into, a different query
recordset than another user. However, both users will still be indirectly
working from a common underlying table recordset. In other words via a
complex query based on the common table, the form recordset will be unique.

Using the design grid, I built a query behind the form that has a join to
another query. The second query has dlookup criteria (to the user's ID which
I store in a Front End table for that user). By joining the 2 queries, the
recordset is filtered correctly.

The problem is that the same join that filters records also prohibits
inserting and updating. I can only view the recordset. But the form has to
have the capability of insert and update to be useful.

Can anyone suggest the best approach for inserting, updating and viewing
into a filtered recordset?

Thanks,
Jody

 

[Allen Browne]

So, you need to use an updatable query. The crucial factor will be
understanding why your query is not updatable, so you can avoid that issue.
It may require some redesign.

This list may help you pin down what's wrong:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html
You can then change the design to get an updatable query. That may be as
simple as providing a unique index on the field on the one- side of the
join. Or it may mean avoiding the join, and using a subquery in the WHERE
clause instead. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Once you solve that, you then want to find a way to do this that does not
mean creating unique front ends for different users. That probably involves
some kind of sign-in when the application starts, and a way to remember who
the user is. You could then use that information in the Open event of your
form to set its RecordSource to a query statement that loads just the right
records for that user. I suspect that will be much easier to maintain than
having to create another query every time another user gets added.

 

[Jody]

Hi Allen,

First of all, thanks for the great advice and providing the valuable Web
resources.

I went through the list of join issues on your site and I have come to the
conclusion that since my subquery legitimately is read only, the stacked
query effect is making the form read-only.

I really like your idea of using a query statement that loads a specific
recordset on Open. I'm not sure how to best write this, so perhaps you could
provide input.

Each record contains a staffID field that determines if the user has privy
to that record or not. Each user is privy to a variable array of staffIDs.
If it were just one staffID, I could perhaps use a dlookup, but
unfortunately, there are a unique set of staffIDs that are different for each
user. I can't even use the In(xID,yID,zID,etc.) criteria expression since
the array will change over time and each array is different for each user.
What I need to do in effect is simulate a variable array of staffIDs when the
form runs. That is why I came to the conclusion that by joining to a query
which defined that array, I would limit the recordset accordingly. Alas,
that led to the read-only issue.

After describing the problem a bit more, can you think of a subquery that
may have the effect of a variable array criteria expression? This could have
reusability in simulating row level security.

Thanks again.
Jody

 

[John W. Vinson]

If it were just one staffID, I could perhaps use a dlookup, but
unfortunately, there are a unique set of staffIDs that are different for each
user. I can't even use the In(xID,yID,zID,etc.) criteria expression since
the array will change over time and each array is different for each user.
What I need to do in effect is simulate a variable array of staffIDs when the
form runs.

How about

IN (SELECT ID FROM PrivyInfo WHERE PrivyInfo.StaffID = yourtable.StaffID)

 

[Jody]

It worked, John! Thanks very much. You provided a key ingredient in this
model for efficient record filtering - which I will no doubt incorporate in
other projects.

Thanks as well to Allen. Your subquery web page got the wheels in motion.

Cheers,
Jody