Validation of one field against another

[tv]

Hi, I am new to this type of question and I have searched the forum but have
found no answer, so here goes.

I have a form which contains a subform which all pulls from a query linked
to a table which will be updated.. The form has drop-downs to select the
center and grid #. Once selected, the subform opens with the data.

The subform has 4 categories. If a review is due, the "Current Year Review"
field will say "yes". If "yes", the "start date", "completed date" and
"technician" fields are to be updated.

I would like to set up a validation for each input field. If there is a
"yes" in "current year review", then require the other 3 fields to be
updated. If there is nothing in the "current year review" field then either
lock the other 3 fields or do not show the entire line for that category.

Can someone help?
thanks

 

[Barry Gilbert]

Hi, I am new to this type of question and I have searched the forum but have
found no answer, so here goes.

I have a form which contains a subform which all pulls from a query linked
to a table which will be updated.. The form has drop-downs to select the
center and grid #. Once selected, the subform opens with the data.

The subform has 4 categories. If a review is due, the "Current Year Review"
field will say "yes". If "yes", the "start date", "completed date" and
"technician" fields are to be updated.

I would like to set up a validation for each input field. If there is a
"yes" in "current year review", then require the other 3 fields to be
updated. If there is nothing in the "current year review" field then either
lock the other 3 fields or do not show the entire line for that category.

This will require a little code.
First, set the Validation Text properties of the three fields to some
message like "Start Date cannot be left blank". This is the message that will
display if we check for blanks for each of the three fields.

In the form's OnCurrent event, go to the code editor (click the elipsis ...
to the right) and paste in this code:

' This assumes that the controls have the same name as the fields
they're bound to. The terms in square brackets should refer to the names of
the form's controls (textboxes, combobxes, etc.)
Private Sub Form_Current()
Const NOT_NULL As String = "Is Not Null"
Dim blnLockControls As Boolean
If Me.[Current Year Review]="Yes" Then
Me.[Start Date].ValidationRule = NOT_NULL
Me.[Completed Date].ValidationRule = NOT_NULL
Me.[technician].ValidationRule = NOT_NULL
Else
Me.[Start Date].ValidationRule = ""
Me.[Completed Date].ValidationRule = ""
Me.[technician].ValidationRule = ""
blnLockControls = True
End If
Me.[Start Date].Locked = blnLockControls
Me.[Completed Date].Locked= blnLockControls
Me.[technician].Locked= blnLockControls
End Sub

Setting the validation rule tells Access to validate the data (make sure
it's not null) and display the message if it fails.

Barry

 

[Jeff Boyce]

For starters, take a look at the BeforeUpdate event for the form. This is
the event you could use 'after the fact' to see if controls had been filled
in.

Also take a look at the .Enabled and .Visible properties of the several
controls. These could be set to match the value of your Yes/No field.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[tv]

Barry,

Thanks for your quick reply. I have done as you stated but it still allows
me to enter one date and then use the drop downs on the form to move to
another grid without filling in the second date and the technician. At
times, we will just be looking to see what needs to be completed and not
filling anything in; however, other times we will be filling in all 3
required fields. Basically speaking, if one of the 3 fields are filled in,
then all fields should be filled in.

Also, I have 3 other categories that need the same code on this form. How
can I tag this into the code?

Here is what I have done which is not working on the 5 yr survey.

thanks again
--
tv

Hi, I am new to this type of question and I have searched the forum but have
found no answer, so here goes.

I have a form which contains a subform which all pulls from a query linked
to a table which will be updated.. The form has drop-downs to select the
center and grid #. Once selected, the subform opens with the data.

The subform has 4 categories. If a review is due, the "Current Year Review"
field will say "yes". If "yes", the "start date", "completed date" and
"technician" fields are to be updated.

I would like to set up a validation for each input field. If there is a
"yes" in "current year review", then require the other 3 fields to be
updated. If there is nothing in the "current year review" field then either
lock the other 3 fields or do not show the entire line for that category.

This will require a little code.
First, set the Validation Text properties of the three fields to some
message like "Start Date cannot be left blank". This is the message that will
display if we check for blanks for each of the three fields.

In the form's OnCurrent event, go to the code editor (click the elipsis ...
to the right) and paste in this code:

' This assumes that the controls have the same name as the fields
they're bound to. The terms in square brackets should refer to the names of
the form's controls (textboxes, combobxes, etc.)
Private Sub Form_Current()
Const NOT_NULL As String = "Is Not Null"
Dim blnLockControls As Boolean
If Me.[Current Year Review]="Yes" Then
Me.[Start Date].ValidationRule = NOT_NULL
Me.[Completed Date].ValidationRule = NOT_NULL
Me.[technician].ValidationRule = NOT_NULL
Else
Me.[Start Date].ValidationRule = ""
Me.[Completed Date].ValidationRule = ""
Me.[technician].ValidationRule = ""
blnLockControls = True
End If
Me.[Start Date].Locked = blnLockControls
Me.[Completed Date].Locked= blnLockControls
Me.[technician].Locked= blnLockControls
End Sub

Setting the validation rule tells Access to validate the data (make sure
it's not null) and display the message if it fails.

Barry

 

[Klatuu]

The simplest way to do this would be to filter the record source for the
subform to include only rows where the "Current Year Review Field" is yes (or
whatever value makes it say yes)

Then use the subform's Before Update event to ensure all field have been
filled in with acceptable values.

 

[tv]

Since I am new to this. I don't know how to filter so many fields on the main
form. I have 4 fields that could say yes or only some could say yes. Can
you explain in detail how I could do this along with the other two filters I
have on the form: Center and Grid?

thanks

 

[tv]

Jeff,

I am not familiar with how to do this. Can you explain in detail?

thanks

 

[Klatuu]

Just string them together with AND

SomeField = "Bozo" AND AnotherField = 3999 AND WhatEverField = "yes"

 

[tv]

Barry,

I feel that I am so close to a solution with your code but I cannot quite
get it to work. First let me update you if I may. I have created actually 4
separate subforms on my master form where I choose a center and a grid #.
Now my "Current Year Review" field is always yes for the 4 types of surveys.
When I use my filters in the main form only those with yes for each survey
type appear. One problem solved.

However, when I fill in the "start date" field for each survey type, it
still lets me choose another grid from the filter before I fill in the
"completed" date and the "technician". This will cause a big error in our
system. I am filling the dates in with a calendar control from Allen Broomes
website, could that be the issue.

Here is the code (of yours revised) that I plan to use on each subform when
I can get it to work. I am hoping if I put a date in the "start date" but
put nothing in the other two fields, "completed" and "technician" for each
form, that it will stop me from choosing another grid from the filter.

I would appreciate any help you can give me.

Here is the code I am using.
thanks

Private Sub Form_Current()
Const NOT_NULL As String = "Is Not Null"
'Dim blnLockControls As Boolean
If Me.[FiveYr_Date_Started] = NOT_NULL Then
Me.[FiveYr_Date_Completed].ValidationRule = NOT_NULL
Me.[FiveYr_Technician].ValidationRule = NOT_NULL
Else
Me.[FiveYr_Date_Completed].ValidationRule = ""
Me.[FiveYr_Technician].ValidationRule = ""
'blnLockControls = True
End If
'Me.[FiveYr_Date_Started].Locked = blnLockControls
'Me.[FiveYrComplete].Locked = blnLockControls
'Me.[FiveYr_Technician].Locked = blnLockControls
End Sub

--
tv

Hi, I am new to this type of question and I have searched the forum but have
found no answer, so here goes.

I have a form which contains a subform which all pulls from a query linked
to a table which will be updated.. The form has drop-downs to select the
center and grid #. Once selected, the subform opens with the data.

The subform has 4 categories. If a review is due, the "Current Year Review"
field will say "yes". If "yes", the "start date", "completed date" and
"technician" fields are to be updated.

I would like to set up a validation for each input field. If there is a
"yes" in "current year review", then require the other 3 fields to be
updated. If there is nothing in the "current year review" field then either
lock the other 3 fields or do not show the entire line for that category.

This will require a little code.
First, set the Validation Text properties of the three fields to some
message like "Start Date cannot be left blank". This is the message that will
display if we check for blanks for each of the three fields.

In the form's OnCurrent event, go to the code editor (click the elipsis ...
to the right) and paste in this code:

' This assumes that the controls have the same name as the fields
they're bound to. The terms in square brackets should refer to the names of
the form's controls (textboxes, combobxes, etc.)
Private Sub Form_Current()
Const NOT_NULL As String = "Is Not Null"
Dim blnLockControls As Boolean
If Me.[Current Year Review]="Yes" Then
Me.[Start Date].ValidationRule = NOT_NULL
Me.[Completed Date].ValidationRule = NOT_NULL
Me.[technician].ValidationRule = NOT_NULL
Else
Me.[Start Date].ValidationRule = ""
Me.[Completed Date].ValidationRule = ""
Me.[technician].ValidationRule = ""
blnLockControls = True
End If
Me.[Start Date].Locked = blnLockControls
Me.[Completed Date].Locked= blnLockControls
Me.[technician].Locked= blnLockControls
End Sub

Setting the validation rule tells Access to validate the data (make sure
it's not null) and display the message if it fails.

Barry