Cancel=True - Me.Undo

[D Willett]

Hi Guys

On after update of a combo I've come across a little dilemma. When a driver has not been selected the code should warn the user and clear the combo or undo the selection.
It doesn't and the case "Send to Driver" is still present in the combo.

Can anyone see any flaws in the code?

Code: Select all

Case "Send to Driver"

    Dim DB4 As DAO.Database
    Dim Rst4 As DAO.Recordset
    Set DB4 = CurrentDb

            Set Rst4 = DB4.OpenRecordset("tblAudit")
                
            Rst4.AddNew
            Rst4!LogUser = NetUser
            Rst4!LogDate = Date
            Rst4!LogDateTime = Now()
            Rst4!JobID = Forms!frmTransportDriverView!sbfTransportDriverViewAM.Form.JobID
            Rst4!Driver = Forms!frmTransportDriverView!sbfTransportDriverViewAM.Form.Driver
            Rst4!Status = Forms!frmTransportDriverView!sbfTransportDriverViewAM.Form.Status
            Rst4.Update
            Set Rst4 = Nothing
            Set DB4 = Nothing
            'Me.Refresh
            DoCmd.RunCommand acCmdSaveRecord
            
If IsNull(Driver) Or Me.Driver = "" Then
MsgBox "PLEASE SELECT A DRIVER TO SEND THIS EVENT TO", vbInformation, "SMS"
Cancel = True

Exit Sub
Else
If MsgBox("Send a new event driver ??", vbYesNo, "SMS") = vbYes Then
MsgBox "Message Sent " & fSendToDriver, , "Send Gmail from M&M Transport"
End If
End If

[HansV]

You write that you do this in the After Update event. Cancel is not an argument of the After Update event procedure, but of the Before Update event procedure.

[HansV]

Also: Shouldn't you check for an empty Driver before adding a record to tblAudit instead of after?

[D Willett]

Yes I overlooked that ..., does this look better ??

Code: Select all

Case "Send to Driver"
            
If IsNull(Driver) Or Me.Driver = "" Then
    MsgBox "PLEASE SELECT A DRIVER TO SEND THIS EVENT TO", vbInformation, "SMS"
    Me.Undo
    Exit Sub

Else
    If MsgBox("Send a new event driver ??", vbYesNo, "SMS") = vbYes Then
        Dim DB4 As DAO.Database
        Dim Rst4 As DAO.Recordset
        Set DB4 = CurrentDb
        Set Rst4 = DB4.OpenRecordset("tblAudit")
                
            Rst4.AddNew
            Rst4!LogUser = NetUser
            Rst4!LogDate = Date
            Rst4!LogDateTime = Now()
            Rst4!JobID = Forms!frmTransportDriverView!sbfTransportDriverViewAM.Form.JobID
            Rst4!Driver = Forms!frmTransportDriverView!sbfTransportDriverViewAM.Form.Driver
            Rst4!Status = Forms!frmTransportDriverView!sbfTransportDriverViewAM.Form.Status
            Rst4.Update
            Set Rst4 = Nothing
            Set DB4 = Nothing
            'Me.Refresh
            DoCmd.RunCommand acCmdSaveRecord

MsgBox "Message Sent " & fSendToDriver, , "Send Gmail from M&M Transport"
    End If
End If

Case Else
'do nothing
End Select

[HansV]

It looks better, but have you tested it?

[D Willett]

It seems to be clearing the combo so I'm pretty confident its ok ... ( famous last words ) :-)

Thanks again Hans.