Control Validation Rules Doesn't Work

[ions]

Dear Access expert.

When I use the Validation Rule Property

<>0 Or Not Is Null

Nothing is triggered if the user doesn't type anything.

So I tried code.

On exit AND lost focus events of the control, I trigger the below code. The code works except for the SetFocus line. Unfortunately the focus doesn't return to the control.

Private Sub cboGeneratorNumber_LostFocus()
    If Me.cboGeneratorNumber = 0 Or IsNull(Me.cboGeneratorNumber) Then
        MsgBox "You must select a Generator.", , appTitle
        Me.cboGeneratorNumber.SetFocus
    End If
End Sub

Thanks for your help.

 

[John Big Booty]

You will need to set the focus to any other control before you can return it to the original control, try something like;

Private Sub cboGeneratorNumber_LostFocus()
    If Me.cboGeneratorNumber = 0 Or IsNull(Me.cboGeneratorNumber) Then
        MsgBox "You must select a Generator.", , appTitle
        [COLOR="DarkOrange"][B]Me.AnyotherConrolOnYourForm.SetFocus[/B][/COLOR]
        Me.cboGeneratorNumber.SetFocus
    End If
End Sub

 

[ions]

Worked Great. Thanks John.

Why didn't the validation work?

 

[John Big Booty]

...

Why didn't the validation work?

I'm not sure.

Is the control holding numeric or text data. I have been able to get your Validation rule to work on a numeric field but not on a text field. BTW it should be expressed as.

<>0 Or Is Not Null

 

[John Big Booty]

My bad, I've just be playing around with this what you need is an AND not an OR, try;

<>0 And Is Not Null

 

[missinglinq]

John, didn't we just answer this same question for the poster cheer?

John's approach is correct if you have to reset focus from the LostFocus event, but I have to tell you you're making a major blunder here!

You're running Validation code to insure that a control actually has data in it, but you're trying to run it off of the LostFocus event of the control, and this approach has a fatal flaw!

If the user simply does not move into the textbox at all, the code is useless!

Code to insure that a field has data in it has to be done in the Form_BeforeUpdate event, which fires just before the record is saved, whether the user ever moves into the textbox in question or not.

 

[ions]

Missingling... I Put it in both areas. Form_BeforeUpdate and lost focus. Thanks for the reminder.

JOHN .... it's a combo box which is bound to a long Integer hidden column.

I put : <>0 And Is Not Null in the validation rule and it still didn't trigger.

 

[ions]

Probably the best place to put the validation rule would be in Table Design? Correct?

Then I wouldn't have to do any of this programming?

I was putting the validation rule in the control property.

 

[John Big Booty]

John, didn't we just answer this same question for the poster cheer?

.......

Phew, I thought I was having De Ja Vue

 

[missinglinq]

Or as Yogi Berra once said, "De Ja Vue, all over again!"

 

[CEH]

A combo? Did you try "If Me.cboGeneratorNumber.column(0) = 0 Or IsNull(Me.cboGeneratorNumber.column(0))

 

[ions]

The VBA works. The validation rule doesn't trigger.

 

[CEH]

Try this.......
If Me.cboGeneratorNumber.column(0) = " " Or IsNull(Me.cboGeneratorNumber.column(0)) on the beforeupdate event and the form onCurrent
Or just try the "IsNull(Me.cboGeneratorNumber.column(0)"

 

[ions]

CEH,

Yes that works.

What doesn't work is the Validation rule in the control property.

"<>0 And Is Not Null in the validation rule and it still didn't trigger"