Macro to print reports based on certain criteria

[jaws1972]

Hopefully there is a simple answer to this question, and I have become a bit snow-blind

I have a form that a user can select a report from, based on a couple of radio buttons, and two list boxes.

On double clicking a list box record, a query will run, based on the criteria selected. The record is passed to a query via Forms![Form_Name]![Record[ method.

The user has to print, on average, about 15 reports, by double-clicking the records.

Is there a way that I can set up a macro, that automatically prints out the reports, when the user clicks on a button, so that the first criteria is passed to the query, the report is generated and then printed, and then moves on to the next criteria, and so on

Hopefully my question is clear, and I would be greatful for any responses

 

[THWatson]

jaws1972
If the report is the same, and only the parameters vary, I'm wondering if, rather than double clicking an individual record to run the report, you could do something like this...

Have an unbound text box. As each desired record is checked, the RecordID goes into the text box, and a String is made of the selected records. Then have a command button that loops through the RecordID's in the text box string, using a For Each...Next structure, and runs the report for each RecordID in the string.

Would that work?

Tom

 

[jaws1972]

Firstly, thanks for the reply

All I was hoping to do was use the OpenQuery action of a macro, passing the type of report to it in the Condition selection (such as Type = Handbags). Follow this with a PrintOut for the report, and then repeat this 2-stage macro for each report I need

 

[THWatson]

jaws1972
What I had in mind was making your list box a multi-select list box. As each record is checked it is sent to a text box. When all desired records are picked, then a command button runs the report based on the accumulated records in the list box.

As an example, I do this with the selection of names for which I want to print envelopes. The code that lies behind a command button on the selection form is as follows...

Private Sub cmdSelectListBox_Click()
On Error GoTo Err_cmdSelectListBox_Click
Dim stDocName As String
stDocName = "rptSelectFromListBox"
Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim strList As String

    strList = ""
    Set frm = Forms!frmSelectAddressesToPrint
    Set ctl = frm!List0
    For Each varItm In ctl.ItemsSelected
       strList = strList & ctl.ItemData(varItm) & ", "
    
    Next varItm
    strList = Left(strList, Len(strList) - 2)
DoCmd.OpenReport stDocName, acPreview, , "[UniqueID] IN (" & strList & ")"

'Then rest the list box and the text box that collects
Dim ndx As Integer
For ndx = 0 To Me.List0.ListCount - 1
Me.List0.Selected(ndx) = False
Next
Me.txtSelected = Null
Me.Text19 = Null
Exit_cmdSelectListBox_Click:
    Exit Sub

Err_cmdSelectListBox_Click:
    MsgBox Err.Description
    Resume Exit_cmdSelectListBox_Click
    
End Sub

The above, of course, assumes that the report is the same for each record selected.

There may be a way to set something up to repeat macros to do a similar thing. I don't know, because I don't use macros.

So, maybe this works in your case. Maybe not. Regardless, just tryin' to help.

Tom

 

[kodyjori]

I'm looking to setup a button to print only the current record from the form to a specific report.

I've tried to use the above example to get this:

Private Sub printthisrecord_Click()
On Error GoTo Err_printthisrecord_Click

Dim stDocName As String
Dim varIssueID As Integer

varIssueID = Me.IssueID
stDocName = "SupportIssueReport"
DoCmd.OpenReport stDocName, acPreview, , "[IssueID] = varIssueID"

Exit_printthisrecord_Click:
Exit Sub

Err_printthisrecord_Click:
MsgBox Err.Description
Resume Exit_printthisrecord_Click

End Sub



I get an error stating that varIssueID is an invalid column.



Joe

 

[kodyjori]

Never mind - I got it.

I had to change the main line to:

DoCmd.OpenReport stDocName, acPreview, , "[IssueID] = " & varIssueID & " "

Joe