Filter a report in an auto email code

[DJRhino1175]

Private Sub Form_Close()
 
If Me.[REJECT AREA].Value = "Purchased" Then
 
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
Dim strReportName As String
 
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
 
strReportName = "Purchased New Reject Tag Report"
 
'This has the filter I need to use
'DoCmd.OpenReport "Reject Tag Report", acViewPreview, , [REJECT TAG NUMBER] = [Forms]![Reject Tag Form]![REJECT TAG NUMBER], acHidden
DoCmd.OutputTo acOutputReport, "Reject Tag Report", acFormatPDF, "N:\Inspect\New Reject Tag Database\Reports" & _
               "\" & strReportName & "-" & Format(DATE, "mm-dd-yy") & ".pdf", False, , , acExportQualityPrint
 
'Retrieve all E-Mail Addressess in tblEMailAddress
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From tblEMailPurchased", dbOpenSnapshot, dbOpenForwardOnly)
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![EmailAddress] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------
 
With oMail
  .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = "Please review the attached report."
  .Subject = Replace(Replace("REJECT TAG#|1 P/N: |2", "|1", Nz(Space(1) & [REJECT TAG NUMBER], "")), "|2", Nz([Part Number] & Space(1) & [Descriptive Reason], ""))
  .Display
  .Attachments.Add "N:\Inspect\New Reject Tag Database\Reports" & "\" & strReportName & "-" & Format(DATE, "mm-dd-yy") & ".pdf"
 
End With
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing
 
Dim aFile As String
aFile = "N:\Inspect\New Reject Tag Database\Tables\Emailed reports\Purchased New Reject Tag Report.pdf"
If Len(Dir$(aFile)) > 0 Then
     Kill aFile
End If
End If
 
End Sub

99.9% of this code works, the issue I'm having is the output of the report to filter out everything but the current report.

This is the section I'm sure I have something wrong -

'This has the filter I need to use
'DoCmd.OpenReport "Reject Tag Report", acViewPreview, , [REJECT TAG NUMBER] = [Forms]![Reject Tag Form]![REJECT TAG NUMBER], acHidden
DoCmd.OutputTo acOutputReport, "Reject Tag Report", acFormatPDF, "N:\Inspect\New Reject Tag Database\Reports" & _
               "\" & strReportName & "-" & Format(DATE, "mm-dd-yy") & ".pdf", False, , , acExportQualityPrint

If I run this as is it exports hundreds of reports.

Any help will be greatly appreciated.

Thanks,

Rhino

[zmbd]

Good Morning:

Best method is to have the report based upon a parameter query that uses the form control value for the WHERE clause then use the DoCmd.OutputTo

Using the code you've posted
- Open the report in preview - you may have to have the report visible...
- Remove the explicit call to the report object. You've already opened it in preview. When one uses the explicit call to the report, the command opens that report as a new run not as the currently open, filtered, report. :)

- You know I usually recommend building the strings outside of the function so that you have something like:

'(...)Air Code so you should double check it
'
Dim myReportWhere As String
'
myReportWhere = "[REJECT TAG NUMBER] = " _
  & [Forms]![Reject Tag Form]![REJECT TAG NUMBER]
'
DoCmd.OpenReport _
  ReportName:="Reject Tag Report", _
  View:=acViewPreview, _
  WhereCondition:=myReportWhere, _
  WindowMode:=acHidden  '<You may have to have the report visible!
'
Dim myOutPutFile As String
'
myOutPutFile = "N:\Inspect\New Reject Tag Database\Reports" _
  & " \" & strReportName & "-" _
  & Format(Date, "mm-dd-yy") & ".pdf"
'
DoCmd.OutputTo _
  objecttype:=acOutputReport, _
  Outputformat:=acFormatPDF, _
  Outputfile:=myOutPutFile, _
  AutoStart:=False, _
  Outputquality:=acExportQualityPrint
'(...)

[DJRhino1175]

DoCmd.OutputTo _
  objecttype:=acOutputReport, _
  Outputformat:=acFormatPDF, _
  Outputfile:=myOutPutFile, _
  AutoStart:=False, _
  Outputquality:=acExportQualityPrint

I put the changes in that you suggested and it errors out at the above code section. This is a different format then I'm use to and having a hard time debugging it.

Error code is 2487 The object argument for the action or method is blank or invalid.

Is this because the report is hidden?

[DJRhino1175]

I got the above to work but getting an error now at

DoCmd.OutputTo acOutputReport, "Reject Tag Report", acOutputReport, myOutPutFile, False, , , acExportQualityPrint

Iget run-time error 2302 Microsoft Access can't save the output data to the file you've selected

Here is the full code

If Me.[REJECT AREA].Value = "Purchased" Then
 
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
Dim strReportName As String
Dim myReportWhere As String
Dim myOutPutFile As String
 
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
 
strReportName = Replace(Replace("REJECT TAG#|1 P/N: |2", "|1", Nz(Space(1) & [REJECT TAG NUMBER], "")), "|2", Nz([Part Number] & Space(1) & [Descriptive Reason], ""))
 
 
myReportWhere = "[REJECT TAG NUMBER] = [Forms]![Reject Tag Form]![REJECT TAG NUMBER]"
 
DoCmd.OpenReport "Reject Tag Report", acViewPreview, , myReportWhere, acWindowNormal
 
myOutPutFile = "N:\Inspect\New Reject Tag Database\Reports" & "\" & strReportName & ".PDF"
 
DoCmd.OutputTo acOutputReport, "Reject Tag Report", acOutputReport, myOutPutFile, False, , , acExportQualityPrint
 

[zmbd]

As I thought, you have to have the report visible.

'(...)Air Code so you should double check it
 '
 Dim myReportWhere As String
 '
 myReportWhere = "[PK_Students] = 1"
 '
 DoCmd.OpenReport _
   ReportName:="ReportStudents", _
   View:=acViewPreview, _
   WhereCondition:=myReportWhere, _
    WindowMode:=acWindowNormal '<You have to have the report visible
 '
 Dim myOutPutFile As String
 '
 myReportWhere = "[REJECT TAG NUMBER] = " _
  & [Forms]![Reject Tag Form]![REJECT TAG NUMBER]
 '
 DoCmd.OutputTo _
   Objecttype:=acOutputReport, _
   Outputformat:=acFormatPDF, _
   Outputfile:=myOutPutFile, _
   AutoStart:=False, _
   Outputquality:=acExportQualityPrint
'
 DoCmd.Close _
   Objecttype:=acReport, _
   ObjectName:="Reject Tag Report", _
   Save:=acSaveNo
'(...)

This is a different format then I'm use to and having a hard time debugging it.

It's the named augment form of the function.
Instead of needing something like
Obj.Command first,,,,,Sixth
You can do something like
Obj.Command Augment1:=First, Augment6:=Sixth
Which I find more pleasing to the eye, often easier to follow down the road when it's a function I don't use often, and has the benefit that if you are missing a comma (or have too many) there are no unexpected errors that are hard to pin-down. :-)

[DJRhino1175]

Success, I had this part incorrect:

strReportName = Replace(Replace("REJECT TAG#|1 P/N: |2", "|1", Nz(Space(1) & [REJECT TAG NUMBER], "")), "|2", Nz([Part Number] & Space(1) & [Descriptive Reason], ""))

You cannot have "\", "/" or ":" in your file naming construct.

[NeoPa]

@DJ.
Not sure what you hope to achieve with the part of your code that reads :

Nz([Part Number] & Space(1) & [Descriptive Reason], "")

The Space(1) will necessarily ensure that the first parameter passed to the Nz() function is not Null.

Also, using Space(1) is a complicated way of saying " " in VBA. The only reason I can imagine for that is if you worry someone will try to re-use your code and you don't want them to understand what you're doing.

If you want a string that resolves to an empty string ("") then you can take advantage of Null Propagation (Where's a good link when you want one?) by saying :

([Part Number] + " ") & [Descriptive Reason] & ""

This ensures that an extra space is added before [Descriptive Reason] - unless [Part Number] is Null.

Null + "Any string" ==> Null
Null & "Any string" ==> "Any string"

I've added the empty string at the end in case both Field values can be Null.

[DJRhino1175]

Space(1) is the number of spaces between Part Number and Descriptive Reason.
I did a google search on how to do a space between the to items and this is what I was told to do.

[twinnyfo]

DJ - part of the logic behind this is very simple. Which is easier:

strTest = "This is" & Space(1) & "a test"

or

strTest = "This is" & " " & "a test"

The most useful purpose of the Space() function is to be able to use a variable in order to add an unknown number of spaces--you can have a variety of reasons for doing this.

However, if you know that there is always only one space needed, using " " is much easier.

You should find the site that told you to use Space(1) and tell them it's not the best idea.

However, you should really look closely at what NeoPa is advising concerning the string you are trying to create. Anything & " " will not be Null

I would think (I would hope so) that if the Part Number were Null--meaining you can't find a corresponding part number, that you would not send out an e-mail with a report on a particular part number. But, if there is ALWAYS a part number, then the string will NEVER be Null.

Just want you to think through what it is you are trying to do.

Hope this hepps!

[DJRhino1175]

I got you on the null part, was trying to figure what you guys ment. NZ() part, after looking closer Nz() doesn't even need to be there at all. Good catch. I will also change the Space(1) to "". For some reason I thought I did that and it did not work, but I'll give it another shot.

[DJRhino1175]

Ok, the reason it did not work for me is I did not add a "Space" between the quotes...and for some reason the NZ() needs to be there, as when I took then out the code quit working. So I put them back in and all is good.

Thanks

[twinnyfo]

Yeah, you need a "space" in your quotes.

:-)

[zmbd]

home > topics > microsoft access / vba > insights > what is null?
DJRhino1175, you might find this article to be helpful for your string... especially the conversation regarding the propagation of null thru the string.

[NeoPa]

DJRhino:
Good catch. I will also change the Space(1) to "". For some reason I thought I did that and it did not work, but I'll give it another shot.

This is one of the reasons why we insist code is always included in the [CODE] tags (although such short strings are allowed exceptions) - See "" in ordinary text and exactly what's there is hard to see. "" on the other hand, shouts at you that you've missed out the space. Experience helps too, but let the rules help you. It's why we also recommend strongly that you never edit code in anything but a text editor (which typically use non-proportional fonts). So many problems are overlooked because people use the wrong tools. Word, for instance, great software though it truly is, is absolutely rubbish for editing code with.

[NeoPa]

DJRhino:
for some reason the NZ() needs to be there, as when I took then out the code quit working. So I put them back in and all is good.

It may well be that the version you tried after removing the Nz() failed to work correctly but I can assure you that the reason wasn't down to removing the Nz(). Something else also changed as Nz() in that context would have no effect anyway.

That's not to say you need to go back and have another go if you don't want to bother. That's up to you. I just wanted to make sure you understood that the issue was not with the Nz().

[DJRhino1175]

I'm leaving the NZ() in there as it works with no issue as for what I need it to do. But I need to start a new thread for a different issue I'm having with coda associated with this.

Thanks for all your inputs as I'm learning a lot. I'm creating .txt documents to keep a lot of the special codes created for any future projects that may need them so I do not have to reinvent the wheel.

[twinnyfo]

DJ,

Another tip--instead of saving as a text file, you can have a separate module (or even a separtae Database) that holds some frequently used Code. This way you can simply cut and paste into your new projects.

All of us here at Bytes are glad you are beginning to look at the bigger picture of DB design, and over time this will benefit you.

Looking forward to working with you on your future projects!

[DJRhino1175]

Thanks twinnyfo, never though of just making a DB to hold all of them. This will definitely be helpfully as I have accumulated several .txt docs.

[NeoPa]

If this were Facebook I'd add 'Like's to both posts #16 & #17.

Good stuff in both and congratulations to DJ on handling his learning & progression so well.

[zmbd]

Oh, yes... I have a database with modules named for the type of VBA (email, excel, outlook, encryption, etc....) contained. I used to have them in *.bas files however I have so many of them now that it was becoming a pain to search