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

Topic

New Reply

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?

Reply | Quote

Replies

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%5B/url%5D 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.

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

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.

Reply | Quote

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?????

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote