Different Access for Different Objects

  • Thread starter Thread starter Pab
  • Start date Start date
P

Pab

Hi,

Here is the situation: I am trying to create a Vacation
Request Form in our company. I want to create a form that
has two parts:

1. The first part will give access to the employee to
fill info such as Name, vacation start date, vacation end
date, etc.. that will all be stored in a table that's
already created. Easy so far.

2. The second part will have a check box or a radio
button (In the same form) for the supervisor to just
approve or deny the vacation. The hard part is, how do I
give access to the supervisor ONLY with out giving access
to the employee for this "Approval" field?

3. I just thought of a third part :-) How do I give
access only to that person who has filled out this
specific form, so other employees will not be able to
change this employee's record?

Note: I am using the "User Level Security", but that only
gives me different rights on the Form itself, not parts
of the form.

Thanks all
 
Hopefully you have setup a Managers group in your security setup. If so,
then simply check for membership in that group in the BeforeUpdate event of
your checkbox:

Public Function IsCurrentUserInGroup(ByVal GroupName As String) As Boolean
'note: credit for this code goes to Brendan Reynolds
Dim usr As DAO.User
Dim grp As DAO.Group

Set usr = DBEngine.Workspaces(0).Users(CurrentUser())
For Each grp In usr.Groups
If grp.Name = GroupName Then
IsCurrentUserInGroup = True
Exit For
End If
Next grp
Set grp = Nothing
Set usr = Nothing

End Function


Sub MyCheckBox_BeforeUpdate(Cancel As Integer)
If IsCurrentUserInGroup("Managers") = False Then
Msgbox "Only members of the Managers group can approve this
schedule", vbOKOnly
Cancel = True
End If
End Sub

As far as allowing users to view their records only, base your form on a
query and filter the form based on the CurrentUser. To do this, you'll need
to add a field to the table storing the scheduling data, and store the value
of CurrentUser when a new record is added. Then on logon, you filter your
form based on the value stored in that field:

SELECT * FROM tblSchedule WHERE strUser = ' " & CurrentUser & " ' "

In this manner users see only those records they have added.
 
Scott,

Although most of your reply is over my head (For now),
you sure have gotten me excited about the capablities of
Access. I will be doing more research on this. Does this
code have to do anything with VBA? Is there a nice
website that will get me started with this functionality
step by step? Much appreciated.
 
Back
Top