Re: Setting up Authorizations for on-Form data entry: Advice requeste

If I read this right what you want is to allow/restrict access to bound forms
and the related datasets (Form RecordSource queries and tables) based on the
boolean values (or training profile fields) in the training profile child
dataset (sub table).

In the scenario you've outlined controlling access to those forms (based on
the user profile) would require all command bar or form buttons to be
enabled/disabled based on the User ID (as you've tried). For this to work
the application would have to be locked down tight by implementing jet
security i.e. require users to log on when they open the application. Then
you can assign read/write permissions to the database and database objects e.
g. forms and related queries/tables depending on the user and the user group
they belong to (permissions can be set at both levels and permissions are a
mix of the two) . This is part of a wider security issue and If you don't
use jet security your users will be able to circumvent your user interface,
no matter how well designed it is. Access Help will give you details of how
to set up Jet Security.

Back to the original problem, enabling/disabling form or command bar controls
based on values in the users training profile record could be done by using
dlookup (or similar code) to find user training profile record values when a
form is loaded (in the form OnOpen or OnLoad events). If you're going to use
a pop form to determine the User ID, use it at the begining of the process
and store the User ID as a global variable (or function) - the pop-up only
has to be opened once if a global variable is referenced in the OnLoad or
OnOpen events i.e.

In the declarations section of a general module declare a public variable:
....
Option Compare Database

Public varUserID As Variant
....

In your pop up form with a text box txtUserID:

Private Sub Form_Open(Cancel As Integer)
'Set txtUserID to public variable varUserID when form opens:
On Error GoTo Err_Form_Open

txtUserID = varUserID

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Name & "_Open Error: " & Err.Number & ": " & Err.Description
Resume Exit_Form_Open
End Sub

Private Sub txtUserID_AfterUpdate()
'Update public variable to txtUserID
On Error GoTo Err_txtUserID_AfterUpdate

varUserID = txtUserID

Exit_txtUserID_AfterUpdate:
Exit Sub

Err_txtUserID_AfterUpdate:
MsgBox "txtUserID_AfterUpdate Error: " & Err.Number & ": " & Err.
Description
Resume Exit_txtUserID_AfterUpdate
End Sub

In susbsequent forms use the OnOpen Event to set control properties. For
example the following form has a subForm control subCanTrain which contains
Training Records. This is disabled in design view and is linked to the value
of [tblUser_TrainingProfile].[blnCanTrain] in the users
tblUser_TrainingProfile record:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

If IsNull(varUserID) Then
Cancel = -1 'Form does not open if an invalid User ID is passed.
Else
With CurrentDb.OpenRecordset("SELECT tblUser_TrainingProfile.* FROM
tblUser_TrainingProfile WHERE (((tblUser_TrainingProfile.[UserID])=" &
varUserID & "))")
If Not EOF Then
Me![subCanTrain].Enabled=![blnCanTrain] 'Control enabled or
disabled depending on boolean value in record field.
End If
End With
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Name & "_Open Error: " & Err.Number & ": " & Err.Description
Resume Exit_Form_Open
End Sub

Of course, you could link the table User ID with the Jet Security User ID as
well so that the popup wouldn't be needed. This is more complex and if you
need a solution quickly it would be easier to hire a programmer. Feel free
to ask any further questions if you'd like to learn how to do this yourself!


Ian

RoadKyng wrote:
I need some advice and the answer may be to hire a programmer!

I have a table of personnel with a sub-table containing their training
profiles. In the training profiles are several check boxes that hold yes/know
responses on what the personnel are authorized to do based on their training
level.

In the database are several forms/tables for different job functions the
personnel perform. On many of these forms are fields which hold access ID's
and the holders name. I need this data is supplied by the personnel/training
profile tables.

That is where I am, where I am trying to go is: after a form is completed
detailing the job function I want the user to enter his ID code (based on a
Popup form). Behind the scene code checks his ID and makes sure he/she has
the authority to perform that function based on the personnel/training
profiles. Their may be multiple levels of authorization on a single job
record based on how detailed the job is and therefore multiple times the
authorization ID popup is called and the verification performed. I made a
rudementary version of this using a combo box and query that finds the match
of the ID number in the personnel/training profile tables and returns the
name if the authorization matches. however I feel there must be a better and
cleaner way. I made a single popup form for authorization ID entry and have
figured out how to get the entered value into the ID field of the calling
form. What I have not figured out is how to then perform the authorization
verification and name entry via code.

We are a classic situation of small company jack of all trades people that
must design their own tools but I believe I am getting over my head. If there
is a fairly easy explanation I would welcome it or if someone knows a
consultant that is reasonable we are open to that too.

thanks

gavin

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200603/1
.