Different formats within the same report

[FogLog]

I am printing statements for clients. Depending on certain criteria about the client, the format of the statement changes completely. This is just how the boss wants it, I have no control. I still want to generate all statements at one time (not separate reports).

I currently have this working using multiple subreports, and changing their Visibility property, only one being visible at any one time. This works, but it is very slow, I assume because I am basically making Access do triple the work (I have 3 subreports).

I pretty confident this is not the intended use of subreports. Is there a "correct" way to do what I am doing? I know I could just manipulate everything with VBA, but having subreports makes editing the different formats very easy.

Thanks for any assistance!

 

[Isskint]

Hi FogLog

If you think about this, you are just using seperate reports and seperate report (templates) is the way i would go. Have a field in the Client table to ID the template, a table of TemplateID & name (eg Temp1, Temp2 etc). Then at print time open up the relevant template. If at print time you print All clients, it is just a piece of loop code OR if you only print selected clients, it is loop code around a selection form (which i would guess you already have?)

 

[FogLog]

I don't want separate reports, though. I want to end up with one alphabetized stack of statements (with varying formats), not 3 separate alphabetized stacks.

When you say to open up the relevant template at print time, that seems simple enough, but then how do I open up a different template for the second statement, assuming it requires a different format?

 

[Isskint]

You have a print statements routine, that loops through your client table in alphabetical order. For each client, you print the relevant report.

I assume currently you do something similar, except you determine which subreport to make visible.

Sub PrintStatements()

Dim rsClients As DAO.Recordset
Dim strReport As String

Set rsClients = CurrentDb.OpenRecordset("SELECT * FROM tblClients ORDERBY ClientName")

If Not (rsClients.EOF And rsClients.BOF) Then
    rsClients.MoveFirst
    Do Until rsClients.EOF = True
        strReport = DLookup("[TemplateName]", "[tblTemplates]", "[TempID]=" & rsClients.ReportID)
        DoCmd.OpenReport strReport
        
        rsClients.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

MsgBox "Finished printing statements."

rsClients.Close
Set rsClients = Nothing
End Sub

 

[FogLog]

Thanks for your help on this!

I am still a bit confused, though. I assume within each of my separate report templates, I would need to set the record source to be a single record that matches the progress of the loop? I think I could manage to do this. Or would I be able to use the rsClients recordset somehow as the record source?

Then, it looks like I would just end up with 1000 one-page reports being opened? Would I also need to do the actual printing from VBA as well? I think I am most likely misunderstanding something about this part.


Currently, I am setting subreport visibility in the detail section's OnFormat.

 

[Isskint]

I think maybe the confusion is from me; typing before really thinking, missing something out!

Set up your 3 reports, using the relevant table/query, in the formats required.
Then use the view and WHERE parameters of the OpenReport method to set the Client ID and print the statement (not just open it). So the DoCmd.OpenReport section of my previous code would look something like...

DoCmd.OpenReport strReport, acViewNormal, , "([ClientID]=" & rsClients.ClientID & ")"

acViewNormal = default mode - Print
([ClientID]=" & rsClients.ClientID & ") tells the report to only display data where the client id = rsClients.ClientID

What this code will do then, is;

• Loop through each record in the Client table
• Identify the template used for the client
• Open the relevant report, use WHERE condition to only print current Client and print it (the report will not be left open)

 

[FogLog]

Where does the actual printing happen? Won't that just open the individual statements in Print Preview? Assuming that's what I have set as the "Default View".

Edit: Nevermind, I see that acViewNormal should send the report to the printer. Is there a way to retain an initial printer dialog box?

 

[FogLog]

I wonder if maybe I am concentrating on the wrong thing here. Since I already have something that works, my main concern is the time it takes to generate all the statements. Currently it seems to take a little over 1 second per statement.

Trying to think of where the slowdown actually is, the record source of my main report is "qry_Statements". The record source of each of my subreports is also "qry_Statements". I actually have 5 subreports (3 in the detail section, 2 in the footer), does this mean that my query is running once when I open the main report, and an additional 5 times per individual statement?

If so, is there a different way I could set it up so that the query only runs once for the entire run of statements? Do the query in VBA and then reference that resulting dataset somehow?

 

[Isskint]

Is there a way to retain an initial printer dialog box?

Do you mean from the point of view of selecting a printer or knowing something is happening? With the former you could have some code run first asking the user to select printer (or set a specific printer if the same printer is always used), with the later I would suggest displaying a modal form with a progress display (Printing x of y... or a bar Printing 17% complete etc)

As for the speed, others can probably answer more concisely, but I would imagine Access IS trying to reformat all sections causing a slower speed than just printing without editing.

 

[FogLog]

I meant for selecting the printer, as well as other options like color, quality, feed tray, etc.

Probably can do without, though.

I did manage to use OpenRecordSet once, and then just feed that recordset into my main report and 5 subreports, but it didn't seem to speed anything up at all .

 

[Isskint]

you can use the Printer object to set most print parameters. Have a look at this link.
https://msdn.microsoft.com/en-us/library/office/aa223133(v=office.11).aspx