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:

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

Open in new window


The code executes perfectly to run the email, but doesn't close the form afterwards.  Thanks.
Jason StewardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
For your IF statements, if you do not want the user to continue, use the Exit Sub in the IF, and then End IF:
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"
        Exit Sub
End If

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.
John TsioumprisSoftware & Systems EngineerCommented:
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
Jason StewardAuthor Commented:
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

Open in new window

Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Learn More
Jason StewardAuthor Commented:
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"

Open in new window

Shaun KlineLead Software EngineerCommented:
Your code would look like:
Private Sub Command799_Click()
On Error GoTo err_Error_handler

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"
        Exit Sub
ElseIf 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"
        Exit Sub
ElseIf 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"
        Exit Sub
End If

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)
    

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

DoCmd.Close acForm, "FieldVisitBookingEntryF"

 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

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jason StewardAuthor Commented:
Ohhhhhhhhh....   Thanks!
Jason StewardAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.