Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Allowing limited access to records via a form (Access 97)

    I have created a file that has database-level security. I have everything setup via forms. No user has access to the tables. I want to take the security one step further and don't know if I can. I want to, upon launching a form this lets users updated their hours, limit the records that the user can view to their own. Can this be done using the existing security system?
    thanks
    christine

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing limited access to records via a form (Access 97)

    It's a lot easier if you have set up workgroup security (since you can just use currentuser() to get the current user). If by database-level security you mean that all you have done is set a database password, then it's a little harder (currentuser() will always be "Admin"). If you are using Windows NT or 2000 (might work in XP - I've never tried), see http://www.mvps.org/access/api/api0008.htm to get the id you logged into NT or 2000 with.

    Now, when user creates a record, you'll need to store the id as part of the record. You can then base the form on a query that only shows records with that id.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Allowing limited access to records via a form (Access 97)

    I have workgroup security setup with approximately 30 users. How would I go about setting up currentuser()?
    thanks
    christine

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Allowing limited access to records via a form (Access 97)

    If the users are logging in with uniquer usernames, it's easiest to set their permissions up by group and then check to see whether the user is in a particular group allowed to do a particular thing. You have to do the checking from the Admins level. Here's some code from the NeatCd97.mdb, downloadable from Microsoft:

    <pre>Function CurrentUserInGroup(GName As String) As Integer
    '
    ' Checks if the current user is in the specified group
    ' Uses a secure workgroup, so it will run for non-privileged users
    '
    Dim w As Workspace, U As user, i As Integer
    CurrentUserInGroup = False
    ' 2nd argument is Admins level user name, 3rd is password
    Set w = DBEngine.CreateWorkspace("", "Admin", "")
    Set U = w.Users(CurrentUser())

    For i = 0 To U.Groups.Count - 1
    If U.Groups(i).Name = GName Then
    CurrentUserInGroup = True
    Exit Function
    End If
    Next i
    End Function</pre>

    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Allowing limited access to records via a form (Access 97)

    As an alternative to the function Charlotte provided, you could also simply create a form that has a query as it's data source, and specify the CurrentUser() function as the criteria for the field containing the UserID. Note that you will either need to set that field default to CurrentUser(), or you will need to default it on the form that you do data entry of new records.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Allowing limited access to records via a form (Access 97)

    I am banging my head on this one. Can't I just use the "On Apply Filter" function built into the form? I have been trying it unsuccessfully, of course. I am trying what seems logical to me: =[Form]![Name]=CurrentUser(), where [Name] is the main field on the form. The user selects their name from a pull down list to update their information for the day. I am just saying that I want the form to not show any records where the name on the record is not the name of the current user. Why can't I get this?????
    thanks
    christine

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing limited access to records via a form (Access 97)

    I'm puzzled why you would be using the On Apply Filter event. If you really want to do it with filters, why not just set the Filter directly? However, from the point of view of maintaining the code, I'd agree with WendellB that simply doing your check in the query behind the form would be simplest. No need for the user to select their name; all they would see would be their own records.

    If you really want to have your security tight, I'd look into Run With Owner Permission queries as well.

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Allowing limited access to records via a form (Access 97)

    I know it all me. I can't get the query process to work. I am missing something out of it and I spent hours today trying to figure it out. During my review is when I came across the filters situation and thought I would give that a try instead. I am really stuck on this one.
    thanks
    christine

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing limited access to records via a form (Access 97)

    Have a query as the recordsource of your form (i.e. the first property when you show all properties of the form). It can either be a distinct query that you've created separately (so the the name of the query shows up as the recordsource of the form) or it can just be a query "behind" the form (so that when you look at the form's properties the recordsource starts with SELECT ...).

    Make sure your query contains all the fields you want your form to be able to display or change, including the field that contains the userid (you must have a field that stores the userid since that is the only way you can "remember" the userid).

    As the criterion for the userid field, type currentuser() . That will ensure that the query can only find records where the stored userid matches the current user's id. You can check your work so far by running the query (click the exclamation mark icon) to see that only the current user's records are being displayed.

    Now, you also need to store the current user's id when a new record is created. If you only care about who created the record, by far the simplest solution is to put currentuser() as the Default Value property of the field in the table definition (note that that will affect new records only; if you created your Userid field in a non-empty table, you'll need to manually fix the existing records).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •