Restricted Employee Access to Reports (1 Viewer)

SetanPutih

Registered User.
Local time
Today, 02:27
Joined
Jun 27, 2019
Messages
27
Hi,

Thanks for taking the time to read this. I'm creating an employee database. I have given each employee a unique username and password. What I want to do is for clients to be able to log in and only see specific reports which relates to them.. e.g. employee profile, annual leave taken etc.. It is vital that employees don't have access to each others information.

I also don't want employees to have access to the other database functions, only the reports. How would I go about doing that?

Thanks for any help. Definetly one of the most supportive online communities i've experienced.

James
 

JHB

Have been here a while
Local time
Today, 10:27
Joined
Jun 17, 2012
Messages
7,732
How "strong" should it be?
Does it allow people with some knowledges of MS-Access to "break" it?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,169
if the navigation pane is not hidden, then the purpose of your goal will be defeated.
next create a Module and on that module add a Global variable that will hold the Employees' ID.

on your log-in form after successfully validating the user, Lookup (using Dlookup) his/here Employee ID using the login name and password as criteria.
Note that you need to add the Employee ID to your valid user table also.
save this ID to the global variable.

for each object that you will open (forms/reports), you need to add a 'Where Clause' specifying only the Employee ID (using the Global variable).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:27
Joined
Oct 29, 2018
Messages
21,357
Hi James. Here's a little article on login security. Hopefully, you could learn something from it.
 

SetanPutih

Registered User.
Local time
Today, 02:27
Joined
Jun 27, 2019
Messages
27
Thanks for your replies,

@JHB - Not that strong at all.

@arnelgp - thanks again for your replies. It's much appreciated. I will have the navigation pane hidden. Unfortunately, I don't think I'm quite at the level of understanding of Access to implement this...

"for each object that you will open (forms/reports), you need to add a 'Where Clause' specifying only the Employee ID (using the Global variable)"

I will try and learn though.

@TheDBGuy - Thanks. I will have a look.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,169
yes, I know im not good in explaining.
to open a form:

docmd.openform FormName:="nameOfForm", WhereCondition:="[Employee ID]='" & theGlobalVariable & "'"

that way only records of this employee is showing.
 

SetanPutih

Registered User.
Local time
Today, 02:27
Joined
Jun 27, 2019
Messages
27
@arnelgp Thanks again. OK, please be patient with me. When and where do I have to enter that code?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,169
here is a crude example with Login screen.
 

Attachments

  • sampleLogin.zip
    50 KB · Views: 120

Micron

AWF VIP
Local time
Today, 05:27
Joined
Oct 20, 2018
Messages
3,476
I thought the goal was to prevent certain things from opening or even hide them, not just restrict them to info pertaining to the user? You can generate error messages by opening (e.g.) a report that has no records for a user who shouldn't be able to open it anyway - etc.
If you are using a switchboard kind of form (I wouldn't use the built in one) then the controls that provide access to specific forms/reports can be hidden from those who shouldn't know of them, or if that was a combo of reports, the list modified to hide some of them from certain users. Many things you can do in this regard.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,169
post #1 states this:
...clients to be able to log in and only see specific reports which relates to them.. e.g. employee profile, annual leave taken etc.. It is vital that employees don't have access to each others information.
 

Mark_

Longboard on the internet
Local time
Today, 02:27
Joined
Sep 12, 2017
Messages
2,111
Two questions;
1) Can you have a separate front end for "Clients" instead of employees? If yes, this generally means you can use network security to help keep people out of areas they should not be in simply by not having those pieces in the front end they use.

2) Are you using a separate back end such as SQL?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,169
that's what I did, the main menu is restricted only to report.

I suggest the op to make to db. one master, non-restricted, and the one with report-only menu. this is always what I have. the report-only db is then distributed to each user (fe-be).
 

SetanPutih

Registered User.
Local time
Today, 02:27
Joined
Jun 27, 2019
Messages
27
Hi Arnelgp, your sample login databse has been extremely helpful. Thank you very much. I have a question which I hope you can help me with..

In Form1 there are two reports available to see "rptEmpActivity" and "rptSalaryManager".. The VBA code can be seen below.

Private Sub List2_DblClick(Cancel As Integer)

Select Case Me.List2.Value
Case "Employee Activity Report"
DoCmd.OpenReport reportname:="rptEmpActivity", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case Else
DoCmd.OpenReport reportname:="rptSalaryHistory", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
End Select
End Sub

My First question is how do I add another report to that list. I tried to add another report using VBA code below and it didn't work.

Private Sub List2_DblClick(Cancel As Integer)

Select Case Me.List2.Value
Case "Employee Activity Report"
DoCmd.OpenReport reportname:="rptEmpActivity", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case Else
DoCmd.OpenReport reportname:="rptSalaryHistory", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case Else
DoCmd.OpenReport reportname:="rptEmployeeDetails", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
End Select
End Sub

My second question is.. I changed your VBA coding so that my coding shows the reports that I want to show.. Please see below..

Private Sub List2_DblClick(Cancel As Integer)

Select Case Me.List2.Value
Case "Training"
DoCmd.OpenReport reportname:="Training", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case Else
DoCmd.OpenReport reportname:="rptShowEmployeeAnnualLeave", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
End Select
End Sub


Although both functions work.. every time I click on Employee Annual Leave option in Form 1 it asks me to "Enter Parameter Value - Employee ID" once I type in the employee ID it does take me the right report but how do I stop it from asking me to enter the Parameter Value everytime?

Thank you for your time Arnelgp. I really do appreciate it.

James
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,169
your first question, you add another Case:
Code:
Select Case Me.List2.Value
Case "Employee Activity Report"
DoCmd.OpenReport reportname:="rptEmpActivity", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case [COLOR="Navy"]"the salary history report here"[/COLOR]
DoCmd.OpenReport reportname:="rptSalaryHistory", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case Else
DoCmd.OpenReport reportname:="rptEmployeeDetails", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
End Select
End Sub
2. check your report "rptShowEmployeeAnnualLeave" in design view specially the the record source. does your recordsource has Employee ID or is it misspelled?
 

SetanPutih

Registered User.
Local time
Today, 02:27
Joined
Jun 27, 2019
Messages
27
Hi Arnel,

I took your advice but unfortunately it didn't work.

In the Main Menu (Form 1) I can access the training report with no problems.
If I try to access the Annual Leave report to says "Enter Parameter Value - Employee ID". This also happens when I try to click on the Employee Details (rptEmployeeAccess) I have checked the source in properties and EmployeeID is spelt the same.

Also, when I click on Employee Details (rptEmployeeAccess) it takes me to the Annual Leave Report (rptShowEmployeeAnnualLeave|). I changed the coding but it still doesn't work. Please see coding below..

Private Sub List2_DblClick(Cancel As Integer)

Select Case Me.List2.Value

Case "Training"
DoCmd.OpenReport reportname:="Training", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case "rptEmployeeAccess"
DoCmd.OpenReport reportname:="rptEmployeeAccess", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case Else
DoCmd.OpenReport reportname:="rptShowEmployeeAnnualLeave", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
End Select
End Sub

I have tried to upload a copy of the database I made but I can't get it below 2MB. Is there any other way I can send it to you so that you can have a look?

As always, thank you for your help. I really value your contributions.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:27
Joined
Sep 21, 2011
Messages
14,037
Employee ID and EmployeeID are not the same?
Also if it is numeric do not use single quotes.

Hi Arnel,

I took your advice but unfortunately it didn't work.

In the Main Menu (Form 1) I can access the training report with no problems.
If I try to access the Annual Leave report to says "Enter Parameter Value - Employee ID". This also happens when I try to click on the Employee Details (rptEmployeeAccess) I have checked the source in properties and EmployeeID is spelt the same.

Also, when I click on Employee Details (rptEmployeeAccess) it takes me to the Annual Leave Report (rptShowEmployeeAnnualLeave|). I changed the coding but it still doesn't work. Please see coding below..

Private Sub List2_DblClick(Cancel As Integer)

Select Case Me.List2.Value

Case "Training"
DoCmd.OpenReport reportname:="Training", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case "rptEmployeeAccess"
DoCmd.OpenReport reportname:="rptEmployeeAccess", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
Case Else
DoCmd.OpenReport reportname:="rptShowEmployeeAnnualLeave", WhereCondition:="[Employee ID]='" & gEmployeeID & "'", view:=acViewReport
End Select
End Sub

I have tried to upload a copy of the database I made but I can't get it below 2MB. Is there any other way I can send it to you so that you can have a look?

As always, thank you for your help. I really value your contributions.
 

SetanPutih

Registered User.
Local time
Today, 02:27
Joined
Jun 27, 2019
Messages
27
Hi there,

I have tweeked the coding and the fields to ensure that all EmployeeID is the same. Now, if I click to access any of the reports it says "Run-time error '3664': Data type mismatch in criteria expression :( I have no idea where to go from here.

Private Sub List2_DblClick(Cancel As Integer)

Select Case Me.List2.Value
Case "Employee Activity Report"
DoCmd.OpenReport reportname:="rptEmpActivity", WhereCondition:="[EmployeeID]='" & gEmployeeID & "'", view:=acViewReport
Case "Training"
DoCmd.OpenReport reportname:="rptTraining", WhereCondition:="[EmployeeID]='" & gEmployeeID & "'", view:=acViewReport
Case "Annual Leave"
DoCmd.OpenReport reportname:="rptShowEmployeeAnnualLeave", WhereCondition:="[EmployeeID]='" & gEmployeeID & "'", view:=acViewReport
Case "Employee Details"
DoCmd.OpenReport reportname:="rptEmployeeAccess", WhereCondition:="[EmployeeID]='" & gEmployeeID & "'", view:=acViewReport
Case Else
DoCmd.OpenReport reportname:="rptSalaryHistory", WhereCondition:="[EmployeeID]='" & gEmployeeID & "'", view:=acViewReport
End Select
End Sub

Thanks for taking the time to read this.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:27
Joined
Sep 21, 2011
Messages
14,037
Normally to me, anything with ID in it would be numeric as I would take that to be an autonumber.?

Is that the case with you.?

If so, it should not be surrounded by single quotes, that is for text.
 

Users who are viewing this thread

Top Bottom