Filter the report based on the current user.

[aditijangir]

Dear Friends,

I am using A2010 Version.I have a report which is displaying the data of all the users, which I want to rectify and display only the data of the current user.

Now to give you basic info:
I have 2 tables:
1)AddUser(Emplo yeeId{Numeric}, Job Title,SecurityI d....)
2)Tasks(ID,Empl oyeeId,Title,De scription,Start Date, Due Date,..)

Now when the current user click on the command button, it redirect to the report. Report contains Fields like
EmployeeId,Empl oyeeName,Title, Description,Sta rt Date,Due Date.

Now EmployeeId and EmployeeName is displayed through the combobox query in Row Source.
Please note: EmployeeId and all the details are fetched from "Tasks" table, while EmployeeName is fetched from "Contacts" table.

At last , I want to filter the data based on the EmployeeId.

I surfed a lot and came across so many solutions but neither are solving my problem, or I am not getting a clear idea how to do that.

Please help me out.

Looking forward for helpful reply. :)

[Seth Schrock]

The exact answer would depend on how you are storing the currently logged in employee. However, the general idea would be something like the following:

DoCmd.OpenReport ReportName:="Report_Name", View:=acViewPreview, WhereCondition:="EmmployeeID = " & [I]GetEmployeeID[/I]

[aditijangir]

Hi Seth,

Thanks for your reply.

I have tried it. But the report is not displaying any records in it.

[jforbes]

Seth's approach is the most straightforward and easiest way of doing what you are asking about, so if it is not working for you, you may want to post the code you are using and we can help you get it working.

[Seth Schrock]

Also, as a troubleshooting technique, try the following:

Dim strCriteria As String

strCriteria = "EmmployeeID = " & [I]GetEmployeeID[/I]
Debug.Print strCriteria

DoCmd.OpenReport ReportName:="Report_Name", View:=acViewPreview, WhereCondition:=strCriteria

This will post exactly what the report is filtering based on in the Immediate window. Please copy that into your post. If you don't see the Immediate window, press Ctrl + G to view it.

[aditijangir]

Hi Seth,

Thank you for your reply.


I have tried implementing your method. But the problem persists.

It can be because the way I fetch the data from the table.

What I am doing is:
I am using Combobox to fetch EmployeeId and EmployeeName in my report.
It works fine for displaying all users data. But not for current user.

I tried your way, but blank report is coming.

Private Sub Individualreport_Click()
'DoCmd.OpenReport ReportName:="IndividualTask", View:=acViewPreview, WhereCondition:="EmployeeID = " & txtUserID
 Dim strCriteria As String

 strCriteria = "EmployeeId = " & txtUserID
 Debug.Print strCriteria

 DoCmd.OpenReport ReportName:="IndividualTask", View:=acViewPreview, WhereCondition:=strCriteria


End Sub

Also, if it helps this way, I tried using other method also earlier for filtering the data(which I found in one site)

Private Sub Individualreport_Click()
'DoCmd.ApplyFilter , "AddUser.EmployeeId = " & Me.txtUserID


    Dim strWhere As String
    If Me.Dirty Then Me.Dirty = False 'save any edits
     If Me.FilterOn Then strWhere = Me.Filter
    DoCmd.OpenReport "IndividualTask", acViewPreview, , strWhere
 DoCmd.OpenReport "IndividualTask", acViewReport, Me.Filter
    On Error GoTo ErrHandler
    DoCmd.OpenReport ReportName:="IndividualTask", View:=acViewPreview, _
        WhereCondition:="[Contacts Extended].EmployeeId=" & Me.txtUserID    '=" & Chr(34) & Me.ID & Chr(34)
'DoCmd.OpenReport "IndividualTask", acViewPreview, , "[Tasks].EmployeeId = " & Me.txtUserID
    Exit Sub

ErrHandler:
    ' Don't show error message if report was canceled
    If Err <> 2501 Then
        MsgBox Err.Description, vbCritical
    End If

End Sub

Please don't mind but also highlight that what should be the control used for fetching EmployeeId . And what properties I need to take care of in Property window.

I want to understand how this filter exactly works.

Thanks in advance.

[Seth Schrock]

We need to see what was outputted to the immediate window in order to solve the filter problem.

A common method to store the EmployeeID for who is logged in is called Temporary Variable or TempVars.

TempVars.Add "EmployeeID", [I]ID_Value[/I]

You would do this when the database first opens. Then you would modify the report criteria to call the TempVars.

strCriteria = "EmployeeID = " & TempVars("EmployeeID")

[jforbes]

I'm also curious if the Report shows all records when opened from the Navigation Bar. If no records are shown when the Report is opened without a Filter, then the Filter would not be the problem.

[aditijangir]

Hi Seth,

Let me tell you one thing. I am using label to store current user. when I debug the line below, I get correct user Id in Immediate window. Also, report was not showing any data.

strCriteria = "EmployeeId = " & txtUserID   ' where as txtUserID is storing the current user id.

But when I used TempVar error was coming as in "Syntax error 3075"

Also the report was including all users data.

Please guide.

[aditijangir]

Also, one more confusion I am having is; Now when m opening the report the instead of employee id , "ID" is displayed! say if Employeeid is 111111 then it is fetching ID(ex: 34) of that row

When earlier I was using the query in (Row Source) , report was atleast displaying EmployeeId. what could be the reason for it?

[Seth Schrock]

So you are saying that if you run the report on its own, all the records get returned, but if you use your button (that has the filter) then no records get returned?

Can you please copy and paste what was in the immediate window? I would like to see what the result is as this is what isn't working.

[Sandy1992]

Hi Seth,

Aditi here. due to some reasons I lost access to my last account temporarily. Leave that part. I need urgent fix to this issue. so I created new account.

"So you are saying that if you run the report on its own, all the records get returned, but if you use your button (that has the filter) then no records get returned? "

No. I am not getting any data in the report.

Please find the output of immediate window.

Debug.Print strCriteria

EmployeeId ='111111'

Please note: I have used Row Source to fetch EmployeeId from table.

SELECT [Contacts Extended].ID
   , [Contacts Extended].[EmployeeId] 
FROM [Contacts Extended] 
ORDER BY [Contacts Extended].[Contact Name]; "

"Contacts Extended" is a query which have all field of "AddUser" table.

Please help me out

[Sandy1992]

Any suggestions and modifications are welcome. Please help

[Seth Schrock]

In your first post, you state that EmployeeID is numeric. Please verify that if you go into design view for this table, that the data type is Number and not Text. The reason that I ask this is that your strCriteria has single quotes around the number. This will only work if the EmployeeID data type is text. If it is Number, then you can't have the single quotes around it. This could be the reason that your query isn't returning any records.

[Sandy1992]

I confirmed it seth.
It is Numeric.