Trouble Sending Report (1 Viewer)

A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
I can't get this code to send the report.


The code creates the report, but does not get past the send command. It never gets to the close report line.


If I modify the code to send a different report, it works fine.


Code: 
Private Sub cmdOrgPrint_Click()
Dim LResponse As Integer

Dim hours As Double

Dim name As String

Dim mailto As String

name = Left(Me.cmbWho, InStr(Me.cmbWho, " ") - 1) ' gets the first name

hours = Time() * 24 ' gets time
Dim str As String
' sets morning, afternoon or evening
If hours < 12 Then
    str = "Good morning " & name & ","
ElseIf hours <= 17 Then
    str = "Good afternoon " & name & ","
ElseIf hours > 17 Then
    str = "Good evening " & name & ","
End If
    
    subject = Me.Job.Column(0) & " " & Me.Job.Column(1) & " To Do Items For " & Me.cmbWho.Column(3)
    
    emailmsg = str & vbCrLf & vbCrLf & "The attached report list various items that I am waiting a response from " & Me.cmbWho.Column(3) & "." _
    & vbCrLf & vbCrLf & "Can you update me on the status of these items?" & vbCrLf & vbCrLf & Closing
   

' This sends the e-mail
Dim strWhere As String


mailto = Me.cmbWho.Column(2)


strWhere = "[Job]='" & Me.Job & "' And [Organization] ='" & Me.cmbWho.Column(3) & "'And [Open] = -1"


DoCmd.OpenReport "rptToDoList", acViewPreview, , strWhere

DoCmd.SendObject acSendReport, "rptToDoList", acFormatPDF, mailto, , , subject, emailmsg, True

DoCmd.Close acReport, "rptToDoList"
End Sub
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:02
Joined
Aug 30, 2003
Messages
36,124
The report has data in it? Does the email open up?
 
J

JHB

Have been here a while
Local time
Today, 10:02
Joined
Jun 17, 2012
Messages
7,732
andy1968 said:
..
If I modify the code to send a different report, it works fine.
Click to expand...
A report with the same criteria?
I can see you're missing a space:
Code: 
strWhere = "[Job]='" & Me.Job & "' And [Organization] ='" & Me.cmbWho.Column(3) & "[B][COLOR=Red]'And[/COLOR][/B] [Open] = -1"
 
A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
pbaldy:


yes the report opens with data in it.


The e-mail does not open.


JHB:


I will add a space.


And no, the other report has different criteria.
 
A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
I think I found the issue.


The report has an On Print event:


Code: 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Const DS_Invisible = 0
' set line draw width
        Me.DrawWidth = 10
' set drawstyle mode
       Me.DrawStyle = DS_Invisible
        '
' Assume ctlBoxName.height is the control box that will expand the most. If you are not sure, use an if then statement to see which text boxes have the higher height
'
If Me.rptToDoListNotes.Height > Me.What.Height Then
  Me.Line (0, 0)-Step(0, Me.rptToDoListNotes.Height + 2000)
  Me.Line (Me.Line25.Left, 0)-Step(0, Me.rptToDoListNotes.Height + 2000)
Else
  Me.Line (0, 0)-Step(0, Me.What.Height + 2000)
  Me.Line (Me.Line25.Left, 0)-Step(0, Me.What.Height + 2000)
End If


End Sub

This line is producing the following error on one of the pages:


Code: 
Me.Line (0, 0)-Step(0, Me.rptToDoListNotes.Height + 2000)


Run-time error 6:

Overflow


Not sure what this means, but will investigate.


The report will print without issue.
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:02
Joined
May 7, 2009
Messages
19,232
do you have this control "Me.rptToDoListNotes". if no, substitute the correct Control name.
 
A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
Yes, I have that control; it is a subreport.



The issue is only effecting one record.



This record has long list of related records in the subreport "rptToDoListNotes"


If I run the report filtered for just this one record, it works. However, when I run it for multiple records, it crashes on the page with the record.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:02
Joined
May 7, 2009
Messages
19,232
you can try to amend it:

Me.Line (0, 0)-Step(0, 6500000)
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:02
Joined
Jan 14, 2017
Messages
18,209
The original code seems for this seems to be from Stephen Lebans website

An overflow error normally occurs when an integer value exceeds the maximum allowed (32767)

The likely culprit has to be Me.rptToDoListNotes.Height.
Is that a subreport?
Check its value using a Debug.Print

If you are ABSOLUTELY SURE of the cause & that it prints OK, you could add error handling to ignore error 6.

Code: 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

On Error GoTo Err_Handler

Const DS_Invisible = 0
' set line draw width
        Me.DrawWidth = 10
' set drawstyle mode
       Me.DrawStyle = DS_Invisible
        '
' Assume ctlBoxName.height is the control box that will expand the most. If you are not sure, use an if then statement to see which text boxes have the higher height
'
If Me.rptToDoListNotes.Height > Me.What.Height Then
  Me.Line (0, 0)-Step(0, Me.rptToDoListNotes.Height + 2000)
  Me.Line (Me.Line25.Left, 0)-Step(0, Me.rptToDoListNotes.Height + 2000)
Else
  Me.Line (0, 0)-Step(0, Me.What.Height + 2000)
  Me.Line (Me.Line25.Left, 0)-Step(0, Me.What.Height + 2000)
End If

Exit_Handler:
  Exit Sub

Err_Handler:
  [B]If err=6 Then 
      Resume Next[/B]
  Else
      MsgBox "Error " & err.Number & " in Detail_Print procedure : " & err.Description
     Resume Exit_Handler
  End If

End Sub

Or you could shrink that report so its height + 2000 is less than 32767 (twips)
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:02
Joined
May 7, 2009
Messages
19,232
or edit this part:

Step(0, Iif(Me.rptToDoListNotes.Height + 2000 > 6500000, 6500000, Me.rptToDoListNotes.Height + 2000))
 
A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
arnelgp


Tried both - no luck


isladogs


Well, it actually only prints up to the offending record, so it doesn't really print correctly.





Yes the rptToDoListNotes is a subreport.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:02
Joined
May 7, 2009
Messages
19,232
comment out the line and see if you see any errors.
maybe has to do with subreport error.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:02
Joined
Jan 14, 2017
Messages
18,209
Can you do the following and tell us the result
Code: 
debug.print Me.rptToDoListNotes.Height

Or if the height varies with the number of records, debug the value on the last record before it fails. What is the record number when it fails?

One other thing. Do you have page numbers in the filter section? If so, does the number of pages exceed 32767?

I'm still unclear why arnelgp specified 6500000 as a limit.
 
A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
isladogs,


The height various with the number of records.


The number of pages in the report is 14, and yes there are page numbers.


Where do I place the debug code?
 
A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
Isladogs,


I put the debug code in the on print event for the form and the error message disappeared.


E-mail was generated.


Thanks!


Can you easily explain to me what the code did?


Thanks again and have a fantastic weekend.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:02
Joined
Jan 14, 2017
Messages
18,209
Immediately after the If....Then line and before the first Me.Line code.
What I want to know is what the final height value is before it fails. The purpose is to see its effect on the following lines.

Your previous answer tells me there isn't an issue with the total number of pages. I had an issue with a report of around 55000 pages (honestly!) which created an interesting side effect due to being over the integer limit of 32767. If interested, see https://www.access-programmers.co.uk/forums/showthread.php?t=298188

That is why I've twice asked arnelgp to explain his 6500000 value. Unfortunately, so far no response. Do you know where that figure came from?

For info, when I want to draw lines on reports, my code goes in the on format event instead of the on print event. That may be worth exploring as well.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:02
Joined
Jan 14, 2017
Messages
18,209
Messages crossed!

Whilst I'd love to tell you doing that fixed your problem, that really isn't true.
What it does is display the value in the Immediate window of the VBE.
However it also slowed your code slightly which may just possibly be relevant.
If so, adding the line DoEvents instead might work....though if so it's very strange.

Where does the email get generated?
 
A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
Really appreciate this info.



I'm dealing with some other issue today, but tomorrow, I will dive back in and see if I can answer your questions.


Have a great Monday!



(no, I have no idea where the 6500000 came from)
 
A

andy1968

Registered User.
Local time
Today, 01:02
Joined
May 9, 2018
Messages
131
Sorry it took so long to get back to you.


needed to learn about the Immediate window 1st! :)


Anyway, here are the heights:


15
1855
2212
15
1855
1943
15
15
1515
1408
15
6876
 
You must log in or register to reply here.

Similar threads

A
Sending Email SMTP setting
Replies
7
Views
360
ions
I
L
Solved Saving a report to a specified folder
Replies
3
Views
222
Gasman
Gasman
L
Send looping Email
Replies
3
Views
298
looma123
L
E
Run-time error '75': Path/File Access Error
2
Replies
20
Views
601
ebs17
E
M
VBA output report to PDF and email
Replies
18
Views
742
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom