Before Update VBA code not working

Question

I have a combo box (PublicationCombo) in a subform that requires an entry before the record can be saved. I have the "Required" property in the corresponding table set to "Yes". The following VBA code is in the subform's property:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(PublicationCombo) Then

    Cancel = True

    MsgBox "Select a value from the dropdown menu."

    Me.PublicationCombo.SetFocus

End If

End Sub

From everything I've read and viewed, this should work; but it doesn't. I'm stumped as to why it doesn't work. Any ideas?

BTW, without the above code Access displays an error message so the record can't be saved, but the message is not in plain language. That's why I want to insert the message box and keep the focus on the field in question.

<PII Removed>

Answer 1

Ken & everyone else who contributed

Happy to say I have solved the problem. The key was me studying and understanding what Ken said about Access setting a default value of zero in the bound table. Once I deleted the Default Value of "0" for all combo box fields (there are 3) and saved the changes, I was able to use the Before Update event as intended in the first place without using the On Error event. Not only that, but I was able to use IF/ElseIF to handle all three combo boxes in the subform. Now the user cannot leave the record without filling in the required fields. Setting the focus and displaying the dropdown menu should make the data entry idiot proof.

BTW, deleting the Before Update event and inserting Ken's code in the On Error event didn't make a difference. Probably because the bound table default value = 0.

Thank you to all of you. It wasn't a straight line, but I got there and learned a few things in the process.

Answer 2

I agree with Duane that a better solution would be to handle the error. The code for the Error event procedure would be along these lines:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
    Const NOT_NULL_VIOLATION = 3314
    Const MESSAGE_TEXT = "Select a value from the Publication control's drop down list."
    
    Select Case DataErr
        Case NOT_NULL_VIOLATION
        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
        Response = acDataErrContinue
        On Error Resume Next
        Me.PublicationCombo.SetFocus
        Me.PublicationCombo.Dropdown
        On Error Goto 0
        Case Else
        Response = acDataErrDisplay
    End Select
        
End Sub

Note that if the combo box is bound to a numeric foreign key, but set up to show the non-key text value from a referenced table, Access will probably have given the foreign key column a DefaultValue property of zero, so this should be removed if the column is to be Null if no value is entered in the form.

The error handling within the Select Case construct is to handle the, albeit unlikely, possibility that a user will delete the value from the combo box in an existing record, rather than leaving the control empty when inserting a new record in the subform.

Answer 3

For required field validation, I would recommend checking out how the Northwind 2 Developer Edition template does it. Once you import modValidation, validating a form is just a few lines of code. To see it in action, click on New Order, enter a value in the Comments field only, then try to save.