Validation Rule: 1 field to multiple fields

[BaronVonBongo]

Hi all,
Hope you can help. I'm very new to Access and programming in general so everything is above my head particularly syntax.

I am trying to create a validation rule whereby If a box is checked it makes sure that before saving and moving to the next form several, though not all fields are valid.

Ive tried many variations of:
([x] Is Not Null) OR ([Y] is Null) And ([x] Is Not Null) OR ([Y] is Null] etc etc for the fields I need covered.

Either the rule doesnt work at all or else all data input is invalid! Please someone help!:banghead:

Im sorry if this is really basic, but I have to start somewhere. Its for work and Im in trouble if I cant get this working. Help much appreciated. Cheers guys!

 

[bob fitz]

Data validation is best done in the forms Before Update event. See post#2 Here

 

[BaronVonBongo]

Thanks Bob. You are a busy man

Ok so my general idea is right? My syntax is correct.

Where is the 'before update event' box?

 

[bob fitz]

The code needs to be in the form's code module. See: The Basics of Writing and Testing VBA Code

 

[BaronVonBongo]

Where is that in office XP? I have 2010 myself, but work uses that for some reason

 

[BaronVonBongo]

Ok I found that. Is my syntax ok for use with VBA?

 

[BaronVonBongo]

Hi. I suppose I must come across as a vague newbie. i am! No idea how to work with VBA.
I suppose I should put this in context.
There is a checkbox on my form that when it is hit I want it to make sure that all fields are complete that need to be completed otherwise a message to check for errors would come up.
Otherwise if I wanted to be more complicated I would have it indicate from which field is null when it shouldn't be. Furthermore I would like it also to expct one particular value from a combo box if its ticked but one of another two values if not.

Furthermore I have a set of boxes which record which security answers are asked. Though I have a list of boxes and the questions asked in separate tables for each set so that each set displays the same security questions, is it possible to disallow the same question being asked twice in the same set, to have a tick box linked to the combo box to record whether the question was answered correctly or incorrectly and limit the total number of incorrects to 2/6 else security fail?

 

[BaronVonBongo]

And I have found the box that allows me to enter code.... and can see where code goes I think though it says

Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)

(HERE I PRESUME?)

End Sub

 

[bob fitz]

Yes, correct place for the code.

If you can explain your criteria for the validation, I will try to help with the actual code.

 

[bob fitz]

I think you may need something like this:
In the code below, validation fails if the check box called CheckBoxName is ticked but control called txt1 is Null (No Value) Or if the check box called CheckBoxName is ticked but control called num1 is not a number.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.CheckBoxName = True And IsNull(Me.txt1)) Or (Me.CheckBoxName = True And Not IsNumeric(Me.num1)) Then
  MsgBox "Validation FAILED"
  Cancel = True
Else
  MsgBox "Validation OK"
End If
End Sub

The attached db has a very simple example of this code in use.