Export report with dynamic criteria

[Sevn]

I currently have a report that consists of ~30 sales reps. The report is opened & exported to PDF in VBA. I am needing to create the same report, but need each rep to have a separate PDF.

Can someone give me a loop procedure that I can use to produce the report from a dynamic list? The reps will change from month to month, so hardcoding the names won't work.

For example:

sub report

i = rep

for each i
     open report
     export report
next i

end sub

I know this won't work, but it's what i envisioned in my head.

Thanks,

 

[DCrake]

Firstly you will create a recordset based on the reps. Then you will open the recordset and create a report for each record

Dim Rs As DAO.Recordset
Dim IntRep As Integer

Set Rs = CurrentDb.OpenRecordset("Select * From Reps")

Do Until Rs.EOF
IntRep = Rs("RepID")
DoCmd.OpenReport "ReportName",,"Where RepID = " & IntRep
Export Report
Rs.MoveNext
Loop

Summary:
Get a list of all your reps
Base you report on the underlying data grouping by RepID

Then open your report by passing the where condition to the open arguments using the RepID as the Condition
Export the report PDF - your code
Move to the next rep in the list


David

 

[Sevn]

Ok. I gave it a try, but must be doing something wrong. I've attahced a screenshot, and explained what i did below. The field contains the "&" character within the reps name. Could this be the problem?

I setup a test table using the rptQry, so the rptQry & tbl show same results.
I used the following code:

Private Sub Command50_Click()
Dim Rs As DAO.Recordset
Dim strRep As String [COLOR="Red"]   ***I change to string because field is alpha[/COLOR]

Set Rs = CurrentDb.OpenRecordset("Select SalesRepSCust From blah")   [COLOR="Red"]***blah is the name of the test tbl.[/COLOR]

Do Until Rs.EOF
strRep = Rs("SalesRepSCust")
DoCmd.OpenReport "rpt-CompRepGrpCat", , "Where SalesRepSCust = " & strRep
DoCmd.OutputTo acOutputReport, "rpt-CompRepGrpCat", acFormatPDF, "C:\Documents and Settings\youngje\My Documents\My WIP\Project0006-CM Forecast Reporting\Exports\" & strRep & ".pdf", , , , acExportQualityPrint
Rs.MoveNext
Loop
End Sub

Any help will be much appreciated.

 

[gemma-the-husky]

its this bit

"Where SalesRepSCust = " & strRep

first you dont need to explicitly use the word where

secondly, if you have a number you can just use the number
if a string you have to surround it in "" "" marks
if a date you have to surround it in # # marks

so instead you need as your condition

SalesRepSCust = " & chr(34) & strRep & chr(34)

chr(34) is ascii for a "" - since you cant use "" on its own, - to get a "", you have to use a double "" - ie """, and it can become unreadable - using chr(34) circumvents that

 

[Sevn]

Thanks for the response, and it did help some.

Currently the process runs without errors, creates the seperate files per Rep, but the reports are blank. The headers only appear. From what I can tell, it doesn't appear to be applying the criteria to the report.

Here's my current code.

Private Sub Command50_Click()
    'DoCmd.OpenReport "Company, Sales Rep SCust, Prod Grp, Prod Cat", acViewPreview

Dim Rs As DAO.Recordset
Dim strRep As String
Dim strRepID As String

Set Rs = CurrentDb.OpenRecordset("Select SalesRepSCustID From blah Group By SalesRepSCustID")

Do Until Rs.EOF
strRepID = Rs("SalesRepSCustID")
[COLOR="Red"]DoCmd.OpenReport "rpt-CompRepGrpCat", acViewReport, , SalesRepSCustID = " & chr(34) & strRepID & chr(34) ", acWindowNormal[/COLOR]
DoCmd.OutputTo acOutputReport, "rpt-CompRepGrpCat", acFormatPDF, "C:\Documents and Settings\youngje\My Documents\My WIP\Project0006-CM Forecast Reporting\Exports\" & strRepID & ".pdf", , , , acExportQualityPrint
Rs.MoveNext
Loop
End Sub

 

[Sevn]

I have tried everything that I can think of, but can't figure this one out.

With this code, the process runs, and exports the separate PDFs, but no data.

Private Sub Command50_Click_WorksWithNoResults()

Dim Rs As DAO.Recordset
Dim strRep As String
Dim strRepID As String

Set Rs = CurrentDb.OpenRecordset("Select SalesRepSCustID From blah Group By SalesRepSCustID")

Do Until Rs.EOF
strRepID = Rs("SalesRepSCustID")
DoCmd.OpenReport "rpt-CompRepGrpCat", acViewReport, , SalesRepSCustID = " & chr(34) & strRepID & chr(34) ", acWindowNormal
DoCmd.OutputTo acOutputReport, "rpt-CompRepGrpCat", acFormatPDF, "C:\Documents and Settings\youngje\My Documents\My WIP\Zzzz=-Complete-=zzzZ\Project0006-CM Forecast Reporting\Exports\" & strRepID & ".pdf", , , , acExportQualityPrint
Rs.MoveNext
Loop
End Sub

If I comment out everything, and hardcode one of the repIDs, I get one report with everyones data.

Private Sub Command50_Click()

'Dim Rs As DAO.Recordset
'Dim strRep As String
'Dim strRepID As String

'Set Rs = CurrentDb.OpenRecordset("Select SalesRepSCustID From blah Group By SalesRepSCustID")

'Do Until Rs.EOF
'strRepID = Rs("SalesRepSCustID")
[COLOR="Red"]DoCmd.OpenReport "rpt-CompRepGrpCat", acViewReport, , SalesRepSCustID = B02, acWindowNormal[/COLOR]
'DoCmd.OutputTo acOutputReport, "rpt-CompRepGrpCat", acFormatPDF, "C:\Documents and Settings\youngje\My Documents\My WIP\Zzzz=-Complete-=zzzZ\Project0006-CM Forecast Reporting\Exports\" & strRepID & ".pdf", , , , acExportQualityPrint
'Rs.MoveNext
'Loop
End Sub

Does anyone have any suggestions?

Any help would be much appreciated.

Thanks,

 

[Sevn]

I finally go my code to work, but now I've added an additional variable so I can name the PDFs after their respective RepName.

In the following code, the strRep variable could have a variety of illegal characters in the result.
Examples:
US JAN/SAN - TODD SPENCER NSM
US NAT'L ACCT - NATHAN HERRMAN

This is causing an error when the module reaches the export portion, and attempts to use the variable as the filename. Is there a way that I can rewrite the string to have it omit or ignore these characters

Private Sub Command50_Click()

Dim Rs As DAO.Recordset
Dim strRepID As String
Dim strRep As String

Set Rs = CurrentDb.OpenRecordset("SELECT blah.SalesRepSCustID, blah.SalesRepSCust FROM blah GROUP BY blah.SalesRepSCustID, blah.SalesRepSCust ORDER BY blah.SalesRepSCustID;")

Do Until Rs.EOF
strRepID = Rs("SalesRepSCustID")
strRep = Rs("SalesRepSCust")
DoCmd.OpenReport "rpt-CompRepGrpCat", acViewReport, , "SalesRepSCustID = '" & strRepID & "'", acWindowNormal
[COLOR="Red"][B]DoCmd.OutputTo acOutputReport, "rpt-CompRepGrpCat", acFormatPDF, "C:\Documents and Settings\youngje\My Documents\My WIP\Zzzz=-Complete-=zzzZ\Project0006-CM Forecast Reporting\Exports\" & strRep & ".pdf", , , , acExportQualityPrint[/B][/COLOR]
Rs.MoveNext
Loop
End Sub

 

[DCrake]

There are at least 2 possible options

1) use the Replace() function to tackle invalid characters
2) add a field to your reps table that contains the valid file name to use when creating the PDF. So no matter what their name is it will use the predefined file name instead.

David

 

[fxmathieu]

Hi Sevn, DCrake

I want to make exactly what you explain, but without sucess. I'm really a newbie in this domain (VBA in Access).

Here follows my exact situation.

I have a list of issues and I want to have a HTML file by issue. This HTML file is the report I made.

I have a query and a report based on this query (it means that the record source is the Query).

Dim Rs As Recordset
Dim QD1 As QueryDef
Dim idBug As Integer
Dim i As Integer

Set QD1 = CurrentDb.QueryDefs![Q_Bugs by project]
QD1.Parameters![Project name] = "project 1"
Set Rs = QD1.OpenRecordset

Do Until Rs.EOF 
   idBug = Rs!id
   MsgBox ("BugId Processed: " & idBug)
   DoCmd.OpenReport "R_Bug Detail", acViewPreview, , "id = " & idBug, acHidden
   DoCmd.OutputTo acOutputReport, "", acFormatHTML, "d:\temp\begDetail" & idBug & ".html", False
   Rs.MoveNext
   i = i + 1
Loop

Rs.Close

The problem I have is that in each produced HTML file, I have ALL issues (the selection is not made).

Can you help me ? Is it clear enought ?
Thanks in advance
François