INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Use paramater to loop through a report

Use paramater to loop through a report

(OP)
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

RE: Use paramater to loop through a report

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

CODE --> vba

Dim strSQL as String
Do While Not rs.EOF
    'assuming reporsto is numeric
    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

RE: Use paramater to loop through a report

(OP)
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

RE: Use paramater to loop through a report

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

CODE --> vba

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

Duane
Hook'D on Access
MS Access MVP

RE: Use paramater to loop through a report

(OP)
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

RE: Use paramater to loop through a report

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

RE: Use paramater to loop through a report

(OP)
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

RE: Use paramater to loop through a report

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

RE: Use paramater to loop through a report

(OP)
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

RE: Use paramater to loop through a report

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

CODE --> vba

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

RE: Use paramater to loop through a report

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

CODE --> vba

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

RE: Use paramater to loop through a report

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close