Validating field on the basis of the value of another field

[Jenni]

Sorry about the unwieldy title, it's difficult to describe it in any
other way.

Basically I have a table in Access 97 which contains a number of
records in different states. When originally created, I do not wish
to make the field Original ID required, however once the state of the
record is changed I require this field to be completed and not be the
value zero.

Tried a number of ValidationRule types at field and table property
value and found as a general rule my validationis too complicated.

i.e. the following was not well received ;)
=iif([Corrected]=Yes, <>0,0) - giving type mismatch errors
any other variant could not be assessed.

Moved to the form itself where the record will change state. I have a
simple save record button and would like to utilise this to validate
the text as follows

Dim strMsg As String

If IsNull(Me![ORIGINAL ID]) Then
If Me!CORRECTED = "Yes" Or Me!Check53 = "Yes" Then
strMsg = "Please Enter Original ID Number"
Else
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End If

It certainly reads and accepts the code, but when I enter validate
data it still jumps to the save record command and accepts it.

Anyone have any ideas on either how to resolve my code or a better way
of doing things.

Many thanks in advance

Jenni

[Pieter Linden]

Put all your validations in the BeforeUpdate event of the form?

[Allen Browne]

1. Open the table in design view.

2. Open the Properties box (View menu).

Locate the Validation Rule in the Propeties box, not the one in the lower
pane (which relates to a field, not the rule for the table).

3. Set the property to:
([Corrected] = False) OR ([Original ID] <> 0)

The rule is satisfied if Corrected is False (regardless of what Original ID
contains). If Corrected is not False, then the rule can only be satisifed if
Original ID contains a value other than zero (which means also that it must
contain a value, so cannot be Null).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Jenni" <jc******@strou dandswindon.co. uk> wrote in message
news:9f******** *************** ***@posting.goo gle.com...

Sorry about the unwieldy title, it's difficult to describe it in any
other way.

Basically I have a table in Access 97 which contains a number of
records in different states. When originally created, I do not wish
to make the field Original ID required, however once the state of the
record is changed I require this field to be completed and not be the
value zero.

Tried a number of ValidationRule types at field and table property
value and found as a general rule my validationis too complicated.

i.e. the following was not well received ;)
=iif([Corrected]=Yes, <>0,0) - giving type mismatch errors
any other variant could not be assessed.

Moved to the form itself where the record will change state. I have a
simple save record button and would like to utilise this to validate
the text as follows

Dim strMsg As String

If IsNull(Me![ORIGINAL ID]) Then
If Me!CORRECTED = "Yes" Or Me!Check53 = "Yes" Then
strMsg = "Please Enter Original ID Number"
Else
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End If

It certainly reads and accepts the code, but when I enter validate
data it still jumps to the save record command and accepts it.

Anyone have any ideas on either how to resolve my code or a better way
of doing things.

Many thanks in advance

Jenni

[Jenni]

Many Thanks Allen that worked a treat

For anyone else you may also be interested to know that when i
required muiltiple validation on the fields I could place a similar
statement in the validation rule of the field in my form. This
allowed me to assess twice the contents of one field on the basis of
the check box.

Thanks again

Jenni