Handling Security at Field Level

[Keith Wilby]

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.

[Neil]

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" <he**@there.comwrote in message
news:47**********@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.

[Keith Wilby]

"Neil" <no****@nospam.netwrote in message
news:cl******************@newssvr11.news.prodigy.n et...

>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.

[lyle]

On Nov 5, 8:44 am, "Keith Wilby" <[e-mail]:

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ès 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.

[Keith Wilby]

"Keith Wilby" <he**@there.comwrote in message
news:47**********@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.

[Neil]

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" <he**@there.comwrote in message
news:47**********@glkas0286.greenlnk.net...

"Keith Wilby" <he**@there.comwrote in message
news:47**********@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.

[rkc]

Keith Wilby wrote:

"Keith Wilby" <he**@there.comwrote in message
news:47**********@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.