Use paramater to loop through a report

[razchip]

In a report, can I loop through a table to pick the parameter. I can't seem to hit the right combination to have the report only show me the "ReportsTo" from the tbl ReportsTo2 for that individual, it continually shows me all results; or do I have to look at this another way.

Private Sub Command58_Click()
On Error GoTo Exit_Command58_Click
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("ReportsTo2") 'table
Dim stDocName As String

stDocName = "5MgrApproval-1"

DoCmd.SetWarnings False
DoCmd.OpenQuery "5MgrApproval3-Test", acNormal, acEdit

rs.MoveFirst
Do While Not rs.EOF

DoCmd.OpenReport stDocName, , , , "reportsto=" & rs!reportsto
DoCmd.SendObject acReport, stDocName, "PdfFormat(*.pdf)", rs!emailAddress, "", "", "Manager Approval", "", True, """"""
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
MsgBox "Reports Completed", vbOKOnly, "Month End PTO"

Exit_Command58_Click:
If MsgBox("Do you want to cancel the run?", vbQuestion Or vbYesNo) = vbYes Then
Exit Sub
Else
Resume Next
End If
End Sub

Appreciate any direction you may give me.

Thanks for the help.
Greg

 

[dhookom]

I would try to change the SQL property of the report's record source as you step through the recordset.

Dim strSQL as String
Do While Not rs.EOF
    [COLOR=#4E9A06]'assuming reporsto is numeric[/color]
    strSQL = "SELECT * FROM BaseQueryName WHERE reportsto=" & rs!reportsto
    CurrentDb.QueryDefs("YourRecordSourceQueryName").SQL = strSQL
    DoCmd.SendObject acReport, stDocName, "PdfFormat(*.pdf)", rs!emailAddress, "", "", "Manager Approval", "", True, """"""
    rs.MoveNext
Loop

Duane
Hook'D on Access
MS Access MVP

 

[razchip]

dhookom,

I don't have any real background with the querydef function, I tried doing what you asked, but I think I'm still out in left field somewhere.
tblMgrApproval is where the data is located (reportsto is a text field).
5MgrAppv is the query the report draws from.

strSQL = "SELECT * FROM tblMgrApproval WHERE reportsto=" & rs!reportsto
CurrentDb.QueryDefs("5MgrAppv").SQL = strSQL

It still returns all the data, so I assume I'm not referencing something correctly.

Gjw

Thanks for the help.
Greg

 

[dhookom]

Since ReportsTo is a text field, you need to try:

 strSQL = "SELECT * FROM tblMgrApproval WHERE reportsto= """ & rs!reportsto & """"
 CurrentDb.QueryDefs("5MgrAppv").SQL = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[razchip]

Sorry for the late response, I've been working on this ever since and have not been able to get this or any other code to cycle through properly. I'm trying to use a query now, so I changed the code, but still can't get it to cycle through the query; all records are returned (tired opening the query also, but I still get all the records).

Private Sub Command53_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("5QryMgr") ' query
Set rs = qdf.OpenRecordset()

With rs
Do Until .EOF
DoCmd.OpenReport "5MgrRpt", acPreview
.MoveNext
Loop
End With

End Sub




Thanks for the help.
Greg

 

[dhookom]

What happened to the code that modified the SQL property of the saved query that was the record source of your report?

Duane
Hook'D on Access
MS Access MVP

 

[razchip]

I was unable to get a result using the code. Maybe I gave up to soon, but it's so frustrating (20+ hours), it continued to give me all the records. So I made some queries to get the SQL results. I hope I can loop through the query to generate the report .

Thanks for the help.
Greg

 

[dhookom]

What is the name of the query your report is based on?

What is the SQL View of the query your report is based on?

What is the SQL view of 5QryMgr? Does this provide records with unique values [reportsto]?

Am I correct that ReportsTo is a text field?

Duane
Hook'D on Access
MS Access MVP

 

[razchip]

What is the name of the query your report is based on?
5MgrApproval

What is the SQL View of the query your report is based on?
qryReportsTo - this lists each manager, no duplicates

SELECT ReportsTo.[Reports-To], ReportsTo.EmailAddress, ReportsTo.EmpNo, ReportsTo.ReportName
FROM ReportsTo;

What is the SQL view of 5QryMgr? Does this provide records with unique values [reportsto]?
No, there may be multiple records for that manager.

5qryMgr
SELECT tblResults.ReportsTo, tblPTO.Mth, tblPTO.EmpNo, tblPTO.Sort, UserInfo.Name, tblEmpBackground.Yr, tblEmpBackground.Balance, tblPTO.Day, tblPTO.UsedHours, tblPTO.FrozenHoursUsed, tblPTO.BankedHoursUsed, [UsedHours]+[FrozenHoursUsed]+[BankedHoursUsed] AS PTO, tblPTO.[4-Day], tblPTO.Comments, tblEmpBackground.CarryOver, tblEmpBackground.Lost, tblEmpBackground.Earned, [9EmpHoursRpt-AllYearC].HrsRem, [5MgrApproval2].SumOfPTO, [tblPTO]![EmpNo]*0.000000000001 AS Xhrs, [HrsRem]+[Xhrs] AS HrsRem2, [SumOfPTO]+[Xhrs] AS SumOfPTO2
FROM ((((tblEmpBackground INNER JOIN tblPTO ON (tblEmpBackground.[Yr] = tblPTO.[Yr]) AND (tblEmpBackground.[EmpNo] = tblPTO.[EmpNo])) INNER JOIN UserInfo ON tblEmpBackground.[EmpNo] = UserInfo.[EmpNo]) INNER JOIN [9EmpHoursRpt-AllYearC] ON tblEmpBackground.[EmpNo] = [9EmpHoursRpt-AllYearC].[EmpNo]) INNER JOIN 5MgrApproval2 ON tblEmpBackground.EmpNo = [5MgrApproval2].EmpNo) INNER JOIN tblResults ON tblEmpBackground.[Reports-To] = tblResults.ReportsTo
WHERE (((tblPTO.Mth)=[Forms]![frmMain2]![Month]) AND ((tblEmpBackground.Yr)=[Forms]![frmMain2]![Year]))
ORDER BY tblPTO.EmpNo, tblPTO.Sort, tblPTO.Day;

Am I correct that ReportsTo is a text field?
Yes, it's a text field

Thanks for the help.
Greg

 

[dhookom]

You didn't provide the SQL view of the report's record source query which I assume is 5MgrApproval but might be 5qryMgr.

Private Sub Command53_Click()

 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim strSQL as String
 Dim qdf As DAO.QueryDef
 Dim stDocname as String
 stDocName = "5MgrRpt"
 strSQL = "SELECT Distinct ReportsTo.[Reports-To] as ReportsTo FROM ReportsTo"
 Set db = CurrentDb()
 Set qdf = db.QueryDefs("5QryMgr") ' query
 Set rs = db.OpenRecordset(strSQL)
 
 With rs
    Do Until .EOF
      qdf.SQL = "SELECT * FROM [Some Query Name] WHERE ReportsTo = """ & rs("ReportsTo") & """"
      DoCmd.SendObject acReport, stDocName, "PdfFormat(*.pdf)", rs!emailAddress, "", "", "Manager Approval", "", True, """"""
      .MoveNext
    Loop
    .Close 
 End With
 Set rs = Nothing
 Set qdf = Nothing
 Set db = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Sorry, the email address should have been in the recordset.

Private Sub Command53_Click()

 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim strSQL as String
 Dim qdf As DAO.QueryDef
 Dim stDocname as String
 stDocName = "5MgrRpt"
 strSQL = "SELECT Distinct [Reports-To] as [ReportsTo], EmailAddress FROM ReportsTo"
 Set db = CurrentDb()
 Set qdf = db.QueryDefs("5QryMgr") ' query
 Set rs = db.OpenRecordset(strSQL)
 
 With rs
    Do Until .EOF
      qdf.SQL = "SELECT * FROM [Some Query Name] WHERE ReportsTo = """ & rs("ReportsTo") & """"
      DoCmd.SendObject acReport, stDocName, "PdfFormat(*.pdf)", rs!emailAddress, "", "", "Manager Approval", "", True, """"""
      .MoveNext
    Loop
    .Close 
 End With
 Set rs = Nothing
 Set qdf = Nothing
 Set db = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[razchip]

Thanks dhookom,

I pulled this in last night and found where I was making the error. I was not opening the recordset properly. When I used your code everything worked fine. I really appreciate the help. I've learned a couple of things, so I'm walking away happy, thanks again.

Thanks for the help.
Greg