Question Email Report (1 Viewer)

rudders

New member
Local time
Today, 17:13
Joined
Oct 20, 2017
Messages
3
Hi, I am trying to email a report automatically and have come to a point where I am stuck.

I have a list of branch codes, some of which have an email address, that require a report to be emailed

I have used the code below which I have borrowed from else where. The code will create the report and email it but its contents is blank. It will go through all branch codes with an email.

To run the report manually I presently load the from a drop down list, so in the query, "location code" field I have the criteria

"[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]"

I think my issue is that I need this line of the code below:

Where = "[qry_data_Dashboard_rpt_email].[Location Code] = " & CStr(rst![Location Code])

to put the Location code "virtuallly" in my drop down box to produce a populated report.

Any help would be appreciated

Thanks

Sub EmailScorecard()

Dim dbName As Database
Dim rst As Recordset
Dim lRecNo As Long
Dim lBillCnt As Long
Dim Where As String
Dim MsgBody As String
Dim Email As String
Dim Subject As String
Dim Docname As String
Docname = "Rpt_RDM_Dashboard_email"
Set dbName = CurrentDb()
Set rst = dbName.OpenRecordset("rdmbranchlist", dbOpenDynaset)
rst.MoveFirst

lBillCnt = 0

Do While Not rst.EOF

If rst![Branch_Email_Address] <> "" Then

Where = "[qry_data_Dashboard_rpt_email].[Location Code] = " & CStr(rst![Location Code])

DoCmd.OpenReport "Rpt_RDM_Dashboard_email", acPreview
Email = rst![Branch_Email_Address]
Subject = "Branch Scorecard" & rst![Location Code] & rst![RDM Name]
MsgBody = "Hi " & rst![Location Code] & vbCrLf & "Please find your Branch Scorecard for Last Week."
DoCmd.SendObject acReport, Docname, acFormatPDF, Email, , , Subject, MsgBody, Save, True
DoCmd.Close acReport, "Rpt_RDM_Dashboard_email", acSaveNo

lBillCnt = lBillCnt + 1 '*** Count Emails Created ***
End If

rst.MoveNext '*** Move to Next Record ***
Loop

MsgBox Format(lBillCnt, "#,###") & " Email Branch Scorecard Created."
Set rst = Nothing '*** Close RecordSet ***



End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:13
Joined
Aug 30, 2003
Messages
36,118
Since the query has the criteria, in your code you should populate that:

[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir] = CStr(rst![Location Code])
 

rudders

New member
Local time
Today, 17:13
Joined
Oct 20, 2017
Messages
3
Hi I have tried that but I get Runtime 2465 telling me the database cant find the field [Dashboard_Branch_Select_Redir]
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:13
Joined
Aug 30, 2003
Messages
36,118
That form would have to be open.
 

rudders

New member
Local time
Today, 17:13
Joined
Oct 20, 2017
Messages
3
hi, thanks for helping.

I have changed it to:-

Where = "[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]" = CStr(rst![Location Code])

and it now removes the error and when I hover over the cstr part of the code it is picking up the Location code, but the report is still blank.

So it is acting as if I am opening the report without populating "[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2002
Messages
42,971
You are not using the where argument of the OpenReport Method.

I prefer to put the criteria into the report's RecordSource query itself. That way, I don't have to open the report, I can just create the PDF and have my criteria recognized. If you are sending only one email at a time, both methods work. However, if you are doing a bulk process where you are sending dozens or even hundreds of emails, you don't want the extra overhead of having to open the report first so that the open report is what is sent to the .pdf. It will seriously slow down the loop.

I run the loop in code and put the criteria into a hidden form field each time I go through the loop. Then the Where clause in the RecordSource query gets the value from the hidden form field:

Where SomeFieldID = Forms!myform!txtSomeFieldID
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:13
Joined
Aug 30, 2003
Messages
36,118
hi, thanks for helping.

I have changed it to:-

Where = "[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]" = CStr(rst![Location Code])

and it now removes the error and when I hover over the cstr part of the code it is picking up the Location code, but the report is still blank.

So it is acting as if I am opening the report without populating "[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]"

No, I meant to replace that, which does nothing, with what I posted, which should populate the form with the value, allowing the query to find it.
 

Users who are viewing this thread

Top Bottom