423,867 Members | 3,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,867 IT Pros & Developers. It's quick & easy.

Store and send reports by customer number Access 2016

P: 62
I've looked through the site for this but nothing quite matches. I have an Access report for monthly accounts called rptAccount.
Each report is grouped by Customer code number, a 3 character number string (012) etc..
Is there a quick and easy (ish) way to send these accounts each month. They print separately but send as one big report. That is fine for archive. At the moment I have to go to Print Preview, save each report page as a pdf which requires about 6 clicks, then I have to go back to the archived pdfs and 'send' to email recipient. It's a bit long winded because there are usually 30+. Thanks for any help, macro if possible, not very conversant with VBA.
Mar 9 '18 #1
Share this Question
Share on Google+
58 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,720
TrevorJ,

Welcome to Bytes!

What you are asking is very simple. There are two parts: the first part is to simply filter the report by the Customer Code; the second part is to export each of those filtered reports as a PDF.

All of this is readily available using VBA.

Have you made any attempts at doing this yourself? Please post your attempted code.
Mar 11 '18 #2

P: 62
Many thanks for you welcome and taking the time to look at my question. As I said I'm not very good with VBA although I am doing an online course. I have found some snippets on the forums and started to modify them but I'm not sure where to put the recordset, how to reference the email addresses in my tblCustomers.email and so on. This is what I've got so far;

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command#_Click()
  2. DoCmd.OpenReport "rptAccount"
  3. Dim stReport As String
  4. Dim stSubject As String
  5. Dim stEmailMessage As String
  6. Dim stCaption As String   'not sure what caption this is
  7. Dim myPath As String
  8.  
  9. stEmailMessage = "Your account from LPS for this month is attached."
  10. stSubject = "Monthly account"
  11. stReport = "tblAccount.rptAccount"
  12.  
  13. stCaption = "Send Account")
  14. myPath = "C:\DocumentsLaura_Access\LPS\Access Database\"
  15.  
  16. DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""
  17. DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , ,
  18. 'this would be a different path to that defined above, i.e. not in 'the database
  19. DoCmd.CloseReport

So not sure where to put the part that loops through the rptAccount.Cuscode until it finds no more records. Cuscode by the way is a 3 letter character field in tblCustomers, tblAccount and rptAccount.
Regards,
Trevor.
Mar 12 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 2,720
Trevor,

Thanks for the additional information.

First, one of the requirements on this forum is to use code tags whenever you post code to the site. All you have to do is simply highlight your code and click the [CODE/] button in your editor. I've done that for your code above.

Second, I want to begin by saying that I would rather teach you proper (or better) ways to do things, from the perspective that if I can teach you how to do something well (and you understand what you are doing), you are better off in the long run. So, I will make some comments and recommendations about your code which may not particularly answer your question, but may help you understand things better.

You say you are a novice in VBA--we've all been novices at one point, but we all keep learning! On to your code!

1. When posting procedures, it is helpful to us that you post the entire procedure from "Private Sub XXXX()" through the end "End Sub". You may feel free to delete irrelevant code, but this lets us know you have an initial procedure built that we are trying to troubleshoot for you.

2. I want to encourage you that your procedure Command#_Click() demonstrates that you are at least thinking about what needs to be done. I would recommend, however, that the for the naming convention of the controls on your forms (the Command Button Command#, in this case) refrain from using special characters, if at all possible. Also, that you have a standard "naming convention" for each type of object you name. For example a Command Button would be named "cmd[Description]", Text boxes could be named "txt[Description]". In your particular case, your Command Button could be named "cmdSendReports". I don't endorse any particular naming convention, only that you have one--and stick with it. This is important when it comes to the default naming convention that MS Access uses for your field controls, as the Field CustomerName from a Table, when added to a Form as a Text Box control will have the name "CustomerName." Then, when you refer to "CustomerName" in your VBA behind that form, technically speaking, you are being ambiguous, because the VBA doesn't know whether Me.CustomerName means the Control named "CustomerName" or the underlying Field named "CustomerName." This does not always (or even usually) cause problems, but it can. Again, this is stuff I've learned over the years and pass along to you.

3. When building your code, think through what you want to do. Again, it looks like you've tried to do that. Here is how I would approach your particular issue:
  • I need a list of Customer Codes
  • I need a list of e-mail addresses
  • I need to either a) save the reports to PDF first, then send them, or b) send those reports as PDFs (either way would work)

So, in my first post, I recommended that you filter your reports, which is a built in feature of Reports. However, as you play around with the OutputTo and SendObject methods in Access, you will see that filtering at runtime is not an option here. If you were simply opening a Report (using the OpenReport method) this would be available. So, how do you do that? I will show you a little trick that you can use across your projects.

First, you need to have a Filter available to your Report based upon the Customer Code. This Filter will be a text string in the form of CustomerCode = 012. It is unclear from your original post if your Customer Codes are true number (as they have leading zeroes) or text strings. If numeric, the above example will work. If text, it will look like this: CustomerCode = '012'. Formatting and syntax is everything!

So, why not declare a variable that can be accessed from anywhere in the DB? to do this, you add a statement declaring this variable right under your top lines of the VBA module for your Form:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public gstrReportFilter As String
This tells the DB that there is a variable that will be available all the time (the Public keyword), named gstrFilter (g, designating "global"; str, designating to you that it is a text string; ReportFilter, telling you what the string is for; and As String, telling the DB what kind of values are stored in this variable. Make sense so far?

The only thing you will have to change is adding a small procedure to your Report like such:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. On Error GoTo EH
  3.  
  4.     If Len(gstrReportFilter) <> 0 Then
  5.         With Me
  6.             .Filter = gstrReportFilter
  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
This code tells the DB that whenever this report is opened, it will look for a filter. If the filter contains information, then filter the report, otherwise, open it with all records.

Now, you may be a bit disappointed here, but I am not going to give you the anser to this, but I will guide you through working toward a solution--again, so that you understand what you are doing. Let's give you a skeleton of your code that you will use.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSendReports_Click()
  2. On Error GoTo EH
  3.     Dim stReport        As String
  4.     Dim stSubject       As String
  5.     Dim stEmailMessage  As String
  6.  
  7.     'Set the Filter to empty to start out
  8.     gstrReportFilter = ""
  9.  
  10.     'Set the Report Name
  11.     stReport = "tblAccount.rptAccount"
  12.     stEmailMessage = "Your account from LPS for this month is attached."
  13.     stSubject = "Monthly account"
  14.  
  15.     'Get a list of Customer Codes and e-mail Addresses
  16.  
  17.     'Loop through this list, one at a time
  18.  
  19.         gstrReportFilter = "CustomerCode = " & 'Your Customer Code
  20.         DoCmd.SendObject acSendReport, stReport, _
  21.             acFormatPDF, "You need e-mail addresses", , , _
  22.             stSubject, stEmailMessage, True
  23.  
  24.     'Go to the next record
  25.  
  26.     'Reset your filter so you can open the Report normally
  27.     gstrReportFilter = ""
  28.  
  29.     Exit Sub
  30. EH:
  31.     MsgBox "There was an error sending the Reports!  " & _
  32.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  33.     Exit Sub
  34. End Sub
Again, I want to point out that little in the code you provided has changed. I added erorr handling (which is a habit all good DBAs should get accustomed to). At this point I did not "solve" your problem, and that is intentional.

Your next step is to work through "getting alist of customer codes and e-mail addresses". My guidance to you is to create a recordset based upon the table that contains that information. Give it your best shot, test it, and let me know what kinds of errors (if any) you receive. I'll be glad to help troubleshoot with you.

This is much longer than you (or I expected), but I remember being in your shoes wishing I had someone to show me how to do things, and not just what to do.

Hope this hepps!
Mar 12 '18 #4

P: 62
Hello twinnyfo,
Thanks for your answer. I welcome your approach so long as you're prepared to tolorate a slow learner.
I get it re. the code window.
When I post my next offering I will include the whole procedure, I also accept your recommendations about naming. I did actually mean Command(whatever)_Click() but I'll be more precise. Working through your post, I don't exactly know what I should be naming items in different places, so can you explain if I had a table field called FirstName in my Customer table, I would refer to it as tblCustomers.FirstName correct? Would I use square brackets in VBA, and with both parts?
When I wanted to place a control on a report I have used the same name for several places where I wanted that value, is that o.k?
Moving on, I think that it would be better if we start with creating and saving the reports as PDFs first. It may well be that they will be checked in this form and sent later. I undertand the logic in the list though.
When you say about a global filter, I understand what you've written about what the filter says, but not why we need a filter, or is it to later filter out each record as we go through?
The customer codes are short text and I understand that they must be referred to in double quotes. Regarding your tip for a Public declaration of the strReportFilter, I can see that. Why do we put (Cancel As Integer) after Private Sub Report_Open? So everything makes sense so far, except for the above. Can I leave it there for your comments, and I'll move on in the meantime?
Thank you again,
Trevor.
Mar 12 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 2,720
I'll try to answer your questions in order, as best as I can:

Working through your post, I don't exactly know what I should be naming items in different places, so can you explain if I had a table field called FirstName in my Customer table, I would refer to it as tblCustomers.FirstName correct?
tblCustomers is a standard naming convention. "FirstName" is a good field name, but "Name" is not a good field name, because it is a reserved word. When you build a Query in the Query Builder, it always refers to fields explicitly, including the Table Name, thus tblCustomers.FirstName is what you will see in queries. When building queries in VBA, it's not a bad habit to get into, but if there is only one table, then the following will work perfectly fine:

Expand|Select|Wrap|Line Numbers
  1. SELECT FirstName FROM tblCustomers;
Would I use square brackets in VBA, and with both parts?
Square brackets are required if there are spaces in the names of your tables/fields. Thus, brackets required for [Customers Table].[First Name], but not tblCustomers.FirstName.

When I wanted to place a control on a report I have used the same name for several places where I wanted that value, is that o.k?
You should be unable to have duplicate controls with the same name. But, you could have multiple controls with the same Record Source, but named differently. For example, you want the Customer ID on all four corners of your report. You could have four Text Boxes with the following names: txtCustIDTopRight, txtCustIDTopLeft, txtCustIDBotLeft, txtCustIDBotRight. Make sense?

Moving on, I think that it would be better if we start with creating and saving the reports as PDFs first. It may well be that they will be checked in this form and sent later. I undertand the logic in the list though.
No problem. The general outline for your procedure will stay the same.

When you say about a global filter, I understand what you've written about what the filter says, but not why we need a filter, or is it to later filter out each record as we go through?
A "filter" shows only particular records on a table, form or report. When you view your master report, you are seeing all your customer IDs. If we filter by one, particular Customer ID, we only see the information pertinent to that customer. Then, if we save it as a PDF, that customer's report can be mailed. This is why understanding filters is important in the long run. In your case, if we cycle through the Customer IDs, save each report with a unique name, and then send it as an attachment, we save your monthly efforts of repetition.

Why do we put (Cancel As Integer) after Private Sub Report_Open?
When you open a report, you have the option to "Cancel" if there is no data and it will close automatically. This is built into your Report via MS Access.

So everything makes sense so far, except for the above. Can I leave it there for your comments, and I'll move on in the meantime?
Yes. Standing by for further assistance.

Hope all this made sense.

BTW, I am exercising a bit of leeway with this thread. Typically, we don't allow multiple questions on a single thread, but I am hoping that due to the nature of this training opportunity, that your question will be viewed by other novices who will also benefit from these explanations. Other Mods are free to chime in and slap my knuckles with a ruler if I need to cease and desist.

:-)
Mar 12 '18 #6

P: 62
twinnyfo
Hello again,
I thought I would try to make a start with something (I thought) was simple and came unstuck straight away. I was just starting step 1 - saving the report. I know it will save the whole report in 1 at the moment but it's no good going on if I can't get this to work;
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Command0_Click()
  5. DoCmd.OutputTo acReport, "tblAccount.rptAccount", acFormatPDF, "C:\PDFs", True
  6. End Sub
  7.  
The error is 'can't find '|1'
I did a long internet repair on access last night and it's still the same now. The debug highlights the \ key after the C: as though I was holding down shift to get the vertical bar ! The folder C:\PDFs does exist.
Trevor.
Mar 14 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 2,720
You need a complete Path\Filename, not just a path.

Also, rememeber that you will want to save each file so that it is unique to each customer (i.e., "Cust ID 123 Report.pdf").
Mar 14 '18 #8

P: 62
Still the same!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Command0_Click()
  5. DoCmd.OutputTo acReport, "tblAccount.rptAccount", acFormatPDF, "C:\PDFs\cust001.pdf", True
  6. End Sub
  7.  
  8.  
It has to be something wrong with the path though because if I leave it blank, the browse for saving comes up, but whatever filename I use (with or without the .pdf I still get the same error message I have also tried it with quotes within quotes for the filename.
Mar 14 '18 #9

twinnyfo
Expert Mod 2.5K+
P: 2,720
Is "tblAccount.rptAccount" the name of the report? Or is it simply"rptAccount"?
Mar 14 '18 #10

P: 62
Yes, that was it thanks. I started off with just the "rptAccount" but put the tblAccount in when it didn't work. Then when you said complete path and filename I didn't try it without the tblAccount part.
At least I have started !
Mar 14 '18 #11

twinnyfo
Expert Mod 2.5K+
P: 2,720
So, we are making progress!!

Let's take another look at what we want our code to do:
  • I need a list of Customer Codes
  • I need a list of e-mail addresses
  • I need to either a) save the reports to PDF first, then send them, or b) send those reports as PDFs (either way would work)
You have the basics of the last step down, although we will have to make some modifications so you can save each customer's report as an individual report that you can then send via-email.

The first two are what we must work on, now. I can only presume that your Customers are in a Table. And hopefully, the Customer ID and the contact e-mail are in the same table? (Please say "yes").

If you've got this customers table, what is your code going to look like to get your Cutomer IDs and E-mail addresses?

I will begin by simply asking how familiar you are with working with Recordsets?--because that is how we are going to approach this solution. Again, this is more of a training session, so we will work through it step-by-step. Other novices will benefit from this process, I believe.
Mar 14 '18 #12

P: 62
Yes, the tblCustomers does have the email address field. At the moment I know I need a recordset to loop through, to find each report one at a time. I think I could use If Then Else, or While and While End. In English it's 'Start through the list, if the field is not empty, get the address, send the email (referring to the string's declared like subject etc.. When that's done it's return to the start until there is no longer a record (EOF).I will need the error handler that you mentioned before.
At the moment I can print the reports individually because they are grouped by Customer code so I assume I will 'call' the records in the report and customer table by that field. After that I 'm back to research but flushed with success that with your help, I can at least save the whole report!
Mar 14 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 2,720
Yes, you can save the entire report--just make sure gstrReportFilter = "".

So, your tasks:
  • Create a Recordset on the Customers Table
  • Using the Customer ID, create a string for the Path\FileName of that Customer's Report
  • Set the Report filter based upon the Customer ID
  • Save the Report
  • Using the e-mail address, send the Report as an attachment
Give it your best shot--even if you have to just go step by step without sending anything to see that each step works individually.

I'm still here to hepp!
Mar 14 '18 #14

P: 62
This is my offering at the end of today;
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Test_Click()
  5.  
  6.     Dim i As Integer
  7.     Dim db As Database
  8.     Dim rs As Recordset
  9.    ' Dim ccrs As Recordset
  10.     Set db = CurrentDb
  11.     Set rs = db.OpenRecordset("tblcustomers")
  12.  
  13. 'While Not rs.EOF
  14. For i = 0 To rs.RecordCount - 25
  15. Debug.Print rs.Fields("cuscode")
  16. Debug.Print rs.Fields("email")
  17. rs.MoveNext
  18. Next i
  19.     'DoCmd.OpenReport "rptAccount", acViewReport, acFormatPDF, 'rs.Fields("cuscode")
  20.     'DoCmd.SendObject acSendReport, , acFormatPDF, rs!email, , , "Your monthly account", "Your account is attached", True
  21.     'DoCmd.OutputTo acOutputReport, "rptAccount", acFormatPDF, "C:pdfs\test.pdf", True
  22.  
  23.  
  24. 'DoCmd.SendObject acSendReport, , acFormatPDF, rs!email, , , stSubject, stEmailMessage, True, ""
  25. 'DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
  26.  
  27. 'Wend
  28. 'DoCmd.Close acReport, stReport, acSaveNo
  29.  
  30.     rs.Close
  31.     Set rs = Nothing
  32.     db.Close
  33. End Sub
  34.  
I have put some extra bits in that I think I'll need next. I understand up to the line after next i. I commented out the While and While End lines because I got mixed up in an endless loop !
The dim ccrs was to make another recordset for the cuscode field for the report but I don't know if I need it or whether it can all be done with the recordset that I have. What I do have works and gives me the cuscode and email address for each of the customer records so I'm happy so far.
I did move on to the email part but it only attaches the whole report and doesn't fill in or send to the email addresses. Note that I don't have Outlook.
I don't know exactly where to put the error handler.
Regards,
Trevor.
Mar 15 '18 #15

P: 62
Forgot to ask what the gstr was for, I assume it stands for global string, why global?
Mar 15 '18 #16

twinnyfo
Expert Mod 2.5K+
P: 2,720
Trevor,

Excellent! You are making progress. You are about 99% there. No, really!!

First, ask/answer a few questions:

The dim ccrs was to make another recordset for the cuscode field for the report but I don't know if I need it or whether it can all be done with the recordset that I have.
You have everything you need, as is. You will soon see.

I did move on to the email part but it only attaches the whole report and doesn't fill in or send to the email addresses.
Thisis because you haven't incorporated that Global Variable....

Note that I don't have Outlook.
How will you send e-mail using Access without Outlook? Office is a pretty standard suite of apps--I thought it came standard with an e-mail application.

I don't know exactly where to put the error handler.
An error handler tells the code what to do if it encounters an error of any type. For example, if you are working with mathematical calculations and try to divide a number by 0, that will cause an error. Without error handling, the code just stops (and breaks), which can cause your DB to crash in extreme cases, but it most likely will cause your app to hang or reset all your variables and a massive rift will develop in the intergalactic cosmic continuum vortex matrix (well, maybe not that). It's just bad. However, if you put error handling in all your procedures, if there is an error, you can "catch it" before it breaks anything and then tell the DB what to do. You can catch particular types of errors, too. If you are working with file management and a file is locked or not found, you can choose to skip that file or just exit your procedure.

Typically, error handling is the first line of my procedures, so that if anything goes wrong anywhere along the way, it takes care of it. But you can add different types of error handling at different parts of your procedures (more than you need to know now).

Because I am very lazy and don't like to type code repeatedly, I've created a small procedure (which does not have Error handling, by the way--shame on me!). I place it in an empty module. Then, in the immediate window of my VB editor, I type "ErrorSub" and it gives me the quick text for error handling. I copy and paste into my procedures and I am good to go!

Expand|Select|Wrap|Line Numbers
  1. Public Function ErrorSub()
  2. 'Just useful and quick way to add Error Handling to Code
  3.  
  4.     Debug.Print "On Error GoTo EH" & vbCrLf
  5.     Debug.Print "    Exit Sub"
  6.     Debug.Print "EH:"
  7.     Debug.Print "    MsgBox " & Chr(34) & _
  8.         "There was an error ....!  " & Chr(34) & " & _"
  9.     Debug.Print "        " & Chr(34) & _
  10.         "Please contact your Database Administrator." & _
  11.         Chr(34) & ", vbCritical, " & Chr(34) & "WARNING!" & Chr(34)
  12.     Debug.Print "    Exit Sub"
  13.  
  14. End Function
Please feel free to steal this code freely.

Forgot to ask what the gstr was for, I assume it stands for global string, why global?
Yes. Global. So you can access it from anywhere in the DB environment.

====================================

Now a couple minor critiques (but not necessarily "corrections") to your code.

Line 6: Dim i As Integer - no need for an integer counter. If you are using large recordsets, an integer will break after record 32,767. Additionally, it uses additional resources. You have the basic idea in your While statement (more to follow on that).

Lines 14-17: It "can" be very dangerous to begin working with recordsets without first verifying that there are definitely records in the set. Also, some think using the .EOF property is "good enough", and in 99% of the cases it is. However, there are cases in which it is not quite right. Also, using the .RecordCount property "may" or "may not" give you the number of records in the recordset. Again, for large batches, sometimes the DB will only load a portoin of the records, just to get things started. Then, it requires a .MoveLast, followed by a .RecordCount. However, .RecordCount guarantees that if it is 0, there are no records, anything else means there are at least some records--that becomes your validation.

Then, the best way to run through the records is While Not .EOF. This ensures you get all the records.

So, based on your excellent opening volley, here are some recommended changes, with some notes included for you:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'Remember this????
  5. Public gstrReportFilter As String
  6.  
  7. Private Sub Test_Click()
  8. On Error GoTo EH
  9.     Dim db          As Database
  10.     Dim rs          As Recordset
  11.     Dim strPath     As String
  12.     Dim strReport   As String
  13.     Dim strFilename As String
  14.     Dim strSubject  As String
  15.     Dim strMessage  As String
  16.  
  17.     strPath = "C:\pdfs\"
  18.     strSubject = "Your monthly account"
  19.     strMessage = "Your account is attached"
  20.  
  21.     Set db = CurrentDb
  22.     Set RS = db.OpenRecordset("tblcustomers")
  23.     'Save some typing and use "With rs"
  24.     With rs
  25.         'First, check to see if there are records
  26.         If Not .RecordCount = 0 Then
  27.             'Always move to the top!
  28.             .MoveFirst
  29.             Do While Not .EOF
  30.                 'This is stuff you already had--I'm not really adding anything
  31.                 Debug.Print !cuscode
  32.                 Debug.Print !Email!
  33.                 'Hey, you've got the cuscode, set the global variable
  34.                 'gstrreportfilter = (you fill in the rest.....)
  35.                 'Hey, you've got the cuscode, save the report
  36.                 'strReport = (you fill in the rest.......)
  37.                 strFilename = strPath & strReport
  38.                 DoCmd.OutputTo acOutputReport, "rptAccount", _
  39.                     acFormatPDF, strFilename
  40.                 'At this point, you've got the PDF
  41.                 'You've also got the e-mail address
  42.                 'Now just send the report
  43.                 DoCmd.SendObject acSendReport, _
  44.                     "rptAccount", acFormatPDF, _
  45.                     !Email, , , strSubject, strMessage, True
  46.                 RS.MoveNext
  47.             Loop
  48.         End If
  49.         .Close
  50.     End With
  51.     db.Close
  52.     Set rs = Nothing
  53.     Set db = Nothing
  54.  
  55.     Exit Sub
  56. EH:
  57.     MsgBox "There was an error sending the reports!  " & _
  58.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  59.     Exit Sub
  60. End Sub
Like I said, you are at the 99% point. But, I hope you've learned a few things......
Mar 15 '18 #17

P: 62
Thank you for your comments and code, I'm working on them now but it seems more like 20% to me !
Just a couple of quickies;
Do I put you EH code into mine under my Private Sub Test_Click() without your Function header and footer or do I put the whole thing above my Private Sub Test_Click() ?
I bought a standalone, no subscription version of Access 2016 so that's all I have, I've read somewhere that Access will use the default email app. which for these experiments is has. If I need to get Outlook, which version please?
Thanks.
Mar 16 '18 #18

twinnyfo
Expert Mod 2.5K+
P: 2,720
Do I put you EH code into mine under my Private Sub Test_Click() without your Function header and footer or do I put the whole thing above my Private Sub Test_Click() ?
See how my code has the error handling? Just like that. Please note the last six lines:

Expand|Select|Wrap|Line Numbers
  1.     Exit Sub
  2. EH:
  3.     MsgBox "There was an error sending the reports!  " & _
  4.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  5.     Exit Sub
  6. End Sub
You want your Code to exit when it gets done, prior to going to EH: Otherwise, your code will "say" you are getting an error, even though there is none.


I bought a standalone, no subscription version of Access 2016 so that's all I have, I've read somewhere that Access will use the default email app. which for these experiments is has. If I need to get Outlook, which version please?
I knew that Access would use your default e-mail--my concern was that you were using a browser version of G-Mail or something. You should be fine.

---------------

Your mission, should you choose to accept it, is to complete lines 34 and 36 above (they'll have to be uncommented in your code, as well). That's it, brother!

Let me know how it works!
Mar 16 '18 #19

P: 62
I'm sorry, I knew I was further away than you thought. I seem to have tried a million things over 2 days and got nowhere, except that the error message and warning bell work - hooray !
I haven't even started with the email part because there seems little point at this stage.
I think I've tried every combination of rpt account and cuscode possible so I don't now even know if that's what gsttrreportfilter should be.
So I've left it just to save all the reports under the incrementing customer codes but of course without a filter it saves every report to each cuscode.
I know there might be a wend missing but I've never let it get that far.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public gstrReportFilter As String
  5.  
  6. Private Sub Test_Click()
  7. On Error GoTo trouble
  8.  
  9.     Dim db As Database
  10.     Dim rs As Recordset
  11.     Dim strPath     As String 'Path to store pDFs
  12.     Dim strReport   As String 'Name of report
  13.     Dim strFilename As String 'Name of file to store
  14.     Dim strSubject  As String 'Email subject Your monthly...
  15.     Dim strMessage  As String 'Email message Your account is attached
  16.  
  17.     strPath = "C:\pdfs\"
  18.     strSubject = "Your account"
  19.     strMessage = "Your monthly account is attached"
  20.  
  21.     Set db = CurrentDb
  22.     Set rs = db.OpenRecordset("tblcustomers")
  23.  
  24.     With rs
  25.         If Not .RecordCount = 0 Then
  26.             .MoveFirst
  27.             Do While Not .EOF
  28.                 Debug.Print !Cuscode
  29.                 Debug.Print !Email
  30.                 'gstrReportFilter = "rptaccount" & !Cuscode
  31.                 Debug.Print gstrReportFilter
  32.                 strReport = "Account " & !Cuscode & ".pdf"
  33.                 strFilename = strPath & strReport
  34.                 DoCmd.OutputTo acOutputReport, "rptAccount", acFormatPDF, strFilename
  35.                 rs.MoveNext
  36.             Loop
  37.         End If
  38.         .Close
  39.     End With
  40.     db.Close
  41.     Set rs = Nothing
  42.     Set db = Nothing
  43.  
  44.     Exit Sub
  45.  
  46. trouble:
  47.     MsgBox "There was a problem handling the reports" & ", please contact your Dad", vbCritical, "WARNING!"
  48.     Exit Sub
  49. End Sub
I totally understand if you want to just recommend I go away and study for a year, and then come back.
Regards Trevor.
I thought I knew what frustration was until I tried this.
Mar 18 '18 #20

NeoPa
Expert Mod 15k+
P: 31,123
Hi Trevor.

You'll find most of us here understand what frustration is and are happy to help as much as we can practically. For now let me just give some general advice and leave the details to TwinnyFo, who's already doing a fine job.

When you come across a problem that's related to logic or programming try to break it down into components that are small enough to deal with in one go. Think of a champion eater. They won't generally stuff everything in their mouth at the get go. They'll approach the mountain of food one small mouthfull at a time.

This works well for development of projects too. Get one bit fully working and understood before moving along to the next. It's always tempting to think you can handle it all in one go and get all parts right first off. As the years go by you may find this works more and more reliably, but even now I find myself having to step back from a project and break it down into smaller chunks so that I can identify the area which I've got wrong.

Be disciplined. Work on one item at a time until you have it working reliably. Then, and only then, move along to the next one in the sure knowledge that this last one won't let you down; won't catch you from behind when you're facing the other way.

A slower and more deliberate approach can seem slow when you're doing it, but often gets you to the finishing post earlier than the hasty one.

Another helpful thing to know about when developing of course, is how to use the Debugging in VBA features to find your precise problems.

Good luck with your project.
Mar 19 '18 #21

twinnyfo
Expert Mod 2.5K+
P: 2,720
@NeoPa: Master, this young Padawan seeks the greater powers of the force. I still think he has what it takes to become a Jedi Master. I will continue to train him!

@TrevorJ: But you are close! And, at least the error message and warning bell work!

When you ran your code, did you see anything listed in your Immediate window? You may notice lines 28, 29 and 31 above. The Debug.Print command is given to display the values of the vairables we have sent it. As your code is written, it should have displayed the CustCode and the Email address for each customer. I am hoping it did.

However, in line 31, I am guessing that it displayed a blank line. This is because Line 30 is still commented out (there is a single quote (') leading the line). So.... Even though you may have wanted to change teh strReportFilter value, you have not--it remains the empty string ("").

Now, even if you un-comment this line of text (remove the single quote), I have a hunch it will still work incorrectly, as you have not created a "filter". Go all the way back up to Post #4, third block of code, line 19: this is the format for your Filter. I do not know what the field name is for your Customer Code, so it was assumed at that point. Just make a text string including the field by which you want to filter, and set it equal to the record set's customer code.

Keep in mind, that all of these field names have been assumed and presumed, based upon previous posts. Based upon an educated guess, if all things have been presumed correctly, that you actually have a folder called "C:\PDFs\", that all the field names are accurate, fixing line 30 will solve your problem.

This is how close you are, and the solution is based mainly upon what you had already developed, with a small additive of the global filter.

NeoPa's advice is noteworthy. Whenever creating a more complex piece of code, work one aspect at a time. In our case, we worked getting the list of cutomers and e-mail addresses; then we work at creating the reports properly. Then, all we have to do is send those reports in e-mail. When each successive piece works properly, move on to the next.

Code well, young Jedi!
Mar 19 '18 #22

P: 62
Thank you NeoPa, I will certainly bear your words in mind.
Trevor.
Mar 19 '18 #23

P: 62
Hi again,
Yes it did display the cuscode and email addresses although the code I left it with is only one of many, many tries. Later I was trying Docmd.OpenReport etc. and using "rptAccount" as the report name and the error was something like "Expected statement at end of line". I did include the WHERE statement without the WHERE, if I recall properly it was "Cuscode =" & !Cuscode. I tried with all combinations of ""s etc. that I could think of.
I have one problem that I do think is affecting my progress (got to blame something other than me) and that is my VBE doesn't work properly and I suspect it's because I am using an HP laptop and they mess about with the F keys so all I get when I use F8 F5 or F4 is a lovely ding. I've tried changing the settings in the BIOS to little effect. Consequently I can't step into or try the code line by line, not with the function keys or with the menu. All I can do is Compile, which doesn't seem much use except to pull out bad syntax (I've perhaps worn it out) or save and F11 (that works) close and open the form and click every time.
I know I left a lot commented out, but nothing I hadn't tried I think, I say I think because my brain was fried by last night. I also tried a lot of things directly, by-passing the string and trying to just define the filter, rather along the lines that NeoPa was suggesting. Indeed the debug.print gstrreportfilter gave a blank line, and I got plenty of yellow ones with which I couldn't do much without F keys.
I know that I am supposed to be using the filter string to find the one report from the bunch with a matching cuscode to that one extracted from the recordset as we loop through. So the recordset moves to the next line, finds the cuscode and email address, then the filter would use the report's cuscode to find the correct report, store it as a (PDF) and then send it and start again.

There is indeed a folder C:\PDFs and what I have so far stores the (entire) report there, named as I would require, along with the cuscode for each batch of reports, not just the one that I need the filter to work for.
I've read through the whole post again so I'll try further.
Thanks both,
Trevor
Mar 19 '18 #24

twinnyfo
Expert Mod 2.5K+
P: 2,720
So, take your code is Post #20. Replace Line 30 with

Expand|Select|Wrap|Line Numbers
  1. gstrReportFilter = "Cuscode = " & !Cuscode
Tell me what happens. Like I said, you have the answer. But something is not quite right when you add all the pieces together......
Mar 19 '18 #25

P: 62
I have removed all the commented lines for clarity. Again I've tried as many perms as I can think of, this is my latest;
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Public gstrReportFilter As String
  4.  
  5. Private Sub SendAccounts_Click()
  6. On Error GoTo trouble
  7.  
  8.     Dim db As Database
  9.     Dim rs As Recordset
  10.     Dim strPath     As String 'Path to store pDFs
  11.     Dim strReport   As String 'Name of report to save and send
  12.     Dim strFilename As String '
  13.     Dim strSubject  As String 'Email subject Your monthly...
  14.     Dim strMessage  As String 'Email message Your account is attached
  15.  
  16.     strPath = "C:\pdfs\"
  17.     strSubject = "Your account"
  18.     strMessage = "Your monthly account is attached"
  19.  
  20.     gstrReportFilter = ""
  21.  
  22.     Set db = CurrentDb
  23.     Set rs = db.OpenRecordset("tblcustomers")
  24.  
  25.   With rs
  26.     If Not .RecordCount = 0 Then
  27.         .MoveFirst
  28.         Do While Not .EOF
  29.         Debug.Print !Cuscode
  30.         Debug.Print !email
  31.  
  32.  
  33. gstrReportFilter = "CusCode = " & !Cuscode
  34. Debug.Print gstrReportFilter
  35. strReport = "Account " & !Cuscode & ".pdf"
  36. strFilename = strPath & strReport
  37.  
  38.     DoCmd.OpenReport "rptaccount", acViewNormal, , gstrReportFilter
  39.     DoCmd.OutputTo acOutputReport, "rptAccount", acFormatPDF, strFilename, False
  40.         rs.MoveNext
  41.     Loop
  42.     End If
  43.     .Close
  44.     End With
  45.     db.Close
  46.     Set rs = Nothing
  47.     Set db = Nothing
  48.  
  49. Exit Sub
  50.  
  51.  
  52. trouble:
  53.     MsgBox "There was a problem handling the reports" & ", please contact your Dad", vbCritical, "WARNING!"
  54.     Exit Sub
  55. End Sub
  56.  
To confirm, my table is tblAccount, report is rptAccount. The report has a control called cuscode and it is used to group the reports so that it prints separate reports. I have 7 Accounts, 7 reports but still it saves 7 reports in the path, each with all 7 reports in.
When I run the code, the debug.print line reveals the cuscode, the email address and then cuscode = 001 etc. (the gstrreportfilter).
Trevor.
Mar 19 '18 #26

twinnyfo
Expert Mod 2.5K+
P: 2,720
Are you sure that you have the filtering code in the on open event of your report? See post #4, code block #2......
Mar 19 '18 #27

P: 62
Good afternoon,
Do you mean the
Expand|Select|Wrap|Line Numbers
  1.   If Len(gstrReportFilter) <> 0 Then
  2.         With Me
  3.             .Filter = gstrReportFilter
  4.             .FilterOn = True
  5.         End With
  6.     End If
If so I haven't got it anywhere ! I don't know why but I thought it had been superseded.
I do have the declaration of gsrtReportFilter as string, directly under Option explicit. If I need the above code, where would I put it because if I set gstrReportFilter to "" (my line 20 on previous post) to start with, it doesn't seem to makes sense to me either above or below that. But then what do I know?
T.
Mar 20 '18 #28

twinnyfo
Expert Mod 2.5K+
P: 2,720
Trevor,

Please re-read Post #4 very carefully. Part of learning is sticking with the details.
Mar 20 '18 #29

P: 62
O.k. I've looked at that yet again and I'm sorry I didn't realise that it was to go in the OnLoad procedure. I hope that I'm now understanding 'The only thing you will have to change is adding a small procedure to your Report like such:', and after the code window 'This code tells the DB that whenever this report is opened, it will look for a filter.'
There was another module for OnLoad so I've deleted that. So now I have the object that we're working with (under 'Microsoft Office class objects', which is called 'Report_RptAccount') and I have made a Module1 which is called from the OnLaad event of the report. It doesn't make any difference as yet when I open the report and click on the button named cmdSendAccounts but when I do, a Compile check it comes up with 'Invalid use of Me keyword' error. I hope this is what you wanted me to do so far.
Mar 20 '18 #30

twinnyfo
Expert Mod 2.5K+
P: 2,720
Step By Step
  1. Open Your DB
  2. Right Click on rptAccount
  3. Click Design View
  4. Double-Click the small black square in the upper left hand corner of the report in design view to bring up the properties
  5. Click the Event Tab of the Properties Window
  6. Put your Cursor in the On Open field
  7. Single click the ellipsis ("...")
  8. This should take you to your VB Editor
  9. Copy and paste Post #4, Code block 2

No need to create new/alternate modules. Each report has a module that is used specifically for that report.
Mar 20 '18 #31

P: 62
O.k. I've now done that, clicking on the top left square on anything doesn't work on mine so I have to just open the properties sheet. So the 'filter' procedure is now on the same page as the rest of the code but under it. I had got it in a separate module which I now know is incorrect. No error message now.
If my first line of the extract here is correct and shouldn't have quotes around the !CusCode, is the DoCmd line correct please?

Expand|Select|Wrap|Line Numbers
  1. gstrReportFilter = "CusCode = " & !Cuscode
  2. Debug.Print gstrReportFilter
  3. strReport = "Account " & !Cuscode & ".pdf"
  4. strFilename = strPath & strReport
  5. DoCmd.OpenReport "rptaccount", acViewNormal, , gstrReportFilter    
  6.  
Thanks.
Mar 20 '18 #32

twinnyfo
Expert Mod 2.5K+
P: 2,720
So the 'filter' procedure is now on the same page as the rest of the code but under it.
What other code is on your Report? Unless there is something else that you are working through on this report, the code from Post 4 should be the only thing in that Report's Module. It should not be in the same module that youa re using on your form.

If my first line of the extract here is correct and shouldn't have quotes around the !CusCode, is the DoCmd line correct please?
Just tom confirm, your CusCode is, in fact, a number, not text, formatted as a number? If it is, in fact, a number, then all should be well.

DoCmd.OpenReport "rptaccount" ....
There should be absolutely no reason to "Open" your report. Just save it to PDF and send it. Unless there is some critical reason to open it. However, if you have this code running, you will probably encounter problems trying to open multiple instances of this same report, but filtered differently.

Line 5 above should simply be:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "rptAccount", acFormatPDF, strFilename, False
Mar 20 '18 #33

P: 62
I probably couldn't misunderstand better if I tried and I am sorry.

By 'Filter procedure' I mean the block of code that starts;
If Len(gstrReportFilter) <> 0 Then...
I have done what you said so as I did that, I got a new subreport UNDER the code that we have been working on. I have put the 'If Len(gstrReportFilter) <> 0 Then...' code there. I can see that this bit of code looks to see if there is any data in gsrtReportFilter, but then I don't see why almost the first thing we do when we click the button is set gstrReportFilter to null 'Set the Filter to empty to start out
gstrReportFilter = ""

Anyway, all of this is now under Microsoft Access Class Objects and called Report_rptAccount in the Project list in the VBE. All of the code we have worked on is now there on that same page in the VBE. I hope this is correct.

I did say in #3 that Cuscode is a character field, and later in #5 'The customer codes are short text', could this prove to be our problem?

I tried the Docmd.OpenReport because it was the only one I could find where the gsrtReportFilter would fit.

Hasta Mañana
Mar 20 '18 #34

twinnyfo
Expert Mod 2.5K+
P: 2,720
#1 - My oversight on CusCode being text, rather than number. Your Filter should look like this:

Expand|Select|Wrap|Line Numbers
  1. gstrReportFilter = "CusCode = '" & !Cuscode & "'"
By 'Filter procedure' I mean the block of code that starts....
There is no "filter procedure". It is the "OnOpen" procedure of your report. This should be the only code in the module attached to your report. It is unclear to me (so I don't say this to insult, but to clarify) as to whether you fully understand that each Form and Report can have its own VBA module. This is so that each object can have code that only executes within the confines of that particular object. So, when we create a procedure in the reports VBA mdule called Report_Open, that report (and only that report) knows to execute this code when the Report is opened--no matter how it is opened. This is why we check the length of the variable gstrReportFilter when the report opens. If it has no length (which we set on the form right away), then the report will generate all records. If there is a filter set (which we do on the form, cycling through the customers), then the report only displays the records that meet those criteria. I hope this makes sense. If not, we can discuss further. But, it is a basic understanding of how objects work in Access.

I have done what you said so as I did that, I got a new subreport UNDER the code that we have been working on. I have put the 'If Len(gstrReportFilter) <> 0 Then...' code there.
I have no idea what this means. You may have mis-typed with "subreport", as a subreport cannot be on a VBA module. Again, this should be all alone on the report's VBA module--nothing else.

At this point, I know of nothing more that I can give that I haven't already given you. Everything listed below is what we have talked about above in this thread. Your entire procedure in your form should look like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Test_Click()
  2. On Error GoTo EH
  3.     Dim db          As Database
  4.     Dim rs          As Recordset
  5.     Dim strPath     As String
  6.     Dim strReport   As String
  7.     Dim strFilename As String
  8.     Dim strSubject  As String
  9.     Dim strMessage  As String
  10.  
  11.     strPath = "C:\pdfs\"
  12.     strSubject = "Your account"
  13.     strMessage = "Your monthly account is attached"
  14.     strFilename = strPath & "All Accounts.pdf"
  15.  
  16.     gstrReportFilter = ""
  17.     DoCmd.OutputTo acOutputReport, _
  18.         "rptAccount", acFormatPDF, strFilename
  19.  
  20.     Set db = CurrentDb
  21.     Set rs = db.OpenRecordset("tblcustomers")
  22.     With rs
  23.         If Not .RecordCount = 0 Then
  24.             .MoveFirst
  25.             Do While Not .EOF
  26.                 Debug.Print !Cuscode
  27.                 Debug.Print !Email
  28.                 gstrReportFilter = "CusCode = '" & !Cuscode & "'"
  29.                 Debug.Print gstrReportFilter
  30.                 strReport = "Account " & !Cuscode & ".pdf"
  31.                 strFilename = strPath & strReport
  32.                 DoCmd.OutputTo acOutputReport, _
  33.                     "rptAccount", acFormatPDF, strFilename
  34.                 DoCmd.SendObject acSendReport, _
  35.                     "rptAccount", acFormatPDF, _
  36.                     !Email, , , strSubject, strMessage, True
  37.                 RS.MoveNext
  38.             Loop
  39.         End If
  40.         .Close
  41.     End With
  42.     db.Close
  43.     Set rs = Nothing
  44.     Set db = Nothing
  45.  
  46.     Exit Sub
  47.  
  48. EH:
  49.     MsgBox "There was an error sending the reports!  " & _
  50.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  51.     Exit Sub
  52. End Sub
Please try this. If you encounter any errors or unexpected results, let me know.
Mar 21 '18 #35

P: 62
Hi,
Thanks for the clarification and please don't worry about insults, I am a novice.
I do understand that we are actually talking of an "OnOpen Event Procedure". The other code that I refer to is an "OnClick Event Procedure" and I was wanting to clarify that both of these procedures are on one page of VBA code, separated into two distinct procedures. When I said subreport, that was indeed a typo, I meant that the OnOpen procedure was a 'sub' procedure and appeared underneath the OnClick 'sub' procedure that we were working on, and that you list above in post #35.
What I was unsure about was whether this vba page of code was in the correct place because it is as I said, a 'class object' called 'Report_rptAccount' and it doesn't mention the word module in the project window, even though there are the options to insert a 'module' or 'class module' which are terms that you refer to. Your comment '...this should be all alone on the report's VBA module--nothing else' confuses me because the module that isn't called a module, also has the OpenClick event.

I hope that I am correct in this explanation of my understanding. I do understand better now though, the need to put the code which checks the Length of the gstrReportFilter string in the OnOpen procedure, thank you.

On the maybe naive assumption that I do understand this, I will move on and make the alterations on the Cuscode quotation marks.
Mar 21 '18 #36

twinnyfo
Expert Mod 2.5K+
P: 2,720
The other code that I refer to is an "OnClick Event Procedure" and I was wanting to clarify that both of these procedures are on one page of VBA code, separated into two distinct procedures. When I said subreport, that was indeed a typo, I meant that the OnOpen procedure was a 'sub' procedure and appeared underneath the OnClick 'sub' procedure that we were working on, and that you list above in post #35.
This is what I was afraid of.

Just for your info, I almost never use the object browser or browse my list of Microsoft Access Class Objects, so your statments are moot, mainly because I can't see which code is going where. This is not to say that you shouldn't use the object browser or browse your list of Microsoft Access Class Objects. However, at the top of your VBA editor (the window title bar) should say the following at the end of the title: [Report_rptAccount (Code)]. If it does not, then go back to Post #31 and follow those steps until your title bar does say that. If it already does, then that "page of code" (your module) should have only the following in it:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Report_Open(Cancel As Integer)
  5. On Error GoTo EH
  6.  
  7.     If Len(gstrReportFilter) <> 0 Then
  8.         With Me
  9.             .Filter = gstrReportFilter
  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. End Sub
This should be all the code that is on that page (module) for that report. Save and close the report.

======================
  1. Right Click on the Form you are using to run these reports
  2. Click Design View
  3. Double-click on the command button you are using to execute this code to bring up the properties window
  4. Click the Event Tab of the Properties Window
  5. Put your Cursor in the On Click field
  6. Single click the ellipsis ("...")
  7. This should take you to your VB Editor

Your VBA Editor should now have this: [Form_frmNameOfYourForm (Code)]. If it does, then it should have the following somewhere in that module:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Test_Click()
  2. On Error GoTo EH
  3.     Dim db          As Database
  4.     Dim rs          As Recordset
  5.     Dim strPath     As String
  6.     Dim strReport   As String
  7.     Dim strFilename As String
  8.     Dim strSubject  As String
  9.     Dim strMessage  As String
  10.  
  11.     strPath = "C:\pdfs\"
  12.     strSubject = "Your account"
  13.     strMessage = "Your monthly account is attached"
  14.     strFilename = strPath & "All Accounts.pdf"
  15.  
  16.     gstrReportFilter = ""
  17.     DoCmd.OutputTo acOutputReport, _
  18.         "rptAccount", acFormatPDF, strFilename
  19.  
  20.     Set db = CurrentDb
  21.     Set rs = db.OpenRecordset("tblcustomers")
  22.     With rs
  23.         If Not .RecordCount = 0 Then
  24.             .MoveFirst
  25.             Do While Not .EOF
  26.                 Debug.Print !Cuscode
  27.                 Debug.Print !Email
  28.                 gstrReportFilter = "CusCode = '" & !Cuscode & "'"
  29.                 Debug.Print gstrReportFilter
  30.                 strReport = "Account " & !Cuscode & ".pdf"
  31.                 strFilename = strPath & strReport
  32.                 DoCmd.OutputTo acOutputReport, _
  33.                     "rptAccount", acFormatPDF, strFilename
  34.                 DoCmd.SendObject acSendReport, _
  35.                     "rptAccount", acFormatPDF, _
  36.                     !Email, , , strSubject, strMessage, True
  37.                 RS.MoveNext
  38.             Loop
  39.         End If
  40.         .Close
  41.     End With
  42.     db.Close
  43.     Set rs = Nothing
  44.     Set db = Nothing
  45.  
  46.     Exit Sub
  47.  
  48. EH:
  49.     MsgBox "There was an error sending the reports!  " & _
  50.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  51.     Exit Sub
  52. End Sub
Please do a copy and paste of the entire procedure.

Now, scroll all the way to the top of the VBA page (module) for your Form. Make sure that these are the top few lines:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public gstrReportFilter As String
Save and close the form.

I know that there has been some confusion as to where everything should go. However, once you understand these things, it will help you with all of your projects and with all your future endeavors with VBA and Access.

Please open your form, click the reports button, and tell me what happens.

Hope this hepps.
Mar 21 '18 #37

P: 62
O.k., before I go cutting and pasting, neither of the scenarios is what I have and that was what I was trying to explain badly.
The page/module DOES have exactly that title ending - [Report_rptAccount (Code)] but it contains all of the code that we have discussed, first the ONClick event (sub) then a line and then the OnOpen event (sub). In the Project list browse, top left, I have the name of my project, expanded to 'Microsoft Access Class Objects', further expanded to 'Report_rptAccount' just like the Windows title bar ending. Not a mention of module and the icon at the side of Report_rptAccount is a notebook, nothing like the module or class module icons.
This browse and icon were placed there by the application, not me, and the VBA page that has the 2 procedures on is the only code relevant to the report. Again, Access placed what I called the 'filter procedure' sub there yesterday when I clicked on the OnClick event.
Mar 21 '18 #38

twinnyfo
Expert Mod 2.5K+
P: 2,720
Trevor, have you been testing any of these procedures as we have been going along? How have you been evaluating whether or not any of this code works if it is in a Report's Module?
Mar 21 '18 #39

P: 62
Only by the results in the Immediate window as a result of the Debug.print lines and the pdfs that have been output to the C:\PDFs folder. As I said earlier I have no F keys functionality on this computer so I can't step in or step through the lines of code, neither with function keys or from the menus.
I have looked at the code that you sent this morning and compared with mine, I had to change line #14 and enter line #17. I also had a 'gstrReportFilter = ""' after your line 18 which I have now commented out. I've gone no furtehr than that.
Mar 21 '18 #40

twinnyfo
Expert Mod 2.5K+
P: 2,720
So........... Is it working?

It appears (which was totally unclear to me) that you have a command button on your report, which is not wrong, but just completely not expected, because 99% of the time we put command buttons on forms to execute code so that other thins can go on in the background (and running code which affects the currently open report may not work properly).

Hence, my guidance to you previously that the long code (which cycles through your records) should go on your form, which, apparently you don't have. I would recommend having a form, separate from your Report, which would be a more typical way of doing things. This also helps you to watch things happen as they happen.

Still don't understand the lack of Function Keys, but that is another story altogether.

But, the bottom line is still, "Does it work?" If not, what is it doing instead?
Mar 21 '18 #41

P: 62
I have tried it with my code and then copied and pasted yours, still not working any differently to before (yesterday) apart from it now saves the 'All accounts.pdf' as well after your line #14. I have commented out the email lines though but I can't see that this would still allow the rest of the code to save 7 Files, each named correctly (001, 002, 003...) but still containing the 7 reports. Am I right?
In the immediate window it now displays;
e.g. 002, xxxxxxxx@laprima-sl.com, '002'
i.e., now with the single quote around the second instance of the Cuscode.
I have noticed that when clicking the button on the report, it moves slightly and leaves a single line border where it was !
Mar 21 '18 #42

twinnyfo
Expert Mod 2.5K+
P: 2,720
Trevor, thank you for posting Immedate Window results. This is most useful!

If you did a copy and paste, then
002, xxxxxxxx@laprima-sl.com, '002' is not what you want. What you want it to be is:
002
xxxxxxxx@laprima-sl.com
CusCode = '002'
.

My thought, again, is that this is not working properly because you are activating everything from the open report, rather than from a Form.
Mar 21 '18 #43

P: 62
O.k. so should I create a form, put the cmd button on it and place the code there? I'll have to add some code to the top lines won't I, so that the code is applied to the report not the form itself?
Mar 21 '18 #44

P: 62
Sorry sorry sorry, the immediate window DOES say Cuscode = '002'
Mar 21 '18 #45

twinnyfo
Expert Mod 2.5K+
P: 2,720
Report Code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Report_Open(Cancel As Integer)
  5. On Error GoTo EH
  6.  
  7.     If Len(gstrReportFilter) <> 0 Then
  8.         With Me
  9.             .Filter = gstrReportFilter
  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. End Sub


Form Code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public gstrReportFilter As String
  5.  
  6. Private Sub Test_Click()
  7. On Error GoTo EH
  8.     Dim db          As Database
  9.     Dim rs          As Recordset
  10.     Dim strPath     As String
  11.     Dim strReport   As String
  12.     Dim strFilename As String
  13.     Dim strSubject  As String
  14.     Dim strMessage  As String
  15.  
  16.     strPath = "C:\pdfs\"
  17.     strSubject = "Your account"
  18.     strMessage = "Your monthly account is attached"
  19.     strFilename = strPath & "All Accounts.pdf"
  20.  
  21.     gstrReportFilter = ""
  22.     DoCmd.OutputTo acOutputReport, _
  23.         "rptAccount", acFormatPDF, strFilename
  24.  
  25.     Set db = CurrentDb
  26.     Set rs = db.OpenRecordset("tblcustomers")
  27.     With rs
  28.         If Not .RecordCount = 0 Then
  29.             .MoveFirst
  30.             Do While Not .EOF
  31.                 Debug.Print !Cuscode
  32.                 Debug.Print !Email
  33.                 gstrReportFilter = "CusCode = '" & !Cuscode & "'"
  34.                 Debug.Print gstrReportFilter
  35.                 strReport = "Account " & !Cuscode & ".pdf"
  36.                 strFilename = strPath & strReport
  37.                 DoCmd.OutputTo acOutputReport, _
  38.                     "rptAccount", acFormatPDF, strFilename
  39.                 DoCmd.SendObject acSendReport, _
  40.                     "rptAccount", acFormatPDF, _
  41.                     !Email, , , strSubject, strMessage, True
  42.                 RS.MoveNext
  43.             Loop
  44.         End If
  45.         .Close
  46.     End With
  47.     db.Close
  48.     Set rs = Nothing
  49.     Set db = Nothing
  50.  
  51.     Exit Sub
  52.  
  53. EH:
  54.     MsgBox "There was an error sending the reports!  " & _
  55.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  56.     Exit Sub
  57. End Sub
Mar 21 '18 #46

NeoPa
Expert Mod 15k+
P: 31,123
@Trevor.
Don't panic that you're leading TwinnyFo in the wrong direction all the time. This is what being a noob is all about. Trust me - it gets better in time. TwinnyFo is very experienced and understands this very well. I won't go as far as saying he won't be feeling frustrated at any time, but he understands that this is part of your process.

What you can do, if you want to progress more quickly, is to go more slowly. Everything is not a panic. Being absolutely precise in what you post is extraordinarily helpful. This you can expect from TwinnyFo too, so read everything he says with great care. He will generally have spent a great deal of time on his posts and will read them over before posting to ensure what he's said is what he meant to say. On the rare occasions he finds he's slipped up he'll tell you ASAP.

So, care and attention are of the utmost importance - both ways. C&I before posting and C&I when reading TwinnyFo's responses.

I'm sure you've already started to appreciate some of these points even without my spelling them out for you, but hopefully I've put them together for you more clearly and more easily than having to go through and learn it all the hard way.

Good luck.
Mar 21 '18 #47

P: 62
Shame this couldn't have been better news ! I made a new form with just one test button, copied in the form code to the now module for the form under the OnClick procedure for the form. This time I didn't comment out the DoCmd.SendObject part on the form code.

I deleted everything from the rptAccount code and copied in the Report_Open code. On Compiling the code, I got a 'variable not defined' error so I did a Dim gstrReportFilter As String just under the On Error GoTo EH line then it complied o.k..

When I click the Test button on the form without the rptAccount open, or with it open,The immediate windows shows as before;
001
email address
Cuscode = '001'

7 pdf files are created in the correct folder, but still each file contains all 7 reports but only 7 emails ARE prepared, not sent, and each attachment has the 7 pdfs in as I would expect.

The fact that the emails aren't sent isn't a problem.

I'm wondering if I have placed the Dim As String for the filter in the correct place on the Report_Open code.
Mar 21 '18 #48

P: 62
Thank you NeoPa, for your words of encouragement. I do find this difficult but I AM learning a lot. I'm not a give up kind of person (except where my wife is concerned) and I will try to do as you suggest.
Trevor.
Mar 21 '18 #49

twinnyfo
Expert Mod 2.5K+
P: 2,720
copied in the form code to the now module for the form under the OnClick procedure for the form
Is this in the button's OnClick event or the Form's OnClick event? Should be for the command button.

I got a 'variable not defined' error so I did a Dim gstrReportFilter As String just under the On Error GoTo EH line then it complied o.k..
Re-read Post #47. Then go back to Post #46 and copy and paste the entire second block of code into your Form's VBA module (this assumes you have a Command Button named "Test". Line 4 declares the variable gstrReportFilter, so no need to declare it anywhere else.

When I click the Test button on the form without the rptAccount open, or with it open,The immediate windows shows as before;
001
email address
Cuscode = '001'
No need to ever have the report open--layout view, design view, report view or print preview view. However, the good news is that the variables are being assigned as expected.

7 pdf files are created in the correct folder, but still each file contains all 7 reports but only 7 emails ARE prepared, not sent, and each attachment has the 7 pdfs in as I would expect.
The only reason that the reports would be created with all 7 reports included must be related to the gstrReportFilter. Are you sure that the code from Post #46 (first block of Code) is in your report? Insert a break point at the beginning of the procedure in your report. It should become highlighted each time the report is opened (you can click the little "play" button to continue the code).

The fact that the emails aren't sent isn't a problem.
If you want to automagically send the e-mails, change the True at the end of line 41 to False. However, I hope you can appreciate why we do not want to change that at this stage of our operations.

I'm wondering if I have placed the Dim As String for the filter in the correct place on the Report_Open code.
See above.

I'm not a give up kind of person (except where my wife is concerned) and I will try to do as you suggest.
Let's hope I misunderstood.... Databases come and go, but a good wife is a once-in-a-lifetime thing. Never give up on that!

:-)
Mar 21 '18 #50

58 Replies

Post your reply

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