Access sends wrong file

Topic

New Reply

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

Reply | Quote

Replies

Why not use SendObject?

Reply | Quote

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

Reply | Quote

What a strange problem! Have you tried single-stepping through the code and inspecting the file during the process?

Reply | Quote

Another question, is, is it happening to other users, if any other users run this module.

Reply | Quote

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.

Reply | Quote

And the problem seems to be restricted to just one user.

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

Reply | Quote

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.

[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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote