BeforeUpdate error trapping not working

[Galley]

I've got my form working, and all of the record updating is working fine, so now I am working on error trapping. I need to check if any of the two textboxes are empty, or nothing has been selected from the combobox. I am using the BeforeUpdate method. I am not getting any syntax errors. I am using an INSERT INTO SQL statement. Thanks.

related DB fields:
SOPNumber (text)
RevisionNumber (text)
TrainingDate (date/time)

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.cboSOPNumber) Then
    Cancel = True
    MsgBox "The SOP number is required", vbOKOnly, "Notice"
    Me.cboSOPNumber.SetFocus
End If

If IsNull(Me.txtRevisionNumber) Then
    Cancel = True
    MsgBox "The Revision Number is required", vbOKOnly, "Notice"
    Me.txtRevisionNumber.SetFocus
End If

If IsNull(Me.txtTrainingDate) Then
    Cancel = True
    MsgBox "The training date is required", vbOKOnly, "Notice"
    Me.txtTrainingDate.SetFocus
End If

 

[ghudson]

Yuo have to EXIT the sub if the null or empty string test = true.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(cboSOPNumber) Or cboSOPNumber = "" Then
    MsgBox "The SOP number is required", vbOKOnly, "Notice"
    Me.cboSOPNumber.SetFocus
    DoCmd.CancelEvent
    Exit Sub
End If

If IsNull(txtRevisionNumber) Or txtRevisionNumber = "" Then
    MsgBox "The Revision Number is required", vbOKOnly, "Notice"
    Me.txtRevisionNumber.SetFocus
    DoCmd.CancelEvent
    Exit Sub
End If

If IsNull(txtTrainingDate) Or txtTrainingDate = "" Then
    MsgBox "The training date is required", vbOKOnly, "Notice"
    Me.txtTrainingDate.SetFocus
    DoCmd.CancelEvent
    Exit Sub
End If

 

[Galley]

Thanks for your quick response, but that doesn't work either. It still appends the table with whatever info it does have, and there are no message boxes. I'm not sure if it a coding problem. Could I possibly have something set wrong?

 

[Rich]

Where's the Insert statement?

 

[ghudson]

Try testing the values of the text box in question with a message box.

Private Sub Form_BeforeUpdate(Cancel As Integer)

MsgBox "cboSOPNumber = " & cboSOPNumber [COLOR="Green"]'this will display the value of the cboSOPNumber combo box.  If the answer is nothing then you know the combo box is null.[/COLOR]
If IsNull(cboSOPNumber) Or cboSOPNumber = "" Then
    MsgBox "The SOP number is required", vbOKOnly, "Notice"
    Me.cboSOPNumber.SetFocus
    DoCmd.CancelEvent
    Exit Sub
End If

 

[ghudson]

I do not use unbound forms so I do not know if the BeforeUpdate event works the same for an unbound form as it does for a bound form. Unbound forms take too much extra coding. Access makes it so easy with a bound form and all the events that you can use and code for.

 

[Galley]

[Update]
I moved the code to before the INSERT INTO clause in the cmdAddRecords procedure and now it works perfectly.
I am using an unbound form because it has a multi-column text box that I only need to retrieve one field from.

 

[Researcher]

Yuo have to EXIT the sub if the null or empty string test = true.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(cboSOPNumber) Or cboSOPNumber = "" Then
    MsgBox "The SOP number is required", vbOKOnly, "Notice"
    Me.cboSOPNumber.SetFocus
    DoCmd.CancelEvent
    Exit Sub
End If

If IsNull(txtRevisionNumber) Or txtRevisionNumber = "" Then
    MsgBox "The Revision Number is required", vbOKOnly, "Notice"
    Me.txtRevisionNumber.SetFocus
    DoCmd.CancelEvent
    Exit Sub
End If

If IsNull(txtTrainingDate) Or txtTrainingDate = "" Then
    MsgBox "The training date is required", vbOKOnly, "Notice"
    Me.txtTrainingDate.SetFocus
    DoCmd.CancelEvent
    Exit Sub
End If

Thanks for posting the routine above GHudson..I was having the exact same problem, where all zeros where being written to the table, so I added the DoCmd.CancelEvent, and now everythings works great, I just wanted to take the time to thank you...

Reseacher