Create multiple reports 1

[TrekBiker]

I currently have this selector form for customers with unpaid invoices.

This allows a report to be generated based on the selected CustomerID, then emailed to the customer using this, which works a treat.

Private Sub cmdStore_Click()
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem  
    FileName = "Unpaid Items"
    FilePath = "C:\Users\David\Documents\" & FileName & ".pdf"   
   'Create temporary file
    DoCmd.OutputTo acOutputReport, "rptDebtorsStatement-copy", acFormatPDF, FilePath

    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If

    Set oEmailItem = oOutlook.CreateItem(olMailItem)    
    With oEmailItem
        .To = Me.sfmDebtorList.Form.[E-Mail]
        .CC = ""
        .Subject = "Debtor Items"
        .Attachments.Add FilePath
        .Display
    End With

    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    Kill FilePath

End Sub

I now want to be able to be able to send reports to any customers with ticks in 'Requested Email', all in one operation.

 

[Andrzejek]

Show the code where you actually Call Test() sub, something like:

...
Call Test(1234)
...

or

...
Call Test(rst!CustomerID)
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Did as you suggested and set the date range before getting to the Call. This part correctly extracts the CustomerID for each report to be emailed. I'm then trying to pass each in turn to the report source.

strSQL = "Select DISTINCT CustomerID From qryDebtorsStatementForEmail2 WHERE (ShipDate BETWEEN #" & [Forms]![frmSales]![Start] & "# AND #" & [Forms]![frmSales]![End] & "#)"

    Set rst = dbs.OpenRecordset(strSQL)   
    If rst.EOF Then Exit Sub
    For x = 1 To rst.RecordCount
        Call Test(rst!CustomerID)
        rst.MoveNext
    Next x

 

[Andrzejek]

You do not need to declare another CustID, you can just use what you pass: lngCustID

Private Sub Test(ByRef [blue]lngCustID[/blue] As Long)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String[green]
[s]'Dim CustID As Long[/s][/green]
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem

On Error GoTo ErrorHandler

    Set dbs = CurrentDb[green]
    'CustID = lngCustID[/green]
    
    strSQL = "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID [blue]= " & lngCustID[/blue]
    Set rst = dbs.OpenRecordset(stSQL)
    Debug.Print rst!CustomerID
    ...

Personally, I don't like 'juggling' variables (if I don't have to)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

So, you should be able to see your CustomerID:

    Set rst = dbs.OpenRecordset(strSQL)   
    If rst.EOF Then Exit Sub
    For x = 1 To rst.RecordCount[red]
        Debug.Print rst!CustomerID[/red]
        Call Test(rst!CustomerID)
        rst.MoveNext
    Next x

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

>With this code, Debug.Print shows nothing

One possible issue - your rst is Empty
Your [tt]qryDebtorsStatementForEmail2[/tt] may not have the CustomerID you are asking for

    ...
    strSQL = "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID = " & lngCustID
    Set rst = dbs.OpenRecordset(stSQL)[red]
    If rst.BOF = rst.EOF Then MsgBox "Huston, we have a problem"[/red]
    Debug.Print rst!CustomerID
    ...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Sorry this is dragging on, and I've been bogged down with another part of this database since the last post.

This fails on the last line below, showing that the Select statement isn't recognising the lngCustID parameter being sent to this procedure.

Private Sub Test(ByRef lngCustID As Long)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem

On Error GoTo ErrorHandler

    Set dbs = CurrentDb
    strSQL = "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID= lngCustID"
    Set rst = dbs.OpenRecordset(strSQL)

So the question remains on how to pass a parameter to a report's Record Source.

 

[Andrzejek]

>the Select statement isn't recognising the lngCustID parameter
How do you know that?

Did you [red]try[/red]:

Private Sub Test(ByRef lngCustID As Long)
[red]
MsgBox lngCustID[/red]

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem

On Error GoTo ErrorHandler

    Set dbs = CurrentDb
    strSQL = "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID [highlight #FCE94F]= " & lngCustID[/highlight][highlight #FCE94F][/highlight]
[red]    MsgBox strSQL[/red]
    Set rst = dbs.OpenRecordset(strSQL)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Hi Andy, and thanks for persevering with this.

Big progress thanks, now getting the procedure to create the first report and send it to preview in Outlook.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
On Error GoTo ErrorHandler
    Set dbs = CurrentDb
    strSQL = "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID= " & lngCustID
    MsgBox strSQL    
    Set rst = dbs.OpenRecordset(strSQL)
    
    FileName = "Unpaid Items"
    FilePath = "C:\Users\David\Documents\" & FileName & ".pdf"
'   Create temporary file
    DoCmd.OutputTo acOutputReport, "rptDebtorsStatementForEmail", acFormatPDF, FilePath

    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If

    Set oEmailItem = oOutlook.CreateItem(olMailItem)
    With oEmailItem
        .To = rst![E-Mail]
        .CC = ""
        .Subject = "Debtor Items"
        .Attachments.Add FilePath
        .Display
    End With

    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    Kill FilePath
    
ErrorHandler:
    Resume Next
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
   
End Sub

This correctly gives strSQL for the first report

There's still something small wrong because after the line KillPath it jumps to Resume Next in the error handler and gives this

I'll try to resolve this, but huge thanks for your time so far.

 

[Andrzejek]

...
    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    Kill FilePath

[highlight #FCE94F]Exit Sub[/highlight]       [green]'<-- you need this here[/green]

ErrorHandler:
    [red]Resume Next[/red]   [green]'<-- Dangerous approach[/green]
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
   
End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks. Progress in that it now generates separate email previews addressed to the current set of 4 customers requesting email reports. But the report contains pages for all 4, so the attachments are always the same, not individual.

The report is rptDebtorsStatementForEmail, based on qryDebtorsStatementForEmail2 but this means it's not getting the Where condition. It needs to use what's in

strSQL, ie "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID= " & lngCustID

I haven't found out how to do this.

 

[Andrzejek]

Somewhere at the beginning you had a code:

...
DoCmd.OutputTo acOutputReport, "rptDebtorsStatement-copy", acFormatPDF, FilePath
...

but this report created one report for all Customers where the 'Requested Email' field is 'Yes'

So you can either:
[ul]
[li]create a rpt report that will accept CustomerID and create a report just for that one Customer, or[/li]
[li]you can cheat and, once you have a list of CustomerID's that you need to process, mark all 'Requested Email' fields to 'No', mark first Customer with 'Requested Email' field 'Yes', process this record, mark it 'No', go to the next Customer from your list and repeat[/li]
[/ul]

I would suggest to figure out first approach.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Yes exactly. that's where I am and trying to pass the lngCustID parameter to the report source.

 

[Andrzejek]

Maybe this will help - Access VBA change Report Record Source

At the bottom you have some code: [blue]Example – Change Report Record Source in VBA[/blue]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Just a wild guess here...

...
    FileName = "Unpaid Items"
    FilePath = "C:\Users\David\Documents\" & FileName & ".pdf"
[green]'   Create temporary file[/green][blue]
    DoCmd.OpenReport "rptDebtorsStatementForEmail", acViewPreview, "qryDebtorsStatementForEmail2"[highlight #FCE94F], "CustomerID = " & lngCustID[/highlight]
    DoCmd.OutputTo acOutputReport, "rptDebtorsStatementForEmail", acFormatPDF, FilePath
    DoCmd.Close acReport, "rptDebtorsStatementForEmail", acSaveNo[/blue]
    [green]'                                           or is it acSaveYes    ?[/green]

    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If
    ...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks again, Andy, this is so close.

DoCmd.OpenReport opens and displays the first report correctly, and the email attachment contains it.

But next time round the attachment hasn't changed because the criterion "CustomerID = " & lngCustID isn't being applied to change customers in rptDebtorsStatementForEmail. I've tried adding on OnOpen procedure to define the RecordSource but something like this clearly can't work and doesn't, asking for lngCustID.

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID= " & lngCustID
End Sub

 

[Andrzejek]

Looks like you are setting the RecordSource of the Form: [tt]Me.RecordSource[/tt] and NOT the RecordSource of your Report.

Did you try to play with the code I gave you in my last reply?

...
DoCmd.OpenReport ...
DoCmd.OutputTo ...
DoCmd.Close ...
...

>DoCmd.OpenReport opens and displays the first report correctly
Do you mean you get the report for just one (first) Customer

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Yes, I tried what you sent and the report for the first customer appeared properly, and the draft email then appeared with this report as attachment.

Next time round the displayed report was okay but it used the first report as the email attachment, and so on for the others. As earlier, my understanding is that rptDebtorsStatementForEmail doesn't have anything to tell it to change customer.

I did try both acSaveYes/No.

 

[Andrzejek]

I kind of 'fly blindfolded' since I don't see your entire code.
I've thought over all approach was:

[pre]
For Customers = 1 to 4
Call CreateAndEMailReport(Customer)
Next

Private Sub CreateAndEMailReport(lngCust As Long)

Create and Save PDF Report for a Customer with ID of lngCust
Start an e-mail with PDF report attached
Delete PDF file/report

End Sub
[/pre]
So, if you do Delete PDF file created, there is no chance of attaching a previous version of that PDF because it's gone - should be deleted.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Ok Andy and sorry if I haven't been totally clear.

Your understanding of my process is correct. Tomorrow I'll see if I can replicate what I'm looking to do with a complete set of components and code. Thanks again for your time spent.

 

[Andrzejek]

Don't get it the wrong way, but do you know how to use Breakpoints in VBA code? How to step thru code line by line and debug your code?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson