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?

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

    OLE objects Access 2000

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

    Drill Down Objects in Access

    ChuckW, Oct 12, 2004, in forum: Microsoft Access Forms
    Replies:
    2
    Views:
    385
    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:
    360
    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:
    715
    Guest
    Mar 15, 2005
  5. Guest

    Access 2003 objects

    Guest, May 9, 2005, in forum: Microsoft Access Forms
    Replies:
    0
    Views:
    234
    Guest
    May 9, 2005
  6. Guest
    Replies:
    0
    Views:
    225
    Guest
    Jun 7, 2005
  7. Guest

    How to Convert Access Form objects to .NET

    Guest, Jun 16, 2005, in forum: Microsoft Access Forms
    Replies:
    0
    Views:
    249
    Guest
    Jun 16, 2005
  8. Guest

    copying access objects from file to file.

    Guest, Aug 24, 2005, in forum: Microsoft Access Forms
    Replies:
    0
    Views:
    239
    Guest
    Aug 24, 2005
Loading...