Textbox access by security level

[iamrdbrown]

I have a database that I have set up (with lots of help from this site). I
am almost completed the database with the exception of setting data entry
control to some of the form textboxes based on security clearance. Here is a
basic description:

I have a table that contains UserID, FirstName, LastName and SecurityLevel.
I have a form that needs to have 3 sections filled in to be complete:
Operator, Supervisor and qaAuditor.

I would like to have it set up so that:
only operators can enter data for Operator Complete
only supervisors can enter data for Supervisor Review
only qaAuditors can enter data for QA Approval

At this point, anyone can approve any section if they are in the database
security table - I don't know how to limit the text input based on 2 fields
in the security table (UserID and SecurityLevel).

Any help would be greatly appreciated.

R Brown

 

[Tom van Stiphout]

On Mon, 17 Nov 2008 11:32:33 -0800, iamrdbrown

I would have a global variable (in a standard module) to represent the
security level. Say it's called g_SecLevel. Set that value as soon as
you've determined that the login is valid.
Then in the Form_Open write:
dim varOperatorControls as variant
dim varSuperControls as variant
varOperatorControls = Array("control1", "control2", "control3")
varSuperControls = Array("control4", "control5", "control6")
dim varCtl as variant
dim ctl as control
for each ctl in me.controls
ctl.Locked = True
select case g_SecLevel
case "Operator"
for each varControl in varOperatorControls
ctl.Locked = False
next varControl
case "Super"
for each varControl in varSuperControls
ctl.Locked = False
next varControl
end select
next ctl

The strategy here is to lock all controls, and unlock the ones given
by their respective arrays.
Now you may find that not all controls have a Locked property (e.g.
buttons), so your code may need to be a bit smarter. For example you
could use:
If TypeOf ctl Is TextBox Then
etc.

-Tom.
Microsoft Access MVP

 

[iamrdbrown]

On Mon, 17 Nov 2008 11:32:33 -0800, iamrdbrown

I would have a global variable (in a standard module) to represent the
security level. Say it's called g_SecLevel. Set that value as soon as
you've determined that the login is valid.
Then in the Form_Open write:
dim varOperatorControls as variant
dim varSuperControls as variant
varOperatorControls = Array("control1", "control2", "control3")
varSuperControls = Array("control4", "control5", "control6")
dim varCtl as variant
dim ctl as control
for each ctl in me.controls
ctl.Locked = True
select case g_SecLevel
case "Operator"
for each varControl in varOperatorControls
ctl.Locked = False
next varControl
case "Super"
for each varControl in varSuperControls
ctl.Locked = False
next varControl
end select
next ctl

The strategy here is to lock all controls, and unlock the ones given
by their respective arrays.
Now you may find that not all controls have a Locked property (e.g.
buttons), so your code may need to be a bit smarter. For example you
could use:
If TypeOf ctl Is TextBox Then
etc.

-Tom.
Microsoft Access MVP

Tom,
I apologize for being a little dense, but I am really new to this coding
thing. This example totally confused me. I went back in and looked at my
UserID table & was able to modify it to have 4 yes/no fields for the 4
security clearances. I set these up as a option group on the UserID data
entry form.

The database now has only 2 tables - tblUnitData and tblUserIDs

The actual UserID is a unique (not primary key) field for each table. I
need to be able to confirm that:
1. The person doing the repair is in the UserID table
2. They have the correct security clearance for the level (operator,
supervisor, QA)
they are trying to sign off on
3. If someone tries to approve something they don't have clearance for, I
want a
message to come up that prompts them for the correct level access.

And do it all before the data is saved, so I'm thinking the place for this
is in the before_update event.

R Brown