Restricting Access to Certain Information

[Guest]

I’m creating a database for budgeting purposes and because I don’t want
budgeting managers to see the salaries for other departments, I’d like to
restrict access.

I have essentially created 3 tables:

The first is a table that has the following fields:
Table #1: Departments
DepartmentID (key)
Department Name (Text)

Table #2: Salaries
EmployeeID (key)
EmployeeName (Text)
Salary (number)
DepartmentID (lookup from Table #1)
Budgeting Manager (yes/no)

Table #3: Expenses
ExpenseID (key)
DepartmentID (lookup from Table #1)
AnnualBudget (number)

I’d like to create forms where budgeting managers can add individuals to
their department (Table #2) or expenses to their department (Table #3).
However, I’d like to restrict the information that they have to their own
department. (i.e. the budgeting manager/s for Department #2 would only be
able to view the Salaries and Expenses associated with Department #2.)

How do I do this?

 

[Allen Browne]

Presumably you have some mechanism whereby the user must sign in when they
open the database, and you can use that to determine which department they
are from.

You would then use the Open event procedure of the form to set its
RecordSource so that the form displays only the data for that department,
e.g.:
Dim strSql As String
strSql = "SELECT Salaries.* FROM Salaries WHERE DepartmentID = 99;"
Me.RecordSource = strSql

 

[Guest]

Thanks Allen. Follow-up question:

You state "Presumably you have some mechanism whereby the user must sign in
when they open the database, and you can use that to determine which
department they are from."

What is the best way to do this given the scenario I've listed above?

 

[Allen Browne]

Use a macro named AutoExec to OpenForm in dialog mode.

This is a simple little unbound form where the user enters whatever you
expect of them, e.g. username, password, department, whatever.

You then set the form's Visible property to yes (so it remains open in the
background), and you can read the department from the form when you need it.