Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I limit users to seeing only their own data on a form?

Security

How can I limit users to seeing only their own data on a form?

by  RickSpr  Posted    (Edited  )
Access user-level security takes care of a lot of our security needs, but one thing it doesn't do: It doesn't control access at the table row level. For example, if you have personnel data in a table and want employees to be able to view their own data, but nobody else's, user-level security can't get you all the way there.

The following technique will allow you to implement row-level security on a form-by-form or report-by-report basis. There are some prerequisites:
1. The database must be protected with Access user-level security. Users must have Read Data permission on each table and form or report where you want them to see some of the data.
2. The records on each form or report must have some field that you can use to decide whether the user should see that record. This might be a user ID or a department code, for example. It must be either a text field or a numeric field.

Warning: Using this method requires that users have Read Data permission on tables that contain some data they shouldn't see. VBA code in the forms and reports will block them from the forbidden rows, but if users can create their own forms and reports in the database, they could use them to see the forbidden data. You should ensure that users can not get to the database window, and do not have permission to create new forms, reports, macros, or modules.

-----------------------
How to do it
-----------------------
First, you'll need to make a new table called UserInfo. (If you have a split database, put this in the back end database.) Give it a column for UserName, and an additional column for each field from a form or report that you'll use to check that the user can see data. For instance, suppose one form has a UserID field, and another has a DeptCode field. Add UserID and DeptCode columns to the UserInfo table. Make UserName the primary key and save the table.

Next, go ahead and enter the user information into the table. Fill in each column for each user. For UserName, use the name the user enters when logging into the database (i.e., the User account name from the Workgroup Information File).

Now you can begin to work on the forms and reports. For each form or report, you first need to build an SQL statement template as follows:
1. Open the form or report in Design View and look at its Record Source property. This can be the name of a table, the name of a query, or an SQL statement.
2. If the Record Source is a table name, your template is "SELECT * FROM [red]table name[/red];".
3. If the Record Source is a query name, open the query in Design View, choose View>SQL from the menu, and copy the SQL statement as your template.
4. If the Record Source is already an SQL statement, that is your template.
5. Next, decide on your "match field", a field in the form or report you want to match with the user information. Note whether it's a text field or a numeric field.
6. Now you need to modify the template. If it doesn't already contain a WHERE clause, you'll insert one; it goes just before any ORDER BY or GROUP BY clause if there is one, otherwise it goes at the end (but before the final ";" if there is one). The WHERE clause will be:
Code: 
    WHERE
[red]match field name[/red]
Code: 
 = '+999'
Note: If your match field is numeric, omit the apostrophes (') above. Also, you can use any string or numeric value for the "+999"; all that matters is that it match the ReplString constant in the code below, and that it doesn't appear elsewhere in the WHERE clause.
7. If your template already has a WHERE clause, figure out where it ends. It ends at the start of an ORDER BY or GROUP BY clause if there is one, otherwise it ends at the end of the SQL statement (but before the final ";" if there is one). Put the whole thing, except for the word "WHERE", in parentheses, then add the following after the right parenthesis:
Code: 
    AND
[red]match field name[/red]
Code: 
 = '+999'
(Omit the apostrophes if your match field is numeric.)
8. Now copy and paste your SQL statement template into the form or report's Record Source property.

Now all you have to do is add VBA code to the form or report's Open event. Set the On Open property to "[Event Procedure]", then click the Build (...) button. A code window will open with the cursor in the Form_Open event procedure. If the form or report already had an Open event procedure, you'll see VBA code between the Sub and End Sub statements, otherwise you'll just see those two statements with nothing between. For example:
Code: 
    Sub Form_Open(Cancel As Integer)
        Const Foo = "bar"   <--- existing code may be here
        Dim Bar As Integer  <---
                            <--- insert new code here
        On Error GoTo Hades <--- more existing code here
    End Sub
If there is existing code in the event procedure, you want to insert after any Const and Dim statements at the top, and anything else which follows them. Here's the code you'll insert:
Code: 
    Const ReplString = "+999"
    Dim intInsPt As Integer, strUserMatch As Variant

    intInsPt = InStr(Me.RecordSource, ReplString)
    If intInsPt = 0 Then
        ' Oops! SQL template has no ReplString in it!
        Beep
        MsgBox "Form Record Source error. Notify programmer.", vbExclamation
        Cancel = True
        Exit Sub
    End If
    strUserMatch = DLookup("
[red]match field name[/red]
Code: 
", _
        "UserInfo", "UserName='" & CurrentUser() & "'")
    If IsNull(strUserMatch) Then
        ' Oops! No match for user in UserInfo table
        Beep
        MsgBox "No permission found for " & CurrentUser() _
            & ". Notify programmer.", vbExclamation
        Cancel = True
        Exit Sub
    End If
    ' Now modify the form record source to select
    ' records for this user only
    Me.RecordSource = Left$(Me.RecordSource, intInsPt - 1) _
        & strUserMatch _
        & Mid$(Me.RecordSource, intInsPt + Len(ReplString))
Ok, that's it! Be sure to test your form or report as thoroughly as you can.

If you add forms or reports in the future, you may need to add additional match fields to your UserInfo table. There's no problem with that--add all you need! Just be sure to add values in the new field for all the existing users, or they won't be able to open the new form or report.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top