Printing multiple reports

[Toby3]

Heya all, I'm needing help with trying to print multiple invoices in one hit of maybe a macro button?

At present there is a button on a form where each customers monthly invoice can be printed (btnPrintReports). The fact that invoice has been printed (with details) also goes to a "Sent Invoice" report that gets cleared at the end of every month.

Im wanting that individual print button left there for individ cases, but also want the ability to have a button on Main switchboard that will print out the whole lot in one go, either by exporting to another format and printing or just printing straight from the button. It must also continue as above to update the "Sent Invoice" report.

How would I go about doing that?

 

[HiTechCoach]

Heya all, I'm needing help with trying to print multiple invoices in one hit of maybe a macro button?

At present there is a button on a form where each customers monthly invoice can be printed (btnPrintReports). The fact that invoice has been printed (with details) also goes to a "Sent Invoice" report that gets cleared at the end of every month.

Im wanting that individual print button left there for individ cases, but also want the ability to have a button on Main switchboard that will print out the whole lot in one go, either by exporting to another format and printing or just printing straight from the button. It must also continue as above to update the "Sent Invoice" report.

How would I go about doing that?

How to do that with your database design is impossible to say since we know nothing about it.

I will give you some general guidance in how I do things.

I normally use the same report for both.

I would change the "filter" to include the desired records depending on what you want to print.

 

[Toby3]

Thanks for your reply but it went right over my head, hehe

Is there any possible way of sending it to you, via rapidshare or suchlike? Its only 30MB in size(backend and frontend collectively) I know this is a HUUUGE ask but Im in a real bind Needing to print off 200 invoices today.

 

[SOS]

Have you tried running COMPACT AND REPAIR on it and then zipping the files (individually)? They may get below the threshold if you do that.

 

[Toby3]

Backend - http://rapidshare.com/files/357296250/Tonys_work_be.MDB.html
Frontend- http://rapidshare.com/files/357297481/Tonys_work.MDB.html

I would honestly be very very humbled and appreciative if someone could have a quick look?????

The frmCustomer has the individual print invoice button
I'm wanting a macro on Main switchboard form to essentially run the btnPrintReports buttons for all customers.
The button to print individual report is reads in VB.....


Private Sub btnPrintReports_Click()
On Error GoTo Err_btnPrintReports_Click
Dim stDocName As String

stDocName = "rptInvoice"
DoCmd.OpenReport stDocName, acNormal, , "[CustomerID] =" & Me.CustomerID

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateSendInvoice"
DoCmd.SetWarnings True

Exit_btnPrintReports_Click:
Exit Sub

Err_btnPrintReports_Click:
MsgBox Err.Description
Resume Exit_btnPrintReports_Click

End Sub

 

[SOS]

I'm taking a look, but just so you know -

You could have helped us out by running Compact and Repair and then ZIPPING the files. By doing so your 24 MB frontend would only take 10MB of space and your backend would only take 989Kb of space. That would have made uploading to your file sharing site faster for you and faster for us downloading.

 

[Toby3]

Thankyou sooooooo much

I thought I had compacted it to death, didnt think it could compact anymore, hehe.
I could have however zipped it tho sorry about that
Looking forward to your reply anxiously

 

[SOS]

Okay, I put a button on your main switchboard ( and month/year selectors). I'll leave it up to you to make it pretty and show up when you want it.

Also, in the code for the button I modified the DoCmd.OpenReport code to PRINT PREVIEW as I didn't want to send to my own printer. You can change it back to acNormal from acViewPreview.

I hope it does what you want.

The database files

 

[Toby3]

Oh my gosh, cant wait to see it but just wanted to say thankyou sooooo much!!!

I wasn't actually expecting you to do it, but then again....with the time it would have taken to walk me through it, it was possibly the faster option, lol.

I shall go look now xxxx

 

[SOS]

Well, it isn't the simplest of code (not real hard but would be a bear to try to explain well enough to be able to get you there without a LOT of back and forth). So, hopefully you'll see what I did and be able to modify to fit your needs if it doesn't quite fit what you need.

 

[Toby3]

Soooooo sorry, but only one hiccup thats my fault.

We went to print them out and it started churning out ALL customers an Invoice, I forgot to say that its only the customers that have Inv ticked in the Customer Information area, the control source = Inv?

 

[Toby3]

OK, added an extra bit to the WHERE line to include the only print ticked Inv customer records.

Now 2 hiccups, first one I can fix which is length of designed invoice, some are going over two pages, no prob fixing that.

2nd and BIG problem is...e.g

John gets lawn mowed 2nd Feb
John pays 10th Feb
John gets lawn mowed again 20th Feb
John pays 26th Feb

John is getting 4 invoices printed hehe oops. It appears to be taking each date a transaction was done. We are only wanting one invoice per customers. Any idea how I could change the script to remedy that?

**********************************

Private Sub cmdPrintAllInvoices_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim rstCust As DAO.Recordset
Dim yes As String
yes = "yes"

strSQL = "SELECT tblCustomers.CustomerID, tblCustomers.Title, tblCustomers.LastName, tblCustomers.FirstName, tblCustomers.CompanyName, tblCustomers.[Mailing Address 1], tblCustomers.[Mailing Add2], tblCustomers.[Mailing Add3], tblCustomers.Postcode, tblCustomers.Email, tblCustomers.[Street No], tblCustomers.StreetName, tblAreas.Area, tblInvoices.Quantity, tblJobDescriptions.JobDescription, tblInvoices.UnitPrice, tblInvoices.Payment, tblInvoices.Date, tblInvoices.Total, Query1.SumOfTotal " & _
"FROM (tblAreas INNER JOIN (tblCustomers INNER JOIN Query1 ON tblCustomers.CustomerID = Query1.CustomerID) ON tblAreas.AreasID = tblCustomers.[Area ID]) INNER JOIN (tblJobDescriptions INNER JOIN tblInvoices ON tblJobDescriptions.JobID = tblInvoices.JobDescription) ON tblCustomers.CustomerID = tblInvoices.CustomerID " & _
"WHERE(((Month([Date])) = " & Me.[Combo128] & ") And ((Year([Date])) = " & Me.[Currentyear] & ") And ((tblCustomers.[Inv?]) = " & yes & ")) " & _
"ORDER BY tblInvoices.Date;"



Set db = CurrentDb
Set rstCust = db.OpenRecordset(strSQL)
rstCust.MoveLast
rstCust.MoveFirst
Debug.Print "rstCust - " & rstCust.RecordCount

Do Until rstCust.EOF
DoCmd.OpenReport "rptInvoice", acNormal, , "[CustomerID]=" & rstCust.Fields("CustomerID") & " AND Month([Date]) = " & Me.Combo128 & " AND Year([Date])=" & Me.Currentyear


strSQL = "UPDATE tblInvoices SET tblInvoices.SendInvoices = -1, tblInvoices.DateInvPrinted = Date() " & _
"WHERE tblInvoices.CustomerID = " & rstCust.Fields("CustomerID") & " AND Month([Date])=" & Me.Combo128 & " AND Year([Date])=" & Me.Currentyear & ";"

db.Execute strSQL, dbFailOnError

*****************

 

[SOS]

Unfortunately I don't have a lot of time today to look at it and I know it took me a bit last night to do. I probably can look at it when I get home but that won't be for at least 9 hours.

 

[Toby3]

OK, many thanks!!

Here's rarred up database as it is now (14MB)

http://rapidshare.com/files/357982820/Tonys_Database.rar.html

Update:

Added this to VB code:
strSQL = "SELECT DISTINCT tblCustomers.CustomerID,"

On Sent Invoices page it has 170 invoices sent which is perfect!!!!
On printing tho with printer paused, it says 471 documents to be printed.
Something still awry but closer!!

We have managed to print off 117 invoices using the bulk button but as it starting printing an invoice for customer for EACH transaction we had to stop. Still 53 to do individually (since we had to cancel the printout) so will do them individually to solve this month.

Its printing an invoice as below 3 times since it has 3 transactions....not consecutively, appears to be in date order of transaction, e.g all first transactions with 1st Feb first, then it starts on 2nd Feb.
So if John gets lawn mowed 1st Feb, then next transaction is not till 28th Feb the printing distance would be e.g 400 pages apart kinda thing.