UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Modifying Report To Send Email To Users, Access 2016    
 
   
HotSauceHero
post Feb 18 2020, 10:36 AM
Post#1



Posts: 5
Joined: 18-February 20



Greetings,

I find myself back in the wonderful world of Access after a 10+ year hiatus and I've ran into a problem. I'm hoping someone can see what I am missing.

I have a report that I generate to for 60+ users.

The report is modified based on the user it is going to. Certain users receive the report with columns removed, while others receive the whole report.

The good news is that the code works. I have the report looking like I want it to and I have the modifications being made at the appropriate times.

I have the code entered at the On Load Event Procedure and when the report is opened manually everything is exactly as it should be.

The problem is when the report is emailed nothing changes.

This is the code I am using to send the report.

DoCmd.OpenReport "rptStoreSalesPeriodandYear", acViewPreview
DoCmd.Close acReport, "rptStoreSalesPeriodandYear", acSaveYes


'Send report to email assigned to store
DoCmd.SendObject acSendReport, "rptStoreSalesPeriodandYear", acFormatPDF, StoreEmail, , , "Weekly Sales Report for" & StoreHeader, "Please review your sales report for this week", False


I have tried moving the code to the On Open event of the report and when I do, I get a 2427 Error.

I'm completely stumped at this point and any help would be greatly appreciated.
I hope I have provided the right amount of information.

Go to the top of the page
 
pere_de_chipstic...
post Feb 18 2020, 10:56 AM
Post#2


UtterAccess Editor
Posts: 10,657
Joined: 8-November 07
From: South coast, England


welcome2UA.gif

How are you trying to modify the report?

--------------------
Warm regards
Bernie
Go to the top of the page
 
HotSauceHero
post Feb 18 2020, 11:13 AM
Post#3



Posts: 5
Joined: 18-February 20



I'm modifying the report by changing some of the parameters of the report.
The code below shows the first if statement.

If Me.Text20 = Me.SumOfSales Then
'Stores with Historic Data, hide YTD Sales Column and Label by turning borders and forecolor white
'Also change existing labels to black forecolor and border. Used to ensure formatting goes back to original status.
Me.SumOfSales.ForeColor = 16777215
Me.SumOfSales.BorderColor = 16777215
Me.SumOfSales.BackColor = 16777215
Me.Label46.ForeColor = 16777215
Me.Label46.BorderColor = 16777215
Me.Label46.BackColor = 16777215
Me.Label21.ForeColor = 0
Me.Label21.BorderColor = 0
Me.Text18.ForeColor = 0
Me.Text18.BorderColor = 0
Me.Text19.ForeColor = 0
Me.Text19.BorderColor = 0
Me.Text20.ForeColor = 0
Me.Text20.BorderColor = 0
Me.Label24.ForeColor = 0
Me.Label24.BorderColor = 0
Me.Label25.ForeColor = 0
Me.Label25.BorderColor = 0
Me.Text22.ForeColor = 0
Me.Text22.BorderColor = 0
Me.Text26.ForeColor = 0
Me.Text26.BorderColor = 0
Me.Text23.ForeColor = 0
Me.Text23.BorderColor = 0
Me.Text27.ForeColor = 0
Me.Text27.BorderColor = 0
Me.SumOfSales.FontSize = 14
Me.SumOfSales.Width = 2520
Me.SumOfSales.Height = 360
Me.Label46.FontSize = 18
Me.Label46.Width = 2520
Me.Label46.Height = 480
Me.Label46.Left = 8220
Me.Label46.Top = 900
Me.SumOfSales.Top = 1500
Me.SumOfSales.Left = 8220

Again, the code works perfectly when the report is opened manually, when this code is used with the On Load event.
When used with the On Open Event, it kicks back error 2427 saying it can't find the text box, even though it is definitely there.


Go to the top of the page
 
theDBguy
post Feb 18 2020, 11:36 AM
Post#4


UA Moderator
Posts: 78,442
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

Just curious... What happens if you simply rearrange your original code to do something like?

DoCmd.OpenReport... acViewPreview
DoCmd.SendObject...
DoCmd.Close...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pere_de_chipstic...
post Feb 18 2020, 11:37 AM
Post#5


UtterAccess Editor
Posts: 10,657
Joined: 8-November 07
From: South coast, England


Hi HotSauceHero

I would look at the timing of the code in relation to opening the report and sending it. Certainly text20 and SumOfSales data would not be available during the OnOpen event and would need to occur later.

Are you opening the report before you use the DoCmd.SendObject acSendReport command? e.g.
CODE
DoCmd.OpenReport "rptStoreSalesPeriodandYear", acViewPreview
DoCmd.SelectObject acReport, "rptStoreSalesPeriodandYear"
DoCmd.SendObject acSendReport, "rptStoreSalesPeriodandYear", acFormatPDF, StoreEmail, , , "Weekly Sales Report for" & StoreHeader, "Please review your sales report for this week", False
DoEvents
DoCmd.Close acReport, "rptStoreSalesPeriodandYear"


Also could you set the Open Report command OpenArgs argument to tell the report which fields are affected? (NB may be easier to hide/unhide the labels/controls rather than amend the border, forecolor and backcolor)

hth


--------------------
Warm regards
Bernie
Go to the top of the page
 
HotSauceHero
post Feb 18 2020, 01:09 PM
Post#6



Posts: 5
Joined: 18-February 20



@theDBGuy

That was the way I originally tried it. The report went out using the original format.

@pere_de_chipstick

Yes! That works!!!


I added the line of code:
DoCmd.SelectObject acReport, "rptStoreSalesPeriodandYear"

The added after the docmd.sendobject:

DoEvents

And it now opens the report and sends them to the appropriate place.

Can I ask what those two lines are actually doing. I'm much better when I understand why something is happening.

Thank you both so much for your help!

Go to the top of the page
 
pere_de_chipstic...
post Feb 19 2020, 09:43 AM
Post#7


UtterAccess Editor
Posts: 10,657
Joined: 8-November 07
From: South coast, England


Hi

Glad that you have got it working thumbup.gif

on the two additional command lines
1. DoCmd.SelectObject
The code opening the report can move on to the following command (i.e. DoCmd.SendObject) before the report on open/onload events have had time to run / complete.
DoCmd.SelectObject ensures that the object selected has the focus and, my understanding is, it allows the report to be fully instantiated before the subsequent commands.
Sometimes, e.g. when printing a report, Access can print the current form because the report isn't yet loaded, using SelectObject ensures the report is printed not the form!

2. DoEvents
DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue.
This prevents the current code from continuing until any current actions are complete e.g. you don't want to close the report before the sendobject action has finished.
I am not sure that it is strictly necessary in this instance but it does no harm to include it!

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    3rd July 2020 - 08:24 AM