Handling Security at Field Level

I have an Access app with ULS applied. It has a main form and a subform
with a 1:M relationship. My client wants some of his users to be able to
edit 3 fields in the subform but read-only the rest. What's the "preferred"
solution to this?

I thought I could either

provide 2 subforms with their queries permissions set accordingly or

provide 1 subform and use code to loop through the objects to lock/unlock
them depending on who's logged on.

The problem with the first solution is keeping the subforms in sync as the
user navigates. Anyone have a neat solution to share?

Many thanks.

Keith.

Re: Handling Security at Field Level

I think looping through the controls when the form is opened is the way to
go. You definitely don't want to create two subforms if you can avoid it. I
would definitely go with the looping option.

In terms of how to code it, first, you can loop through all controls on the
form using something like:

Dim ctl as control

For each ctl in me.controls
'do whatever
Next

In that case, though, you'd have to check for the type of control, since not
all controls would have a Locked property. So you'd use TypeOf to determine
if it's a text box or combo box or whatever.

Another way would be to set the Tag property of the controls you want to
lock/unlock, and only work with those:

If ctl.tag="whatever" then
'do locking/unlocking on it
End If

That would be very clean; but then, if you need the Tag property for
something else, it might get a little messy.

The third way would be to store the names of the controls to lock/unlock in
an array or a table. Then, when you loop through all the controls, match the
control name against the array or table, and, if it matches, then apply
locking/unlocking. I would definitely use this third method.

HTH,

Neil


"Keith Wilby" wrote in message
news:472f1a6f$1_1@glkas0286.greenlnk.net...
>I have an Access app with ULS applied. It has a main form and a subform
>with a 1:M relationship. My client wants some of his users to be able to
>edit 3 fields in the subform but read-only the rest. What's the
>"preferred" solution to this?
>
> I thought I could either
>
> provide 2 subforms with their queries permissions set accordingly or
>
> provide 1 subform and use code to loop through the objects to lock/unlock
> them depending on who's logged on.
>
> The problem with the first solution is keeping the subforms in sync as the
> user navigates. Anyone have a neat solution to share?
>
> Many thanks.
>
> Keith.

Re: Handling Security at Field Level

"Neil" wrote in message
news:clGXi.52952$RX.35796@newssvr11.news.prodigy.net...
>I think looping through the controls when the form is opened is the way to
>go. You definitely don't want to create two subforms if you can avoid it. I
>would definitely go with the looping option.
>
> In terms of how to code it, first, you can loop through all controls on
> the form using something like:
>
> Dim ctl as control
>
> For each ctl in me.controls
> 'do whatever
> Next
>
> In that case, though, you'd have to check for the type of control, since
> not all controls would have a Locked property. So you'd use TypeOf to
> determine if it's a text box or combo box or whatever.
>
> Another way would be to set the Tag property of the controls you want to
> lock/unlock, and only work with those:
>
> If ctl.tag="whatever" then
> 'do locking/unlocking on it
> End If
>
> That would be very clean; but then, if you need the Tag property for
> something else, it might get a little messy.
>
> The third way would be to store the names of the controls to lock/unlock
> in an array or a table. Then, when you loop through all the controls,
> match the control name against the array or table, and, if it matches,
> then apply locking/unlocking. I would definitely use this third method.
>

Thanks very much Neil. I did consider the table method before I posted but
I do like the idea of the tag property method. The tag property is
something I've never used (until now) so I think I'll give it a try.

Thanks again.
Keith.

Re: Handling Security at Field Level

On Nov 5, 8:44 am, "Keith Wilby" wrote:

> The problem with the first solution is keeping the subforms in sync as the
> user navigates.

I don't understand this. I'm assuming a user will navigate via the
Main Form. Surely both sub-forms will update automatically?
If not, it's tr=E8s easy to use a full-blown form as a sub-form and to
update its recordsource and the default value of its linking field in
the OnCurrent event code of the main form. Using a form as a pseudo
sub-form and rolling your own linking gives some advantages such as
having as many as you want (or memory allows), placement where you
like (not necessarily within the boundaries of the main-form), moving
and sizing the sub-form.
Application Security, IMO, is not really security. What's to prevent a
user from opening the table or query through linking from another mdb,
adp or non-Access application? Security may be more secure when it
exists at the database engine level.
Of course there is no security that will beat the determined hacker. I
use Database Engine Security, two or three fake Security Strands in
Code, the Crypt API, and my own Reciprocal XORing procedures. Will
that beat everybody? Nope! But recently it's prevented my Aunt Alice
from hacking into my RedHeads.mdb and being horrified about my private
life. Of course, she died fifteen or so years ago; that may have
helped too.

Re: Handling Security at Field Level

"Keith Wilby" wrote in message
news:472f35d2$1_1@glkas0286.greenlnk.net...

In case anyone's interested, I assigned the usernames to the tag property

User1User2User3

and then used the left, mid and right functions to compare the tag
properties to CurrentUser

If Left(Me.ActiveControl.Tag,5) = CurrentUser Or .... etc

Keith.

Re: Handling Security at Field Level

I can see how that would work for you. But, personally, I find such
approaches messy. I think a much cleaner and sounder approach would be to
set up a simple table with four fields: CtrlName, User1, User2, User3.
CtrlName is text, and User1-3 are boolean(yes/no). You would enter the
controls you want to lock for one more users in the table, and check the box
for the users for which is should be locked. Then, when looping through the
controls: 1) if the control is not listed in the table, do nothing; 2) if
the control is listed in the table, look up the value in the field that
corresponds to the current user, and, if True, then lock the field;
otherwise, unlock the field.

This approach, obviously, has its limitations, since, if you add users,
you'd have to add fields to the table. But, from what it sounds like, that
sounds like it would be infrequent, if at all.

A better approach still would be to use an array based on a user-defined
type, using the same structure as noted above for the table. That would be
very easy to modify and wouldn't have the overhead of the table.

Anyway, that's how I would do it. But if what you've done works for you,
then great. Glad you got it working!

Neil


"Keith Wilby" wrote in message
news:4730210f$1_1@glkas0286.greenlnk.net...
> "Keith Wilby" wrote in message
> news:472f35d2$1_1@glkas0286.greenlnk.net...
>
> In case anyone's interested, I assigned the usernames to the tag property
>
> User1User2User3
>
> and then used the left, mid and right functions to compare the tag
> properties to CurrentUser
>
> If Left(Me.ActiveControl.Tag,5) = CurrentUser Or .... etc
>
> Keith.

Re: Handling Security at Field Level

Keith Wilby wrote:
> "Keith Wilby" wrote in message
> news:472f35d2$1_1@glkas0286.greenlnk.net...
>
> In case anyone's interested, I assigned the usernames to the tag property
>
> User1User2User3
>
> and then used the left, mid and right functions to compare the tag
> properties to CurrentUser
>
> If Left(Me.ActiveControl.Tag,5) = CurrentUser Or .... etc
>
> Keith.

I think I would delimit the values with a space and use Instr()
if I were to decide this was a good idea.