Validation Rule Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark S - 03 Dec 2005 03:17 GMT

I'm am a beginner at this so go easy on me. I am attempting to come up with a
validation rule in a form that must be met or the record won't be inserted in
the table. I need to know what syntax to use. Example: If field A is filled
then fields B and C (both combo dropdowns) must be filled or record will not
be inserted. Can this be done with a Validation rule at the form field level,
or am I going to have to learn VBA? Which field would i write the rule in?

Any help is appreciated...

Reply to this Message

Allen Browne - 03 Dec 2005 04:19 GMT

Use a table-level validation rule. No code, and it works no matter where the
record is entered.

1. Open your table in design view.

2. Open the Properties box (View menu.)

3. Beside the Validation Rule in the Properties box, enter:
([FieldA] Is Null) OR ([FieldB] Is Not Null AND [FieldC] Is Not Null)

The rule can be satisfied two ways:
- If FieldA is null, it's happy.
- If FieldA has an entry, the only other way the rule can be satisfied is if
the are 2 fields have an entry as well.

Because you are comparing fields, be sure to use the rule in the Properties
box (the rule for the table), and not the rule for a particular field (in
the lower pane of table design.)

It is also possible to use the BeforeUpdate event of the of the *form* (not
control) to perform record-level validation. But the table-level rule is
much better unless you have something that is too complex or you just want
to give warnings (allow the user to override the rule.)

Signature

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.

> I'm am a beginner at this so go easy on me. I am attempting to come up
> with a
[quoted text clipped - 7 lines]
> level,
> or am I going to have to learn VBA? Which field would i write the rule in?

Reply to this Message

Mark S - 03 Dec 2005 13:11 GMT

Thanks Allen. I tried the validation at the table level and got an error with
SQL not able to use two columns? I also tried to use at the form level with a
macro (beforeupdate) and nothing happened as it let the record be entered.
Here is what I used.

([RTC] Is Not Null) And ([Competitor] Is Not Null And [Reason] Is Not Null)
Meaning if the field Roll to Competitor amount field is not empty then the
Competitor (name) and Reason field are not allowed to be empty.

What am I doing wrong?

>I'm am a beginner at this so go easy on me. I am attempting to come up with a
>validation rule in a form that must be met or the record won't be inserted in
[quoted text clipped - 4 lines]
>
>Any help is appreciated...

Reply to this Message

Allen Browne - 03 Dec 2005 15:13 GMT

Are you sure you used the *table*-level validation rule, i.e. the one in the
Properties box?

You also have the OR as an AND.

Signature

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.

> Thanks Allen. I tried the validation at the table level and got an error
> with
[quoted text clipped - 23 lines]
>>
>>Any help is appreciated...

Reply to this Message

Mark S - 04 Dec 2005 15:16 GMT

I tried the table validation rule. This is not applicable. I can not use at
the table level. I think I may not have explained this correctly. I have a
single form that is being filled in and before the record is enter into the
table it must check for one field. If the Roll To Competitor field has data
in it, then the user must also fill in the Competitor (name) field as well as
the Reason field. The record can not be written if the aforementioned has not
been completed. I'll even tried VBA but get lost after

Private Sub Form_Current()
IF RTC Is Not Null Then
??????
End Sub

Thanks again for putting up with me being slow at this

Reply to this Message

Brendan Reynolds - 04 Dec 2005 15:34 GMT

This certainly could be done with a table-level validation rule ...

([Roll To Competitor] Is Null) Or ([Competitor] Is Not Null And [Reason] Is
Not Null)

... but if you want to do it in code it would look something like ...

It could be done in VBA, but not in the Form_Current event, you'll need the
Form_BeforeUpdate event ...

If Not IsNull([RollToCompetitor]) Then
If IsNull([Competitor]) Or IsNull([Reason]) Then
MsgBox "Please enter Competitor and Reason"
Cancel = True
End I
End If

Note: Is Null in SQL, IsNull() in VBA.

Signature

Brendan Reynolds

>I tried the table validation rule. This is not applicable. I can not use at
> the table level. I think I may not have explained this correctly. I have a
[quoted text clipped - 28 lines]
>>
>>Any help is appreciated...

Reply to this Message

Mark S - 05 Dec 2005 23:40 GMT

Brendan,
I really appreciate your help. One situation I ran into with this is that I
will have a condition that if I RTO (roll to own) then I can need to have
Competitor and Reason Null. I tried to modify this but can't get past the
If IsNull([Competitor]) Or IsNull([Reason]) part.

So when I enter RTO I get the Msgbox. I need to be able to RTO with
Competitor & reason Null but I also need to be able to RTC and if I do then
Competitor & Reason not null. Please help....thanks

>This certainly could be done with a table-level validation rule ...
>
[quoted text clipped - 20 lines]
>>>
>>>Any help is appreciated...

Reply to this Message

Mark S - 04 Dec 2005 16:02 GMT

First of all...Thanks so much your quci response. Here is what I created as
far as code.

Private Sub Form_BeforeInsert()

If Not IsNull([RTC]) Then
If IsNull([Competitor]) Or IsNull([Reason]) Then
MsgBox "Please enter Competitor and Reason fields"
Cancel = True
End If
End If
End Sub

As soon as i start entering data in the first field of the form I get an
error that says:

Procedure declaration does not match description of event or procedure having
the same name.

Not sure why I am getting this.

>I tried the table validation rule. This is not applicable. I can not use at
>the table level. I think I may not have explained this correctly. I have a
[quoted text clipped - 16 lines]
>>
>>Any help is appreciated...

Reply to this Message

Rick Brandt - 04 Dec 2005 16:11 GMT

> First of all...Thanks so much your quci response. Here is what I
> created as far as code.
>
> Private Sub Form_BeforeInsert()

BeforeInsert is the wrong event. Use BeforeUpdate.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Mark S - 04 Dec 2005 16:23 GMT

Thanks Rick, that worked. I really appreciate your quick repsonse and lesson
in VBA

>First of all...Thanks so much your quci response. Here is what I created as
>far as code.
[quoted text clipped - 22 lines]
>>>
>>>Any help is appreciated...

Reply to this Message