Batch Printing Reports

[Missionary]

I need to batch print reports. I've looked at past posts about batch printing, but I don't know visual basic, so I'll need some coaching.

I have a report based on a parameter query. I have it set to take the parameter value from a form that I use to preview and print reports.
I have to repeatedly print that report using a different value each time. I want to set it up to batch print, taking the values from a list in a table.

Thanks for your time

[jaxjagfan]

I need to batch print reports. I've looked at past posts about batch printing, but I don't know visual basic, so I'll need some coaching.

I have a report based on a parameter query. I have it set to take the parameter value from a form that I use to preview and print reports.
I have to repeatedly print that report using a different value each time. I want to set it up to batch print, taking the values from a list in a table.

Thanks for your time

Easiest way if you're not familiar with VBA

Change the report datasource so that the parameter is table driven, link to the paramater table, create a group for the parameter and have the report do a page break on the parameter group.

The parameter table could have a yes/no field that you could use to toggle the parameter on and off.

[Missionary]

Easiest way if you're not familiar with VBA

Change the report datasource so that the parameter is table driven, link to the paramater table, create a group for the parameter and have the report do a page break on the parameter group.

The parameter table could have a yes/no field that you could use to toggle the parameter on and off.

That is basically how I had it before. But I ran into a problem with the headers and footers. For each report (or group) I have a report header that goes above the page header on the first page. How can I make the group header come above the page header?

Also, I want the page numbers in the page footer to restart with each group. I found how to do that on this site:

http://www.mvps.org/access/reports/rpt0013.htm

and I followed the directions, but it didn't work. Perhaps my settings on the controls were wrong. I'm not sure what he's talking about when he says "control name of the group" And what type of control should ctlGrpPages be? should it be unbound? etc.

So that's where I got held up and thought that batch printing might be easier. Any help would be apreciated.

I'm not afraid of VBA, I'm just brand new at it and need some coaching.

Thanks alot!

[mshmyob]

Hello Missionary,

Group headers CANNOT come above Page Headers.

That is basically how I had it before. But I ran into a problem with the headers and footers. For each report (or group) I have a report header that goes above the page header on the first page. How can I make the group header come above the page header?

Also, I want the page numbers in the page footer to restart with each group. I found how to do that on this site:

http://www.mvps.org/access/reports/rpt0013.htm

and I followed the directions, but it didn't work. Perhaps my settings on the controls were wrong. I'm not sure what he's talking about when he says "control name of the group" And what type of control should ctlGrpPages be? should it be unbound? etc.

So that's where I got held up and thought that batch printing might be easier. Any help would be apreciated.

I'm not afraid of VBA, I'm just brand new at it and need some coaching.

Thanks alot!

[Missionary]

Hello Missionary,
Group headers CANNOT come above Page Headers.

Thanks, mshmyob; that's what I figgured. What I could do is put the "Report Heading" in the Page Header, then just turn it on and off depending on the page number. Following is the Code I mentioned for the Page numbering that I got from

http://www.mvps.org/access/reports/rpt0013.htm

I added in some lines to make the ctlText visible or invisible.
Now, I could never get the page numbers to work in the first place, so this is deffinately a stab in the dark.
Where did I go wrong in the code?
Pls keep in mind that this is my first venture into VBA and I don't know the lingo.

Option Compare Database

'************ Code Start *************
' This code was originally written by James H Brooks.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' James H Brooks
'
Option Explicit

Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
  If Me.Pages = 0 Then
    ReDim Preserve GrpArrayPage(Me.Page + 1)
    ReDim Preserve GrpArrayPages(Me.Page + 1)
    GrpNameCurrent = Me!ctlWard
    If GrpNameCurrent = GrpNamePrevious Then
        Me!ctlText.Visible = False     'Turn off the "Heading"
        GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
        GrpPages = GrpArrayPage(Me.Page)
            For i = Me.Page - ((GrpPages) - 1) To Me.Page
                GrpArrayPages(i) = GrpPages
            Next i
    Else
        Me!ctlText.Visible = True   'Turn on the "Heading"
        GrpPage = 1
        GrpArrayPage(Me.Page) = GrpPage
        GrpArrayPages(Me.Page) = GrpPage
    End If
  Else
    Me!ctlGrpPages = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
  End If
  GrpNamePrevious = GrpNameCurrent
End Sub
'************ Code End *************

I have a textbox called ctlWard in the Group Header that shows the Group value.
I have an unbound textbox called ctlGrpPages that is supposed to show the page number In the page footer. Whenever I preview the Report, ctlGrpPages just shows up empty. Also, ctlText doesn't disapear.

Sorry this question is so long. I think I may be getting ahead of myself, but I'm hoping it will work.

[jaxjagfan]

That is basically how I had it before. But I ran into a problem with the headers and footers. For each report (or group) I have a report header that goes above the page header on the first page. How can I make the group header come above the page header?

Also, I want the page numbers in the page footer to restart with each group. I found how to do that on this site:

http://www.mvps.org/access/reports/rpt0013.htm

and I followed the directions, but it didn't work. Perhaps my settings on the controls were wrong. I'm not sure what he's talking about when he says "control name of the group" And what type of control should ctlGrpPages be? should it be unbound? etc.

So that's where I got held up and thought that batch printing might be easier. Any help would be apreciated.

I'm not afraid of VBA, I'm just brand new at it and need some coaching.

Thanks alot!

In your case I sometimes drop the report and page header/footer and use group header/footer. I even create a "dummy" column to group on in some cases to allow for a bit of creativity in the report layout.

With that being said heres some VBA. I have a table which lists all my reports by their Access name, Descriptive name, description - lstReports. I have a "Parameter" table - data in lstParam.

You can customize to loop thru and print each seperately.

Private Sub cmdGenReport_Click()

If Me.lstParams.ItemsSelected.Count = 0 Then
DoCmd.OpenReport Me.lstReports.Column(0), acViewPreview
Else
Call ParamList
DoCmd.OpenReport Me.lstReports.Column(0), acViewPreview, , strFilter
End If
strFilter = ""
Me.lstParams.Requery

End Sub

Public Function ParamList()

Dim ctl As Control, varParam As Variant
Set ctl = Me.lstParams
For Each varParam In ctl.ItemsSelected
    If strFilter = "" Then
    strFilter = "ParamField = " & "'" & ctl.ItemData(varParam) & "'"
    Else: strFilter = strFilter & " Or " & "ParamField = " & "'" & ctl.ItemData(varParam) & "'"
    End If
Next varParam
    
End Function

[NeoPa]

You say that you have a report which is driven by a control on your form.
How about some code on your form which loops through all the items you need reports for, and, for each iteration through the loop, sets the control to the current value then opens the report. When the loop is finished all your reports should have printed off.

NB. This will not work well for viewing the results in PrintPreview mode, as each subsequent call would replace the data from the previous one.

[Missionary]

That sounds good NeoPa, but I'm still struggling with the code.

Jaxjagfan, your post went a bit over my head. Could you explain in more basic terms. Where do I paste this code? What parts of the code do I need to change to my own table/field names, etc.


Thank you all for your help.

[NeoPa]

That sounds good NeoPa, but I'm still struggling with the code.
...

That's fine Missionary.
Put together what you can and we can fill in the gaps. We don't (shouldn't) just provide solutions, as that doesn't help you to develop your skills or understanding.

[Missionary]

That's fine Missionary.
Put together what you can and we can fill in the gaps. We don't (shouldn't) just provide solutions, as that doesn't help you to develop your skills or understanding.

I didn't get to work on this very long as I'm going out of town this weekend, but here is what I've got so far:
I have a form with a comboBox called Wards that the query looks at to find the what records to show in the report.

Here's the code:

Private Sub cmdPrintAll_Click()
  If Me.Wards.ItemsSelected.Count = 0 Then
  'What is "ItemsSelected"?  It didn't recognise that
    DoCmd.OpenReport rptRetentionReport, acViewNormal
    'I don't know what this If statement is for.  Perhaps to print the first one?
  Else
    Call ParamList
    DoCmd.OpenReport rptRetentionReport, acViewNormal, , strFilter
    'This prints on condition
  End If
  strFilter = ""
  'This is the condition.  I Don't understand what this says
  Me.Wards.Requery
  'I think this refreshes the form, but not sure
End Sub

Public Function ParamList()
  Dim ctl As Control, varParam As Variant
  Set ctl = Me.Wards
  For Each varParam In ctl.ItemsSelected
  'again, it didn't like "ItemsSelected"
    If strFilter = "" Then
      strFilter = "ParamField = " & "'" & ctl.ItemData(varParam) & "'"
    Else
      strFilter = strFilter & " Or " & "ParamField = " & "'" & ctl.ItemData(varParam) & "'"
    End If
  Next varParam
End Function

[NeoPa]

ItemsSelected is a collection property of a ListBox control. Only ListBoxes have the Multi-Select ability. Only one item can be selected at a time from a ComboBox.

[NeoPa]

Line 2 Asks if there are NO items selected.
Line 4 Opens the report with no filter - I guess this is where the code needs to loop through each Ward.
Line 7 A call to ParamList() sets up the filter (should).
Line 11 Resets strFilter.
Line 13 Resets the Wards ComboBox.

ParamList() is clearly trying to set up a filter string to pass to the report such that only matching ParamField records are included.

We can look at this in more detail later (many changes to make in here) when you've had a chance to digest what I've explained so far. Post back when you're ready.

[Missionary]

Well, we have a mission conference coming up and some other things, so I'm quite busy, but I'll keep sneaking chances here and there to work on this. This is what I've got:
1. It seems like jaxjagfan's code was meant to be put in the report somehow. Now I'm trying to run it from a form.
2. I'm still not entirely sure how this filter is working.
It looks like the public function just defines a tool that will loop through the values in me.wards, and then the private sub, invokes this loop (is that the right term?) to run through the values.
3. Now that you cleared up that bit about the list box, I guess he made it to batch print only those that were selected in the list. That seems pretty cool, but I don't think I need that for this. First of all, there are like 30 wards, and that would never fit on a form. Also, we will really only be printing all, or one. Maybe I'll use that list box thing later in my access career, but for now, if it's easier, we could just work to loop through all the values in a column of a table or query.
4. I wish I had taken a VBA class in high school. Do you know of any good online tutorials? I'm sure google can find me something.

Thank you so much for your help and patience!

[NeoPa]

I used +"access 2003" +"vba" +"tutorial" and the first few all looked good. I don't want to link directly to any individual link, but that should get you started.

Have a go through some basics, then come back and we can look at :

1. Tidying up your understanding of exactly what you want to achieve.
2. Sorting out what your elemental items are (Objects; Functions; etc).
3. Putting together an idea of what the code does that you're going to write and how it will link from the elementals to the call.

[NeoPa]

In the mean-time, I'll just deal with a couple of the points you made.

1. It seems like jaxjagfan's code was meant to be put in the report somehow. Now I'm trying to run it from a form.

You'll notice that the code actually opens the report, so this is code that calls the report (repeatedly), not code embedded in the report (which is possible - just not done here).

2. I'm still not entirely sure how this filter is working.
It looks like the public function just defines a tool that will loop through the values in me.wards, and then the private sub, invokes this loop (is that the right term?) to run through the values.

Not quite sure where you're going with this but perhaps if I explain what a filter does then that might make things a little clearer.
A filter is like a SQL WHERE clause. To illustrate what that does I'll show a table with some data and show which records are displayed in a report built on the table if the following filter is applied :
Filter

[Surname] = 'Smith'

Table = tblName

[U]ForeName[/U]  [U]Surname[/U]
Adam      Blythe
Brian     Smith
Charles   Freeth
Donald    Blythe
Edward    Smith
Frederic  Bloggs

The only records that will show in the report are "Brian Smith" and "Edward Smith".
If the filter were :

[ForeName] = 'Edward'

... then the only record shown would be "Edward Smith".
This is a very simple filter for illustration purposes. Filters can be a lot more complex and powerful when required.

PS> See post #14 for a road-map of how to proceed.