multiple instances of one report????

[suenpete]

I need help figuring out how I can have one report preview multiple times. Right now I loop based on the number of entries in a query and then modify a query based on the value of the query. The report is then saved off, modifing the name of the report with the value from the query. I want to be able to do the loop but also preview each instance of the report. Thanks

pete

 

[randysmid]

Hi,
I am not sure if I understand the problem, but you can run the queries and reports from inside a form (usually the clicked event of a command button).

DoCmd.OpenQuery "qryRun1"
DoCmd.OpenQuery "qryRun2"
DoCmd.OpenQuery "qryRun3"

If this is not what you need, can you please clarify the problem?

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[suenpete]

randy,
Sorry. Here is what I would like to do. I call a sub that has this code;

Set rstRs = CurrentDb.OpenRecordset("qryNETs")
'QryNets results
' Net
' 1
' 2
' 3
' 4
' 5

While Not rstRs.EOF
strSql = "SELECT Tablename.Net, Tablename.GroupTime, Tablename.Bits "
strSql = strSql & "FROM Tablename "
strSql = strSql & "WHERE (((Tablename.Net)=" & rstRs!Net & "));"

'' qryNetLoadGraph is the query that the graph on the report, NetLaodGraph, uses.
CurrentDb.QueryDefs("qryNetLoadGraph").SQL = strSql

'''''' Here is the PROBLEM !
DoCmd.OpenReport "NetLoadGraph", acViewPreview
'''''' Report not closed

rstRs.MoveNext
Wend

I would like to have the report "NetLoadGraph" previewed multiple times without haveing to close the report between preview windows.
Is there a way to create a new instance of the report every time I want to display it in the loop?

I hope this makes sense
Thanks for replying
Pete

 

[ByteMyzer]

Here's your solution:

In the Report's Code Module, after the Option Explicit Statement, copy and paste the following text:

Public rKey As String

Private Sub Report_Close()
rCol.Remove rKey
End Sub



In a separate Module, after the Option Explicit Statement, paste the following code:
'''''Begin Code'''''
Public rCol As Collection, rTemp As Report
' rCol is the collection of open instances of the report
' rTemp is the temporary Report type used to add each instance to the Collection

Public Sub rpt_Multi()

Dim rstRs As Recordset
Dim strSql As String

Set rstRs = CurrentDb.OpenRecordset("qryNETs")
'QryNets results
' Net
' 1
' 2
' 3
' 4
' 5

While Not rstRs.EOF
strSql = "SELECT Tablename.Net, Tablename.GroupTime, Tablename.Bits "
strSql = strSql & "FROM Tablename "
strSql = strSql & "WHERE (((Tablename.Net)=" & rstRs!Net & "));"

'' qryNetLoadGraph is the query that the graph on the report, NetLaodGraph, uses.
CurrentDb.QueryDefs("qryNetLoadGraph").SQL = strSql

'''''' Here is the PROBLEM !
'DoCmd.OpenReport "NetLoadGraph", acViewPreview
'''''' Report not closed

'''''' PROBLEM SOLVED !
Set rTemp = New Report_NetLoadGraph
rTemp.hKey = CStr(rTemp.hWnd)
rTemp.Visible = True
rCol.Add rTemp, rTemp.hKey

rstRs.MoveNext
Wend

End Function
'''''End Code'''''


...and save the module as "rpt_Multi Module".

Try this and see if this is what you wanted.

 

[ByteMyzer]

Whoops, in the rpt_Multi() code, the following should read:

'''''' PROBLEM SOLVED !
Set rTemp = New Report_NetLoadGraph
rTemp.hKey = CStr(rTemp.hWnd)
rTemp.Visible = True
rCol.Add rTemp, rTemp.hKey
DoEvents ' forgot this statement, very important

rstRs.MoveNext
Wend

 

[ByteMyzer]

Once more, my apologies, the first part of the Module should read:

'''''Begin Code'''''
Public rCol As New Collection, rTemp As Report


(if it doesn't say NEW collection, the object isn't set. Sorry, been a long day)

 

[suenpete]

ByteMyzer,
Sorry for the long delay in replying. Thank you so very much. It works just fine.
Pete

 

[LLBP]

ByteMyzer,

I found this code to be very helpful for what I am trying to do. I want to print a report for every customer automatically. I have a form with a button that executes the following code. I get "Type mismatch" error when I click the button. Can you help?

Option Compare Database

Private Sub btnDLPrint_Click()
On Error GoTo Err_btnDLPrint_Click

Dim stDocName As String
Dim rst As Recordset
Dim strSql As String

' Open Recordset and print report.

' Open Recordset
Set rst = CurrentDb.OpenRecordset("qryDL-MassPrint")

With rst
.MoveFirst
While Not rst.EOF
strSql = "SELECT tblDLVLCustomerDetail.CustomerID "
strSql = strSql & "FROM tblDLVLCustomerDetail "
strSql = strSql & "WHERE (((tblDLVLCustomerDetail.CustomerID)"
strSql = strSql & " " & rst!CustomerID & "));"
'qryCustomerDLNetPricing is the query that the report is based on
CurrentDb.QueryDefs("qryCustomerDLNetPricing").SQL = strSql
stDocName = "rptCustomerNetPrices-DL"
' Print the report for current record
DoCmd.OpenReport stDocName, acViewNormal
.MoveNext
Wend

End With

Exit_btnDLPrint_Click:
Exit Sub

Err_btnDLPrint_Click:
MsgBox Err.Description
Resume Exit_btnDLPrint_Click

End Sub

Thanks, Brent

 

[ByteMyzer]

There appears to be a syntax error in the SQL String, for starters. You might try this revision:

strSql = "SELECT CustomerID "
strSql = strSql & "FROM tblDLVLCustomerDetail "
strSql = strSql & "WHERE CustomerID="
strSql = strSql & "'" & rst!CustomerID & "';"

The single-quotes are being used on the premise that CustomerID is a String-type field.

 

[LLBP]

Thanks for the quick response!

I made the changes you suggested but still get the type mismatch error. CustomerID is a text field (string).

Can you see something else?

Is this the correct un-broken string?
"SELECT CustomerID FROM tblDLVLCustomerDetail WHERE CustomerID= ' " & rst!CustomerID & "';

 

[ByteMyzer]

The correct un-broken string should be:

"SELECT CustomerID FROM tblDLVLCustomerDetail WHERE CustomerID= '" & rst!CustomerID & "';"

(take out the space before the " & rst!...)

Without knowing what "qryDL-MassPrint" consists of, I couldn't tell you what the problem might be, only that that is one more place to look for the source of the problem. The code itself (with the revised SQL String) is correct.

 

[LLBP]

It definitely has something to do with the syntax of the WHERE statement. I tried every combination I know, which isn't much. I commented out the SQL assignment statement and ran it with the existing querydef and it opened the report.

I made quite a few changes. I had the queries backward, but I still get the type mismatch error.

Here is the SQL statement from qryDL-MassPrint

SELECT qryCustomers.CustomerID, qryDLNetPricing.Model, qryDLNetPricing.Size, qryDLNetPricing.DLNetPrice, qryCustomers.CompanyName, qryCustomers.MailAddress, qryCustomers.MailCity, qryCustomers.MailState, qryCustomers.MailZip, qryCustomers.ShipAddress, qryCustomers.ShipCity, qryCustomers.ShipState, qryCustomers.ShipZip, qryCustomers.Phone, qryCustomers.ProdGroupChoice, qryCustomers.ProdGroupChoice2, qryCustomers.ProdGroupChoice3, qryCustomers.ProdGroupChoice4, qryCustomers.Terms, qryCustomers.Freight, qryCustomers.Allowances, qryCustomers.Notes, qryCustomers.EffectiveDate, qryDLNetPricing.ID
FROM qryDLNetPricing INNER JOIN qryCustomers ON qryDLNetPricing.CustomerID = qryCustomers.CustomerID
WHERE (((qryCustomers.CustomerID)="05073"));

Here is the modified code:
Private Sub btnDLPrint_Click()
On Error GoTo Err_btnDLPrint_Click

Dim stDocName As String
Dim rst As Recordset
Dim strSql As String

' Open Recordset and print report.

' Open Recordset
Set rst = CurrentDb.OpenRecordset("qryCustomerDetail")

With rst
.MoveFirst
While Not rst.EOF
strSql = "SELECT qryCustomers.CustomerID, qryDLNetPricing.Model, "
strSql = strSql & "qryDLNetPricing.Size, qryDLNetPricing.DLNetPrice, "
strSql = strSql & "qryCustomers.CompanyName, qryCustomers.MailAddress, "
strSql = strSql & "qryCustomers.MailCity, qryCustomers.MailState, "
strSql = strSql & "qryCustomers.MailZip, qryCustomers.ShipAddress, "
strSql = strSql & "qryCustomers.ShipCity, qryCustomers.ShipState, "
strSql = strSql & "qryCustomers.ShipZip, qryCustomers.Phone, "
strSql = strSql & "qryCustomers.ProdGroupChoice, "
strSql = strSql & "qryCustomers.ProdGroupChoice2, "
strSql = strSql & "qryCustomers.ProdGroupChoice3, "
strSql = strSql & "qryCustomers.ProdGroupChoice4, "
strSql = strSql & "qryCustomers.Terms, qryCustomers.Freight, "
strSql = strSql & "qryCustomers.Allowances, qryCustomers.Notes, "
strSql = strSql & "qryCustomers.EffectiveDate, qryDLNetPricing.ID "
strSql = strSql & "FROM qryDLNetPricing "
strSql = strSql & "INNER JOIN qryCustomers "
strSql = strSql & "ON qryDLNetPricing.CustomerID = qryCustomers.CustomerID "
strSql = strSql & "WHERE (((qryCustomers.CustomerID)='" & rst!CustomerID & "');"
'qryCustomerDLNetPricing is the query that the report is based on
CurrentDb.QueryDefs("qryDL-MassPrint").SQL = strSql
stDocName = "rptCustomerNetPrices-DL-MassPrint"
' Print the report for current record
DoCmd.OpenReport stDocName, acViewPreview, "qryDL-MassPrint"
.MoveNext
Wend

End With
rst.Close
Set rst = Nothing

Exit_btnDLPrint_Click:
Exit Sub

Err_btnDLPrint_Click:
MsgBox Err.Description
Resume Exit_btnDLPrint_Click

 

[LLBP]

By the way, this will save hours of labor if it will work. There are nearly 400 customers and two reports to run each every time we have a price change. Automation would be awesome. Your help is much appreciated!