How do I validate a field against the data in another field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please excuse what is probably a stupid question.
I have two fields side by side on my form "Service-Type" and "Service
Explanation".

If "Service-Type" = "NA", then I need the second field to be > space.

As an old Cobol programmer, I tried creating an Event Procedure using the If
Then Else routine but it throws out compile errors.

If someone can help, I would be extremely grateful.
 
In the before update event of the form, add a validation that check the
values in the fields

If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If
=============
Me = current form
Service-Type, Service Explanation = need to be the name of the fields in the
form
Len = check the size of the field
 
Hi Ofer,

Thanks for the help. I cut and pasted your code, putting it between the
Private Sub...... and the End Sub labels like this:

Private Sub Service_Explanation_BeforeUpdate(Cancel As Integer)
If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If

End Sub

I am obviously doing something wrong, though because the error message is
not appearing. I have checkled the field names and they are correct.

Should I make the DB Field in the table "Require=Yes" and "allow Zero Length
= Yes"? At the moment they are "No" and "Yes" respectively.

Sorry to be a pain.

Regards

Ray

Ofer said:
In the before update event of the form, add a validation that check the
values in the fields

If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If
=============
Me = current form
Service-Type, Service Explanation = need to be the name of the fields in the
form
Len = check the size of the field
--
\\// Live Long and Prosper \\//
BS"D


Ray said:
Please excuse what is probably a stupid question.
I have two fields side by side on my form "Service-Type" and "Service
Explanation".

If "Service-Type" = "NA", then I need the second field to be > space.

As an old Cobol programmer, I tried creating an Event Procedure using the If
Then Else routine but it throws out compile errors.

If someone can help, I would be extremely grateful.
Click to expand...
Click to expand...
 
You need to put the code on the BeforeUpdate event of the form, and not the
fields, so the validation will work before therecord is saved.

--
\\// Live Long and Prosper \\//
BS"D


Ray said:
Hi Ofer,

Thanks for the help. I cut and pasted your code, putting it between the
Private Sub...... and the End Sub labels like this:

Private Sub Service_Explanation_BeforeUpdate(Cancel As Integer)
If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If

End Sub

I am obviously doing something wrong, though because the error message is
not appearing. I have checkled the field names and they are correct.

Should I make the DB Field in the table "Require=Yes" and "allow Zero Length
= Yes"? At the moment they are "No" and "Yes" respectively.

Sorry to be a pain.

Regards

Ray

Ofer said:
In the before update event of the form, add a validation that check the
values in the fields

If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If
=============
Me = current form
Service-Type, Service Explanation = need to be the name of the fields in the
form
Len = check the size of the field
--
\\// Live Long and Prosper \\//
BS"D


Ray said:
Please excuse what is probably a stupid question.
I have two fields side by side on my form "Service-Type" and "Service
Explanation".

If "Service-Type" = "NA", then I need the second field to be > space.

As an old Cobol programmer, I tried creating an Event Procedure using the If
Then Else routine but it throws out compile errors.

If someone can help, I would be extremely grateful.
Click to expand...
Click to expand...
Click to expand...
 
Right. Done that. No change.

Could it be because the Service-Type field is a combi-box with details
extracted from a different table? This table has 2 columns, the first of
which is the data pulled into the Service-Type field on the form.



Ofer said:
You need to put the code on the BeforeUpdate event of the form, and not the
fields, so the validation will work before therecord is saved.

--
\\// Live Long and Prosper \\//
BS"D


Ray said:
Hi Ofer,

Thanks for the help. I cut and pasted your code, putting it between the
Private Sub...... and the End Sub labels like this:

Private Sub Service_Explanation_BeforeUpdate(Cancel As Integer)
If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If

End Sub

I am obviously doing something wrong, though because the error message is
not appearing. I have checkled the field names and they are correct.

Should I make the DB Field in the table "Require=Yes" and "allow Zero Length
= Yes"? At the moment they are "No" and "Yes" respectively.

Sorry to be a pain.

Regards

Ray

Ofer said:
In the before update event of the form, add a validation that check the
values in the fields

If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If
=============
Me = current form
Service-Type, Service Explanation = need to be the name of the fields in the
form
Len = check the size of the field
--
\\// Live Long and Prosper \\//
BS"D


:

Please excuse what is probably a stupid question.
I have two fields side by side on my form "Service-Type" and "Service
Explanation".

If "Service-Type" = "NA", then I need the second field to be > space.

As an old Cobol programmer, I tried creating an Event Procedure using the If
Then Else routine but it throws out compile errors.

If someone can help, I would be extremely grateful.
Click to expand...
Click to expand...
Click to expand...
 
You right, my mistake, the len doesn't return Zero when the field is Null.
Change it to
If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation] & "") = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If

--
\\// Live Long and Prosper \\//
BS"D


Ray said:
Right. Done that. No change.

Could it be because the Service-Type field is a combi-box with details
extracted from a different table? This table has 2 columns, the first of
which is the data pulled into the Service-Type field on the form.



Ofer said:
You need to put the code on the BeforeUpdate event of the form, and not the
fields, so the validation will work before therecord is saved.

--
\\// Live Long and Prosper \\//
BS"D


Ray said:
Hi Ofer,

Thanks for the help. I cut and pasted your code, putting it between the
Private Sub...... and the End Sub labels like this:

Private Sub Service_Explanation_BeforeUpdate(Cancel As Integer)
If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If

End Sub

I am obviously doing something wrong, though because the error message is
not appearing. I have checkled the field names and they are correct.

Should I make the DB Field in the table "Require=Yes" and "allow Zero Length
= Yes"? At the moment they are "No" and "Yes" respectively.

Sorry to be a pain.

Regards

Ray

:

In the before update event of the form, add a validation that check the
values in the fields

If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If
=============
Me = current form
Service-Type, Service Explanation = need to be the name of the fields in the
form
Len = check the size of the field
--
\\// Live Long and Prosper \\//
BS"D


:

Please excuse what is probably a stupid question.
I have two fields side by side on my form "Service-Type" and "Service
Explanation".

If "Service-Type" = "NA", then I need the second field to be > space.

As an old Cobol programmer, I tried creating an Event Procedure using the If
Then Else routine but it throws out compile errors.

If someone can help, I would be extremely grateful.
Click to expand...
Click to expand...
Click to expand...
 
Brilliant. Works like a treat.

Many many thanks for sorting that one out for me.

All the best

Ray


Ofer said:
You right, my mistake, the len doesn't return Zero when the field is Null.
Change it to
If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation] & "") = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If

--
\\// Live Long and Prosper \\//
BS"D


Ray said:
Right. Done that. No change.

Could it be because the Service-Type field is a combi-box with details
extracted from a different table? This table has 2 columns, the first of
which is the data pulled into the Service-Type field on the form.



Ofer said:
You need to put the code on the BeforeUpdate event of the form, and not the
fields, so the validation will work before therecord is saved.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,

Thanks for the help. I cut and pasted your code, putting it between the
Private Sub...... and the End Sub labels like this:

Private Sub Service_Explanation_BeforeUpdate(Cancel As Integer)
If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If

End Sub

I am obviously doing something wrong, though because the error message is
not appearing. I have checkled the field names and they are correct.

Should I make the DB Field in the table "Require=Yes" and "allow Zero Length
= Yes"? At the moment they are "No" and "Yes" respectively.

Sorry to be a pain.

Regards

Ray

:

In the before update event of the form, add a validation that check the
values in the fields

If Me.[Service-Type] = "NA" And Len(Me.[Service Explanation]) = 0 Then
MsgBox "Must enter value in Service Explanation"
Me.[Service Explanation].SetFocus ' Set the focus back to the field
Cancel = True ' wont let the user exit
End If
=============
Me = current form
Service-Type, Service Explanation = need to be the name of the fields in the
form
Len = check the size of the field
--
\\// Live Long and Prosper \\//
BS"D


:

Please excuse what is probably a stupid question.
I have two fields side by side on my form "Service-Type" and "Service
Explanation".

If "Service-Type" = "NA", then I need the second field to be > space.

As an old Cobol programmer, I tried creating an Event Procedure using the If
Then Else routine but it throws out compile errors.

If someone can help, I would be extremely grateful.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top