Hello.
I need to be able to send an email with the most recently updated record every time a form is submitted, and save the record. Then, when the form is opened again, I need it to open a new record with blank fields.
I was using DoCmd.SendObject to email the entire Form, but that sends ALL the records, and I need it to send only the most recent one each time the form is submitted.
I have non-working code that I've been playing with... I will post it here but I think a complete re-write is necessary, and I'm fine with that.
Code:
Private Sub Submit_Click()
'Mandates data entry
Dim cCont As Control
For Each cCont In Me.Controls
If TypeName(cCont) = "TextBox" Then
If IsNull(cCont) Then
MsgBox "Please provide a description for " & cCont.Name & " field(s)!"
Exit Sub
End If
End If
Next cCont
For Each cCont In Me.Controls
If TypeName(cCont) = "ComboBox" Then
If IsNull(cCont) Then
MsgBox "Please provide a description for " & cCont.Name & " field(s)!"
Exit Sub
End If
End If
Next cCont
'Traps error when form closed without sending email
Dim db As DAO.Database
Dim rs As DAO.Recordset
On Error GoTo ErrorHandler
'Code here is the problem. Goal is to send email of most recent record, then move to a blank record (so the next time form opens its ready to edit), then save and exit the form.
DoCmd.GoToRecord acDataForm, "Entry Log", acLast
DoCmd.SendObject acSendModule, , acFormatXLSX, "email.name@company.com", "", "", "Incident Report", "", True
DoCmd.GoToRecord , , acNewRec
DoCmd.Close acForm, "Entry Log", acSaveYes
ExitHandler: 'Clean up as necessary and exit
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err 'Specific Case statements for errors we can anticipate, the "Else" catches any others
Case 2501 'Action OpenReport was cancelled.
DoCmd.Hourglass False
Resume ExitHandler
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub