Validation Rules and Code

[Dalan]

I apparently need a bit of assistance regarding the structure of some
validation code on the BeforeUpdate or AfterUpdate event on a form for
several fields that need to controlled. I did search the archives, but
found nothing precise enough to address my specific needs.

1. There are three text box fields on the form and one of the three
need to have a text value provided. I simply need to ensure that one
field has data, not two, not three and that a choice has to be made so
any of the three cannot be null before leaving record.

2. The other validation is similar. There is a check box on the form
and if checked by a user, then two other fields (one date, one
currency) on the same form required data to be entered before leaving
the record.

Thanks for anyone's assistance. Dalan

[Arno R]

Dalan,
I made some assumptions about your controlnames:
You have to adjust this of course.
(Air code! Not tested)

Copy the functions FrmValidationx() in your forms module and use this code in the BeforeUpdate event
of your form

If not FrmValidation1() or not FrmValidation2() then
Msgbox "Input is not correct, bla bla"
cancel=true
end if
Function FrmValidation1() as integer
Dim intFilled as integer
If not IsNull(Me!txtFld1) Then intFilled=intFilled +1
If not IsNull(Me!txtFld2) Then intFilled=intFilled +1
If not IsNull(Me!txtFld3) Then intFilled=intFilled +1
If IntFilled =1 Then FrmValidation1 =true
end function

Function FrmValidation2() as integer
If Me!chkBox then
If not IsNull(DateField) AND not IsNull(CurField) Then
FrmValidation2 =true
end if
end if
end function

--
Hope this helps
Arno R
"Dalan" <ot***@safe-mail.net> schreef in bericht
news:50**************************@posting.google.c om...

I apparently need a bit of assistance regarding the structure of some
validation code on the BeforeUpdate or AfterUpdate event on a form for
several fields that need to controlled. I did search the archives, but
found nothing precise enough to address my specific needs.

1. There are three text box fields on the form and one of the three
need to have a text value provided. I simply need to ensure that one
field has data, not two, not three and that a choice has to be made so
any of the three cannot be null before leaving record.

2. The other validation is similar. There is a check box on the form
and if checked by a user, then two other fields (one date, one
currency) on the same form required data to be entered before leaving
the record.

Thanks for anyone's assistance. Dalan

[Allen Browne]

1. Open your table in design view.

2. Click the Properties box View menu).

3. Set the Validation Rule in the Properties box to:
(Abs([Text1] Is Not Null)+Abs([Text2] Is Not Null)+Abs([Text3] Is Not
Null))=1

4. Repace "Text1", "Text2", and "Text3" with the name of your 3 fields.
Notes:
Make sure this goes into the Validation Rule for the table (in the
properties box), not that of a field (in the lower pane of table design).

It works like this:
If Text1 has a value, the phrase:
[Text1] Is Not Null
returns True. To Access, that's -1.
The Abs() function converts that to a number without the negative.
If it's not the case (i.e. if the field IS null), the value is zero.
Summing the 3 values gives you a count of the fields that have a value.
The rule is only satisfied if the count is exactly 1 (not 0 or 2 or 3).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

I apparently need a bit of assistance regarding the structure of some
validation code on the BeforeUpdate or AfterUpdate event on a form for
several fields that need to controlled. I did search the archives, but
found nothing precise enough to address my specific needs.

1. There are three text box fields on the form and one of the three
need to have a text value provided. I simply need to ensure that one
field has data, not two, not three and that a choice has to be made so
any of the three cannot be null before leaving record.

2. The other validation is similar. There is a check box on the form
and if checked by a user, then two other fields (one date, one
currency) on the same form required data to be entered before leaving
the record.

Thanks for anyone's assistance. Dalan

[Phil Stanton]

Something like
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(Field1) And IsNull(Field2) And IsNull(Field3) Then
Exit Sub
End If

If IsNull(Field1) And Not IsNull(Field2) And IsNull(Field3) Then
Exit Sub
End If

If IsNull(Field1) And IsNull(Field2) And Not IsNull(Field3) Then
Exit Sub
End If

MsgBox "1 field only", vbCritical
Cancel = True

End Sub
Phil

"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

I apparently need a bit of assistance regarding the structure of some
validation code on the BeforeUpdate or AfterUpdate event on a form for
several fields that need to controlled. I did search the archives, but
found nothing precise enough to address my specific needs.

1. There are three text box fields on the form and one of the three
need to have a text value provided. I simply need to ensure that one
field has data, not two, not three and that a choice has to be made so
any of the three cannot be null before leaving record.

2. The other validation is similar. There is a check box on the form
and if checked by a user, then two other fields (one date, one
currency) on the same form required data to be entered before leaving
the record.

Thanks for anyone's assistance. Dalan