Store and send reports by customer number Access 2016

[TrevorJ]

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.

[twinnyfo]

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.

[TrevorJ]

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.em ail and so on. This is what I've got so far;

Private Sub Command#_Click()
DoCmd.OpenReport "rptAccount"
Dim stReport As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String   'not sure what caption this is
Dim myPath As String
 
stEmailMessage = "Your account from LPS for this month is attached."
stSubject = "Monthly account"
stReport = "tblAccount.rptAccount"
 
stCaption = "Send Account")
myPath = "C:\DocumentsLaura_Access\LPS\Access Database\"
 
DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , ,
'this would be a different path to that defined above, i.e. not in 'the database
DoCmd.CloseReport

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

[twinnyfo]

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 "CustomerNa me." Then, when you refer to "CustomerNa me" 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 "CustomerNa me" or the underlying Field named "CustomerNa me." 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:

Option Compare Database
Option Explicit
 
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:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo EH
 
    If Len(gstrReportFilter) <> 0 Then
        With Me
            .Filter = gstrReportFilter
            .FilterOn = True
        End With
    End If
 
    Exit Sub
EH:
    MsgBox "There was an error initializing the Report!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
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.

Private Sub cmdSendReports_Click()
On Error GoTo EH
    Dim stReport        As String
    Dim stSubject       As String
    Dim stEmailMessage  As String
 
    'Set the Filter to empty to start out
    gstrReportFilter = ""
 
    'Set the Report Name
    stReport = "tblAccount.rptAccount"
    stEmailMessage = "Your account from LPS for this month is attached."
    stSubject = "Monthly account"
 
    'Get a list of Customer Codes and e-mail Addresses
 
    'Loop through this list, one at a time
 
        gstrReportFilter = "CustomerCode = " & 'Your Customer Code
        DoCmd.SendObject acSendReport, stReport, _
            acFormatPDF, "You need e-mail addresses", , , _
            stSubject, stEmailMessage, True
 
    'Go to the next record
 
    'Reset your filter so you can open the Report normally
    gstrReportFilter = ""
 
    Exit Sub
EH:
    MsgBox "There was an error sending the Reports!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
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!

[TrevorJ]

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(whateve r)_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.Fi rstName 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.

[twinnyfo]

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.Fi rstName 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.Fi rstName 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:

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.Fi rstName.

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: txtCustIDTopRig ht, txtCustIDTopLef t, txtCustIDBotLef t, txtCustIDBotRig ht. 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.

:-)

[TrevorJ]

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;

Option Compare Database
Option Explicit
 
Private Sub Command0_Click()
DoCmd.OutputTo acReport, "tblAccount.rptAccount", acFormatPDF, "C:\PDFs", True
End Sub
 

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.

[twinnyfo]

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").

[TrevorJ]

Still the same!

Option Compare Database
Option Explicit
 
Private Sub Command0_Click()
DoCmd.OutputTo acReport, "tblAccount.rptAccount", acFormatPDF, "C:\PDFs\cust001.pdf", True
End Sub
 
 

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.

[twinnyfo]

Is "tblAccount.rpt Account" the name of the report? Or is it simply"rptAccou nt"?