Enforcing Data Entry with VBA Code

[Manuel]

Hi,

I have two fiends on a form: [Date] and [Reason], which tie back to a table
(via a query). I'd like to be able to force the user to enter a value in one
field if the other field is populated. I can't make these fields mandetory
in the table because they're not mandetory for each record, only if there is
data in one of the fields must the other field also be populated.

Any idea's how I'd go about this...

Thanks,
Manuel

 

[Allen Browne]

Open your table in design view.
Open the Properties box (View menu.)
Set the Validation Rule for the table to:
([Date] Is Null AND [Reason] Is Null) OR ([Date] Is Not Null AND
[Reason] Is Not Null)

Note that you cannot use the Validation Rule in the lower pane of table
design. That applies to one field only. Since you are comparing fields, you
must use the Validation Rule for the table (in the Properties box).

BTW, Date is a reserved word in JET and also in VBA. It will cause you
problems (not here, but in other contexts.) I suggest you rename the field.

There are more than 1000 field names that can cause problems. See:
http://allenbrowne.com/AppIssueBadWord.html

 

[Manuel]

That worked lake a charm thanks! And not to wory, the field is not actually
called "Date". It's called the Removal Date - but I didn't feel like typing
the whole thing out - ya talk about lazy!

Open your table in design view.
Open the Properties box (View menu.)
Set the Validation Rule for the table to:
([Date] Is Null AND [Reason] Is Null) OR ([Date] Is Not Null AND
[Reason] Is Not Null)

Note that you cannot use the Validation Rule in the lower pane of table
design. That applies to one field only. Since you are comparing fields, you
must use the Validation Rule for the table (in the Properties box).

BTW, Date is a reserved word in JET and also in VBA. It will cause you
problems (not here, but in other contexts.) I suggest you rename the field.

There are more than 1000 field names that can cause problems. See:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have two fiends on a form: [Date] and [Reason], which tie back to a
table
(via a query). I'd like to be able to force the user to enter a value in
one
field if the other field is populated. I can't make these fields
mandetory
in the table because they're not mandetory for each record, only if there
is
data in one of the fields must the other field also be populated.

Any idea's how I'd go about this...

Thanks,
Manuel

 

[Rod Plastow]

"Two fiends on a form?" Must be a hellish application!

Sorry, couldn't resist that.

Rod