Struggling with a validation rule / before update

[thompjas]

Hi,

aplogies if my first post seems 'basic' to the more advanced users of this forum, I have an Access form where I wish to ensure that of a checkbox is ticked I need the inputter to enter a number in a corresponding field.

So..

If Sub Location Field is ticked (i.e. True), then Reference field should not be empty (i.e. Null)

I have tried a validation rule - this only worked when I deleted the reference field data and then tried to tab from it - on a new form I can leave this field blank and it 'skips past'



So then I tried a before update event, but cannot seem to work out the code that is needed

Any help greatfully received !!

[Bob Fitz]

Perhaps something like the following code in the Forms BeforeUpdate event

If Me.Location = True Then
  If IsNull(Me.Reference) Then
    Cancel = True
    MsgBox "Please enter Reference"
    Me.Reference.SetFocus
  End If
End If 

[thompjas]

Hi, thanks for the reply, this still allows the user to 'skip' entry of the reference field, though strangley if I add a value and then delete it the code prompts me to enter a reference number !

Then I get an error



Any ideas ??

here is the code entered as a before update event in the forms design page

Private Sub HOIDNumber_BeforeUpdate(Cancel As Integer)

If Me.SubLocation = True Then
If IsNull(Me.HOIDNumber) Then
Cancel = True
MsgBox "Please enter a Head Office ID Number"
Me.HOIDNumber.SetFocus
End If
End If

[thompjas]

Sorry forgot screen shot or error message

[Missinglinq]

You didn't follow Bob's instructions!

code in the Forms BeforeUpdate event

You put your code in the Control's BeforeUpdate event; not the same thing!

Private Sub HOIDNumber_BeforeUpdate(Cancel As Integer)

needs to be

Private Sub Form_BeforeUpdate(Cancel As Integer)

Validation to see if a field is populated cannot be done in the Control's event for the very reason you gave...the user can simply 'skip' entry in the pertinent Control to foil the validation!

Linq ;0)>

[thompjas]

Sorry, newbie mistake - now sorted - top bananas !!!

[Missinglinq]

...Sorry, newbie mistake...

Nothing to be sorry about...it's called learning!

Glad we could help!

Linq ;0)>