By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

Filter a report in an auto email code

100+
P: 144
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.  
  3. If Me.[REJECT AREA].Value = "Purchased" Then
  4.  
  5. Dim strEMail As String
  6. Dim oOutlook As Object
  7. Dim oMail As Object
  8. Dim strAddr As String
  9. Dim MyDB As DAO.Database
  10. Dim rstEMail As DAO.Recordset
  11. Dim strReportName As String
  12.  
  13. Set oOutlook = CreateObject("Outlook.Application")
  14. Set oMail = oOutlook.CreateItem(0)
  15.  
  16. strReportName = "Purchased New Reject Tag Report"
  17.  
  18. 'This has the filter I need to use
  19. 'DoCmd.OpenReport "Reject Tag Report", acViewPreview, , [REJECT TAG NUMBER] = [Forms]![Reject Tag Form]![REJECT TAG NUMBER], acHidden
  20. DoCmd.OutputTo acOutputReport, "Reject Tag Report", acFormatPDF, "N:\Inspect\New Reject Tag Database\Reports" & _
  21.                "\" & strReportName & "-" & Format(DATE, "mm-dd-yy") & ".pdf", False, , , acExportQualityPrint
  22.  
  23. 'Retrieve all E-Mail Addressess in tblEMailAddress
  24. Set MyDB = CurrentDb
  25. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMailPurchased", dbOpenSnapshot, dbOpenForwardOnly)
  26.  
  27. With rstEMail
  28.   Do While Not .EOF
  29.     'Build the Recipients String
  30.     strEMail = strEMail & ![EmailAddress] & ";"
  31.       .MoveNext
  32.   Loop
  33. End With
  34. '--------------------------------------------------
  35.  
  36. With oMail
  37.   .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  38.   .Body = "Please review the attached report."
  39.   .Subject = Replace(Replace("REJECT TAG#|1 P/N: |2", "|1", Nz(Space(1) & [REJECT TAG NUMBER], "")), "|2", Nz([Part Number] & Space(1) & [Descriptive Reason], ""))
  40.   .Display
  41.   .Attachments.Add "N:\Inspect\New Reject Tag Database\Reports" & "\" & strReportName & "-" & Format(DATE, "mm-dd-yy") & ".pdf"
  42.  
  43. End With
  44.  
  45. Set oMail = Nothing
  46. Set oOutlook = Nothing
  47.  
  48. rstEMail.Close
  49. Set rstEMail = Nothing
  50.  
  51. Dim aFile As String
  52. aFile = "N:\Inspect\New Reject Tag Database\Tables\Emailed reports\Purchased New Reject Tag Report.pdf"
  53. If Len(Dir$(aFile)) > 0 Then
  54.      Kill aFile
  55. End If
  56. End If
  57.  
  58. 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 -
Expand|Select|Wrap|Line Numbers
  1. 'This has the filter I need to use
  2. 'DoCmd.OpenReport "Reject Tag Report", acViewPreview, , [REJECT TAG NUMBER] = [Forms]![Reject Tag Form]![REJECT TAG NUMBER], acHidden
  3. DoCmd.OutputTo acOutputReport, "Reject Tag Report", acFormatPDF, "N:\Inspect\New Reject Tag Database\Reports" & _
  4.                "\" & 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
3 Weeks Ago #1
Share this Question
Share on Google+
19 Replies


zmbd
Expert Mod 5K+
P: 5,380
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:
Expand|Select|Wrap|Line Numbers
  1. '(...)Air Code so you should double check it
  2. '
  3. Dim myReportWhere As String
  4. '
  5. myReportWhere = "[REJECT TAG NUMBER] = " _
  6.   & [Forms]![Reject Tag Form]![REJECT TAG NUMBER]
  7. '
  8. DoCmd.OpenReport _
  9.   ReportName:="Reject Tag Report", _
  10.   View:=acViewPreview, _
  11.   WhereCondition:=myReportWhere, _
  12.   WindowMode:=acHidden  '<You may have to have the report visible!
  13. '
  14. Dim myOutPutFile As String
  15. '
  16. myOutPutFile = "N:\Inspect\New Reject Tag Database\Reports" _
  17.   & " \" & strReportName & "-" _
  18.   & Format(Date, "mm-dd-yy") & ".pdf"
  19. '
  20. DoCmd.OutputTo _
  21.   objecttype:=acOutputReport, _
  22.   Outputformat:=acFormatPDF, _
  23.   Outputfile:=myOutPutFile, _
  24.   AutoStart:=False, _
  25.   Outputquality:=acExportQualityPrint
  26. '(...)
3 Weeks Ago #2

100+
P: 144
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo _
  2.   objecttype:=acOutputReport, _
  3.   Outputformat:=acFormatPDF, _
  4.   Outputfile:=myOutPutFile, _
  5.   AutoStart:=False, _
  6.   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?
3 Weeks Ago #3

100+
P: 144
I got the above to work but getting an error now at
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. If Me.[REJECT AREA].Value = "Purchased" Then
  2.  
  3. Dim strEMail As String
  4. Dim oOutlook As Object
  5. Dim oMail As Object
  6. Dim strAddr As String
  7. Dim MyDB As DAO.Database
  8. Dim rstEMail As DAO.Recordset
  9. Dim strReportName As String
  10. Dim myReportWhere As String
  11. Dim myOutPutFile As String
  12.  
  13. Set oOutlook = CreateObject("Outlook.Application")
  14. Set oMail = oOutlook.CreateItem(0)
  15.  
  16. strReportName = Replace(Replace("REJECT TAG#|1 P/N: |2", "|1", Nz(Space(1) & [REJECT TAG NUMBER], "")), "|2", Nz([Part Number] & Space(1) & [Descriptive Reason], ""))
  17.  
  18.  
  19. myReportWhere = "[REJECT TAG NUMBER] = [Forms]![Reject Tag Form]![REJECT TAG NUMBER]"
  20.  
  21. DoCmd.OpenReport "Reject Tag Report", acViewPreview, , myReportWhere, acWindowNormal
  22.  
  23. myOutPutFile = "N:\Inspect\New Reject Tag Database\Reports" & "\" & strReportName & ".PDF"
  24.  
  25. DoCmd.OutputTo acOutputReport, "Reject Tag Report", acOutputReport, myOutPutFile, False, , , acExportQualityPrint
  26.  
3 Weeks Ago #4

zmbd
Expert Mod 5K+
P: 5,380
As I thought, you have to have the report visible.
Expand|Select|Wrap|Line Numbers
  1. '(...)Air Code so you should double check it
  2.  '
  3.  Dim myReportWhere As String
  4.  '
  5.  myReportWhere = "[PK_Students] = 1"
  6.  '
  7.  DoCmd.OpenReport _
  8.    ReportName:="ReportStudents", _
  9.    View:=acViewPreview, _
  10.    WhereCondition:=myReportWhere, _
  11.    WindowMode:=acWindowNormal '<You have to have the report visible
  12. '
  13.  Dim myOutPutFile As String
  14.  '
  15.  myReportWhere = "[REJECT TAG NUMBER] = " _
  16.   & [Forms]![Reject Tag Form]![REJECT TAG NUMBER]
  17.  '
  18.  DoCmd.OutputTo _
  19.    Objecttype:=acOutputReport, _
  20.    Outputformat:=acFormatPDF, _
  21.    Outputfile:=myOutPutFile, _
  22.    AutoStart:=False, _
  23.    Outputquality:=acExportQualityPrint
  24. '
  25.  DoCmd.Close _
  26.    Objecttype:=acReport, _
  27.    ObjectName:="Reject Tag Report", _
  28.    Save:=acSaveNo
  29. '(...)
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. :-)
3 Weeks Ago #5

100+
P: 144
Success, I had this part incorrect:
Expand|Select|Wrap|Line Numbers
  1. 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.
2 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,347
@DJ.
Not sure what you hope to achieve with the part of your code that reads :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. ([Part Number] + " ") & [Descriptive Reason] & ""
This ensures that an extra space is added before [Descriptive Reason] - unless [Part Number] is Null.
Expand|Select|Wrap|Line Numbers
  1. Null + "Any string" ==> Null
  2. Null & "Any string" ==> "Any string"
I've added the empty string at the end in case both Field values can be Null.
2 Weeks Ago #7

100+
P: 144
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.
2 Weeks Ago #8

twinnyfo
Expert Mod 2.5K+
P: 3,184
DJ - part of the logic behind this is very simple. Which is easier:

Expand|Select|Wrap|Line Numbers
  1. strTest = "This is" & Space(1) & "a test"
or
Expand|Select|Wrap|Line Numbers
  1. 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!
2 Weeks Ago #9

100+
P: 144
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.
2 Weeks Ago #10

100+
P: 144
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
2 Weeks Ago #11

twinnyfo
Expert Mod 2.5K+
P: 3,184
Yeah, you need a "space" in your quotes.

:-)
2 Weeks Ago #12

zmbd
Expert Mod 5K+
P: 5,380
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.
2 Weeks Ago #13

NeoPa
Expert Mod 15k+
P: 31,347
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.
2 Weeks Ago #14

NeoPa
Expert Mod 15k+
P: 31,347
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().
2 Weeks Ago #15

100+
P: 144
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.
2 Weeks Ago #16

twinnyfo
Expert Mod 2.5K+
P: 3,184
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!
2 Weeks Ago #17

100+
P: 144
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.
2 Weeks Ago #18

NeoPa
Expert Mod 15k+
P: 31,347
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.
2 Weeks Ago #19

zmbd
Expert Mod 5K+
P: 5,380
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
2 Weeks Ago #20

Post your reply

Sign in to post your reply or Sign up for a free account.