Emailing Last Record & exit handling

[malamute20]

*Edit:

Guys, I'm normally all about learning to fish rather than having one handed to me, but at this point I'd happily take the fish and learn before my next meal.



What I need is code to: When button is pressed, confirm fields contain data. If so (and only if so) send an email with the data from those fields on form, save the record, and close the form.

I realize this is a tall order, and I've been playing with my partially-functional code contained in this thread for some time to get it working. I am finding that I lack the experience to get the bugs worked out. I'm really hoping someone who has the experience will be willing to take me by the hand and shove my face in the solution, so to speak, because I haven't been able to figure it out through methodical trial and error.

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.

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

[June7]

Mal,
Don't know what else I can offer, except to look at your project if you want to make it available.

I just tested my code at home with Access2010. I put code behind form button click and emailed values from current record. It worked perfect.

I did have to comment out the Attachment line.

I had to add only the Outlook library reference and of course set up Outlook on this machine.

[malamute20]

Mal,
Don't know what else I can offer, except to look at your project if you want to make it available.

I just tested my code at home with Access2010. I put code behind form button click and emailed values from current record. It worked perfect.

I did have to comment out the Attachment line.

I had to add only the Outlook library reference and of course set up Outlook on this machine.

Hey,

I commented out the attachment line to see if that made a difference, but I'm still getting the same error to pop. Let me see about getting my project uploaded...

**Edit: Looks like the file is too big by half to upload. Is there any way to break it down?

[June7]

Did you run Compact & Repair first? Zip it with Windows Compression. A much larger zip file is allowed as attachment. If still exceed 2mb, upload to a fileshare site such as box.net and post link to file.

[malamute20]

Ah, here we go.

[June7]

I opened your project and found that the VBA reference for MS Outlook was not set. So I set it and had no problem sending e-mail with your procedure.

[malamute20]

Very strange. I had it added, but you're obviously right, because I have it working now. I believe I must have missed saving it, or performed some other process error without realizing it.

Thank you very much. The email works, I still need exit handling to function correctly. Chiefly, I need to save the record and close the form when submit is pressed, and have a new, blank record be available next time the form is opened. Should I create a new ticket or can we continue here?

[June7]

You can set the form's DataEntry property to yes to force the form to always open to new record. Or in a button click to open form have code: DoCmd.GoToRecord , , acNewRec

[malamute20]

Works! Thank you.

How would I go about making my button close the form?

[June7]

DoCmd.Close acForm, Me.Name

[malamute20]

Sorry I thought I replied to this last week, but I've been swamped and haven't come back to this project until now.

This

DoCmd.Close acForm, Me.[Entry Log]

Is not working. It does not return an error. It just doesn't do what I understand it ought to: that is, close the Form on activation.

I've tried moving the line around in my code, keeping towards the bottom, to see if I can figure anything out, but it doesn't work anywhere I place it, even at the top of the code body.