Data Validation

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms![batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS" Or CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms![batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window and get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.
 
Have you tried stepping through the code to see if the TextBox's
BeforeUpdate event is firing? I would suspect that it isn't and that you
would want to move your code to the BeforeUpdate event of your form.
 
You can always an iif statement in the validation field.

=iif("Condition","Truepart","Falsepart") Just remember
you can embed iif's with the true or false parts.
=iif("condition 1",iif("condition2","a","B"),"first
falsepart")
 
What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?

Thanks.
-----Original Message-----
Have you tried stepping through the code to see if the TextBox's
BeforeUpdate event is firing? I would suspect that it isn't and that you
would want to move your code to the BeforeUpdate event of your form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms![batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS" Or CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms! [batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window and get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.
Click to expand...


.
Click to expand...
 
Sorry, nix that last message. It is in the BeforeUpdate.
-----Original Message-----
What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?

Thanks.
-----Original Message-----
Have you tried stepping through the code to see if the TextBox's
BeforeUpdate event is firing? I would suspect that it isn't and that you
would want to move your code to the BeforeUpdate event of your form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms![batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS" Or CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms! [batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window and get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.
Click to expand...


.
Click to expand...
.
Click to expand...
 
Bonnie said:
What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?
Click to expand...

AfterUpdate doesn't have a Cancel argument.
 
Mainly because the BeforeUpdate event of a control doesn't fire until the
record is updated. That's why I suggested putting it on the BeforeUpdate
event of the form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Bonnie said:
What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?

Thanks.
-----Original Message-----
Have you tried stepping through the code to see if the TextBox's
BeforeUpdate event is firing? I would suspect that it isn't and that you
would want to move your code to the BeforeUpdate event of your form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms![batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS" Or CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms! [batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window and get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.
Click to expand...


.
Click to expand...
Click to expand...
 
Oh I see. Where could I put it to make it check the
field before going on? Of course we would rather not
have to key the entire record if 1 field is wrong?
Thanks.
-----Original Message-----
Mainly because the BeforeUpdate event of a control doesn't fire until the
record is updated. That's why I suggested putting it on the BeforeUpdate
event of the form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?

Thanks.
-----Original Message-----
Have you tried stepping through the code to see if the TextBox's
BeforeUpdate event is firing? I would suspect that it isn't and that you
would want to move your code to the BeforeUpdate
Click to expand...
event
of your form.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



"Bonnie" <[email protected]> wrote
Click to expand...
in
message
Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms! [batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS"
Click to expand...
Or
CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms! [batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window
Click to expand...
and
get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.



.
Click to expand...
Click to expand...


.
Click to expand...
 
By George I thing I've got it! Thanks for your help. I
made it a function and put it in the next field's
OnGotFocus - working like a dream.

Thanks again.
-----Original Message-----
Mainly because the BeforeUpdate event of a control doesn't fire until the
record is updated. That's why I suggested putting it on the BeforeUpdate
event of the form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?

Thanks.
-----Original Message-----
Have you tried stepping through the code to see if the TextBox's
BeforeUpdate event is firing? I would suspect that it isn't and that you
would want to move your code to the BeforeUpdate
Click to expand...
event
of your form.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



"Bonnie" <[email protected]> wrote
Click to expand...
in
message
Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms! [batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS"
Click to expand...
Or
CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms! [batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window
Click to expand...
and
get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Oops... let me back up and restate that, Bonnie. The before Update event
should fire when something is typed into the field and then the focus is
moved to another field. I'm not exacly sure whether yours isn't firing or,
if the conditions you expect are not being met. That's why I suggested you
step through the code. It will give you a clear idea of what's happening
and why the form is not behaving as you expect.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Bonnie said:
Oh I see. Where could I put it to make it check the
field before going on? Of course we would rather not
have to key the entire record if 1 field is wrong?
Thanks.
-----Original Message-----
Mainly because the BeforeUpdate event of a control doesn't fire until the
record is updated. That's why I suggested putting it on the BeforeUpdate
event of the form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?

Thanks.

-----Original Message-----
Have you tried stepping through the code to see if the
TextBox's
BeforeUpdate event is firing? I would suspect that it
isn't and that you
would want to move your code to the BeforeUpdate event
of your form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



message
Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens
first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms! [batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS" Or
CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms!
[batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window and
get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Bonnie,
That will work if you can guarantee that the user will always navigate to
"the next field" from the one you are trying to check.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Bonnie said:
By George I thing I've got it! Thanks for your help. I
made it a function and put it in the next field's
OnGotFocus - working like a dream.

Thanks again.
-----Original Message-----
Mainly because the BeforeUpdate event of a control doesn't fire until the
record is updated. That's why I suggested putting it on the BeforeUpdate
event of the form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?

Thanks.

-----Original Message-----
Have you tried stepping through the code to see if the
TextBox's
BeforeUpdate event is firing? I would suspect that it
isn't and that you
would want to move your code to the BeforeUpdate event
of your form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



message
Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens
first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms! [batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS" Or
CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms!
[batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window and
get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Bonnie,
I looked at the code you had behind your BeforeUpdate event again. In both
IF statements you are checking to see if [Text96] is null. However, the
only way that could occur is if nothing is entered by the user in the field.
Therefore, the BeforeUpdate event would NOT fire because nothing was
entered.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Bonnie said:
Oh I see. Where could I put it to make it check the
field before going on? Of course we would rather not
have to key the entire record if 1 field is wrong?
Thanks.
-----Original Message-----
Mainly because the BeforeUpdate event of a control doesn't fire until the
record is updated. That's why I suggested putting it on the BeforeUpdate
event of the form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



What would make it not fire? After update seemed the
best place to put it because I could use Cancel=True to
rollback before going into the next field. Will
BeforeUpdate accomplish the same thing?

Thanks.

-----Original Message-----
Have you tried stepping through the code to see if the
TextBox's
BeforeUpdate event is firing? I would suspect that it
isn't and that you
would want to move your code to the BeforeUpdate event
of your form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



message
Hi, any help appreciated. I am trying to validate data
that is keyed in a form. I have a form that opens
first
to collect batch information that then opens the data
entry form. Here's the code I have been working with:

Private Sub Text96_BeforeUpdate(Cancel As Integer)
If IsNull(Forms![Liens]![Text96]) And Forms! [batch
info]![Text7] = "JS" Then
If CStr(Forms![batch info]![Text7]) = "JS" Or
CStr
(Forms![batch info]![Text7]) = "PL" Then
DoCmd.Beep
MsgBox "Requires a release date!",
vbExclamation, "Release Date"
Cancel = True
End If
End If
If CStr(Forms![batch info]![Text7]) <> "JS" Or
Not IsNull(Forms![Liens]![Text96]) And CStr(Forms!
[batch
info]![Text7]) <> "PL" Then
DoCmd.Beep
MsgBox "NO release date required!? Hit
Escape.", vbExclamation, "Release Date"
Cancel = True
End If
End If
End Sub

I've tried the conditions in the immediate window and
get
the expected answer (True or False) but they don't work
from the form. Any ideas? The conditions are too
complicated to put into the validation property. I've
tried all kinds of syntax and constructs on the IF THEN
ELSE.

Thanks.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top