Jason Steward
asked on
I need form to close after an email is sent.
Access doesn't execute DoCmd.Close to close the form after an email is sent.
Here's my entire code:
The code executes perfectly to run the email, but doesn't close the form afterwards. Thanks.
Here's my entire code:
Private Sub Command799_Click()
Me.Dirty = False
If IsNull(Me.txtChargeCode.Value = True) And Me.cboMancamp.Value > 0 Then
MsgBox "You must enter an AFE, Work Order, or Cost Center", vbOKOnly, "CHARGE CODE REQUIRED"
Else
If Me.cboMancamp.Value > 0 And IsNull(Me.txtStartDate.Value = True) Then
MsgBox "You must enter an arrivial and check-out date", vbOKOnly, "LODGING DATES REQUIRED"
Else
If Me.cboMancamp.Value > 0 And IsNull(Me.txtEndDate.Value = True) Then
MsgBox "You must enter an arrivial and check-out date", vbOKOnly, "LODGING DATES REQUIRED"
Else
DoCmd.RunCommand acCmdSaveRecord
Dim strBody As String
Dim strEmail As String
Dim strSubject As String
Dim objOutlook As Object
Dim objMailItem As Object
Const olMailItem As Integer = 0
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(olMailItem)
On Error GoTo err_Error_handler
strEmail = [TO_Group]
strCc = [CC_GROUP]
strSubject = "Field Visit Booking (Request ID# " & Me.FieldVisitBookingID & ") For " & txtContactName & " Starting " & Me.txtStartDate & " Finishing " & Me.txtEndDate
strBody = "<IMG alt='' hspace=0 src='L:\SharedData\Harrison\Midstream\ACIST\Support Files\Graphics\OPEN_email header-02.jpg' align=baseline border=0> <br>" & _
"<br><br><font face=Calibri style=font-size:14pt;>All,<br><br> " & "" & _
"A representative from " & Me.txtCompanyFullName & ", " & Me.txtContactName & ", (" & Me.txtContactMobile & ") will be visiting the field between <b>" & Me.txtStartDate & "</b> and <b>" & Me.txtEndDate & "</b>. " & _
"We would like to book them at the " & Me.cboMancamp & " and charge to <b>" & txtChargeCode & "</b>.<br /> "
objMailItem.SentOnBehalfOfName = strFro
objMailItem.To = strEmail
objMailItem.CC = strCc
objMailItem.Subject = strSubject
objMailItem.Importance = olImportanceHigh
objMailItem.HTMLBody = strBody
objMailItem.BodyFormat = olFormatHTML
objMailItem.Attachments.Add ("L:\test.pdf")
objMailItem.Display
Set objOutlook = Nothing
Set objMailItem = Nothing
exit_Error_handler:
On Error Resume Next
Set objOutlook = Nothing
Set objMailItem = Nothing
Exit Sub
err_Error_handler:
Select Case Err.Number
Case 287
MsgBox "Canceled by user.", vbInformation
Case Else
MsgBox "Error " & Err.Number & " " & Err.Description
End Select
Resume exit_Error_handler
DoCmd.Close acForm, "FieldVisitBookingEntryF"
End If
End If
End If
End Sub
The code executes perfectly to run the email, but doesn't close the form afterwards. Thanks.
Last Comment
The Docmd.Close exists only in the Error Handler which is never reached because of the Exit Sub mentioned by the other Expert...an idea would be to extract the code to a function that returns a boolean ...if everything went fine..then Docmd.Close ...else report back what went wrong
ASKER
So is this what I need to do?
Me.Dirty = False
If IsNull(Me.txtChargeCode.Value = True) And Me.cboMancamp.Value > 0 Then
MsgBox "You must enter an AFE, Work Order, or Cost Center", vbOKOnly, "CHARGE CODE REQUIRED"
Else
If Me.cboMancamp.Value > 0 And IsNull(Me.txtStartDate.Value = True) Then
MsgBox "You must enter an arrivial and check-out date", vbOKOnly, "LODGING DATES REQUIRED"
Else
If Me.cboMancamp.Value > 0 And IsNull(Me.txtEndDate.Value = True) Then
MsgBox "You must enter an arrivial and check-out date", vbOKOnly, "LODGING DATES REQUIRED"
Else
Exit Sub
End If
End If
End If
DoCmd.RunCommand acCmdSaveRecord
ASKER
John, how do I change the code below to trigger a close?
exit_Error_handler:
On Error Resume Next
Set objOutlook = Nothing
Set objMailItem = Nothing
Exit Sub
err_Error_handler:
Select Case Err.Number
Case 287
MsgBox "Canceled by user.", vbInformation
Case Else
MsgBox "Error " & Err.Number & " " & Err.Description
End Select
Resume exit_Error_handler
DoCmd.Close acForm, "FieldVisitBookingEntryF"
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Ohhhhhhhhh.... Thanks!
ASKER
I was thinking the last action would need to happen at the bottom without thinking about how it was actually executing. Thanks for correcting that. Your code change solution worked perfectly! Thanks so much!
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window
You have a jump point (line) for the error handler, but you do not have an On Error Goto statement at the beginning of the subroutine.
Finally, the Close Statement occurs after a Resume statement with a jump point (line) which prevents the close statement from running.