I Need to Print all 675 Report Variations

[hulaman]

Hello All,

I have a cascading drop down box with 4 fields which works wonderfully to produce individual reports based on a querydef written in VBA. Everything works great, I can bring up all 675 variations of the product with respective costs to produce each one.

It would take a couple of days to select and print each one individually.

So what I need to do is have a method to print them all (each variation) without having to select each one using the cascading form.
Naturally I won't want 675 reports hitting the printer at once so I could break it up into size or color or style, etc.

Any suggestions? I understand that a macro or VBA is in order, but where do I start? Here is my vba that works so well already...

Private Sub cmdRunMirDoorsQry_Click()
On Error GoTo cmdOK_Click_err
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryMirDoors")

'Here is the select statement

strSQL = "SELECT tblMirDoors.* " & _
"FROM tblMirDoors " & _
"WHERE tblMirDoors.strMirDrSeriesID='" & Me.cboSeries.Value & "' " & _
"AND tblMirDoors.strMirDrSizID='" & Me.cboSizes.Value & "' " & _
"AND tblMirDoors.strMirDrColrID='" & Me.cboFrColr.Value & "' " & _
"AND tblMirDoors.strMirDrPanelID='" & Me.cboPanels.Value & "' " & _
"ORDER BY tblMirDoors.strMirDrSeriesID;"


DoCmd.Echo False
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryMirDoors") = acObjStateOpen Then
DoCmd.Close acQuery, "qryMirDoors"
End If
qdf.SQL = strSQL
DoCmd.OpenReport "rptMirDoors", acViewPreview

DoCmd.Echo True

'qdf.SQL = strSQL
'DoCmd.OpenQuery "qryMirDrSelect"
cmdOK_Click_exit:
DoCmd.Echo True
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
MsgBox "There is no such Mirror Door for the selection you made." & _
vbCrLf & "The software allowed an incorrect choice, sorry." & _
vbCrLf & "Please hit RESET and try again!" & _
vbCrLf & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub

Thanks for any help you can suggest...

 

[hulaman]

Thanks Pat,

I think an important aspect of this (or maybe not?) that I didn't mention is that the main report (that I need to print 675 variations of) has 8 subreports that it drawns on for the data. This is because of the one to many table relationships. Each product has multiple parts with respective part cost based on either an inventory price or a calculated price based on it's size.

In order for the price of a mirror to show, it takes data from the mirror table, the frame price from the frame table, screws from hardw, etc. They all do link to a joinID key I made. Othewise a simple report with grouping and separating would work, or can I in fact get your fix to work with multiple tables feeding multiple subreports on the single main report?

I really appreciate your effort on this.
Chris

PS - In the meantime I will work on the direction you gave...thx

 

[hulaman]

I found the answer and it was easy thanks to all for your suggestions. Here is the fix:

Firstly, what was holding me back was the criteria in the qry (from form) that the report was based on (the cascading drop down boxes). Once I removed the criteria where my selection equaled ([Forms]![frmMirDoors]![cboSeries],etc) and let the main report spit out all records with all the repective subreports, I got my 675 records with each showing up on it's own page. Now I simply need to break it up into manageble printing sizes.

Thanks to all and thanks to my Boss who knew more about the particulars!