Printing multiple reports at once based on condition

[justinwright]

I admittedly didn't search before posting this one, since I wasn't sure what to search for, so forgive me if it's been asked before.



I have a linked Excel file with a bunch of names on it, those names have outstanding balances on them (some don't, obviously).

I have a set template (that I'll remake in Access as a report) to print an envelope that will print the amount of the balance plus a late fee (calculated by 10% of another field in said linked Excel file) on the back inside, while the front is another report entirely (generic for all). I was wondering if there was a way to iterate through the entire list, and print the items that have an outstanding balance of over, say $100 (would show as 100.00 on the Excel sheet).

I already know how to hide calculations, in the footer or wherever, in order to get the amount of that 10% late fee and other things. I'm just clueless on how to print multiple reports (without clicking or selecting again and again) based on a certain criteria. I assume a For/While loop, but I'm absolutely crap at those.

We'll say, for the sake of example, that the name is the first column (F1) and the amount is, in, say the 5th column (F5), while the base amount to charge the late fee to is in the 4th column (F4).

Would a For/While loop work for report printing? Or am I way off?

As always, thanks for helping someone as inept as me! Hopefully I'm improving, though

[pbaldy]

Create a query that returns the desired records, and then loop it:

http://www.granite.ab.ca/access/email/recordsetloop.htm

That loop emails, but you can simply print the report instead, using a criteria or this to restrict to the current record:

BaldyWeb wherecondition

[justinwright]

Sorry to kinda necro this, but I haven't really had time to mess with it until now (other projects sidetracking, etc., I'm sure we all know how that goes).

Thank you for pointing me in the right direction, but I did have a question or two. I took out the unnecessary parts of the code (at least I think so), but I'm not sure how to proceed exactly.

The code is as follows atm:

Option Compare Database
 
Private Sub cmdVALetter_Click()
 
On Local Error GoTo Some_Err
 
Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
 
DoCmd.RunCommand.acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)
 
Set RS = MyDB.OpenRecordset _ 
     ("VA_List")
     lngRSCount = RS.RecordCount
     If lngRSCount = 0 Then
          MsgBox "No items to print.", vbInformation
     Else
          RS.MoveLast
          RS.MoveFirst
          Do Until RS.EOF
               lngCount = lngCount + 1
               ' This is where the print report goes in at 
 
 
               ' This is the end of where the print report operation is
          RS.MoveNext
    Loop
    End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close
 
Exit Sub
 
' Error handler:
Some_Err:
     MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
          vbExclamation, "Error!"
 
End Sub

I assume the code works by going to one item, doing stuff, and then going to the next item. If this is the case, how would I open a Query with the report? The report I am using is a generic letter, with the important information attached to the person at the top (different fields for name, address, city, state, zip).

I assume I'd add something along these lines:

DoCmd.OpenReport "LetterName",  acViewNormal

Would I put the query name where it says

Set RS = MyDB.OpenRecordset _ 
     ("VA_List")

I'm hoping that's it, otherwise I might have thousands of papers flying out of the printer here with a very guilty look on my face!

As always, thanks for all the help

EDIT:

In case it might help, the code from a button on an unbound form.

I also meant to ask, how would I do any operations on the records to the report (for the sake of presentation, and concatenating city, state, and zip on the letter)?
The only way I've done it (kind of rigged I know), is making the person opening the reprot and using the "OnLoad" event with a "Print" button in the report footer that was only visible "On Screen."

[pbaldy]

In the line

Set RS = MyDB.OpenRecordset ("VA_List")

"VA_List" would be the name of the query that returns the customers you want to print reports on.

Your report would be filtered in some way. Three common options are the method in my link, which would look like:

DoCmd.OpenReport "LetterName", acViewNormal, , "FieldName = " & RS!FieldName

which would use the value from the recordset to restrict the report. You can also have the report's source query point to a form control, in which case you would include something like this within the loop:

Forms!FormName.ControlName = RS!FieldName

which would put the current value from the loop into that form control. You can also let the report filter itself like this:

http://www.granite.ab.ca/access/emai...recipients.htm

[justinwright]

Okay, all of that makes perfect sense, and it clears up a lot of questions I had.

My only question left, then, would be the presentation of said letter/report.

The fields City, State, and Zip need to be concatenated for the purposes of the letter. The way I usually did this was using acViewReport, then adding a print button in the footer of the report (that way I could use the OnLoad event to fill in the Label.Caption properties for concatenating the fields. Since it won't be filtered to just one result, and the report won't be opened (merely printed), I was wondering how I might go about doing that.

The link you posted was concerning the OnOpen event, would this work for just the acViewNormal?

[pbaldy]

The open event occurs for printing just like it does for preview, so code in that event should still do what you want. I use the filter technique in that last link for outputting to PDF and it works for printing too. Not clear on what you're doing in code. You can do simple concatenation in a textbox like:

=[City] & ", " & [State] & " " & [Zip]

The textbox should get you the same look as a label. If you're doing more sophisticated stuff in code, I'd probably use the format event of the section containing the control you're manipulating, which will also fire whether printing or previewing. The advantage would be that if you had multiple records, it would fire for each, not just the first as would occur in the open event. For instance, I have a report for customer statements with code that concatenates several address fields into one textbox, to get rid of potentially empty space caused by unused fields. I have it in the group header format event, so it fires for every customer, whether I print 1 or 100.

[justinwright]

The query I have narrows down all the records I need to restrict on it.

I set the source of the report to the query, and it seems to pull the first record just fine on the print preview.

I added that line

DoCmd.OpenReport "LetterName", acViewNormal

Where I left the blank at.
It wouldn't print, so I changed the query I knew would return only one record to "acView Preview" in the code, and it gave me the error:

Run-time error '2427':
You entered an expression that has no value:


And each time it does that, when I debug, it highlights where I set the caption for the labels to be the value of the current record as Name, Address, City, State, Zip, etc as I comment them out to debugg line by line.

From what I've read, it's saying there's no value there. I'm absolutely CERTAIN there is for each field called. I also tried this, which was suggested elsewhere, to no avail:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data"
On Error Resume Next
Cancel = True
End Sub

A thought occurred to me as well. The query is based off of a linked Excel spreadsheet (with the top row marked as column headers). Would this matter?

It's also worth noting that when I *first* tried it, the debug pointed to this line:

DoCmd.RunCommand.acCmdSaveRecord

EDIT2:

I tried importing and changing things around (instead of using an Excel linked table) but the same error occurred, so I suppose there is no difference.

EDIT3:

From what I've been able to figure out, it's erroring since the function is executing without an argument into it BEFORE the OnOpen event occurs (which defines the captions for the labels on the report). At least, what I read here:

http://forums.devx.com/showthread.php?t=79034

Led me to believe that.

[pbaldy]

Is your code in the open event? I'm pretty sure data is not yet available in the open event, and you'd get that error. It is in the load event. That said, that's another reason I use the format event of the applicable section.

[justinwright]

From when I was playing with it, I have the code in the OnLoad and the OnOpen event. Unfortunately I'm not sure I follow on a "Format" event.. I don't see one anywhere on the event tab for the Report.

The code almost at the top (the code that is based off the email code you pointed me to originally) is for a button on an unbound form if that helps.

Also you mentioned that a text box looks the same as a label on a report. Should I try using that instead of using the .Caption property to try to set a label?

[pbaldy]

It's almost certainly going to error in the open event. You can get to a section's events by right-clicking on the horizontal bar containing the section (report header, group header, detail, etc) or double clicking on an empty part of the section.

[justinwright]

Okay, that seems to have worked, but one more problem arose.

I changed the vew back to acViewNormal, and hit the button once, and it started printing the first record over and over again (let it go on for about 15 pages until I hit cancel).

I don't know much about vba programming in the scheme of things (as evidenced by my constant questions on the forum), but in any other language there's a terminating condition for the loop. Other than the EOF (end of file marker or something?), I don't see any terminating condition. Even then, it doesn't really explicitly jump out at me as a terminating condition.

[pbaldy]

Well,

Do Until RS.EOF

is telling it to loop until it runs out of records,

RS.MoveNext

increments the records. If you have some condition that if met you want to exit the loop, you can use:

Exit Do

Failing that, it will print the report for every record in the recordset. Let's clarify the goal. You have a report that contains many customers, and you want to print selected customers one by one? How are you filtering the report? It sounds like you modified it to print a specific customer, which would explain why it keeps printing the same one. You need to use one of the methods to filter it to the current customer in the recordset.

[justinwright]

I switched it back to the preview view. It turns out that it's printing the letter for the correct number of records, but the top (the part that I defined in the Detail section's Format event) it is staying the same and not changing per record (hence why I thought it was just printing the same one over and over again). So when I printed the report based on the Query with just one person on it, it only printed one paper with that person's name on it. Printing the Query that had 15 names on it printed the 15 letters, but had the information for the *first* person on the letter each time.

There is one field that is either a "Yes" or "No." I have two queries; that is what I'm filtering by, since the letter changed based upon that alone.

I DO want it to print for every record in the recordset, so that's a good thing. The problem seems to be that it's printing for the *number* of records, but it isn't changing the caption information for each (which is what I put in that Format event that you mentioned).

[pbaldy]

Can you post a sample of the db, or at least your code from the format event?

[justinwright]

Format Event code:

Me.lblDate.Caption = Format(Date, "mmmm dd, yyyy")
Me.lblName.Caption = Me.Name
Me.lblCompanyAndCompanyNum = Me.CompanyName & "  (Company # " & Me.CompanyNum & ")"
Me.lblAddress.Caption = Me.Address
Me.lblCityStateZip.Caption = Me.City & ", " & Me.State & " " & Me.Zip

This is in the Detail section of the report, not the header or anything (the header is invisible anyways, though I'm not certain that matters for the purpose of code).