Need help in writing VB code to loop through a List Box and create separate PDFs (1 Viewer)

[brosenfelt]

So I will admit -- have NOT been working with Access for long (as well as VB)....but have a need, so ready to do whatever necessary to learn!

I have a sales report that I generate each week based on a query, which relies on the input from a combo box on a form. This works great - but I have select each salesperson, one at a time and run their reports separately.

I'd like to have some code that will run through each person on the list, generate their report, create the PDF, and email (or save) the file.

From my initial research, it appears I will need to utilize a List Box (vs a combo box), so I can select multiple salespersons for the report.

I've found a few examples of code to use...but a little lost on how to implement. I create a new form with a list box that queries the property table for the list. I then added a button to execute the code, and attempted to add this (with my control and reports names substituted). Getting all sorts of errors -- so not sure I'm on the correct path.

'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003 with a Combo Box

Dim intCounter As Integer
Dim cboCode As ComboBox

Set cboCode = Me![ YourControl ]

'If Your Data Type is Numeric use this section
For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport " YourReport ", acViewPreview, , _
"[ YourFieldControlSource ] = " & cboCode.ItemData(intCounter)
DoEvents
DoCmd.OutputTo acReport, " YourReport ", acFormatRTF, " DriveLetter :/ FolderName / FileName " & Format(intCounter, "000") & ".rtf"
DoCmd.Close acReport, " YourReport "
Next


'If Your Data Type is a String use this section
For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport " YourReport ", acViewPreview, , _
"[ YourFieldControlSource ] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
DoCmd.OutputTo acReport, " YourReport ", acFormatRTF, " DriveLetter :/ FolderName / FileName " & Format(intCounter, "000") & ".rtf"
DoCmd.Close acReport, " YourReport "
Next

Happy to my additional research on tutorials, but was hoping for some direction.

Thanks in advance.

Here is some relevant info from my database:

Query name: qry_pipeline_parse_allrm
Report name: Pipeline - All RMs
New form I created: frm_rmpick_listbox
List box control on that form: List0
Button control on that form: Command2

Thank you!:banghead:

 

[Ranman256]

close....

For intCounter = 0 To cboCode.ListCount - 1  
       'set the list to the next item
   cboCode = cboCode.dataitem(intCounter)   
         'make the report
   DoCmd.OutputTo acReport, " YourReport ", acFormatRTF, "c:\FolderName\FileName" & Format(intCounter, "000") & ".rtf"
Next

which do you want , PDF, or RTF?

 

[brosenfelt]

Looking to generate PDF

 

[brosenfelt]

Here's the code I am attempting to use (tied to a button on the form with the list box):

Private Sub Command2_Click()
Dim intCounter As Integer
Dim cboCode As ListBox

Set cboCode = Me![List0]
For intCounter = 0 To cboCode.ListCount - 1
'set the list to the next item
cboCode = cboCode(intCounter)
'make the report
DoCmd.OutputTo acReport, " YourReport ", acFormatRTF, "c:\FolderName\FileName" & Format(intCounter, "000") & ".rtf"
Next
End Sub

But getting an error: "Run-time error '13': Type mismatch"

Thoughts?

 

[brosenfelt]

Slight revision to the code:

Private Sub Command2_Click()
Dim intCounter As Integer
Dim cboCode As ListBox

Set cboCode = Me![List0]
For intCounter = 0 To cboCode.ListCount - 1
'set the list to the next item
cboCode = cboCode.ItemData(intCounter)
'make the report
DoCmd.OutputTo acReport, "Pipeline - All Rms - AUTO-REPORT", acFormatPDF, "K:\CLE05\Business Resource Center\Opportunity Tracking\pipelinereport" & RM_FULL_NAME & ".pdf"
Next
End Sub

This now runs without error, but creates a single report with all salespeople selected.

Would like to append the name of the salesperson to the file, and create a separate report for each.

 

[jdraw]

Which line is error occurring on?

 

[brosenfelt]

Actually no errors now -- but it is only creating one big report. no matter which salesperson I select from the List Box.

 

[jdraw]

I think you are missing the code that creates the report with criteria

DoCmd.OpenReport " YourReport ", acViewPreview, , _
"[ YourFieldControlSource ] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents

 

[brosenfelt]

Not quite sure what to put for "YourFieldControlSource". The List Box is generated by a query on an existing table. The List Box name is List0. The query that generates the list is:

SELECT BRC_RM.ID, BRC_RM.RM_FULL_NAME
FROM BRC_RM;

 

[brosenfelt]

The report I am running is based on this query:

SELECT ParseText([pipeline]![Short Description],0) AS ARL, pipeline.Status, pipeline.[Estimated Close Date], pipeline.[Probability of Close], ParseText([pipeline]![Short Description],1) AS [BRANCH RM], ParseText([pipeline]![Short Description],2) AS DESCRIPTION, ParseText([pipeline]![Short Description],3) AS [UPDATE], ParseText([pipeline]![Short Description],4) AS UPDATE_DATE, IIf([pipeline]![Business] Is Null,[pipeline]![Contact First Name] & " " & [pipeline]![Contact Last Name],[pipeline]![Business]) AS BUSINESSNAME, BRC_RM.RM_FULL_NAME, IIf(ParseText([pipeline]![Short Description],2)="","",(ParseText([pipeline]![Short Description],2)) & "/" & ParseText([pipeline].[Short Description],3)) AS DESCRIPTION2, pipeline.Product, pipeline.[Incr New Commitment]
FROM pipeline INNER JOIN BRC_RM ON pipeline.[Primary Officer Last Name] = BRC_RM.RM_LAST_NAME;

 

[jdraw]

How do you generate the individual reports? What makes 1 report different than the next?
Is it the ID, or the RM_FULL_NAME?

Where is your OpenReport line? Did you ever have it working for indivduals?

 

[brosenfelt]

The criteria that changes each report is based on the field RM_FULL_NAME.

I didn't use VB code for the original report -- used Access macros based on form input.

SELECT ParseText([pipeline]![Short Description],0) AS ARL, pipeline.Status, pipeline.[Estimated Close Date], pipeline.[Probability of Close], ParseText([pipeline]![Short Description],1) AS [BRANCH RM], ParseText([pipeline]![Short Description],2) AS DESCRIPTION, ParseText([pipeline]![Short Description],3) AS [UPDATE], ParseText([pipeline]![Short Description],4) AS UPDATE_DATE, IIf([pipeline]![Business] Is Null,[pipeline]![Contact First Name] & " " & [pipeline]![Contact Last Name],[pipeline]![Business]) AS BUSINESSNAME, BRC_RM.RM_FULL_NAME, IIf(ParseText([pipeline]![Short Description],2)="","",(ParseText([pipeline]![Short Description],2)) & "/" & ParseText([pipeline].[Short Description],3)) AS DESCRIPTION2, pipeline.Product, pipeline.[Incr New Commitment]
FROM pipeline INNER JOIN BRC_RM ON pipeline.[Primary Officer Last Name] = BRC_RM.RM_LAST_NAME
WHERE (((BRC_RM.RM_FULL_NAME)=[Forms]![frm_rmpick]![Combo41]));

 

[jdraw]

It seems the sql you just sent is looking fo the fullname to be in
[Forms]![frm_rmpick]![Combo41]

so I think that is the control referred to in the original code

Set cboCode = Me![ YourControl ]

More of a guess than fact on my part.

 

[brosenfelt]

Yes - but in this case, I'm trying to automate the report using a different control (a List Box vs the original Combo Box I used in the individual report). That control is List0. I'm using a List Box so I can select multiple salespeople at the same time. So this is what I have right now:

Private Sub Command2_Click()
Dim intCounter As Integer
Dim cboCode As ListBox

Set cboCode = Me![List0]
For intCounter = 0 To cboCode.ListCount - 1
'set the list to the next item
cboCode = cboCode.ItemData(intCounter)
'make the report
DoCmd.OpenReport "Pipeline - All Rms - AUTO-REPORT", acViewPreview, , _
"[List0] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents

Next
End Sub

When I run this command, the report generates, but again, only a single report with ALL salespersons (regardless of which sub-set of salespeople I select from my List Box).

 

[jdraw]

I don't understand??
If you are selecting from the listbox, then it isn't using the selection to limit what is getting into the report.
If you are not getting 1 individual per loop, then the ode isn't doing what you think.

I think there is a basic difference in combo vs list

see this for selected item in Listbox

see this one also

 

[brosenfelt]

I am confused -- not sure how to proceed. I just want to be able to select multiple items in my List Box, then have the code generate reports (one at a time) for each item selected.

Do I need to re-write the query the report is using so it references my new List Box control name (List0) as the criteria???

 

[jdraw]

How do you plan to select individuals/items for each report -- manually?
Here is a link that shows cycling thru a list
http://stackoverflow.com/questions/2933113/access-2007-cycling-through-values-in-a-list-box-vba

but you want to select the items now???

 

[brosenfelt]

I modified the query to use the new LIst Box form as its criteria

Private Sub Command2_Click()
Dim intCounter As Integer
Dim cboCode As ListBox

Set cboCode = Me![List0]
For intCounter = 0 To cboCode.ListCount - 1
       'set the list to the next item
   cboCode = cboCode.ItemData(intCounter)
         'make the report
   DoCmd.OpenReport "Pipeline - All Rms - AUTO-REPORT", acViewPreview, , _
"[List0] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents

Next
End Sub

Now when I run it -- it is prompting me for "List0".

 

[brosenfelt]

And yes....I plan on select multiple salespeople from the List Box on the form prior to running the code.

 

[brosenfelt]

So - I've re-written the code a bit....not getting errors....but the code is not passing the value from the List Box to the report -- so I'm getting a blank report.

Private Sub Command3_Click()

Dim i As Integer
Dim lbox As ListBox
Dim myPath As String
Dim strReportName As String

Set lbox = Me![List0]
myPath = "K:\CLE05\Business Resource Center\Opportunity Tracking\"
strReportName = lbox.ItemData(i)

For i = 0 To lbox.ListCount - 1
'Access each item with
'Me.ListBoxName.ItemData(i)
DoCmd.OutputTo acOutputReport, "Pipeline - All Rms - AUTO-REPORT", acFormatPDF, myPath & strReportName & ".pdf", False, , , acExportQualityPrint

'Debug.Print lbox.ItemData(i)
Next i

End Sub

SUGGESTIONS???? I feel like I'm close. When I uncomment the "Debug.Print", I can see it is looping through each of the names in my list -- so the loop seems to be working properly....just not passing that selection to the report/query.

Thanks!