DoCmd Syntax

[mike60smart]

Hi everyone

I have crossposted this on Utter Access but as yet had no response.

https://www.utteraccess.com/topics/2062573/posts/2792592

How do I modify the following 2 lines of Code to include a WHERE condition?

20 DoCmd.OutputTo acOutputReport, "rptSaleGuarantee", acFormatPDF, "c:\Emails\rptSaleGuarantee.pdf", False

30 DoCmd.OutputTo acOutputReport, "rptChangeofOwnership", acFormatPDF, "c:\EMails\rptChangeofOwnership.pdf", False

The 2 files are being placed into a Temp Folder before being deleted at the end of the Send process.

I need the 2 reports to refer to a specific DogID

,"[DogID]=" & Me.DogID & "

Any help appreciated

 

[Minty]

You can't.

You have to open the report (docmd.openreport) with the where clause to get it how you need it, then do the output DoCmd.OutputTo, then close the report object.

 

[oleronesoftwares]

How do I modify the following 2 lines of Code to include a WHERE condition?

What kind of where condition, please explain better.

The report you are trying to export is the place the where condition will be inserted, i.e inside the reports data source, i.e a Select Query

 

[mike60smart]

You can't.

You have to open the report (docmd.openreport) with the where clause to get it how you need it, then do the output DoCmd.OutputTo, then close the report object.

Hi Minty
The Code below is what I am trying to use for the process, can you indicate where I should modify?

Private Sub cmdSale_Click()

10        On Error GoTo cmdSale_Click_Error

      Dim appOutLook As Object
      Dim MailOutLook As Object
      Dim olMailItem As String
      
          'create the two reports temporarily
20        DoCmd.OutputTo acOutputReport, "rptSaleGuarantee", acFormatPDF, "c:\Emails\rptSaleGuarantee.pdf", False
30        DoCmd.OutputTo acOutputReport, "rptChangeofOwnership", acFormatPDF, "c:\EMails\rptChangeofOwnership.pdf", False
          
          'assign our object references
40        Set appOutLook = CreateObject("Outlook.Application")
50        Set MailOutLook = appOutLook.CreateItem(olMailItem)
        
60        With MailOutLook
        'set the recipient list
70      .To = Me.Email
        
        'set the subject
80      .Subject = "Guarantee and Change of Ownership"
        
        'set the body text
90      .body = "Find attached your Guarantee and Change of Ownership Form"
        
        'add the reports we created
100     .attachments.Add "c:\Emails\rptSaleGuarantee.pdf"
110     .attachments.Add "c:\Emails\rptChangeofOwnership.pdf"
        
        'send the email
120     .Display
130       End With
          
          'tidy up..
          
          'get rid of our object references
140       Set appOutLook = Nothing
150       Set MailOutLook = Nothing


          'delete our temporary files
160       Kill "c:\Emails\rptSaleGuarantee.pdf"
170       Kill "c:\Emails\rptChangeofOwnership.pdf"

        
180       On Error GoTo 0
190       Exit Sub

cmdSale_Click_Error:

200       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSale_Click, line " & Erl & "."


End Sub

 

[Minty]

I would do one of them like this

    Dim strWhere  As String
  
    strWhere = "Where YourField = 2"

    DoCmd.OpenReport "rptSaleGuarantee", acViewPreview, , strWhere, acWindowNormal
    DoCmd.OutputTo acOutputReport, "rptChangeofOwnership", acFormatPDF, "c:\EMails\rptChangeofOwnership.pdf", False
    DoCmd.Close acReport, "rptSaleGuarantee"

Rinse and repeat for the second one. You can change it to acHidden if you don't want it to appear on the screen briefly.

 

[mike60smart]

I would do one of them like this

    Dim strWhere  As String
 
    strWhere = "Where YourField = 2"

    DoCmd.OpenReport "rptSaleGuarantee", acViewPreview, , strWhere, acWindowNormal
    DoCmd.OutputTo acOutputReport, "rptChangeofOwnership", acFormatPDF, "c:\EMails\rptChangeofOwnership.pdf", False
    DoCmd.Close acReport, "rptSaleGuarantee"

Rinse and repeat for the second one. You can change it to acHidden if you don't want it to appear on the screen briefly.

Hi Minty

OK I changed the code to that shown below and now get the following error:-

Hit the Debug and it highlights Line 120

Private Sub cmdSale_Click()


10        On Error GoTo cmdSale_Click_Error


      Dim appOutLook As Object
      Dim MailOutLook As Object
      Dim olMailItem As String
      Dim strWhere  As String
        
20        strWhere = Me.DogID


30        DoCmd.OpenReport "rptSaleGuarantee", acViewPreview, , "DogID=" & Me.DogID, acWindowNormal
40        DoCmd.OutputTo acOutputReport, "rptSaleGuarantee", acFormatPDF, "c:\EMails\rptSaleGuarantee.pdf", False
50        DoCmd.Close acReport, "rptSaleGuarantee"


60        DoCmd.OpenReport "rptChangeofOwnership", acViewPreview, , "DogID=" & Me.DogID, acWindowNormal
70        DoCmd.OutputTo acOutputReport, "rptChangeofOwnership", acFormatPDF, "c:\EMails\rptChangeofOwnership.pdf", False
80        DoCmd.Close acReport, "rptSaleGuarantee"
      
          
          'assign our object references
110       Set appOutLook = CreateObject("Outlook.Application")
120       Set MailOutLook = appOutLook.CreateItem(olMailItem)
        
130       With MailOutLook
        'set the recipient list
140     .To = Me.Email
        
        'set the subject
150     .Subject = "Guarantee and Change of Ownership"
        
        'set the body text
160     .body = "Find attached your Guarantee and Change of Ownership Form"


                
        
        'add the reports we created
170     .attachments.Add "c:\Emails\rptSaleGuarantee.pdf"
180     .attachments.Add "c:\Emails\rptChangeofOwnership.pdf"
        
        'send the email
190     .Display
200       End With
          
          'tidy up..
          
          'get rid of our object references
210       Set appOutLook = Nothing
220       Set MailOutLook = Nothing


          'delete our temporary files
230       Kill "c:\Emails\rptSaleGuarantee.pdf"
240       Kill "c:\Emails\rptChangeofOwnership.pdf"




          
250       On Error GoTo 0
260       Exit Sub


cmdSale_Click_Error:


270       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSale_Click, line " & Erl & "."


End Sub

 

[Minty]

Hmm that looks okay, the problem is further up here;

Dim olMailItem As String

This should be an object.

 

[mike60smart]

Hmm that looks okay, the problem is further up here;

Dim olMailItem As String

This should be an object.

Hi Minty

I changed the references for Outlook and the process now works.

Only problem is the PDF in the Email is a Word Document with the extension .Pdf.
When I open the attachment from the EMail it says Word is going to convert to PDF Format?

Any thoughts as to why this is?

Private Sub cmdSale_Click()

10 On Error GoTo cmdSale_Click_Error

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

20 Set OutApp = CreateObject("Outlook.Application")
30 Set OutMail = OutApp.CreateItem(0)

Dim strTo As String
Dim strWhere As String

40 strTo = Me.Email
50 strWhere = Me.DogID

60 DoCmd.OpenReport "rptSaleGuarantee", acViewPreview, , "DogID=" & Me.DogID, acWindowNormal
70 DoCmd.OutputTo acOutputReport, "rptSaleGuarantee", acFormatPDF, "c:\Emails\rptSaleGuarantee.pdf", False

80 DoCmd.Close acReport, "rptSaleGuarantee"

90 DoCmd.OpenReport "rptChangeofOwnership", acViewPreview, , "DogID=" & Me.DogID, acWindowNormal
100 DoCmd.OutputTo acOutputReport, "rptChangeofOwnership", acFormatPDF, "c:\EMails\rptChangeofOwnership.pdf", False

110 DoCmd.Close acReport, "rptSaleGuarantee"

120 With OutMail
130 .Display
140 .To = Me.Email
150 .Subject = "Guarantee and Change of Ownership"
160 .Body = "Find attached your Guarantee and Change of Ownership Form"

'add the reports we created
170 .attachments.Add "c:\Emails\rptSaleGuarantee.pdf"
180 .attachments.Add "c:\Emails\rptChangeofOwnership.pdf"

'send the email
190 .Display
200 End With

'tidy up..
210 Set OutMail = Nothing
220 Set OutApp = Nothing


'delete our temporary files
230 Kill "c:\Emails\rptSaleGuarantee.pdf"
240 Kill "c:\Emails\rptChangeofOwnership.pdf"

250 On Error GoTo 0
260 Exit Sub

cmdSale_Click_Error:

270 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSale_Click, line " & Erl & "."

End Sub

 

[Minty]

Only problem is the PDF in the Email is a Word Document with the extension .Pdf.
When I open the attachment from the EMail it says Word is going to convert to PDF Format?

I'm pretty sure it isn't. Access can't create a word document (well not in an report output easily), it can create a RTF, but that's not what's happening here.

What happens if you open any other pdf file on your PC?
It seems as if you have possibly set the file association for PDF's to open in Word?

 

[mike60smart]

I'm pretty sure it isn't. Access can't create a word document (well not in an report output easily), it can create a RTF, but that's not what's happening here.

What happens if you open any other pdf file on your PC?
It seems as if you have possibly set the file association for PDF's to open in Word?

Hi Minty
I have gone and redefined the association and it now works a treat.
Many Many thanks for the help.