Different Access for Different Objects

Discussion in 'Microsoft Access Forms' started by Pab, Dec 11, 2003.

  1. Pab

    Pab Guest

    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
     
    Pab, Dec 11, 2003
    #1
    1. Advertisements

  2. 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 McDaniel
    CS Computer Software
    Visual Basic - Access - Sql Server - ASP
    "Pab" <> wrote in message
    news:065f01c3bf9f$5d5d80a0$...
    > 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
     
    Scott McDaniel, Dec 11, 2003
    #2
    1. Advertisements

  3. Pab

    Pab Guest

    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.

    >-----Original Message-----
    >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 McDaniel
    >CS Computer Software
    >Visual Basic - Access - Sql Server - ASP
    >"Pab" <> wrote in

    message
    >news:065f01c3bf9f$5d5d80a0$...
    >> 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

    >
    >
    >.
    >
     
    Pab, Dec 13, 2003
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Helen

    OLE objects Access 2000

    Helen, Jun 22, 2004, in forum: Microsoft Access Forms
    Replies:
    0
    Views:
    142
    Helen
    Jun 22, 2004
  2. ChuckW

    Drill Down Objects in Access

    ChuckW, Oct 12, 2004, in forum: Microsoft Access Forms
    Replies:
    2
    Views:
    216
    Larry Linson
    Oct 13, 2004
  3. Guest

    inserting jpg ole objects in a form access 2003

    Guest, Feb 3, 2005, in forum: Microsoft Access Forms
    Replies:
    0
    Views:
    221
    Guest
    Feb 3, 2005
  4. Tobi

    max count of objects in an access form

    Tobi, Mar 15, 2005, in forum: Microsoft Access Forms
    Replies:
    1
    Views:
    571
    Guest
    Mar 15, 2005
  5. Guest

    Access 2003 objects

    Guest, May 9, 2005, in forum: Microsoft Access Forms
    Replies:
    0
    Views:
    149
    Guest
    May 9, 2005
Loading...

Share This Page