Looping Code

[Leesha]

Hi,

I have a user that sends invoices each month. They get frequent requests to have the invoices resent for a specified time frame as their clients don't always save them. Currently they print them one by one to a pdf and then email the individual pdf's. They would like to be able to enter a date range and have the invoices individually saved to a file so they don't have to do them one by one.

My thought was to set up a temporary table that will have an append query save each individual invoice to the table. I'm assuming I would use some sort of looping code to have the invoices created for a date range that would be specified. I would then use a similar looping code to run / output each of the invoices from the table to a specified folder.

I'm stuck on what the looping code would look like so I can set it up to begin testing. Is it possible to get a sample of how it should be sent up (I would then revise to fit the DB info) or a link to a good article on developing the code. Of course if there is a better way to approach this I am open to ideas that have worked for others.

Thanks,
Leesha

[HansV]

Without knowing anything whatsoever about the setup it's hard to provide help...

[Leesha]

Sorry Hans, I thought I described but will try to give more detail the way I see it working in my head. I haven't created yet.

Step 1 = User enters date range for invoices into frmInvoiceHistory. Date range controls = txtStartDate and txtEndDate
Step 2 = User enters StoreID for the invoices into txtStoreID on frmInvoiceHistory
Step 3 = qryAppendInvoicebyDateRange is run for each invoice that is in the date range controls and for the StoreID that is entered in txtStoreID. Each time the append query is run, it will append to tblInvoiceHistory.
Step 4 = rptInvoice is created as a pdf and saved to folder "InvoiceHistory"
Step 5 = qryDeleteInvoiceByDateRange deletes Info in tblInvoiceHistory
Step6 = Loop to restart process until the end of the date range.

Does this help?
Leesha

[HansV]

What is the record source of rptInvoice?

[Leesha]

Well it's going to pull the data from tblInvoicehistory and there is a unique ID [InvoiceID] for each invoice. Is that what you are looking for?

[HansV]

I'm still confused. Do you want an individual PDF file for each invoice or a single PDF file for all invoices for the specified StoreID that fall within the specified date range?

[Leesha]

Hi Hans,
I was thinking and individual pdf file for each month but if it's not too much trouble would love to see code to have all the invoices in one pdf so the user has the option.
Thanks!

[HansV]

I'd create a query that selects records from the original invoices table (not tblInvoicehistory) with the following criteria:
For StoreID: [Forms]![frmInvoiceHistory]![txtStoreID]
For the invoice date field: Between [Forms]![frmInvoiceHistory]![txtStartDate] And [Forms]![frmInvoiceHistory]![txtEndDate]
Save this query, and set the Record Source of rptInvoice to the name of this query.
You can then use

DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, "path\filename"

to save the report as a PDF file.

[Gasman]

Here is how I did something similar.
This however prints individual pdfs for each ship, but all at once.

Code: Select all

Sub Print_All_Ships()
 Dim rs As DAO.Recordset
 Dim db As Database
 Dim stSQL As String, stDate As String, stDBpath As String, stFTPpath As String
 Dim stRptName As String, stParam As String, stLinkCriteria As String, stAlphabet As String, astAlpa(1, 26) As String
 Dim stStart As String, stEnd As String, iloop As Integer
 Dim iOKCancel As Integer
  
 ' GoTo rptalpha
  
 stRptName = "Main_by_Ship"
 Set db = CurrentDb
' Generate all the Ship reports
' GoTo rptleave:
stDBpath = CurrentProject.Path & "\"
stFTPpath = stDBpath & "Gazette\"

 stSQL = "SELECT Ship.Ship FROM Ship WHERE (((Ship.ID)<> 26 and (Ship.ID)<> 27 and (Ship.ID)<> 60))ORDER BY Ship.Ship"

 Set rs = db.OpenRecordset(stSQL)

 Do While Not rs.EOF
 ' Need to convert any spaces in ship name to _ for website
    stParam = LCase(Replace(rs!Ship, " ", "_"))
    stLinkCriteria = "[Ship] = '" & rs!Ship & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
    DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria, acHidden
 ' Pause for 5 seconds to save report
    'Pause (5)
    DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
    DoCmd.Close acReport, stRptName
    'DoCmd.DeleteObject acReport, stParam

    rs.MoveNext
    
'    iOKCancel = MsgBox("OK to proceed?", vbOKCancel)
'    If iOKCancel = vbCancel Then
'        Exit Sub
'    End If
    
 Loop
 rs.Close
 Set rs = Nothing
End Sub

[Leesha]

Thanks Hans and Gasman!
I will most likely try using a combination of these approaches since the invoices need to be calculated separately for each month in order for the credit/balances from previous months to be captured on the invoice. I'm thinking I'd use Gasman's looking code to append the calculations for each invoice to the temporary table and then Han's code to include all of the invoices in one pdf.
Can't wait to get to work!
Leesha

[Gasman]

TBH, I do not like multiple pdfs in one, as I then have to split them to attach to relevant transactions.

I'd much rather you sent multiple pdfs in one email?

[Leesha]

That would be the end user's call as to how they want to send them. I'll probably give them both options so they can meet their customer's needs. It's all about customer satisfaction lol!