Email sending, Record Saving and Error Handling.

[abodi]

Hi Everyone,

I have a form with fields that people enter information into.
I have code to confirm all fields are not empty - This works.
The code then sets up an email with details from the email - Generating the email is fine.
Then this is where i'm clearly missing something about error handling.
If the email sends (no error)
Then I would like the record to save, clear the form (i've not done this yet) and move to the next record (currently it keeps saving over the current record.)
Else the email doesn't send and it does generate the error, but at this point i'd like the proceedure to exit without clearing the form or saving the record.

Essentially unless the email sends no action is taken.



Currently the silly thing, saves regardless and keeps saving over the current record.

Here is my feeble code.
Any help on where i'm going wrong would be greatly appreciated.

Private Sub cmdSave_NLR_Click()
    On Error GoTo HandleErr
    
TestingFields:
        If NLR_ApplicantName = "" Or CboTeamNumber = "Please Select" Or LeaveType = "Please Select" Or LeaveStartDate = "" Or LeaveEndDate = "" Or Status = "Please Select" Or ApprovedBy = "" Then
        MsgBox "You must complete all the required fields."
        Exit Sub
        End If

    EmailProc:
    '-- The Variables are used for sending the leave request email.
    Dim stRecipient As String   '-- This variable will contain the primary recipient.
    Dim stCC As String          '-- This variable is  who is included CC.
    Dim stSubject As String     '-- This variable is the subject line.
    Dim stBody As String        '-- This variable is the body of the Email.
    
    
    '-- This sets the information contained in the variables.
    stSubject = "UMG Leave Request is " & Status & ".  ** " & stGivenName & " " & stSurname & " from Team " & CboTeamNumber
    stRecipient = NLR_ApplicantName
   stBody = "Your leave request for " & LeaveStartDate & " to " & LeaveEndDate & " has been " & Status & "." & Chr$(13) & Chr$(13) & _
             "What you need to do next...."
    
    DoCmd.SendObject , , acFormatTXT, stRecipient, "Jimmy@abc.com", , stSubject, stBody, -1 '-- Email command.
    
    HandleErr:
    If Err.Number = 2501 Then
    MsgBox "The automated email was cancelled or failed." & Chr$(13) & Chr$(13) & "Please inform the applicant of the status of this application"
    Exit Sub
    Else
    MsgBox Err.Description
    End If

End Sub

Private Sub Save_NLR() '-- Saves the record

On Error GoTo cmdSave_NLR_Click_Err    On Error Resume Next
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , "", acNext
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If

cmdSave_NLR_Click_Exit:
    Exit SubcmdSave_NLR_Click_Err:
    MsgBox Error$
    Resume cmdSave_NLR_Click_ExitEnd Sub

Looking at the code, i'm not sure how the second sub is even being called

[June7]

I don't understand Sub Save_NLR at all. I don't see it called. Why is there two On Error lines? There is no cmdSave_NLR_Click_Err: to go to.

If you don't want data to overwrite existing record, then move to new record row first.

Record is committed to table when form closes, move to another record, or run code. If you don't want an initiated record saved then must cancel the update action and undo edits.

[abodi]

Yeah I got confused, because i thougth it was saving and not updating the subform, but it was only saving because i was clicking away and coming back.

I fixed the code, and used CALL to call the second sub. everything works now, except saving when you click away which is a problem. because I thought i'd use a menu and not opening and not closing screens with buttons.



Would a on click command on the menu buttons to undo record before changing work in that instance?

[burrina]

NO. You would need a Undo command to clear the required fields. You need to keep the focus on that form until complete if possible.

[June7]

This is a form/subform arrangement? Main form and subform have the same data source? Have you considered a Split form?

[abodi]

NO. You would need a Undo command to clear the required fields. You need to keep the focus on that form until complete if possible.

Which would certainly work if i had a menu screen that I clicked on a button which then opened the new screen in it's own window, because then I could used an exit without saving button to undo the record.

With the menu system as show in the screenshow above, you can click between the screens at will, which then saves the record.

This is a form/subform arrangement? Main form and subform have the same data source? Have you considered a Split form?

Theres the menu on the left, which calls a form on the right depending on the button chosen.
(edit: its a nagivation form)

[burrina]

I would use a OnGotFocusEvent for the subform and if the fields on the main form are not filled in, then re set focus back to the main form. If I understand correctly.

Also, if all of those fields must contain data, then use something like:

Dim Ctrl As Control
For Each Ctrl In Me.Controls
If Ctrl.ControlType = acTextBox Or Ctrl.ControlType = acComboBox Then
If IsNull(Ctrl) = True Then
MsgBox "Hey...You didn't fill in all the data " & _
"fields for this record. Please do so now.", _
vbExclamation, "** Required Data Missing **"
Cancel = True
Exit For
End If
End If
Next Ctrl

Or, simply set required in the table.

HTH