Supress Access Error in Field Validation Routine using BeforeUpdate

[David C. Holley]

I'm using the following routine to validate a value entered by a user.
Access
is throwing an error - The value violates the validation rule for the field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub

 

[Arvin Meyer [MVP]]

Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True

 

[David C. Holley]

Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left with
the problem of clearing the value and retuning the user to the field. If I
call the .SetFocus function, I get an error about having to save the record
first. If I try to explicity set the value, I get an error. Its been quite
awhile since I've had to deal with validation in Access and its driving me
nuts.

Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

I'm using the following routine to validate a value entered by a user.
Access
is throwing an error - The value violates the validation rule for the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub

 

[Jeanette Cunningham]

You are using the before update event for the control (and not the form)
To get the value from the control inputTrailerDOTNumber, you can't just use
Me.inputTrailerDOTNumber ,
instead use Me.inputTrailerDOTNumber.Text

This is necessary because the control inputTrailerDOTNumber has the focus
when that code is called.

I don't know much about your database, but from the little that is posted
here, I question why you would want to do this check anyway.

Is it because you are trying to catch an incorrect entry for that control?
If you already know the inputTrailerDOTNumber, why not just pre-populate the
control with that value?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left with
the problem of clearing the value and retuning the user to the field. If I
call the .SetFocus function, I get an error about having to save the
record first. If I try to explicity set the value, I get an error. Its
been quite awhile since I've had to deal with validation in Access and its
driving me nuts.

Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

I'm using the following routine to validate a value entered by a user.
Access
is throwing an error - The value violates the validation rule for the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub

 

[David H]

The value is a foreign key. I was using a comboBox until I realized that we
were already up to 700 records. The nature of the records are such that
there's no way to predict an upper limit to them as each record represents a
rental vehicle. We may only use it once or any number of times, hence over
the life of the database the number could grow very large and very quickly.
Given that the input is being used on a continuous form, my concern was that
there'd be a noticable performance hit when the form loads.

The paperwork used to enter the information is guaranteed to have the value
of the foreign key on it.

Of course I might be confusing Access development with ASP.NET development
where I encountered a similar issue that was nothing short of horrific.

David

You are using the before update event for the control (and not the form)
To get the value from the control inputTrailerDOTNumber, you can't just use
Me.inputTrailerDOTNumber ,
instead use Me.inputTrailerDOTNumber.Text

This is necessary because the control inputTrailerDOTNumber has the focus
when that code is called.

I don't know much about your database, but from the little that is posted
here, I question why you would want to do this check anyway.

Is it because you are trying to catch an incorrect entry for that control?
If you already know the inputTrailerDOTNumber, why not just pre-populate the
control with that value?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left with
the problem of clearing the value and retuning the user to the field. If I
call the .SetFocus function, I get an error about having to save the
record first. If I try to explicity set the value, I get an error. Its
been quite awhile since I've had to deal with validation in Access and its
driving me nuts.

Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"David C. Holley" <David.C.Holley> wrote in message
I'm using the following routine to validate a value entered by a user.
Access
is throwing an error - The value violates the validation rule for the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub

.

 

[David H]

I went with the .Undo method of the Form which has eliminated the Access
error that was being displayed. However, its not quite what I'm looking for.
The code below basically does what I'm looking except for the Access error
that's presented on the first time the user attempts to enter an invalid
value.

1) User goes to the new record place holder on the form.
2) User enters a value
3) The value is validated as invalid
4) My msgbox is displayed
5) Access displays its own error - "The value violates the validation rule
for the field or record" yadda, yadda, yadda, press OK or HELP.
6) User re-enters an invalid value, only my msgbox is displayed. (The Access
error is never presented again.)
6)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.txtTrailerDOTNumber.Text & "'") = 0 Then
MsgBox "Trailer #" & Me.txtTrailerDOTNumber.Text & " does not exist
in the database.", vbInformation
Cancel = True
End If

You are using the before update event for the control (and not the form)
To get the value from the control inputTrailerDOTNumber, you can't just use
Me.inputTrailerDOTNumber ,
instead use Me.inputTrailerDOTNumber.Text

This is necessary because the control inputTrailerDOTNumber has the focus
when that code is called.

I don't know much about your database, but from the little that is posted
here, I question why you would want to do this check anyway.

Is it because you are trying to catch an incorrect entry for that control?
If you already know the inputTrailerDOTNumber, why not just pre-populate the
control with that value?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left with
the problem of clearing the value and retuning the user to the field. If I
call the .SetFocus function, I get an error about having to save the
record first. If I try to explicity set the value, I get an error. Its
been quite awhile since I've had to deal with validation in Access and its
driving me nuts.

Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"David C. Holley" <David.C.Holley> wrote in message
I'm using the following routine to validate a value entered by a user.
Access
is throwing an error - The value violates the validation rule for the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub

.

 

[Jeanette Cunningham]

Once you have several thousand records in your combo, there is a workaround
to avoid any hit when the form loads.
Have a look at this example for combos with tens of thousands of records:

http://allenbrowne.com/ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

The value is a foreign key. I was using a comboBox until I realized that
we
were already up to 700 records. The nature of the records are such that
there's no way to predict an upper limit to them as each record represents
a
rental vehicle. We may only use it once or any number of times, hence over
the life of the database the number could grow very large and very
quickly.
Given that the input is being used on a continuous form, my concern was
that
there'd be a noticable performance hit when the form loads.

The paperwork used to enter the information is guaranteed to have the
value
of the foreign key on it.

Of course I might be confusing Access development with ASP.NET development
where I encountered a similar issue that was nothing short of horrific.

David

You are using the before update event for the control (and not the form)
To get the value from the control inputTrailerDOTNumber, you can't just
use
Me.inputTrailerDOTNumber ,
instead use Me.inputTrailerDOTNumber.Text

This is necessary because the control inputTrailerDOTNumber has the
focus
when that code is called.

I don't know much about your database, but from the little that is posted
here, I question why you would want to do this check anyway.

Is it because you are trying to catch an incorrect entry for that
control?
If you already know the inputTrailerDOTNumber, why not just pre-populate
the
control with that value?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist
in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left
with
the problem of clearing the value and retuning the user to the field.
If I
call the .SetFocus function, I get an error about having to save the
record first. If I try to explicity set the value, I get an error. Its
been quite awhile since I've had to deal with validation in Access and
its
driving me nuts.



Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"David C. Holley" <David.C.Holley> wrote in message
I'm using the following routine to validate a value entered by a
user.
Access
is throwing an error - The value violates the validation rule for the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0
Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub

.

 

[David C. Holley]

Not an option. If the comboBox isn't populated, the value in the underlying
table won't be displayed thus making the field appear blank.

Once you have several thousand records in your combo, there is a
workaround to avoid any hit when the form loads.
Have a look at this example for combos with tens of thousands of records:

http://allenbrowne.com/ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

The value is a foreign key. I was using a comboBox until I realized that
we
were already up to 700 records. The nature of the records are such that
there's no way to predict an upper limit to them as each record
represents a
rental vehicle. We may only use it once or any number of times, hence
over
the life of the database the number could grow very large and very
quickly.
Given that the input is being used on a continuous form, my concern was
that
there'd be a noticable performance hit when the form loads.

The paperwork used to enter the information is guaranteed to have the
value
of the foreign key on it.

Of course I might be confusing Access development with ASP.NET
development
where I encountered a similar issue that was nothing short of horrific.

David

You are using the before update event for the control (and not the form)
To get the value from the control inputTrailerDOTNumber, you can't just
use
Me.inputTrailerDOTNumber ,
instead use Me.inputTrailerDOTNumber.Text

This is necessary because the control inputTrailerDOTNumber has the
focus
when that code is called.

I don't know much about your database, but from the little that is
posted
here, I question why you would want to do this check anyway.

Is it because you are trying to catch an incorrect entry for that
control?
If you already know the inputTrailerDOTNumber, why not just pre-populate
the
control with that value?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"David C. Holley" <David.C.Holley> wrote in message
Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not
exist in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left
with
the problem of clearing the value and retuning the user to the field.
If I
call the .SetFocus function, I get an error about having to save the
record first. If I try to explicity set the value, I get an error. Its
been quite awhile since I've had to deal with validation in Access and
its
driving me nuts.



Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"David C. Holley" <David.C.Holley> wrote in message
I'm using the following routine to validate a value entered by a
user.
Access
is throwing an error - The value violates the validation rule for
the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0
Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub







.

 

[Jeanette Cunningham]

The reason you are having such a struggle is the way the form is set up and
used.

1) User goes to the new record place holder on the form.
2) User enters a value
3) The value is validated as invalid


I don't know how much work is involved in creating this form, but let me
explain how we usually set up this type of thing.

The form is based on a query using a table of licence plate numbers.
In the header of the form is a combo with a list of all licence plate
numbers.
User selects a licence number and the form populates with the vehicle
details.
Use the FindFirst method to do this.

If the user can't find the licence number in the list, then user clicks the
button next to the combo.
The button opens a form to allow addition of a new licence plate number.

The above method would eliminate the pain around trying to validate the new
record place holder licence plate number.
As mentioned, you will have to decide if the time to create a new form is
worth the pain you are getting right now.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I went with the .Undo method of the Form which has eliminated the Access
error that was being displayed. However, its not quite what I'm looking
for.
The code below basically does what I'm looking except for the Access error
that's presented on the first time the user attempts to enter an invalid
value.

1) User goes to the new record place holder on the form.
2) User enters a value
3) The value is validated as invalid
4) My msgbox is displayed
5) Access displays its own error - "The value violates the validation rule
for the field or record" yadda, yadda, yadda, press OK or HELP.
6) User re-enters an invalid value, only my msgbox is displayed. (The
Access
error is never presented again.)
6)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.txtTrailerDOTNumber.Text & "'") = 0 Then
MsgBox "Trailer #" & Me.txtTrailerDOTNumber.Text & " does not exist
in the database.", vbInformation
Cancel = True
End If

You are using the before update event for the control (and not the form)
To get the value from the control inputTrailerDOTNumber, you can't just
use
Me.inputTrailerDOTNumber ,
instead use Me.inputTrailerDOTNumber.Text

This is necessary because the control inputTrailerDOTNumber has the
focus
when that code is called.

I don't know much about your database, but from the little that is posted
here, I question why you would want to do this check anyway.

Is it because you are trying to catch an incorrect entry for that
control?
If you already know the inputTrailerDOTNumber, why not just pre-populate
the
control with that value?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist
in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left
with
the problem of clearing the value and retuning the user to the field.
If I
call the .SetFocus function, I get an error about having to save the
record first. If I try to explicity set the value, I get an error. Its
been quite awhile since I've had to deal with validation in Access and
its
driving me nuts.



Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"David C. Holley" <David.C.Holley> wrote in message
I'm using the following routine to validate a value entered by a
user.
Access
is throwing an error - The value violates the validation rule for the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0
Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub

.

 

[David C. Holley]

The form HAS to be a continuous form. Not to mention that its related to
major cost-savings initiative so there's not much time for fineesse.
Fortunately, I'm adapting a nealry complete project that was scrapped in the
11th-hour.

The reason you are having such a struggle is the way the form is set up
and used.

1) User goes to the new record place holder on the form.
2) User enters a value
3) The value is validated as invalid


I don't know how much work is involved in creating this form, but let me
explain how we usually set up this type of thing.

The form is based on a query using a table of licence plate numbers.
In the header of the form is a combo with a list of all licence plate
numbers.
User selects a licence number and the form populates with the vehicle
details.
Use the FindFirst method to do this.

If the user can't find the licence number in the list, then user clicks
the button next to the combo.
The button opens a form to allow addition of a new licence plate number.

The above method would eliminate the pain around trying to validate the
new record place holder licence plate number.
As mentioned, you will have to decide if the time to create a new form is
worth the pain you are getting right now.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I went with the .Undo method of the Form which has eliminated the Access
error that was being displayed. However, its not quite what I'm looking
for.
The code below basically does what I'm looking except for the Access
error
that's presented on the first time the user attempts to enter an invalid
value.

1) User goes to the new record place holder on the form.
2) User enters a value
3) The value is validated as invalid
4) My msgbox is displayed
5) Access displays its own error - "The value violates the validation
rule
for the field or record" yadda, yadda, yadda, press OK or HELP.
6) User re-enters an invalid value, only my msgbox is displayed. (The
Access
error is never presented again.)
6)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.txtTrailerDOTNumber.Text & "'") = 0 Then
MsgBox "Trailer #" & Me.txtTrailerDOTNumber.Text & " does not
exist
in the database.", vbInformation
Cancel = True
End If

You are using the before update event for the control (and not the form)
To get the value from the control inputTrailerDOTNumber, you can't just
use
Me.inputTrailerDOTNumber ,
instead use Me.inputTrailerDOTNumber.Text

This is necessary because the control inputTrailerDOTNumber has the
focus
when that code is called.

I don't know much about your database, but from the little that is
posted
here, I question why you would want to do this check anyway.

Is it because you are trying to catch an incorrect entry for that
control?
If you already know the inputTrailerDOTNumber, why not just pre-populate
the
control with that value?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"David C. Holley" <David.C.Holley> wrote in message
Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0 Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not
exist in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left
with
the problem of clearing the value and retuning the user to the field.
If I
call the .SetFocus function, I get an error about having to save the
record first. If I try to explicity set the value, I get an error. Its
been quite awhile since I've had to deal with validation in Access and
its
driving me nuts.



Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"David C. Holley" <David.C.Holley> wrote in message
I'm using the following routine to validate a value entered by a
user.
Access
is throwing an error - The value violates the validation rule for
the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0
Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub







.

 

[Jeanette Cunningham]

Good luck - it's not much fun trying to do it on such a tight budget.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

The form HAS to be a continuous form. Not to mention that its related to
major cost-savings initiative so there's not much time for fineesse.
Fortunately, I'm adapting a nealry complete project that was scrapped in
the 11th-hour.

The reason you are having such a struggle is the way the form is set up
and used.

1) User goes to the new record place holder on the form.
2) User enters a value
3) The value is validated as invalid


I don't know how much work is involved in creating this form, but let me
explain how we usually set up this type of thing.

The form is based on a query using a table of licence plate numbers.
In the header of the form is a combo with a list of all licence plate
numbers.
User selects a licence number and the form populates with the vehicle
details.
Use the FindFirst method to do this.

If the user can't find the licence number in the list, then user clicks
the button next to the combo.
The button opens a form to allow addition of a new licence plate number.

The above method would eliminate the pain around trying to validate the
new record place holder licence plate number.
As mentioned, you will have to decide if the time to create a new form is
worth the pain you are getting right now.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I went with the .Undo method of the Form which has eliminated the Access
error that was being displayed. However, its not quite what I'm looking
for.
The code below basically does what I'm looking except for the Access
error
that's presented on the first time the user attempts to enter an invalid
value.

1) User goes to the new record place holder on the form.
2) User enters a value
3) The value is validated as invalid
4) My msgbox is displayed
5) Access displays its own error - "The value violates the validation
rule
for the field or record" yadda, yadda, yadda, press OK or HELP.
6) User re-enters an invalid value, only my msgbox is displayed. (The
Access
error is never presented again.)
6)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.txtTrailerDOTNumber.Text & "'") = 0
Then
MsgBox "Trailer #" & Me.txtTrailerDOTNumber.Text & " does not
exist
in the database.", vbInformation
Cancel = True
End If

:

You are using the before update event for the control (and not the
form)
To get the value from the control inputTrailerDOTNumber, you can't just
use
Me.inputTrailerDOTNumber ,
instead use Me.inputTrailerDOTNumber.Text

This is necessary because the control inputTrailerDOTNumber has the
focus
when that code is called.

I don't know much about your database, but from the little that is
posted
here, I question why you would want to do this check anyway.

Is it because you are trying to catch an incorrect entry for that
control?
If you already know the inputTrailerDOTNumber, why not just
pre-populate the
control with that value?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"David C. Holley" <David.C.Holley> wrote in message
Nope. Still getting it after MsgBox.

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0
Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not
exist in
the database.", vbInformation
DoCmd.SetWarnings False
Me!inputTrailerDOTNumber.Undo
Cancel = True
DoCmd.SetWarnings True
End If

End Sub

If I nix the Cancel = True, the error is supressed, however I'm left
with
the problem of clearing the value and retuning the user to the field.
If I
call the .SetFocus function, I get an error about having to save the
record first. If I try to explicity set the value, I get an error.
Its
been quite awhile since I've had to deal with validation in Access
and its
driving me nuts.



Try using:

DoCmd.SetWarnings False

then in your exit routine:

SoCmd.SetWarnings True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"David C. Holley" <David.C.Holley> wrote in message
I'm using the following routine to validate a value entered by a
user.
Access
is throwing an error - The value violates the validation rule for
the
field
or record.

How can I supress the error?

Private Sub inputTrailerDOTNumber_BeforeUpdate(Cancel As Integer)

If ECount("txtTrailerDOTNumber", "tblTrailerInventory",
"[txtTrailerDOTNumber] = '" & Me.inputTrailerDOTNumber & "'") = 0
Then
MsgBox "Trailer #" & Me.inputTrailerDOTNumber & " does not exist in
the database.", vbInformation
Cancel = True
Me!inputTrailerDOTNumber.Undo
End If

End Sub







.