Save the record before runing the report

[mavisyew]

Hi All,

I have created a database for case incident.
When the user create a case, and the Status = "new" and Priority = "Critical" Then when i click on the Save button, it suppose to save the record then send out the report.

Below is my code.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strResponse As String
If Me.Dirty Then
Select Case MsgBox( _
Prompt:="This record has been changed" & _
vbCrLf & "Do you want to save?", _
Buttons:=vbExclamation Or vbYesNo)
Case vbYes
DoCmd.RunCommand acCmdSave

If Me.Priority = "Critical" And Me.Status = "New" Then
DoCmd.SendObject acSendReport, "ReportCriticalCase", acFormatPDF, "123@yahoo.com", 123@yahoo.com, , "Critical Case", "Critical Case", False
End If

Case vbNo
DoCmd.RunCommand acCmdUndo
End Select
End If
End Sub

Now the problem is that it will send out the report before it save the data and my report sent out is empty.



Anyone can advice what happen to my code?

[June7]

Try: acCmdSaveRecord

[ItsMe]

The following code is in the middle of your MsgBox code. Perhaps it should be somewhere else. Also, It seems like the criteria is looking for a record that has not been saved yet.

If Me.Priority = "Critical" And Me.Status = "New" Then
DoCmd.SendObject acSendReport, "ReportCriticalCase", acFormatPDF, "123@yahoo.com", 123@yahoo.com, , "Critical Case", "Critical Case", False
End If

[mavisyew]

Hi June7,

I changed the code to 'DoCmd.RunCommand acCmdSaveRecord' but it prompt me a macro error message.


Hi ItsMe,

I want the system to auto send out the report when i save the record.
Where do you suggest to put the code?

[ItsMe]

Regarding the constant, acCmdSaveRecord, June is correct. If you are getting an error on that line, it may be because you do not have focus on the form object. So maybe
Me.SomeControlName.SetFocus
DoCmd.RunCommand acCmdSaveRecord

or use
Me.Dirty = False

However, I just realized you are using the BeforeUpdate event. This may be causing the error by itself. The form is already in the process of updating itself so no need to call the save.

I would start by commenting out the code to send the report and concentrate on saving/committing the record to the table first. You have a couple of issues here and are trying to accomplish too much in one block of code. Split it up.

Maybe something like this for your BeforeUpdate event handler would be more appropriate.

Dim intResponse As Integer
intResponse = MsgBox("Do you want to save the changes made to this record?", vbYesNo + vbDefaultButton2 + vbQuestion, "Save Changes")

    If intResponse = vbNo Then
        Cancel = True
    End If

[June7]

Good point about the BeforeUpdate event already in process of saving record. I did not notice.

Perhaps the code to send report should be in AfterUpdate.

[mavisyew]

Hi Both,

Thanks!
Think i got it.

I have move the send report to AfterUpdate and is working.