Pass parameters to a report

[beaniebear]

I have a report based on the following query :-

SELECT Employee.EmpFirstName, Employee.EmpSurname, EmployeeReportsTo.EmployeeID, EmployeeReportsTo.ReportsToID
FROM Employee INNER JOIN EmployeeReportsTo ON Employee.EmpID = EmployeeReportsTo.EmployeeID
WHERE (((EmployeeReportsTo.ReportsToID)in (SELECT EmployeeReportsTo.EmployeeID
FROM Employee INNER JOIN EmployeeReportsTo ON Employee.EmpID = EmployeeReportsTo.EmployeeID
WHERE (((EmployeeReportsTo.ReportsToID)=[managerid]));
)));

UNION SELECT Employee.EmpFirstName, Employee.EmpSurname, EmployeeReportsTo.EmployeeID, EmployeeReportsTo.ReportsToID
FROM Employee INNER JOIN EmployeeReportsTo ON Employee.EmpID = EmployeeReportsTo.EmployeeID
WHERE EmployeeReportsTo.ReportsToID =[managerid];

using a access 2003 form I want to loop through a list of manager id's pass it to the report and print them out

Dim rec
Set rec = New ADODB.Recordset


strSQL = "SELECT Employee.EmpID, Employee.EmpManager FROM Employee WHERE (((Employee.EmpManager)=Yes));"

With rec
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adOpenDynamic
.Source = strSQL
.Open
.MoveFirst
Do Until .EOF
DoCmd.OpenReport "reportstoquery", , "[managerid] ='" & .Fields(0) & "'"

.MoveNext
Loop
End With

Can anyone tell me what I am doing wrong as the manager id is not being passed to the report and the message box is popping up for me to enter the managerid manually?

Any help would be much appreciated.

 

[smedvid]

Just couple of comments...

1. If I have (25) Managers, then the DoCmd.OpenReport "reportstoquery", , "[managerid] ='" & .Fields(0) & "'" would be fired (25) times. Not efficient.

2. Why not simplify: Add "Employee.EmpManager" field to the SELECT within the report. Then replace the Loop with a single criteria "Employee.EmpManager"="Yes". ..

3. Also, should the criteria really be "EmployeeReportsTo.ReportsToID="?

4. Do you need to add [managerid] to Select statement?

htwh?




Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[beaniebear]

The query has been written this way so for each manager you get all the staff that report to them and also any members of staff that reports to a member of staff that reports to them.

i.e. Mr A is manager of Mr B, and Mr B is Manager of Mr C.

So on the report for Mr A you get Mr B and Mr C. Where as on Mr B's report you'd only get Mr C.

I know it's not efficient to call the same report for each manager but I can't see another way to do it. Although I'm open to any suggestions.

Is it possible to pass a parameter to a query as you call a report as I have been trying to do?

Thanks.

 

[PHV]

Have a textbox named, say, txtManagerID, in your form lauching the report.
Then in the query replace all occurences of [managerid] with [Forms]![name of mainform]![txtManagerID]
Finally in the code:
Do Until .EOF
Me![txtManagerID] = .Fields(0)
DoCmd.OpenReport "reportstoquery"
.MoveNext
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[beaniebear]

Thanks, just what I was looking for! Works a treat now.