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

[RoadKyng]

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

 

[IanOxon via AccessMonster.com]

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]

Greetings Ian
thanks for the time you spent on a response. I am going to print it out and
study for awhile, then get back to you. One thing, my original try did not
look at the user's login id as far as the database is concerned. I assume you
mean a login when they open the database. I have not set security up yet as I
am still in the developement stage (also, I'm no pro at this so I did not
think that far ahead).

What I have is a table that holds people and a permissions subtable that has
check boxes of what they can do. They are tied together by a certificate
number. When useing the form in my original post, when at the field where an
authorization is needed to perform the function a popup asks for the
Certificate number and checks against the subtable of permissions. If = yes
then the form contues. At least that's where I am trying to go.

gavin hill
--
GmH

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

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

 

[IanOxon via AccessMonster.com]

Hi Gavin,

Yes I was talking about logging onto the database. But if your "certicate
number" is the primary key field (unique record identifier) then the table
you've constructed and adapting the example code, will help you achieve you
initial objectives.

I'd still advise you to look into Jet Security (even at this early stage) -
at the moment you're using it without realising it. The default system.mdw
file your using comes with all access installations and when you create a new
database all members of the default Users group have adminstrative
permissions for all existing database objects and ones that will be created
later. Newly created users are automatically added to the Users group (which
cannot be deleted). These two things mean that anybody with access to your
database file can edit or delete database objects, and/or records until you:

1. Create a new workgroup information file (.mdw) and alter permissions of
the Users group
2. Start creating groups and users of your own with specific permissions.

It also means a password logon - no bad thing as this helps secure any
sensitive data.

This may seem like a daunting task, but you're already halfway there because
you're thinking about what people should be allowed access to which database
objects.


All The Best


Ian

Greetings Ian
thanks for the time you spent on a response. I am going to print it out and
study for awhile, then get back to you. One thing, my original try did not
look at the user's login id as far as the database is concerned. I assume you
mean a login when they open the database. I have not set security up yet as I
am still in the developement stage (also, I'm no pro at this so I did not
think that far ahead).

What I have is a table that holds people and a permissions subtable that has
check boxes of what they can do. They are tied together by a certificate
number. When useing the form in my original post, when at the field where an
authorization is needed to perform the function a popup asks for the
Certificate number and checks against the subtable of permissions. If = yes
then the form contues. At least that's where I am trying to go.

gavin hill

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

[quoted text clipped - 133 lines]

 

[RoadKyng]

Thanks Ian,
I'll read this over this weekend. Again, thanks for your time.
--
GmH

Hi Gavin,

Yes I was talking about logging onto the database. But if your "certicate
number" is the primary key field (unique record identifier) then the table
you've constructed and adapting the example code, will help you achieve you
initial objectives.

I'd still advise you to look into Jet Security (even at this early stage) -
at the moment you're using it without realising it. The default system.mdw
file your using comes with all access installations and when you create a new
database all members of the default Users group have adminstrative
permissions for all existing database objects and ones that will be created
later. Newly created users are automatically added to the Users group (which
cannot be deleted). These two things mean that anybody with access to your
database file can edit or delete database objects, and/or records until you:

1. Create a new workgroup information file (.mdw) and alter permissions of
the Users group
2. Start creating groups and users of your own with specific permissions.

It also means a password logon - no bad thing as this helps secure any
sensitive data.

This may seem like a daunting task, but you're already halfway there because
you're thinking about what people should be allowed access to which database
objects.


All The Best


Ian

Greetings Ian
thanks for the time you spent on a response. I am going to print it out and
study for awhile, then get back to you. One thing, my original try did not
look at the user's login id as far as the database is concerned. I assume you
mean a login when they open the database. I have not set security up yet as I
am still in the developement stage (also, I'm no pro at this so I did not
think that far ahead).

What I have is a table that holds people and a permissions subtable that has
check boxes of what they can do. They are tied together by a certificate
number. When useing the form in my original post, when at the field where an
authorization is needed to perform the function a popup asks for the
Certificate number and checks against the subtable of permissions. If = yes
then the form contues. At least that's where I am trying to go.

gavin hill

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

[quoted text clipped - 133 lines]

gavin