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

Send email with report as an attachment

100+
P: 163
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnMail1_Click()
  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.  
  10. Set oOutlook = CreateObject("Outlook.Application")
  11. Set oMail = oOutlook.CreateItem(0)
  12.  
  13. 'Retrieve all E-Mail Addressess in tblEMailAddress
  14. Set MyDB = CurrentDb
  15. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", dbOpenSnapshot, dbOpenForwardOnly)
  16.  
  17. With rstEMail
  18.   Do While Not .EOF
  19.     'Build the Recipients String
  20.     strEMail = strEMail & ![EmailAddress] & ";"
  21.       .MoveNext
  22.   Loop
  23. End With
  24. '--------------------------------------------------
  25.  
  26. With oMail
  27.   .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  28.   .Body = "Please review the attached ECN and complete any and all tasks that pertain to you."
  29.   .Subject = Replace(Replace("ECN# |1: |2", "|1", Nz([ECN#], "")), "|2", Nz([NewPN], ""))
  30.     .Display
  31.   .Attachments.Add
  32.  
  33. End With
  34.  
  35. Set oMail = Nothing
  36. Set oOutlook = Nothing
  37.  
  38. rstEMail.Close
  39. Set rstEMail = Nothing
  40.  
  41.  
  42. End Sub

This is what I have so far, the ".Attachment.add" is where I'm stuck. I want it to send a report that I have, based of the data in the form where this button is located, by the ID #.

I have tried it with out the .Attachment part and it does everything its supposed to, now I just need to get it to pull the report filtered based off of ID# on the Form.

Does anyone have any Ideas? I have done some searching and everything says to export the report first then attach it, which I prefer not to do.

Thanks for all the help.
Aug 13 '18 #1
Share this Question
Share on Google+
35 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
DJ - you can only add a file with Attachments.Add.

SO, you must export your Report first (I usually make a PDF), keep track of the file name, and then attach it in your code.

That is the easiest way I know of.
Aug 13 '18 #2

100+
P: 163
Darn it. I usually us the Send object, but I cannot add anymore email address to it, so this is my next object. Its so weird you can do one but not the other....So now I'll have to figure out the export part.

Thanks for the help.
Aug 13 '18 #3

NeoPa
Expert Mod 15k+
P: 31,494
DJRhino:
Does anyone have any Ideas? I have done some searching and everything says to export the report first then attach it, which I prefer not to do.
Unfortunately that doesn't make too much sense as an attachment, as you probably know, is a file - typically with an extension that indicates the type of file. Trying to use anything that isn't a file, quite apart from there being no interface to allow you to send it, would also be meaningless when it's received. Ask yourself what the recipient would do when they receive it if they receive some data as an attachment that isn't even a file, and doesn't have an extension that allows it to be opened with a specified application.
Aug 13 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 3,284
DJ,

Also, there are some other threads on this forum that discuss filtering reports and saving them.

That could be another threaded question if you have challenges working through that. We'd love to hepp!
Aug 13 '18 #5

100+
P: 163
Neopa,

Its because I'm used to sending things with the send-object function and it does everything for you, unfortunately each line in the email address bar only allows 255 characters. This is why i stated "Does anyone have any Ideas? I have done some searching and everything says to export the report first then attach it, which I prefer not to do."

As the send-object filters and attaches the report for you, with out exporting, renaming and all that.
Aug 13 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 3,284
DJRhino1175
Does anyone have any Ideas? I have done some searching and everything says to export the report first then attach it, which I prefer not to do.
We get it! There are some things that Access does and there are some things that Access does not do. This is one of them Access does not do.

The only way around this is to use the method that you prefer not to use. However, I would suggest that using this method will also help you understnad additional aspects of Access that maybe you previously did not understand so well--and thus add those tools to your tool kit!
Aug 13 '18 #7

NeoPa
Expert Mod 15k+
P: 31,494
Hi DJ.

I could point to the difference in the parameters expected/allowed for the two separate procedures, but if I'm honest I have to admit that I'm surprised the SendObject() would do that much for you. I expect it must take the internal Access object and first create a temporary file for you in order to add it as an attachment. It's still true that an attachment must first be available as a file within the file system, but if that's created for you by the procedure itself that may be less obvious.

So, your question wasn't as senseless as I'd imagined. Fair play.
Aug 13 '18 #8

zmbd
Expert Mod 5K+
P: 5,397
+ How many address are you attempting to use with the DoCmd.SendObject? I've sent stored reports to around 100 to 150 recipients all at once using this simple function.

+ Outlook automation
TwinnyFo is correct about using the Application-Automation approach you're showing in your first post... I usually use something like
Expand|Select|Wrap|Line Numbers
  1. '(... THIS IS AIR CODE ...)
  2. 'that is to say, I'm pulling this out of my memory without aid of the VBA-Editor so there may be a typo or two :)
  3. DoCmd.OutputTo Objecttype:=acOutputReport, ObjectName:="YourReport", _
  4.   Outputformat:=acFormatPDF, Outputfile:=strToFileLocationWithName
  5. '(...)
  6. With outLookMailItem
  7.   .Subject = Replace(Dir(strOutputPath), ".pdf", "")
  8.   .Attachments.Add strToFileLocationWithName
  9.   .Close (0)
  10.   .Display
  11. End With
  12. '(...)
Use a for-next-loop to cycle the .Attachments.Add to add multiple attachments.
Aug 13 '18 #9

100+
P: 163
I think its like 30, but our e-mail format is very long and eats a lot of characters. Its firstname.Lastname@cooperstandard.com, so it doesn't take long to use up 255 characters in each of the lines to:, CC:, BCC:. As a side note I use a macro to do this so this might be what is giving me the limit. I am kind of liking the approach I'm taking. Just need to figure the output to and how to name the file, I'm guessing it will need to be a generic name. I wouldn't mind it if I could pull the ID# as part of the title.

Where in the code that I put up would I put the output at? can it go any where before the display part? A lot of this is new for me so I'm a little nervous with playing around with the code I have as I might not be able to fix it if i screw it up.
Aug 14 '18 #10

zmbd
Expert Mod 5K+
P: 5,397
DJRhino1175,

+ You mention Macros here - these are very limited in their application - and yes, indeed, if you are directly entering the emails into the Macro parameters you will hit the 255 limit quite quickly.

+ Try the docmd.sendobject via VBA, I think you'll find that the only limit you will hit is set by your Mail connection. If that doesn't punch the ticket the you'll have to use either the Outlook Automation or (if your admins have left it enabled) the CDO methods.
Aug 14 '18 #11

100+
P: 163
I'm going to try both methods tomorrow to see which one I like better. But it is good to know the Docmd.sendobject in VBA is like the one in the macro but way better. Did not even think of that and I knew it was there...Trying to over think it I guess.
Aug 14 '18 #12

twinnyfo
Expert Mod 2.5K+
P: 3,284
DJ,

The big sticking point with .SendObject is that you cannot apply filters directly using that method. You must apply filters either programatically (using the Report's .Filter) or at the Query-level (applying some sort of filter using parameters that hte Query grabs from the Form's Controls.

However, theoritically, if you had a list of Customers with a CustID, your VBA outline would look like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSendEMail()
  2.     Dim your variables
  3.  
  4.     Get your list of Customer IDs
  5.  
  6.     Loop through your list of IDs
  7.         Create and save your Report based upon the specific ID (name it appropriately)
  8.         Using the file name you just created for your report, attach it to your Outlook Object.
  9.  
  10.     Clean up your objects
  11.  
  12.     Don't forget your Error Handling  ;-)
  13. End Sub
I do this all the time.

We are all glad to hepp you through additional aspects of this process.
Aug 14 '18 #13

100+
P: 163
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub btnMail1_Click()
  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 = "New ECN Report"
  17.  
  18. DoCmd.OutputTo acOutputReport, "rptECN", acFormatPDF, CurrentProject.Path & _
  19.                "\" & strReportName & ".pdf", False, , , acExportQualityPrint
  20.  
  21. 'Retrieve all E-Mail Addressess in tblEMailAddress
  22. Set MyDB = CurrentDb
  23. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", dbOpenSnapshot, dbOpenForwardOnly)
  24.  
  25. With rstEMail
  26.   Do While Not .EOF
  27.     'Build the Recipients String
  28.     strEMail = strEMail & ![EmailAddress] & ";"
  29.       .MoveNext
  30.   Loop
  31. End With
  32. '--------------------------------------------------
  33.  
  34. With oMail
  35.   .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  36.   .Body = "Please review the attached ECN and complete any and all tasks that pertain to you."
  37.   .Subject = Replace(Replace("ECN# |1: |2", "|1", Nz([ECN#], "")), "|2", Nz([NewPN], ""))
  38.     .Display
  39.   .Attachments.Add CurrentProject.Path & "\" & strReportName & ".pdf"
  40.  
  41. End With
  42.  
  43. Set oMail = Nothing
  44. Set oOutlook = Nothing
  45.  
  46. rstEMail.Close
  47. Set rstEMail = Nothing
  48.  
  49.  
  50. End Sub
Here is what I have as of right now. 2 issues...

1) How do I filter the report so it only outputs the current record
2) After the the file is attached, is it possible to delete the file that was created?

Other than this, it is working perfectly, out puts the file, It attaches the file, adds everyone to the e-mail.
Aug 14 '18 #14

twinnyfo
Expert Mod 2.5K+
P: 3,284
Issue 2: Use Kill [FileName]

Issue 1: A little more complex.

There are several ways you could do this.

You could use the value in the Form's Control as one of the parameters of the Report's underlying Query:

Expand|Select|Wrap|Line Numbers
  1. "WHERE CustomerID = " & Forms![FormName]![ControlName]
This can be done in the Query Editor's Expression Builder to get the syntax correct. The drawback to this is that this Query is then limited to that Form. Additionally, sometimes these types of Queries can be tricky--especially if they are used as sub-queriees for another Query. There are a few ways around that, but, in general I prefer to avoid that. However, it is the most "direct" way to approach the problem.

Second, you could use a Global filter string which the Report uses at execution. The basics of it go this way:
  1. Establish a Global String Vairable or use TempVars().
  2. Just prior to executing the report, you set the value of the Filter: gstrFilter = "CustID = " & Me.txtCustID
  3. In the Report's OnOpen procedure, you check to see if the Filter is empty (which you can set if you want all records). If not, you set the Record's Filter.
  4. Save the Report, using the Customer's Name or CustID--however you want it--in the name of the Report. This item is essentially the same as your current method.

That's really the basics. I have found this second method to be a bit more complicated to design, but I have really liked how it executes. I've had no problems with it whatsoever.

There are probably others ways to skin this cat.

Hope this hepps!
Aug 14 '18 #15

100+
P: 163
Expand|Select|Wrap|Line Numbers
  1. Dim aFile As String
  2. aFile = "N:\Unsecure Share\New ECN System\New ECN Report.pdf"
  3. If Len(Dir$(aFile)) > 0 Then
  4.      Kill aFile
What I used for the Kill, seems to work ok.

The solution to Issue 1 is a little over my head at the moment.

The Form this cmdButton is on will already be filter to the record, so is this the code I would use just before the Docmd.outputTo
Expand|Select|Wrap|Line Numbers
  1. gstrFilter = "ECN# = " & Me.txtECN#
The report doesn't truely execute in the code anywhere.
Aug 14 '18 #16

twinnyfo
Expert Mod 2.5K+
P: 3,284
First, some sage advice, learned the hard way: When you name a field or control, refrain from using special characters like "#". This can cause problems as you start to use these field names throughout your VBA. Field name of ECN, control named txtECN should be sufficient for any purposes you have.

Remember to declar this Global String in a standalone VBA Module--otherwise you won't be able to use it beyond the confines of the Form.

Then, when you are on your Form, this is where you set the Filter, as you have described:

Expand|Select|Wrap|Line Numbers
  1. gstrFilter = "ECN = " & Me.txtECN
(I removed the hashtag)

Now, on the Report's OnOpen Event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. On Error GoTo EH
  3.  
  4.     If Len(gstrFilter) <> 0 Then
  5.         With Me
  6.             .Filter = gstrFilter
  7.             .FilterOn = True
  8.         End With
  9.     End If
  10.  
  11.     Exit Sub
  12. EH:
  13.     MsgBox "There was an error initializing the Report!  " & _
  14.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  15.     Exit Sub
  16. End Sub
Do you see what we are doing? We check to see if the filter is empty first. If not, we apply the filter to the Report. This will show only the data for that particular ECN.

Make sense?

This has saved me many haedachees and recoding of queries, and I personally think it's pretty easy to do, once you understand the basic principles behind it.
Aug 14 '18 #17

100+
P: 163
Ok I got the report part in place and ' blocked out from running.

I don't completely understand the "Remember to declare this Global String in a standalone VBA Module" part.

Do I need to create a new module and put this as the code in the module?
Aug 14 '18 #18

zmbd
Expert Mod 5K+
P: 5,397
Alternative to the global variable in the report is to use the tempvars collection and pass the value there...
Then query, report, macro, and vba can all access the value.

Really depends on how the report is structured. IMHO: The query should be such that it pulls the WHERE clause to restrict the records fed to the report instead of filtering the report along the lines of:

(I've pulled a lot out of this SQL indicated by (...))
Expand|Select|Wrap|Line Numbers
  1. SELECT t_testkit.pk_testkit, (...)
  2. FROM t_testYear INNER JOIN  (...)
  3. WHERE (((...)(t_testkit.fk_testYear) 
  4.    Like [TempVars]![ReportYears]))
  5. ORDER BY t_testinglaboratory.testinglaboratory_name, t_testkit.fk_testYear, t_testkit.fk_testmonth, t_samplingpoint.samplingpoint_name;
My code sets several [tempvars] that the query runs against which then feeds the report.

I've also done this directly as a report filter:
(simple example)



Simple code:
This is in my cancel button, it resets a couple of the tempvars so that if the report is open directly it will default to showing everything in query.
You'd use the same concepts here to set the member of the tempvars collection with the form's value.
Expand|Select|Wrap|Line Numbers
  1. Private Sub zctrl_btn_Cancel_Click()
  2. 'Temp Code
  3.   If [TempVars]![reportyears] & "" = "" Then
  4.     [TempVars].Add "ReportYears", "*"
  5.   Else
  6.     [TempVars]![reportyears] = "*"
  7.   End If
  8.   If [TempVars]![openreportfor] & "" = "" Then
  9.     [TempVars].Add "OpenReportFor", "3"
  10.   Else
  11.       [TempVars]![openreportfor] = 3
  12.   End If
  13.   DoCmd.Close acForm, Me.Name, acSaveNo
  14. End Sub
Attached Images
File Type: jpg 971227.JPG (65.0 KB, 1746 views)
Aug 14 '18 #19

twinnyfo
Expert Mod 2.5K+
P: 3,284
DJ,

The standalone Module would be as simple as this:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private gstrFiler As String
In general, I agree with Z's statement of restricting the records sent to the Report first, rather than making the Report filter.

There are ways of using a Global string instead of TempVars(). Either method works. You just have to remember to treat TempVars as an object and not a variable.

It comes down to how comfortable you are with each method (or personal preference).
Aug 14 '18 #20

100+
P: 163
I created a module and Named it ReportFilter using this code:

Expand|Select|Wrap|Line Numbers
  1. Private gstrFilter As String
Then on the open event of the report I have:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3.     Dim gstrFilter As String
  4.  
  5.     On Error GoTo EH
  6.  
  7.     If Len(gstrFilter) <> 0 Then
  8.         With Me
  9.             .Filter = gstrFilter
  10.             .FilterOn = True
  11.         End With
  12.     End If
  13.  
  14.     Exit Sub
  15. EH:
  16.     MsgBox "There was an error initializing the Report!  " & _
  17.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  18.     Exit Sub
  19.  
  20. End Sub
And this is the Code for e-mailing:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnMail1_Click()
  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 gstrFilter As String
  11.  
  12. Set oOutlook = CreateObject("Outlook.Application")
  13. Set oMail = oOutlook.CreateItem(0)
  14.  
  15. strReportName = "New ECN Report"
  16.  
  17. DoCmd.OutputTo acOutputReport, "rptECN", acFormatPDF, CurrentProject.Path & _
  18.                "\" & strReportName & ".pdf", False, , , acExportQualityPrint
  19.  
  20. 'Retrieve all E-Mail Addressess in tblEMailAddress
  21. Set MyDB = CurrentDb
  22. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", dbOpenSnapshot, dbOpenForwardOnly)
  23.  
  24. With rstEMail
  25.   Do While Not .EOF
  26.     'Build the Recipients String
  27.     strEMail = strEMail & ![EmailAddress] & ";"
  28.       .MoveNext
  29.   Loop
  30. End With
  31. '--------------------------------------------------
  32.  
  33. With oMail
  34.   .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  35.   .Body = "Please review the attached ECN and complete any and all tasks that pertain to you."
  36.   .Subject = Replace(Replace("ECN# |1: |2", "|1", Nz([ECN#], "")), "|2", Nz([NewPN], ""))
  37.     .Display
  38.   .Attachments.Add CurrentProject.Path & "\" & strReportName & ".pdf"
  39.  
  40. End With
  41.  
  42. Set oMail = Nothing
  43. Set oOutlook = Nothing
  44.  
  45. rstEMail.Close
  46. Set rstEMail = Nothing
  47.  
  48. Dim aFile As String
  49. aFile = "N:\Unsecure Share\New ECN System\New ECN Report.pdf"
  50. If Len(Dir$(aFile)) > 0 Then
  51.      Kill aFile
  52. End If
  53.  
  54. End Sub
I got something wrong as it is trying to output all records of the report and not the one that's filtered on the Form. I have never done a global string or Tempvars so I don't really know how to do them correctly.

I use a Embedded Macro with a where condition of:

Expand|Select|Wrap|Line Numbers
  1. ="[ECN#]=" & Nz([ECN#],0)
This opens the form filtered to the ECN# I clicked on to view.
Aug 14 '18 #21

NeoPa
Expert Mod 15k+
P: 31,494
That should say (In Twinny's post.) :
Expand|Select|Wrap|Line Numbers
  1. Public gstrFilter As String
Aug 14 '18 #22

twinnyfo
Expert Mod 2.5K+
P: 3,284
Thanks Neo!

Yes - Public

Line 3 of your second block of code and line 10 of your third block of code should be deleted. Since you have declared it publicly, re-declaring it makes it local and it would only apply to the Report. It probably threw an error, because I had fat-fingered my previous instructions as "Private".

Also in your third block of code, you must set the Filter's value:

Expand|Select|Wrap|Line Numbers
  1. gstrFilter = "[ECN#]=" & Me.txtECN#
You could make this line 16.

Delete the embedded macro you described in your fourth block of code.

Unless I've missed something obvious, this should do it.

Believe it or not, your description of what is happening is exactly what we told it to do. But, this should fix it....
Aug 14 '18 #23

100+
P: 163
The embedded macro is there for a reason,It opens the form to the specific ECN# that needs to be reviewed for editing.

I made the changes you suggested but it wont compile...

Error says Method or data member not found. This is on the second block of code.

Expand|Select|Wrap|Line Numbers
  1. Public gstrFilter As String
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnMail1_Click()
  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.  
  11. Set oOutlook = CreateObject("Outlook.Application")
  12. Set oMail = oOutlook.CreateItem(0)
  13.  
  14. strReportName = "New ECN Report"
  15.  
  16. gstrFilter = "[ECN#]=" & Me.txtECN#
  17.  
  18. DoCmd.OutputTo acOutputReport, "rptECN", acFormatPDF, CurrentProject.Path & _
  19.                "\" & strReportName & ".pdf", False, , , acExportQualityPrint
  20.  
  21. 'Retrieve all E-Mail Addressess in tblEMailAddress
  22. Set MyDB = CurrentDb
  23. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", dbOpenSnapshot, dbOpenForwardOnly)
  24.  
  25. With rstEMail
  26.   Do While Not .EOF
  27.     'Build the Recipients String
  28.     strEMail = strEMail & ![EmailAddress] & ";"
  29.       .MoveNext
  30.   Loop
  31. End With
  32. '--------------------------------------------------
  33.  
  34. With oMail
  35.   .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  36.   .Body = "Please review the attached ECN and complete any and all tasks that pertain to you."
  37.   .Subject = Replace(Replace("ECN# |1: |2", "|1", Nz([ECN#], "")), "|2", Nz([NewPN], ""))
  38.     .Display
  39.   .Attachments.Add CurrentProject.Path & "\" & strReportName & ".pdf"
  40.  
  41. End With
  42.  
  43. Set oMail = Nothing
  44. Set oOutlook = Nothing
  45.  
  46. rstEMail.Close
  47. Set rstEMail = Nothing
  48.  
  49. Dim aFile As String
  50. aFile = "N:\Unsecure Share\New ECN System\New ECN Report.pdf"
  51. If Len(Dir$(aFile)) > 0 Then
  52.      Kill aFile
  53. End If
  54.  
  55. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3.     On Error GoTo EH
  4.  
  5.     If Len(gstrFilter) <> 0 Then
  6.         With Me
  7.             .Filter = gstrFilter
  8.             .FilterOn = True
  9.         End With
  10.     End If
  11.  
  12.     Exit Sub
  13. EH:
  14.     MsgBox "There was an error initializing the Report!  " & _
  15.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  16.     Exit Sub
  17.  
  18. End Sub

Attached Images
File Type: jpg Enbedded macro.jpg (193.1 KB, 1715 views)
Aug 14 '18 #24

twinnyfo
Expert Mod 2.5K+
P: 3,284
Error says Method or data member not found. This is on the second block of code.
Which specific line is highlighted with this error?
Aug 14 '18 #25

100+
P: 163
gstrFilter = "[ECN#]=" & Me.txtECN#
Aug 14 '18 #26

twinnyfo
Expert Mod 2.5K+
P: 3,284
This is on your Form? You have a control named "txtECN#" (see your Post #16)?

txtECN# should be the name of the Control that has the ECN# in it. You could also use Me.ECN#.

Please keep in mind that every time I write that control/field name a shiver runs up my spine.

That pound sign ("#") could be causing some problems--I think someone might have mentioned that a while back....


#itsapoundsign

:-D
Aug 14 '18 #27

100+
P: 163
Ok, I went in and changed ECN# to ECNID...

I'm still getting an error on this part:

Expand|Select|Wrap|Line Numbers
  1. gstrFilter = "[ECNID]=" & Me.ECNID
Aug 14 '18 #28

twinnyfo
Expert Mod 2.5K+
P: 3,284
DJ,

Do you have a split form with the datasheet on the bottom or do you have a form with subform in datasheet view on the bottom. This is probably where it is being hung up.

Wherever you are getting your ECNID must be where you get the same ECNID for your Filter.

Me.ECNID and Me.txtECNID always assume a form which moves through records and the current record holding the value you can use. Apparently you are not doing it that way and the main from is not bound to that same set of data.

I hope I am making sense. But you need to assign the ECNID to the Filter--but I don't know where you are getting the ECNID from.
Aug 14 '18 #29

100+
P: 163
ECNID is on my form, but it is located in the Header. Could this be the issue?
Aug 14 '18 #30

100+
P: 163
Dang it, Now I'm having issues with my reports. It keeps Telling me to:
Enter Parameter Value ECN#

But I change these in everything. Tables open up fine, queries open up fine, Forms open up fine, but not any of the reports....I can feel my hair turning grayer...
Aug 14 '18 #31

twinnyfo
Expert Mod 2.5K+
P: 3,284
If it is in a control, then you can use it. just use Me.[Name of the Control] in your VBA.

Do you understand that you have to get the value of ECNID into the Filter. Since I don't have your DB in front of me, I am unable to click around and determine your structure and the names of all your controls.

Understanding how to reference objects and controls in your VBA will great dividends for you in the long run.

Please forgive us if we take some of these things for granted.
Aug 14 '18 #32

twinnyfo
Expert Mod 2.5K+
P: 3,284
Somewhere in your report there is a refeerence to the ECN# instead of the ECNID. Is it being sorted by that field, perhaps? this will sometimes cause such an error even after you have changed all your controls.
Aug 14 '18 #33

100+
P: 163
I'm sending it to my house laptop so I can see if I can fix it with out any interruptions...
Aug 14 '18 #34

100+
P: 163
I think I got it working completely now. Once I went through all my reports and fixed the sorting and grouping, I gave the code a try as it is and it seemed to work. As a matter of fact I tried it multiple times and it worked every time.

Thanks twinnyfo, Its a good thing you are very patient, I can be tough to straighten out...
Aug 14 '18 #35

twinnyfo
Expert Mod 2.5K+
P: 3,284
That's awesome! I'm glad we could get this working for you.

On to the next project, eh?
Aug 14 '18 #36

Post your reply

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