Different Access for Different Objects

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
 
S

Scott McDaniel

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

Pab

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top