Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,

    I have a peculiar problem.

    A user of my application uses a button to send a report by email. The report file (rtf) seems to be created, but the CONTENT of the file is from the previous time this same report had been run by the user.

    In other words: The name of the RTF file correctly reflects the record Id, but the report shows the data of the previous record that was sent, with obviously a different recId.

    Does anyone know what is going on here (and how to fix it)?

    Here's the relevant code:
    Code:
    Public Function SendMailMessage(stDocName As String, sTo As String, _
    								sOrderNumber As String, sSubject As String) As Boolean
    	Dim sFileName As String
    	Dim sFullFileName As String
    	Dim sTempFolder As String
    	sTempFolder = TempPath
    	sFileName = sOrderNumber
    	sFullFileName = sTempFolder & sFileName
    	gsReportOrderNumber = sOrderNumber
    	If stDocName Like "rpt*" Then
    		sFullFileName = sFullFileName & ".rtf"
    		DoCmd.OutputTo acOutputReport, stDocName, acFormatRTF, sFullFileName, False
    		DoEvents
    		SendMailMessage = True
    	ElseIf stDocName Like "qry*" Then
    		sFullFileName = sFullFileName & ".xls"
    		DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, sFullFileName, False
    		DoEvents
    		SendMailMessage = True
    	Else
    		MsgBox "Rapport type onbekend (geen qry of rpt). Verzenden email geannuleerd.", _
    			   vbExclamation + vbOKOnly, GCSAPPNAME
    		SendMailMessage = False
    		Exit Function
    	End If
    'If report has been succesfully created then create email message
    	If SendMailMessage Then
    		If CreateMail(sTo, sSubject, " ", sFullFileName) Then
    			SendMailMessage = True
    		Else
    			SendMailMessage = False
    		End If
    	End If
    	On Error Resume Next
    	Kill sFullFileName
    TidyUp:
    	gsReportOrderNumber = ""
    	Exit Function
    End Function
    
    Private Function CreateMail(sTo As String, sSubject As String, sBody As String, sAttachment As String) As Boolean
    	Dim oMailItem As Object
    	Dim oOLapp As Object
    	On Error GoTo LocErr
    	'Fire up Outlook
    	Set oOLapp = GetObject(, "Outlook.application")
    	'Open email object
    	Set oMailItem = oOLapp.CreateItem(0)
    	With oMailItem
    		.To = sTo
    		.Subject = sSubject
    		.body = " "
    		.attachments.Add sAttachment
    		'Display the message so user can edit and decide whether or not to send
    		.Display
    		Set oOLapp = Nothing
    		Set oMailItem = Nothing
    	End With
    	CreateMail = True
    	Exit Function
    TidyUp:
    	CreateMail = False
    	Exit Function
    LocErr:
    	If Err.Number = 429 Then
    		MsgBox "Outlook is niet gestart, s.v.p. Outlook starten en opnieuw proberen.", vbExclamation + vbOKOnly, "Outlook is niet gestart"
    		Resume TidyUp
    	End If
    
    	MsgBox "Foutmelding tijdens maken email bericht. Foutboodschap:" & vbNewLine & _
    		   Err.Description, vbOKOnly + vbExclamation, "Fout tijdens maken email"
    
    	Resume TidyUp
    End Function
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why not use SendObject?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I did try sendobject easrlier in this project. This however also exhibits the problem at hand that I have described, it is for this reason that I actually started to use the outlook route in the first place.

    Let me give a bit more info.
    The report has a report_Open routine that I have copied below. Before running the report I pass the record Id to the report by putting it in a public variable called "gsReportOrderNumber".

    I then have the report filtered on that number:

    Code:
    Private Sub Report_Open(Cancel As Integer)
    ' Set public variable to true to indicate that the report
    ' is in the Open event
    	Dim sOrderId As String
    	bInReportOpenEvent = True
    
    	' Cancel Report if User Clicked the Cancel Button
    	If IsLoaded("frmOrders") = False Then
    		sOrderId = InputBox("Geef s.v.p. het order nummer", "Bestelbon vellen P7")
    		If sOrderId = "" Then
    			Cancel = True
    		Else
    			Me.Filter = "OrderId=" & sOrderId
    			Me.FilterOn = True
    		End If
    	ElseIf Len(gsReportOrderNumber) > 0 Then
    		Me.Filter = "OrderId=" & gsReportOrderNumber
    		Me.FilterOn = True
    	Else
    		Cancel = True
    	End If
    
    	' Set public variable to false to indicate that the
    	' Open event is completed
    	bInReportOpenEvent = False
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What a strange problem! Have you tried single-stepping through the code and inspecting the file during the process?

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Another question, is, is it happening to other users, if any other users run this module.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Single stepping produces the expected file odd enough.
    And the problem seems to be restricted to just one user.
    Maybe something else is going on, I'll check the other events of the form, see if anything can go amiss there.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='pieterse' post='781638' date='25-Jun-2009 09:40']And the problem seems to be restricted to just one user.[/quote]
    The reason I bring this up, is, if the user does not have the requisite permissions on the folder that contains the report, that may prevent them from deleting the old and/or creating a new file. However, if a file with the correct file name is created, then -- that can't be the issue. Another thing is if they have a full version of Access and somehow managed to change something that has fixed the filter to a certain ID and now cannot change it -- I'm just grasping at straws, but since it affects only one user, it makes it less likely that there is an error in your code and more likely it's something in the user's environment.
    Pat

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='pieterse' post='781638' date='25-Jun-2009 20:40']Single stepping produces the expected file odd enough.
    And the problem seems to be restricted to just one user.
    Maybe something else is going on, I'll check the other events of the form, see if anything can go amiss there.[/quote]
    [Is the sending of the email too quick for the completion of the report?

    In other words, you have previously created a report that is sitting on disk.

    You use OutputTo to run the report to the file on disk and immediately send the email.

    When you step through it does the right thing, so it suggests to me that it is a timing issue.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mystery solved!

    I managed to find the culprit.

    The report in question may be called up in a couple of cases, sometimes in preview mode, sometimes printed directly and sometimes to send as email attachment.

    If the user still has a preview window of the report open (might be from a different record) and has alt+tabbed back to the order form, pressing the email button does not fire the reports open event, since it is already open. This means the report's filter setting is not updated to the current record and the report is saved with a record other than the file name suggests.

    Adding a line of code that closes the report window just before creating the report file has solved the issue.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Great! That kind of thing is difficult to get a finger behind.

    Another workaround would be to set the report's Modal property to Yes. The user won't be able to activate the order form while the report is open in print preview.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •