Restricting access to records

[Beverly Darvill]

Hi

We have a database that for a user to access it they must have a username,
password and assigned to a specific group.

The groups that we have are
Users (default group)
Admins
Program Managment
Senior Management
Actionees
Administrators (default group)

The database holds data of projects for people both in the UK and in States
(in two locations) and we need to restrict people from accessing all
information for all records in a specific reporting period in all areas.

We want them just to access the projects in their own location. They can
only update their own project as the project is password protected from
within the database.

Is this possible without creating duplicate databases as the main admins are
in the UK and need to access all data at anytime.

Thanks

Beverly

 

[Keith Wilby]

Hi

We have a database that for a user to access it they must have a username,
password and assigned to a specific group.

The groups that we have are
Users (default group)
Admins
Program Managment
Senior Management
Actionees
Administrators (default group)

The database holds data of projects for people both in the UK and in
States
(in two locations) and we need to restrict people from accessing all
information for all records in a specific reporting period in all areas.

We want them just to access the projects in their own location. They can
only update their own project as the project is password protected from
within the database.

Is this possible without creating duplicate databases as the main admins
are
in the UK and need to access all data at anytime.

Thanks

Beverly

I'm assuming that you have a "location" field, you could set up a
user-dependent filter on that field when the form is opened. Something like
this in the form's Open event:

Dim strSQL As String
strSQL = "Select * From qryMyQuery Where "

If CurrentUser = "UK" Then
strSQL = strSQL & "[Location] = 'UK'"
Else
strSQL = strSQL & "[Location] <> 'UK'"
End If

Me.RecordSource = strSQL

Keith.
www.keithwilby.co.uk

 

[Beverly Darvill]

Yes we do have a location field of which there are 4 possible locations,
Titchfield, South Molton, Irvine and Euclid - how can we restrict someone
from say Eculid looking a project in Titchfield and vice versa.

Hi

We have a database that for a user to access it they must have a username,
password and assigned to a specific group.

The groups that we have are
Users (default group)
Admins
Program Managment
Senior Management
Actionees
Administrators (default group)

The database holds data of projects for people both in the UK and in
States
(in two locations) and we need to restrict people from accessing all
information for all records in a specific reporting period in all areas.

We want them just to access the projects in their own location. They can
only update their own project as the project is password protected from
within the database.

Is this possible without creating duplicate databases as the main admins
are
in the UK and need to access all data at anytime.

Thanks

Beverly

I'm assuming that you have a "location" field, you could set up a
user-dependent filter on that field when the form is opened. Something like
this in the form's Open event:

Dim strSQL As String
strSQL = "Select * From qryMyQuery Where "

If CurrentUser = "UK" Then
strSQL = strSQL & "[Location] = 'UK'"
Else
strSQL = strSQL & "[Location] <> 'UK'"
End If

Me.RecordSource = strSQL

Keith.
www.keithwilby.co.uk

 

[Keith Wilby]

Yes we do have a location field of which there are 4 possible locations,
Titchfield, South Molton, Irvine and Euclid - how can we restrict someone
from say Eculid looking a project in Titchfield and vice versa.

There are many ways you could do this, one would be to set up a user account
for each location and name that account the same as each location. So your
code would look something like:

Dim strSQL As String
strSQL = "Select * From qryMyQuery Where [Location] = '" & CurrentUser & "'"
Me.RecordSource = strSQL

This would make it easier to manage new locations if/when they emerge.

It's worth repeating that middle line of code with extra spaces in just for
clarity:

strSQL = "Select * From qryMyQuery Where [Location] = ' " & CurrentUser & "
' "

HTH - Keith.
www.keithwilby.co.uk