DoCmd Syntax (1 Viewer)

mike60smart

Registered User.
Local time
Today, 07:53
Joined
Aug 6, 2017
Messages
1,899
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

AWF VIP
Local time
Today, 07:53
Joined
Jul 26, 2013
Messages
10,355
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

Passionate Learner
Local time
Today, 00:53
Joined
Sep 22, 2014
Messages
1,159
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

Registered User.
Local time
Today, 07:53
Joined
Aug 6, 2017
Messages
1,899
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?

Code:
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

AWF VIP
Local time
Today, 07:53
Joined
Jul 26, 2013
Messages
10,355
I would do one of them like this

SQL:
    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

Registered User.
Local time
Today, 07:53
Joined
Aug 6, 2017
Messages
1,899
I would do one of them like this

SQL:
    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

Code:
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
 

Attachments

  • error.JPG
    error.JPG
    17.7 KB · Views: 233
  • Highlights.JPG
    Highlights.JPG
    23.7 KB · Views: 241

Minty

AWF VIP
Local time
Today, 07:53
Joined
Jul 26, 2013
Messages
10,355
Hmm that looks okay, the problem is further up here;

Dim olMailItem As String

This should be an object.
 

mike60smart

Registered User.
Local time
Today, 07:53
Joined
Aug 6, 2017
Messages
1,899
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

AWF VIP
Local time
Today, 07:53
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 07:53
Joined
Aug 6, 2017
Messages
1,899
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.
:):)
 

Users who are viewing this thread

Top Bottom