Validation Rule

[Radhika]

I have a table called 'tbl_Records'. The four fields I am having trouble with
are called 'MDACC#'(primary key 1), 'MDACC#1', 'Date'(Primary key 2) and
'Date1'. I am creating two of each of the fields to ensure that the same
numbers are entered both times. I triend to create a validate rule in the
field 'MDACC#1' that is as follows:
="MDACC#!" .
This is to ensure the the value of 'MDACC#1' is the same as that entered for
'MDACC#'.
I would like to do the same with the 'Date' field.
This rule does not seem to work. What am I doing wrong? Is there any other
way I can do this?

Thankyou!

 

[Ken Sheridan]

I'll take it on trust that you really do need the pairs of fields. Instead
of using a ValidationRule:

1. In the BeforeUpdate procedure of 'MDACC# put code along these lines:

Const ConMESSAGE = _
"Both MDACC# fields must have the same value."

If Not IsNull(Me.[MDACC#1]) Then
If [MDACC#1] <> [MDACC#] Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

2. In the BeforeUpdate procedure of 'MDACC#1 put:

Const ConMESSAGE = _
"Both MDACC# fields must have the same value."

If Not IsNull(Me.[MDACC#]) Then
If [MDACC#] <> [MDACC#1] Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

Do the same with the date fields. I'd strongly advise you not to call a
field or control date, however. It’s the name of a built in function, so
could cause problems. If you do use it as an object name, though, be sure to
wrap it in brackets when referring to it, like this: [Date]. But it would be
better to use something more specific like TransactionDate or whatever is
appropriate.

Make sure the Required property of all 4 fields is set to True (Yes) in the
table design to prevent any Nulls.

Ken Sheridan
Stafford, England

 

[Radhika]

That was very helpful..Thankyou!

I'll take it on trust that you really do need the pairs of fields. Instead
of using a ValidationRule:

1. In the BeforeUpdate procedure of 'MDACC# put code along these lines:

Const ConMESSAGE = _
"Both MDACC# fields must have the same value."

If Not IsNull(Me.[MDACC#1]) Then
If [MDACC#1] <> [MDACC#] Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

2. In the BeforeUpdate procedure of 'MDACC#1 put:

Const ConMESSAGE = _
"Both MDACC# fields must have the same value."

If Not IsNull(Me.[MDACC#]) Then
If [MDACC#] <> [MDACC#1] Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

Do the same with the date fields. I'd strongly advise you not to call a
field or control date, however. It’s the name of a built in function, so
could cause problems. If you do use it as an object name, though, be sure to
wrap it in brackets when referring to it, like this: [Date]. But it would be
better to use something more specific like TransactionDate or whatever is
appropriate.

Make sure the Required property of all 4 fields is set to True (Yes) in the
table design to prevent any Nulls.

Ken Sheridan
Stafford, England

I have a table called 'tbl_Records'. The four fields I am having trouble with
are called 'MDACC#'(primary key 1), 'MDACC#1', 'Date'(Primary key 2) and
'Date1'. I am creating two of each of the fields to ensure that the same
numbers are entered both times. I triend to create a validate rule in the
field 'MDACC#1' that is as follows:
="MDACC#!" .
This is to ensure the the value of 'MDACC#1' is the same as that entered for
'MDACC#'.
I would like to do the same with the 'Date' field.
This rule does not seem to work. What am I doing wrong? Is there any other
way I can do this?

Thankyou!