Validation Rule Help

[Mark S via AccessMonster.com]

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...

 

[Allen Browne]

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.)

 

[Mark S via AccessMonster.com]

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?

 

[Allen Browne]

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.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
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
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...

 

[Mark S via AccessMonster.com]

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

 

[Brendan Reynolds]

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.

 

[Mark S via AccessMonster.com]

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
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

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...

 

[Rick Brandt]

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.

 

[Mark S via AccessMonster.com]

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.

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]

 

[Mark S via AccessMonster.com]

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 ...

([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.

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]