Problem with Validation Error

[Rob Parker]

I have a form with a textbox control bound to a table field. The
table has no validation properties set for this field; the field is
indexed with 'No Duplicates'. The textbox itself has no validation
properties set for the field. I have the following code in the
textbox's BeforeUpdate event:

If Not (txtCARDocNumber Like "C####") Then
MsgBox "The CAR Number must be in the format 'Cnnnn'," & vbNewLine
_
& "where n is any numeric character." _
, vbExclamation + vbOKOnly, "CAR Number Format
Incorrect ..."
Cancel = True
txtCARDocNumber.Undo
End If

If I enter an invalid entry into the textbox, my message box appears.
But I then get a message from Access "The value in the field or record
violates the validation rule for the record or field. ..." Why is
this happening, and how can I prevent it. I've tried adding
DoCmd.SetWarnings False
before the If statement, but that has no effect.

TIA,

Rob

 

[Daryl S]

Rob -

One thought - is your txtCARDocNumber.Undo statement making this field null?
If so, you would be violating the unique index. Do you need to undo this,
or can you let the user re-type it? If you want the nulls allowed as
values, then you can set the 'ignore nulls' property of the index.

 

[Jeff Boyce]

Rob

I assume you're having the user enter the CAR number for a reason, probably
to tell Access which record to display.

If this is the purpose, instead of forcing the user to remember and
(properly) enter a number, why not use a combobox and have the user select
the correct one? If you set the LimitToList property to Yes, Access won't
accept a non-existant number...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Rob Parker]

Hi Jeff,

Thanks for the response. No, I'm not having the user enter a number to
show which record to display. If so, I would have done what you suggest.
I'm setting up a form to allow entry of new CAR records, and I need to
ensure that the format is correct. [Don't ask, it's a crazy situation:
usually CAR records will be generated within the database, but they may
occasionally be generated outside the database, which checks to see if
applicable Word .docs exist within the CARs folder before creating a new
record. I'm trying to set up a form to allow users to enter details of such
externally-generated CARs.]

Rob

 

[Rob Parker]

Hii Daryl,

Thanks for the response. That's maybe on the right track, but I'm still not
sure exactly what's happening, or how to fix it. If I comment out the .Undo
line I still have the same error. [FYI: running Access 2003, SP3]. The
form is opened for a new entry record (for details, see my response to Jeff
Boyce in another branch of this thread), via:
DoCmd.GoToRecord , , acNewRec
in the form's Open event.

I can let the user retype the entry, but I'm trying to clear the textbox
control before that. And, as I say, removing the
txtCARDocNumber.Undo
statement doesn't prevent the error.

I don't understand why this code, and the error message, would be generated
from the BeforeUpdate event of a form control - not the BeforeUpdate of the
form itself. That event should not to be trying to save the record - which
is when I would expect an error to arise from a table property (Required
field, duplicates not allowed). I would expect that to occur (if the texbox
control, bound to the field, contains erroneous data) when an event which
causes the record to be saved occurs - such as closing the form.

I'm also puzzled by your statement "... set the 'ignore nulls' property of
the index." I can't find this property anywhere in the table's property
dialog; how can I set this (I'm using an Access .mdb back-end, not a
SQLServer file).

Further comments/explanation welcomed,

Rob

 

[Daryl S]

Rob -

As for the ignore nulls, bring up the table design and look at the indexes.
That is where you can set them to be unique (though the key field is
automatically set this way). This is also where the 'ignore nulls' is. You
may want to validate the other indexes while you are there, as Access will
build indexes automatically if you copy/paste some types of fields from one
table to another.

I would also add a breakpoint on your BeforeUpdate event and step through
the code to find out where the error message comes from. There may be
something else happening that we aren't thinking of.

--
Daryl S

Hii Daryl,

Thanks for the response. That's maybe on the right track, but I'm still not
sure exactly what's happening, or how to fix it. If I comment out the .Undo
line I still have the same error. [FYI: running Access 2003, SP3]. The
form is opened for a new entry record (for details, see my response to Jeff
Boyce in another branch of this thread), via:
DoCmd.GoToRecord , , acNewRec
in the form's Open event.

I can let the user retype the entry, but I'm trying to clear the textbox
control before that. And, as I say, removing the
txtCARDocNumber.Undo
statement doesn't prevent the error.

I don't understand why this code, and the error message, would be generated
from the BeforeUpdate event of a form control - not the BeforeUpdate of the
form itself. That event should not to be trying to save the record - which
is when I would expect an error to arise from a table property (Required
field, duplicates not allowed). I would expect that to occur (if the texbox
control, bound to the field, contains erroneous data) when an event which
causes the record to be saved occurs - such as closing the form.

I'm also puzzled by your statement "... set the 'ignore nulls' property of
the index." I can't find this property anywhere in the table's property
dialog; how can I set this (I'm using an Access .mdb back-end, not a
SQLServer file).

Further comments/explanation welcomed,

Rob

Rob -

One thought - is your txtCARDocNumber.Undo statement making this
field null? If so, you would be violating the unique index. Do you
need to undo this, or can you let the user re-type it? If you want
the nulls allowed as values, then you can set the 'ignore nulls'
property of the index.

.

 

[Rob Parker]

Daryl,

Thanks for the advice on setting index properties - it's not something that
I've ever done; I normally simply set the Indexed property to one of the
options available on the General tab of the table's property sheet.
However, changing this to Ignore Nulls has no effect.

Setting a breakpoint shows that the message is triggered when the Sub ends
(ie. after the End Sub statement, which is immediately after the End If
statement).

Checking the table after entering a valid CAR number in the textbox confirms
that the record is not added at this time. I'm completely puzzled as to why
the validation message is occurring when the record should not be being
saved.

In desperation, thinking that maybe the textbox had previously had a
validation rule applied to it in a previous life (it's likely to be a copy
of a copy of a copy ..., since I often generate new forms from something
similar (perhaps even in a different database) and simply change names and
recordsources), I deleted the textbox and added a new one (directly form the
toolbox, giving it a different name) which I then bound to the CARNumber
field. I entered the same code for this new control - which certainly has
never had any validation associated with it - and found exactly the same
behaviour.

I've now implemented a work-around, as follows:

Dim ValidData as Boolean 'Set module-level variable

Private Function ValidData() As Boolean
ValidData = True
If Not (Text27 Like "C####") Then
ValidData = False
MsgBox "Error"
Text27.SetFocus
End If
End Function

And finally, in the form's cmdAddCAR button (the only way out which saves
the record - there's also a Cancel button which has Me.Undo and then closes
the form):

Private Sub cmdAddCAR_Click()
If ValidData Then DoCmd.Close acForm, "frmAddCAR"
End Sub

This code works as I expect - no validation message occurs. But I'm still
deeply puzzled.

Rob

Rob -

As for the ignore nulls, bring up the table design and look at the
indexes. That is where you can set them to be unique (though the key
field is automatically set this way). This is also where the 'ignore
nulls' is. You may want to validate the other indexes while you are
there, as Access will build indexes automatically if you copy/paste
some types of fields from one table to another.

I would also add a breakpoint on your BeforeUpdate event and step
through the code to find out where the error message comes from.
There may be something else happening that we aren't thinking of.

Hii Daryl,

Thanks for the response. That's maybe on the right track, but I'm
still not sure exactly what's happening, or how to fix it. If I
comment out the .Undo line I still have the same error. [FYI:
running Access 2003, SP3]. The form is opened for a new entry
record (for details, see my response to Jeff Boyce in another branch
of this thread), via: DoCmd.GoToRecord , , acNewRec
in the form's Open event.

I can let the user retype the entry, but I'm trying to clear the
textbox control before that. And, as I say, removing the
txtCARDocNumber.Undo
statement doesn't prevent the error.

I don't understand why this code, and the error message, would be
generated from the BeforeUpdate event of a form control - not the
BeforeUpdate of the form itself. That event should not to be trying
to save the record - which is when I would expect an error to arise
from a table property (Required field, duplicates not allowed). I
would expect that to occur (if the texbox control, bound to the
field, contains erroneous data) when an event which causes the
record to be saved occurs - such as closing the form.

I'm also puzzled by your statement "... set the 'ignore nulls'
property of the index." I can't find this property anywhere in the
table's property dialog; how can I set this (I'm using an Access
.mdb back-end, not a SQLServer file).

Further comments/explanation welcomed,

Rob

Rob -

One thought - is your txtCARDocNumber.Undo statement making this
field null? If so, you would be violating the unique index. Do you
need to undo this, or can you let the user re-type it? If you want
the nulls allowed as values, then you can set the 'ignore nulls'
property of the index.


I have a form with a textbox control bound to a table field. The
table has no validation properties set for this field; the field is
indexed with 'No Duplicates'. The textbox itself has no validation
properties set for the field. I have the following code in the
textbox's BeforeUpdate event:

If Not (txtCARDocNumber Like "C####") Then
MsgBox "The CAR Number must be in the format 'Cnnnn'," &
vbNewLine _
& "where n is any numeric character." _
, vbExclamation + vbOKOnly, "CAR Number Format
Incorrect ..."
Cancel = True
txtCARDocNumber.Undo
End If

If I enter an invalid entry into the textbox, my message box
appears. But I then get a message from Access "The value in the
field or record violates the validation rule for the record or
field. ..." Why is this happening, and how can I prevent it. I've
tried adding DoCmd.SetWarnings False
before the If statement, but that has no effect.

TIA,

Rob



.

.

 

[Daryl S]

Rob -

I'm glad you have a work-around. Another thought - what if you put your
Cancel = True after the .Undo? I don't know what Access thinks of the .Undo
if it is after the Cancel... Just a thought.

--
Daryl S

Daryl,

Thanks for the advice on setting index properties - it's not something that
I've ever done; I normally simply set the Indexed property to one of the
options available on the General tab of the table's property sheet.
However, changing this to Ignore Nulls has no effect.

Setting a breakpoint shows that the message is triggered when the Sub ends
(ie. after the End Sub statement, which is immediately after the End If
statement).

Checking the table after entering a valid CAR number in the textbox confirms
that the record is not added at this time. I'm completely puzzled as to why
the validation message is occurring when the record should not be being
saved.

In desperation, thinking that maybe the textbox had previously had a
validation rule applied to it in a previous life (it's likely to be a copy
of a copy of a copy ..., since I often generate new forms from something
similar (perhaps even in a different database) and simply change names and
recordsources), I deleted the textbox and added a new one (directly form the
toolbox, giving it a different name) which I then bound to the CARNumber
field. I entered the same code for this new control - which certainly has
never had any validation associated with it - and found exactly the same
behaviour.

I've now implemented a work-around, as follows:

Dim ValidData as Boolean 'Set module-level variable

Private Function ValidData() As Boolean
ValidData = True
If Not (Text27 Like "C####") Then
ValidData = False
MsgBox "Error"
Text27.SetFocus
End If
End Function

And finally, in the form's cmdAddCAR button (the only way out which saves
the record - there's also a Cancel button which has Me.Undo and then closes
the form):

Private Sub cmdAddCAR_Click()
If ValidData Then DoCmd.Close acForm, "frmAddCAR"
End Sub

This code works as I expect - no validation message occurs. But I'm still
deeply puzzled.

Rob

Rob -

As for the ignore nulls, bring up the table design and look at the
indexes. That is where you can set them to be unique (though the key
field is automatically set this way). This is also where the 'ignore
nulls' is. You may want to validate the other indexes while you are
there, as Access will build indexes automatically if you copy/paste
some types of fields from one table to another.

I would also add a breakpoint on your BeforeUpdate event and step
through the code to find out where the error message comes from.
There may be something else happening that we aren't thinking of.

Hii Daryl,

Thanks for the response. That's maybe on the right track, but I'm
still not sure exactly what's happening, or how to fix it. If I
comment out the .Undo line I still have the same error. [FYI:
running Access 2003, SP3]. The form is opened for a new entry
record (for details, see my response to Jeff Boyce in another branch
of this thread), via: DoCmd.GoToRecord , , acNewRec
in the form's Open event.

I can let the user retype the entry, but I'm trying to clear the
textbox control before that. And, as I say, removing the
txtCARDocNumber.Undo
statement doesn't prevent the error.

I don't understand why this code, and the error message, would be
generated from the BeforeUpdate event of a form control - not the
BeforeUpdate of the form itself. That event should not to be trying
to save the record - which is when I would expect an error to arise
from a table property (Required field, duplicates not allowed). I
would expect that to occur (if the texbox control, bound to the
field, contains erroneous data) when an event which causes the
record to be saved occurs - such as closing the form.

I'm also puzzled by your statement "... set the 'ignore nulls'
property of the index." I can't find this property anywhere in the
table's property dialog; how can I set this (I'm using an Access
.mdb back-end, not a SQLServer file).

Further comments/explanation welcomed,

Rob


Daryl S wrote:
Rob -

One thought - is your txtCARDocNumber.Undo statement making this
field null? If so, you would be violating the unique index. Do you
need to undo this, or can you let the user re-type it? If you want
the nulls allowed as values, then you can set the 'ignore nulls'
property of the index.


I have a form with a textbox control bound to a table field. The
table has no validation properties set for this field; the field is
indexed with 'No Duplicates'. The textbox itself has no validation
properties set for the field. I have the following code in the
textbox's BeforeUpdate event:

If Not (txtCARDocNumber Like "C####") Then
MsgBox "The CAR Number must be in the format 'Cnnnn'," &
vbNewLine _
& "where n is any numeric character." _
, vbExclamation + vbOKOnly, "CAR Number Format
Incorrect ..."
Cancel = True
txtCARDocNumber.Undo
End If

If I enter an invalid entry into the textbox, my message box
appears. But I then get a message from Access "The value in the
field or record violates the validation rule for the record or
field. ..." Why is this happening, and how can I prevent it. I've
tried adding DoCmd.SetWarnings False
before the If statement, but that has no effect.

TIA,

Rob



.


.

.

 

[Rob Parker]

Hi Daryl,

Thanks for the suggestion. I can't try it now, since I deleted all the
previous code in the control's BeforeUpdate event when I immplemented the
work-around. But having said that, I'm almost certain that, while I was
trying to troubleshoot what was happening, I commented out the .Undo line,
with no effect. In fact, scrolling back through this thread I see that I
wrote:
I think I'll just put this one in the "Access Oddities" basket ;-)

Rob

Rob -

I'm glad you have a work-around. Another thought - what if you put
your Cancel = True after the .Undo? I don't know what Access thinks
of the .Undo if it is after the Cancel... Just a thought.

Daryl,

Thanks for the advice on setting index properties - it's not
something that I've ever done; I normally simply set the Indexed
property to one of the options available on the General tab of the
table's property sheet. However, changing this to Ignore Nulls has
no effect.

Setting a breakpoint shows that the message is triggered when the
Sub ends (ie. after the End Sub statement, which is immediately
after the End If statement).

Checking the table after entering a valid CAR number in the textbox
confirms that the record is not added at this time. I'm completely
puzzled as to why the validation message is occurring when the
record should not be being saved.

In desperation, thinking that maybe the textbox had previously had a
validation rule applied to it in a previous life (it's likely to be
a copy of a copy of a copy ..., since I often generate new forms
from something similar (perhaps even in a different database) and
simply change names and recordsources), I deleted the textbox and
added a new one (directly form the toolbox, giving it a different
name) which I then bound to the CARNumber field. I entered the same
code for this new control - which certainly has never had any
validation associated with it - and found exactly the same behaviour.

I've now implemented a work-around, as follows:

Dim ValidData as Boolean 'Set module-level variable

Private Function ValidData() As Boolean
ValidData = True
If Not (Text27 Like "C####") Then
ValidData = False
MsgBox "Error"
Text27.SetFocus
End If
End Function

And finally, in the form's cmdAddCAR button (the only way out which
saves the record - there's also a Cancel button which has Me.Undo
and then closes the form):

Private Sub cmdAddCAR_Click()
If ValidData Then DoCmd.Close acForm, "frmAddCAR"
End Sub

This code works as I expect - no validation message occurs. But I'm
still deeply puzzled.

Rob

Rob -

As for the ignore nulls, bring up the table design and look at the
indexes. That is where you can set them to be unique (though the key
field is automatically set this way). This is also where the
'ignore nulls' is. You may want to validate the other indexes
while you are there, as Access will build indexes automatically if
you copy/paste some types of fields from one table to another.

I would also add a breakpoint on your BeforeUpdate event and step
through the code to find out where the error message comes from.
There may be something else happening that we aren't thinking of.


Hii Daryl,

Thanks for the response. That's maybe on the right track, but I'm
still not sure exactly what's happening, or how to fix it. If I
comment out the .Undo line I still have the same error. [FYI:
running Access 2003, SP3]. The form is opened for a new entry
record (for details, see my response to Jeff Boyce in another
branch of this thread), via: DoCmd.GoToRecord , , acNewRec
in the form's Open event.

I can let the user retype the entry, but I'm trying to clear the
textbox control before that. And, as I say, removing the
txtCARDocNumber.Undo
statement doesn't prevent the error.

I don't understand why this code, and the error message, would be
generated from the BeforeUpdate event of a form control - not the
BeforeUpdate of the form itself. That event should not to be
trying to save the record - which is when I would expect an error
to arise from a table property (Required field, duplicates not
allowed). I would expect that to occur (if the texbox control,
bound to the field, contains erroneous data) when an event which
causes the record to be saved occurs - such as closing the form.

I'm also puzzled by your statement "... set the 'ignore nulls'
property of the index." I can't find this property anywhere in the
table's property dialog; how can I set this (I'm using an Access
.mdb back-end, not a SQLServer file).

Further comments/explanation welcomed,

Rob


Daryl S wrote:
Rob -

One thought - is your txtCARDocNumber.Undo statement making this
field null? If so, you would be violating the unique index. Do
you need to undo this, or can you let the user re-type it? If
you want the nulls allowed as values, then you can set the
'ignore nulls' property of the index.


I have a form with a textbox control bound to a table field. The
table has no validation properties set for this field; the field
is indexed with 'No Duplicates'. The textbox itself has no
validation properties set for the field. I have the following
code in the textbox's BeforeUpdate event:

If Not (txtCARDocNumber Like "C####") Then
MsgBox "The CAR Number must be in the format 'Cnnnn'," &
vbNewLine _
& "where n is any numeric character." _
, vbExclamation + vbOKOnly, "CAR Number Format
Incorrect ..."
Cancel = True
txtCARDocNumber.Undo
End If

If I enter an invalid entry into the textbox, my message box
appears. But I then get a message from Access "The value in the
field or record violates the validation rule for the record or
field. ..." Why is this happening, and how can I prevent it.
I've tried adding DoCmd.SetWarnings False
before the If statement, but that has no effect.

TIA,

Rob



.


.

.

 

[Rob Parker]

Hi Bruce,

The Me. prefix is not required in this situation (but, as you say, is
preferable - in some cases, it is required). Running the code in debug mode
and hovering the mouse cursor over variables/controlnames shows that the
code as I wrote it works as expected (except for the Validation error) - I
do not need to place the control's value in a pre-defined string variable
for the comparison; and if I enter a valid CARNumber in the control the If
statement is not executed, as expected.

Also, the MsgBox does not have incorrect syntax, and the code compiles. The
Space Ampersand before the final Space Underscore is for the continuation of
the message string in the next line; it is functionally the same as:
MsgBox "The CAR Number must be in the format 'Cnnnn'," & vbNewLine _
& "where n is any numeric character." _
...
[And, BTW, in your suggested code you've got the line continuation character
in the continued line - it would certainly spit the dummy when trying to
compile it ;-) ]

And finally, yes, I am certain that while testing this I was entering a
unique number, which did not exist in the current data.

Thanks for trying,

Rob

I would use the Me. prefix for all controls, properties, and so forth:
Me.txtCARDocNumber

Another thing to try would be declaring a string variable:

Dim strNum as String

strNum = Me.txtCARDocNumber

If Not (strNum Like "C####") Then
MsgBox "The CAR Number must be in the format 'Cnnnn'," & vbNewLine
& _ "where n is any numeric character." _
, vbExclamation + vbOKOnly, "CAR Number Format Incorrect ..."
Cancel = True
Me.txtCARDocNumber.Undo
End If

Another thing I noted is that your MsgBox statement has incorrect
syntax for the line continuation. When concatenating, the line
continuation is as shown above: space ampersand space underscore.
Did the code compile?

The problem may be that Access was not able to evaluate
txtCARDocNumber, so the comparison always failed. BTW, are you
certain you were entering a unique number, as the field requires?

Hi Daryl,

Thanks for the suggestion. I can't try it now, since I deleted all
the previous code in the control's BeforeUpdate event when I
immplemented the work-around. But having said that, I'm almost
certain that, while I was trying to troubleshoot what was happening,
I commented out the .Undo line, with no effect. In fact, scrolling

... And, as I say, removing the
txtCARDocNumber.Undo
statement doesn't prevent the error.

I think I'll just put this one in the "Access Oddities" basket ;-)

Rob

Rob -

[quoted text clipped - 141 lines]

.

 

[Rob Parker]

Thanks again for the follow-up comments Bruce. As you say, there definitely
seems to be an anomaly. For now, I'm happy with my work-around - it does
exactly what I want.

Rob

When I said the message box does not appear, I mean the Access notice
that the data is not valid. The message box in the code does appear
as needed.

I see that I was in error about concatenation. I thought I had run
into problems with that in the past, but it must have been a
different context. However, the line continuation error you thought
you observed in my suggested code seems to have been a line-wrapping
thing in the newsreader, as it works in my tests.

For the rest, I suggested you could try some things, not that those
things were necessarily the problem. I was curious, and did some
testing. The one thing I can see is that the message box seems not
to appear if Allow Zero Length is set to Yes in table properties.
When I stepped through the Before Update code I found that as long
as I was in the procedure the text box showed the incorrect
(original) value, even after Undo, yet the text box is cleared.
There definitely seems to be an anomaly going on here.

Maybe an input mask instead of the code you are using? I realize
this is a completely different approach than you are using. It is
just a thought.

One other observation is that as I understand having the index
ignore nulls just means records with null values are not included in
the index, not that null is allowed in the field. If Required is
Yes the field cannot be null.

Hi Bruce,

[quoted text clipped - 28 lines]

.