Error on required field if left blank

[BradCollins]

I have a combo box on my form that is a required field, what I would like to do is display a message if the user leaves it blank.

The code I have on the 'lostfocus' event is:

    If IsNull(Me.cmbCategory) Or Me.cmbCategory = "" Then
        MsgBox "You must select a category.", vbOKOnly
        Me.cmbCategory.SetFocus
        Exit Sub
    End If

Whilst this seems to work ok, if the user selects something from the list and then removes it again, then you get the standard microsoft error of "you must enter a value in this field"

How can I capture that error message and only have my own show
Thanks for you advice.

 

[missinglinq]

You have two problems, as I see it:

Since you're getting the standard Access message, I assume you have the field defined as required in the table definition. By doing that and doing validation in the form, you're essentially making it "required" twice! The simplest solution would be to go into you table design view and change the Required property to No, and rely on your validation code in the form to insure a value.

The second problem is the placement. If the user enters the combobox and doesn't make a selection or makes a selection and then deletes it, your code would work, but what if they simply ignore the combobox altogether? Your validation would fail.

Because of this, all validation routines intended to insure that a value is present in a control has to be in the form's BeforeUpdate event. I'd use something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.cmbCategory) Or Me.cmbCategory = "" Then
 MsgBox "You must select a category.", vbOKOnly            
 Cancel = True 
 Me.cmbCategory.SetFocus
End If
End Sub

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[BradCollins]

Thats the trick, thank you very much.

I knew it would be somehting simple I was missing

Thank you

 

[missinglinq]

Glad you got it working!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!