Validation Rule

  • Thread starter Thread starter S Jackson
  • Start date Start date
S

S Jackson

I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the controls is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
 
Try something like the following in the form's after update...


Private Sub Form_AfterUpdate()
a:
On Error GoTo Err_Form_Afterupdate
Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Dim strInput As String
strInput = InputBox("CSA Date is required")
If strInput <> "" Then
Do While strInput < 0 Or strInput > 1
strInput = InputBox(strMsg)
Loop
SCADate = strInput
End If
Loop

Exit_Form_Afterupdate:
Exit Sub
Err_Form_Afterupdate:
GoTo a
End Sub




Rick B




I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the controls is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
 
Oops - I copied that code from my database. You can remove or to change the
line...
Do While strInput < 0 Or strInput > 1
....to fit your edits.

Rick B



Try something like the following in the form's after update...


Private Sub Form_AfterUpdate()
a:
On Error GoTo Err_Form_Afterupdate
Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Dim strInput As String
strInput = InputBox("CSA Date is required")
If strInput <> "" Then
Do While strInput < 0 Or strInput > 1
strInput = InputBox(strMsg)
Loop
SCADate = strInput
End If
Loop

Exit_Form_Afterupdate:
Exit Sub
Err_Form_Afterupdate:
GoTo a
End Sub




Rick B




I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the controls is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
 
Rick,
Validation functions like that should go in the form's BeforeUpdate event.
It's too late by the time you get to the AfterUpdate event -- the record has
already been written.
 
Rick:

Thank you so much for your prompt reply. I managed to get the code to work.
I took out the Do While Statement within the If statement.

However, my understanding of VB is extremely basic and I have a question
about the code. I figure since I am going to use it, I need to understand
what it is doing. I looked in my handbook, but can't figure this part out:

If strInput <> "" Then

Forgive my ignorance, but doesn't that statement say: If strInput (the
Input Box) is less than or greater than "" Then . . . Oh boy, in my
ignorance, I can't even figure out how to ask what I want to know. Huh, .
.. does it mean that if the user doesn't put anything in the Input box (less
than) or the user "does" put something in the Input box (greater than),
CSA=strInput? To me that means the user is allowed to exit the Input box
(less than) without making an entry.

I did tested the code out and it does work - the user cannot exit the input
box unless they put in a date. But, I just don't understand how based on my
understanding of the above statement. What part of the code forces the user
to make an entry into the Input box? The Input box itself?

Save me from my ignorance.
S. Jackson
 
That is simply saying that if the user makes an entry, then check to see
that it is between 0 and 1 (in my case). You could probably do the same
thing by saying something like..

If IsNotNull(strInput) Then

What requires it to be enterd are the...

Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Loop




Rick B





to leave the message box blank (in other words, enters a value equal to "",
then...) In this case, it keeps asking. You could do something like pop up
another message that says "entry is required" and the loop back to asking
for an entry.

All your questions are about the same thing. You asked what the <>"" and
Hope that helps,

Rick B


Rick:

Thank you so much for your prompt reply. I managed to get the code to work.
I took out the Do While Statement within the If statement.

However, my understanding of VB is extremely basic and I have a question
about the code. I figure since I am going to use it, I need to understand
what it is doing. I looked in my handbook, but can't figure this part out:

If strInput <> "" Then

Forgive my ignorance, but doesn't that statement say: If strInput (the
Input Box) is less than or greater than "" Then . . . Oh boy, in my
ignorance, I can't even figure out how to ask what I want to know. Huh, .
.. does it mean that if the user doesn't put anything in the Input box (less
than) or the user "does" put something in the Input box (greater than),
CSA=strInput? To me that means the user is allowed to exit the Input box
(less than) without making an entry.

I did tested the code out and it does work - the user cannot exit the input
box unless they put in a date. But, I just don't understand how based on my
understanding of the above statement. What part of the code forces the user
to make an entry into the Input box? The Input box itself?

Save me from my ignorance.
S. Jackson


Rick B said:
Oops - I copied that code from my database. You can remove or to change the
line...
Do While strInput < 0 Or strInput > 1
...to fit your edits.

Rick B



Try something like the following in the form's after update...


Private Sub Form_AfterUpdate()
a:
On Error GoTo Err_Form_Afterupdate
Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Dim strInput As String
strInput = InputBox("CSA Date is required")
If strInput <> "" Then
Do While strInput < 0 Or strInput > 1
strInput = InputBox(strMsg)
Loop
SCADate = strInput
End If
Loop

Exit_Form_Afterupdate:
Exit Sub
Err_Form_Afterupdate:
GoTo a
End Sub




Rick B




I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the controls is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
Click to expand...
 
Lynn is correct. I just tried it and was able to get around it.

Rick B




Rick,
Validation functions like that should go in the form's BeforeUpdate event.
It's too late by the time you get to the AfterUpdate event -- the record has
already been written.
 
If IsNotNull(strInput) results in a error during compliation:

Sub or Function not defined.

S. Jackson

Rick B said:
That is simply saying that if the user makes an entry, then check to see
that it is between 0 and 1 (in my case). You could probably do the same
thing by saying something like..

If IsNotNull(strInput) Then

What requires it to be enterd are the...

Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Loop




Rick B





to leave the message box blank (in other words, enters a value equal to "",
then...) In this case, it keeps asking. You could do something like pop up
another message that says "entry is required" and the loop back to asking
for an entry.

All your questions are about the same thing. You asked what the <>"" and
Hope that helps,

Rick B


Rick:

Thank you so much for your prompt reply. I managed to get the code to work.
I took out the Do While Statement within the If statement.

However, my understanding of VB is extremely basic and I have a question
about the code. I figure since I am going to use it, I need to understand
what it is doing. I looked in my handbook, but can't figure this part out:

If strInput <> "" Then

Forgive my ignorance, but doesn't that statement say: If strInput (the
Input Box) is less than or greater than "" Then . . . Oh boy, in my
ignorance, I can't even figure out how to ask what I want to know. Huh, ..
. does it mean that if the user doesn't put anything in the Input box (less
than) or the user "does" put something in the Input box (greater than),
CSA=strInput? To me that means the user is allowed to exit the Input box
(less than) without making an entry.

I did tested the code out and it does work - the user cannot exit the input
box unless they put in a date. But, I just don't understand how based on my
understanding of the above statement. What part of the code forces the user
to make an entry into the Input box? The Input box itself?

Save me from my ignorance.
S. Jackson


Rick B said:
Oops - I copied that code from my database. You can remove or to change the
line...
Do While strInput < 0 Or strInput > 1
...to fit your edits.

Rick B



Try something like the following in the form's after update...


Private Sub Form_AfterUpdate()
a:
On Error GoTo Err_Form_Afterupdate
Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Dim strInput As String
strInput = InputBox("CSA Date is required")
If strInput <> "" Then
Do While strInput < 0 Or strInput > 1
strInput = InputBox(strMsg)
Loop
SCADate = strInput
End If
Loop

Exit_Form_Afterupdate:
Exit Sub
Err_Form_Afterupdate:
GoTo a
End Sub




Rick B




I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the controls is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
Click to expand...
Click to expand...
 
Why do I need an "If statement" at all if its the first statement that
forces the user to make the entry? Why can't I simply put:

CSADate = strInput

S. Jackson

Rick B said:
That is simply saying that if the user makes an entry, then check to see
that it is between 0 and 1 (in my case). You could probably do the same
thing by saying something like..

If IsNotNull(strInput) Then

What requires it to be enterd are the...

Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Loop




Rick B





to leave the message box blank (in other words, enters a value equal to "",
then...) In this case, it keeps asking. You could do something like pop up
another message that says "entry is required" and the loop back to asking
for an entry.

All your questions are about the same thing. You asked what the <>"" and
Hope that helps,

Rick B


Rick:

Thank you so much for your prompt reply. I managed to get the code to work.
I took out the Do While Statement within the If statement.

However, my understanding of VB is extremely basic and I have a question
about the code. I figure since I am going to use it, I need to understand
what it is doing. I looked in my handbook, but can't figure this part out:

If strInput <> "" Then

Forgive my ignorance, but doesn't that statement say: If strInput (the
Input Box) is less than or greater than "" Then . . . Oh boy, in my
ignorance, I can't even figure out how to ask what I want to know. Huh, ..
. does it mean that if the user doesn't put anything in the Input box (less
than) or the user "does" put something in the Input box (greater than),
CSA=strInput? To me that means the user is allowed to exit the Input box
(less than) without making an entry.

I did tested the code out and it does work - the user cannot exit the input
box unless they put in a date. But, I just don't understand how based on my
understanding of the above statement. What part of the code forces the user
to make an entry into the Input box? The Input box itself?

Save me from my ignorance.
S. Jackson


Rick B said:
Oops - I copied that code from my database. You can remove or to change the
line...
Do While strInput < 0 Or strInput > 1
...to fit your edits.

Rick B



Try something like the following in the form's after update...


Private Sub Form_AfterUpdate()
a:
On Error GoTo Err_Form_Afterupdate
Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Dim strInput As String
strInput = InputBox("CSA Date is required")
If strInput <> "" Then
Do While strInput < 0 Or strInput > 1
strInput = InputBox(strMsg)
Loop
SCADate = strInput
End If
Loop

Exit_Form_Afterupdate:
Exit Sub
Err_Form_Afterupdate:
GoTo a
End Sub




Rick B




I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the controls is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
Click to expand...
Click to expand...
 
You are checking to see if the user made an entry when asked.

The box pops up and tells the user to enter a date. What if the user
ignores it ans simply presses the enter key, without entering a date?

Rick B


Why do I need an "If statement" at all if its the first statement that
forces the user to make the entry? Why can't I simply put:

CSADate = strInput

S. Jackson

Rick B said:
That is simply saying that if the user makes an entry, then check to see
that it is between 0 and 1 (in my case). You could probably do the same
thing by saying something like..

If IsNotNull(strInput) Then

What requires it to be enterd are the...

Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Loop




Rick B





to leave the message box blank (in other words, enters a value equal to "",
then...) In this case, it keeps asking. You could do something like pop up
another message that says "entry is required" and the loop back to asking
for an entry.

All your questions are about the same thing. You asked what the <>"" and
Hope that helps,

Rick B


Rick:

Thank you so much for your prompt reply. I managed to get the code to work.
I took out the Do While Statement within the If statement.

However, my understanding of VB is extremely basic and I have a question
about the code. I figure since I am going to use it, I need to understand
what it is doing. I looked in my handbook, but can't figure this part out:

If strInput <> "" Then

Forgive my ignorance, but doesn't that statement say: If strInput (the
Input Box) is less than or greater than "" Then . . . Oh boy, in my
ignorance, I can't even figure out how to ask what I want to know. Huh, ..
. does it mean that if the user doesn't put anything in the Input box (less
than) or the user "does" put something in the Input box (greater than),
CSA=strInput? To me that means the user is allowed to exit the Input box
(less than) without making an entry.

I did tested the code out and it does work - the user cannot exit the input
box unless they put in a date. But, I just don't understand how based on my
understanding of the above statement. What part of the code forces the user
to make an entry into the Input box? The Input box itself?

Save me from my ignorance.
S. Jackson


Rick B said:
Oops - I copied that code from my database. You can remove or to change the
line...
Do While strInput < 0 Or strInput > 1
...to fit your edits.

Rick B



Try something like the following in the form's after update...


Private Sub Form_AfterUpdate()
a:
On Error GoTo Err_Form_Afterupdate
Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Dim strInput As String
strInput = InputBox("CSA Date is required")
If strInput <> "" Then
Do While strInput < 0 Or strInput > 1
strInput = InputBox(strMsg)
Loop
SCADate = strInput
End If
Loop

Exit_Form_Afterupdate:
Exit Sub
Err_Form_Afterupdate:
GoTo a
End Sub




Rick B




I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the controls is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
Click to expand...
Click to expand...
 
I took out the If statement. The input box still will not let you cancel or
click ok without making an entry. So therefore, if you can't leave the
input box without making an entry, you wouldn't need the If statement,
right?

I very much appreciate your help, but I am just trying to understand what I
am doing here.

S. Jackson

Rick B said:
You are checking to see if the user made an entry when asked.

The box pops up and tells the user to enter a date. What if the user
ignores it ans simply presses the enter key, without entering a date?

Rick B


Why do I need an "If statement" at all if its the first statement that
forces the user to make the entry? Why can't I simply put:

CSADate = strInput

S. Jackson

Rick B said:
That is simply saying that if the user makes an entry, then check to see
that it is between 0 and 1 (in my case). You could probably do the same
thing by saying something like..

If IsNotNull(strInput) Then

What requires it to be enterd are the...

Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Loop




Rick B





to leave the message box blank (in other words, enters a value equal to "",
then...) In this case, it keeps asking. You could do something like
Click to expand...
pop
up
another message that says "entry is required" and the loop back to asking
for an entry.

All your questions are about the same thing. You asked what the <>"" and
Hope that helps,

Rick B


Rick:

Thank you so much for your prompt reply. I managed to get the code to work.
I took out the Do While Statement within the If statement.

However, my understanding of VB is extremely basic and I have a question
about the code. I figure since I am going to use it, I need to understand
what it is doing. I looked in my handbook, but can't figure this part out:

If strInput <> "" Then

Forgive my ignorance, but doesn't that statement say: If strInput (the
Input Box) is less than or greater than "" Then . . . Oh boy, in my
ignorance, I can't even figure out how to ask what I want to know. Huh, .
. does it mean that if the user doesn't put anything in the Input box (less
than) or the user "does" put something in the Input box (greater than),
CSA=strInput? To me that means the user is allowed to exit the Input box
(less than) without making an entry.

I did tested the code out and it does work - the user cannot exit the input
box unless they put in a date. But, I just don't understand how based
Click to expand...
on
my
understanding of the above statement. What part of the code forces the user
to make an entry into the Input box? The Input box itself?

Save me from my ignorance.
S. Jackson


Rick B said:
Oops - I copied that code from my database. You can remove or to
Click to expand...
change
the
line...
Do While strInput < 0 Or strInput > 1
...to fit your edits.

Rick B



Try something like the following in the form's after update...


Private Sub Form_AfterUpdate()
a:
On Error GoTo Err_Form_Afterupdate
Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Dim strInput As String
strInput = InputBox("CSA Date is required")
If strInput <> "" Then
Do While strInput < 0 Or strInput > 1
strInput = InputBox(strMsg)
Loop
SCADate = strInput
End If
Loop

Exit_Form_Afterupdate:
Exit Sub
Err_Form_Afterupdate:
GoTo a
End Sub




Rick B




I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the
Click to expand...
controls
is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
Sounds like you have it. Again, in my code, I was actually checking hte
value.

Rick B


I took out the If statement. The input box still will not let you cancel or
click ok without making an entry. So therefore, if you can't leave the
input box without making an entry, you wouldn't need the If statement,
right?

I very much appreciate your help, but I am just trying to understand what I
am doing here.

S. Jackson

Rick B said:
You are checking to see if the user made an entry when asked.

The box pops up and tells the user to enter a date. What if the user
ignores it ans simply presses the enter key, without entering a date?

Rick B


Why do I need an "If statement" at all if its the first statement that
forces the user to make the entry? Why can't I simply put:

CSADate = strInput

S. Jackson

Rick B said:
That is simply saying that if the user makes an entry, then check to see
that it is between 0 and 1 (in my case). You could probably do the same
thing by saying something like..

If IsNotNull(strInput) Then

What requires it to be enterd are the...

Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Loop




Rick B





to leave the message box blank (in other words, enters a value equal to "",
then...) In this case, it keeps asking. You could do something like
Click to expand...
pop
up
another message that says "entry is required" and the loop back to asking
for an entry.

All your questions are about the same thing. You asked what the <>"" and
Hope that helps,

Rick B


Rick:

Thank you so much for your prompt reply. I managed to get the code to work.
I took out the Do While Statement within the If statement.

However, my understanding of VB is extremely basic and I have a question
about the code. I figure since I am going to use it, I need to understand
what it is doing. I looked in my handbook, but can't figure this part out:

If strInput <> "" Then

Forgive my ignorance, but doesn't that statement say: If strInput (the
Input Box) is less than or greater than "" Then . . . Oh boy, in my
ignorance, I can't even figure out how to ask what I want to know. Huh, .
. does it mean that if the user doesn't put anything in the Input box (less
than) or the user "does" put something in the Input box (greater than),
CSA=strInput? To me that means the user is allowed to exit the Input box
(less than) without making an entry.

I did tested the code out and it does work - the user cannot exit the input
box unless they put in a date. But, I just don't understand how based
Click to expand...
on
my
understanding of the above statement. What part of the code forces the user
to make an entry into the Input box? The Input box itself?

Save me from my ignorance.
S. Jackson


Rick B said:
Oops - I copied that code from my database. You can remove or to
Click to expand...
change
the
line...
Do While strInput < 0 Or strInput > 1
...to fit your edits.

Rick B



Try something like the following in the form's after update...


Private Sub Form_AfterUpdate()
a:
On Error GoTo Err_Form_Afterupdate
Do While (([cmbDisposition]="Settlement Reached") and IsNull([CSADate]))
Dim strInput As String
strInput = InputBox("CSA Date is required")
If strInput <> "" Then
Do While strInput < 0 Or strInput > 1
strInput = InputBox(strMsg)
Loop
SCADate = strInput
End If
Loop

Exit_Form_Afterupdate:
Exit Sub
Err_Form_Afterupdate:
GoTo a
End Sub




Rick B




I have a subform that loads into the main form after the user selects that
tab on the main form (tabctrl event).

I have a cmbo box called cmbDisposition. If the user selects "Settlement
Reached" a series of other controls become enabled. One of the
Click to expand...
controls
is
[CSADate]. I want the user to be required to input a date into this field
before being able to move onto another record. I tried this Validation
Rule, but it didn't work:

=IIf(([cmbDisposition]="Settlement Reached"), IsNotNull([CSADate]))

How do I fix this expression?
TIA
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
Back
Top