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(EmployeeId{Numeric},Job Title,SecurityId....)
2)Tasks(ID,EmployeeId,Title,Description,Start Date, Due Date,..)

Now when the current user click on the command button, it redirect to the report. Report contains Fields like
EmployeeId,EmployeeName,Title,Description,Start 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 = " & GetEmployeeID

[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 = " & GetEmployeeID
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", ID_Value

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.

[Seth Schrock]

Then you need to remove the single quotes from around the EmployeeID as this will cause it to fail.

[Sandy1992]

Hi seth,

sorry for confusing you. the output of immediate window is:

EmployeeId =100001

which is correct.

But now I am getting Runtime-error=2501.

"The OpenReport action was cancelled"

on line.

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

[Seth Schrock]

Do you get this error only when opening the report by clicking the button, or also if you try to open it from the navigation pane?

[Sandy1992]

No. The error occurs only when I click command button. While when I try to open through navigation pane. I displays the report but blank.

In both the case the report is blank.

[Sandy1992]

Please find the series of error occurs when I open the report via command button.

1) Dialog occurs stating:
The section width is greater than the page width , and there are no items in the additional space, so some pages may be blank.
For example, the report width may be wider then the page width.

when I click "OK" button.

Run time Error 2501 is displayed on the same line.

(in immediate window)

EmployeeId=111111
 

When I click on "Cancel" button in dialog
Blank report is displayed.

[Seth Schrock]

Try putting a break point at the first line of code that executes when you click the button and then step through the code. This will help you find what line of code is causing the report to cancel the opening process.

[Sandy1992]

Hi Seth,

I tried doing that , same thing. The problem is in line:

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

Also, does any code results in report print?

[Seth Schrock]

acNormal view does just print the report to the default printer without displaying it.

Is there any code that runs when the report opens? Like in the On_Load or On_Open events?

[Sandy1992]

Ok. My mistake uptil now I was using acViewPreview instead of that.
Just to see if it helps.

With Preview also it shows same error :(

[Sandy1992]

Please help to get this thing work

[Seth Schrock]

Is there any code that runs when the report opens? Like in the On_Load or On_Open events?

[Sandy1992]

No code is there.

Ok can you guide how to fetch data using query?

the whole process.

If this report is having problem, I will fetch the data using form and query. but the question is will it fetch current user?

[Sandy1992]

Actually I tried fetching data using query. I used it to fetch based on the date. Which is working fine. But when I tried it using Textbox(EmployeeId) , again blank datasheet came.

Also,I tried using combobox, but no success.

I can share my code where I used combobox.

Like [Forms]![Form3]![EmpID]

where form3 is the form where combobox and command button is. And EmpID is the name of combobox.

[Sandy1992]

I really want this thing to work ASAP.

All suggestions are welcome

[jforbes]

What concerns me most right now is:

No. The error occurs only when I click command button. While when I try to open through navigation pane. I displays the report but blank.

In both the case the report is blank.

If your report is blank when opened through the Navigation Pane, most likely the report is going to be blank no matter how it is opened.

List of questions:

1. So how did this report get created?
2. Did you create it?
3. What is the RecordSource of the Report? Is it a Table or a Query, or a Query with a where clause?
4. Was there something special done when it was created? Like changing the RecordSource in code.
5. Is there any code in your Report?

Getting the report to open and show data from the Nav Pane is probably the first step to getting your Report to work.

[jforbes]

Also after seeing your other thread, http://bytes.com/topic/access/answer...mbobox-textbox it makes me think that the query you are using as the basis for your report has a where clause that refers to a Textbox on a Form.

There are two common ways to narrow down the contents of a Report in Access:

1. Base the Report on a Query that has a where clause that either has a parameter in it or an expression that refers to Control on a Form.
2. Filter a Report while opening with the DoCmd.OpenReport

These are typically mutually exclusive and only one way should be used at a time. So basing the Report on a Query with a Where Clause with a Parameter/Epression and then opening the Report with a Filter (Where Condition) will, if your not very careful, give some seemingly random results.

[Sandy1992]

Hi jforbes,

Thanks for your response.
Currently I have used combobox to solve my problem. And it is working fine. It is displaying the result in form.

I thought of using reports to do so. But was unsuccessful in that . I tried so many things but still it was not showing any data.

anyways, I am happy atleast what I wanted is happening in datasheet view.

You people are good.

I appreciate "Seth" a lot. You are great my friend you stood with me till now. I was feeling that atleast I will get reply to my queries which I very well did.

Thanks a lot Seth.:)

thanks jforbes. :)