Repeated use of a report

[Peter Bellamy]

This is related to an earlier post I made, but I have now moved on to the printing stage.

To recap
I have Price lists for 10 products for 6 customers whch are usually printed in sets, a couple of times a year, The reports are all the same differing only by the customer, their discount and the product. 60 reports with 60 underlying queries each 'set' actioned by a Macro.
It all works but seemed a lot of code and takes a lot of space.

I have now written some vba and SQL that builds a query with the variables and loops around the 10 products each time creating a table for a 'template' report to be based on.
This works fine until it gets to the Open Report stage, reusing the table and template report seems to be the issue
The first product price list previews but the code does not pause to allow printing and closing of that report.
If I put a breakpoint at the OpenReport line it can all be made to work.

How can I introduce a pause to allow the choice of the printer or to discard the current report, close it and allow the next to be built?

Cheers

For i = 1 To 9
Select Case i
Case 1
    QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern 30"
    Str2 = " [41 555 02]"
    Str3 = "Warmworld HE30 [41 555 04]"
    Str4 = "Servowarm Elite HE 30 [41 555 07]"
Case 2
QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern 40"
    Str2 = " [41 555 03]"
    Str3 = "Warmworld HE40 [41 555 05]"
    Str4 = "Servowarm Elite HE 40 [41 555 08]"
Case 3
QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern 50"
    Str2 = " [41 555 01]"
    Str3 = "Warmworld HE50 [41 555 06]"
    Str4 = "Servowarm Elite HE 50 [41 555 09]"
Case 4
QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern 70"
    Str2 = ""
    Str3 = "Warmworld HE70"
    Str4 = ""
Case 5
QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern 70/80"
    Str2 = " [41 555 10]"
    Str3 = "Warmworld HE Combi [41 555 011"
    Str4 = "Servowarm Elite HE Combi [41 555 12]"
Case 6
QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern Combi"
    Str2 = " [47 555 02]"
    Str3 = "Warmworld HE30 [47 555 03]"
    Str4 = "Servowarm Elite HE 30 [47 555 04]"
Case 7
QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern 1020"
    Str2 = " [41 555 17]"
    Str3 = "Warmworld FFC 35/60 [41 555 15]"
    Str4 = "Servowarm Elite 21 Plus [41 555 13]"
Case 8
QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern 2026"
    Str2 = " [41 555 18/19]"
    Str3 = "Warmworld FFC 65/80 [41 555 16/21]"
    Str4 = "Servowarm Elite 21 [41 555 14]"
Case 9
QrySQL = "DELETE * FROM [Price List Data];"
    DoCmd.RunSQL QrySQL
    Str1 = "Malvern Dbi Combi"
    Str2 = " [47 555 01/05]"
    Str3 = "Warmworld FFC Combi [47 555 08/09]"
    Str4 = "Servowarm Elite 21 Combi [47 555 06]"
End Select

QrySQL = "INSERT INTO [Price List Data] IN 'z:\Database\2000\Service2k.mdb' " & _
    "SELECT Parts.parts_no, Parts.parts_name, Parts.parts_cost, Parts.parts_packaging, Parts.parts_carriage, Parts.parts_price1, Parts.parts_price2, Parts.parts_price3, Parts.parts_price4, ' " & Str1 & Str2 & "' AS Expr1, '" & Str3 & "' AS Expr2, '" & Str4 & "' AS Expr3, ([parts_price2]+[parts_packaging]+[pandp_value1]) AS Servo1, Parts.parts_inc_date " & _
    "FROM Carriage RIGHT JOIN Parts ON Carriage.pandp_index = Parts.parts_carriage " & _
    "WHERE (((Parts.parts_cost) > 0) And ((Parts.parts_live) = Yes) And ((Parts.parts_usedon1) ='" & Str1 & "')) Or (((Parts.parts_live) = Yes) And ((Parts.parts_usedon2) ='" & Str1 & "')) Or (((Parts.parts_live) = Yes) And ((Parts.parts_usedon3) ='" & Str1 & "')) Or (((Parts.parts_live) = Yes) And ((Parts.parts_usedon4) ='" & Str1 & "')) " & _
    "ORDER BY Parts.parts_no; "

'Debug.Print QrySQL

DoCmd.RunSQL QrySQL

DoCmd.OpenReport "Parts Price List Office", acViewReport

Next

 

[PNGBill]

I guess you have been asked this before but why can't this be done by one report and just print each customer as required?
This is what a Database is supposed to be all about. Everyone the same except for variables ie Name, Product, Price etc.

VBA or Macro can automate the process of going though the customers one at a time or you can produce one report that appears to be 60 reports and you separate the pages after it is printed.
Each Customer's price list will appear to be a new report.

If you have 2007 or above, I understand you can include a unique file name in the report, if printing separate reports, and hence they should all output without causing an issue.

Can you add a MsgBox to the Code to act as a break? - will require a key press to continue.

 

[Peter Bellamy]

Thanks for your reply.
I have contstructed it by customer so yes, each customer report set is seperately requested/printed, it is the next level, the printing reports by product I need to interupt.

I have tried using a message box but that takes focus and does not allow opening menus or the print preview to be closed.
There is a Modal setting in MsgBox but I can't find how to set it as non-modal which might make this a solution

 

[PNGBill]

Peter,
Still think your Price Lists should be able to be processed on One Report and have Groups with Headers and Footers and Page Breaks accordingly.

 

[SOS]

Thanks for your reply.
I have contstructed it by customer so yes, each customer report set is seperately requested/printed,

So you have ONE report and then change it's recordsource based on customer information? This is what Bill is saying. The code you gave shows HARD CODED information which should not be like that.

str1, str2, str3 and str4 should not be hard-coded in the VBA.

 

[Peter Bellamy]

OK last post as I have been able to complete this part of the project.

Where as MsgBox is modal and does not allow any other window to take focus there is another message box available in user32.dll which isn't.
By adding:

Public Declare Function MessageBox Lib "user32" Alias "MessageBoxA" _

In the declartions in a module it creates a new fuction 'MessageBox' which is non modal and enables the report in another window to be printed.
MessageBoxA is described in Msofts help pages but it works just like MsgBox but with an added first variable to attach it, or not, to a window.

I used it this way

Int1 = MessageBox(&O0, "Print Price List then close print preview window before pressing this OK", "Print Help", vbOKOnly + vbSystemModal)

The result is perhaps not very elegant but I have been able to reduce down from 6 macros, 60 reports and 6 queries to 6 reports and a table.
It took nearly 1Mb off the code.

Along the way I also found an accurate timing function in a dll which is quite useful but I will post that separately.

pnb