On cancel=true, extra Access-warning?

[Arno R]

Hi all

I come across a 'feature' of Access (2000) that I don't like and would like to disable.
I am checking if a value already exists in a table.

I a subform I use code like :
Private Sub Choice_BeforeUp date(Cancel As Integer)
If <validation on inputted value already in table is true> Then
MsgBox "This choice already exists...", vbCritical, "Validation "
Cancel = True
End If
End Sub

My msgbox appears and the update is cancelled when the input is trapped by my validation,
but after I click OK Access also comes with a info-message:
"The value in the field or record is violating ...."
I would like to disable this second warning.

If I don't use cancel=true then the second message doesn't come up !
Any idea's?

Trap unsolved errors is 'on' in the vb-editor.

Thanks
Arno R

[Tim Marshall]

Arno R wrote:

Hi all

I come across a 'feature' of Access (2000) that I don't like and would like to disable.
I am checking if a value already exists in a table.

I a subform I use code like :
Private Sub Choice_BeforeUp date(Cancel As Integer)
If <validation on inputted value already in table is true> Then
MsgBox "This choice already exists...", vbCritical, "Validation "
Cancel = True
End If
End Sub

My msgbox appears and the update is cancelled when the input is trapped by my validation,
but after I click OK Access also comes with a info-message:
"The value in the field or record is violating ...."
I would like to disable this second warning.

If I don't use cancel=true then the second message doesn't come up !
Any idea's?

I'm not familiar with the error format you're using, ie " If
<validation on inputted value already in table is true> " (I always use
GoTo Err_Proc at the beginning of any sub and have a select case
err.number after Err_Proc: at the end of the procedure.

IN anyevent, what you want to do is trap this error at the form level,
in the form On Error event.

The first thing to do is find out what error number this is. It should
be displayed on the error message you're getting that you don't like.
Or you can press Help to find the err.number. I'll use x in the
procedure below.

Next put this into the on error event of your form. The response =
acdataerrcontin ue is what bypasses the Access error message:

Private Sub Form_Error(Data Err As Integer, Response As Integer)

Select Case DataErr

Case <your error number>

Msgbox <your message>

<your error handling, if any>

Response = acDataErrContin ue

Case Else

Response = acDataErrDispla y

End Select

End Sub

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[MGFoster]

Tim Marshall wrote:

Arno R wrote:

Hi all

I come across a 'feature' of Access (2000) that I don't like and would
like to disable.
I am checking if a value already exists in a table.

I a subform I use code like :
Private Sub Choice_BeforeUp date(Cancel As Integer)
If <validation on inputted value already in table is true> Then
MsgBox "This choice already exists...", vbCritical, "Validation "
Cancel = True
End If
End Sub

My msgbox appears and the update is cancelled when the input is
trapped by my validation,
but after I click OK Access also comes with a info-message:
"The value in the field or record is violating ...."
I would like to disable this second warning.

If I don't use cancel=true then the second message doesn't come up !
Any idea's?

I'm not familiar with the error format you're using, ie " If <validation
on inputted value already in table is true> " (I always use GoTo
Err_Proc at the beginning of any sub and have a select case err.number
after Err_Proc: at the end of the procedure.

IN anyevent, what you want to do is trap this error at the form level,
in the form On Error event.

The first thing to do is find out what error number this is. It should
be displayed on the error message you're getting that you don't like. Or
you can press Help to find the err.number. I'll use x in the procedure
below.

Next put this into the on error event of your form. The response =
acdataerrcontin ue is what bypasses the Access error message:

Private Sub Form_Error(Data Err As Integer, Response As Integer)

Select Case DataErr

Case <your error number>

Msgbox <your message>

<your error handling, if any>

Response = acDataErrContin ue

Case Else

Response = acDataErrDispla y

End Select

End Sub

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Beside what Tim says - your table definition, or control "Choice," may
have a Validation Rule that is being evaluated after the BeforeUpdate
event. Either let that Validation Rule/Validation Text handle the
validation, or set those properties to empty strings.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgQTAIechKq OuFEgEQL1bQCdFD aHeqkbwfGkle7S3 6Wvg4hO65sAoI9+
kH+gK/qf3lKPH473Oo68g PDV
=VkwY
-----END PGP SIGNATURE-----

[Arno R]

Hi Tim and MGFoster,

The first thing to do is find out what error number this is. It should be displayed on the error
message you're getting that you don't like. Or you can press Help to find the err.number. I'll
use x in the procedure below.

Strange case is that there was no err.number displayed.
I was able to find the Err number. It was 2116. With code like:
Private Sub Form_Error(Data Err As Integer, Response As Integer)
Select Case DataErr
Case 0
Case Else
MsgBox DataErr
End Select
End sub

Now the code is like:
Private Sub Form_Error(Data Err As Integer, Response As Integer)
Select Case DataErr
Case 2116 'Unneeded Access-warning
Response = acDataErrContin ue
Case Else
Response = acDataErrDispla y
End Select
End Sub

Problem is 'solved' now but ...
I still don't understand why Access comes up with this error in the first place ...
There is NO validation set in the table, no validation in the fields, no validation in the form.
I was thinking about corruption ? (already tried /decompile)

Thanks
Arno R

[MGFoster]

Arno R wrote:

Hi Tim and MGFoster,

The first thing to do is find out what error number this is. It should be displayed on the error
message you're getting that you don't like. Or you can press Help to find the err.number. I'll
use x in the procedure below.

Strange case is that there was no err.number displayed.
I was able to find the Err number. It was 2116. With code like:
Private Sub Form_Error(Data Err As Integer, Response As Integer)
Select Case DataErr
Case 0
Case Else
MsgBox DataErr
End Select
End sub

Now the code is like:
Private Sub Form_Error(Data Err As Integer, Response As Integer)
Select Case DataErr
Case 2116 'Unneeded Access-warning
Response = acDataErrContin ue
Case Else
Response = acDataErrDispla y
End Select
End Sub

Problem is 'solved' now but ...
I still don't understand why Access comes up with this error in the first place ...
There is NO validation set in the table, no validation in the fields, no validation in the form.
I was thinking about corruption ? (already tried /decompile)

Try compact & repair.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Tim Marshall]

Arno R wrote:

Strange case is that there was no err.number displayed.
I was able to find the Err number. It was 2116. With code like:
Private Sub Form_Error(Data Err As Integer, Response As Integer)
Select Case DataErr
Case 0
Case Else
MsgBox DataErr
End Select
End sub
Yes, I do this myself sometimes and should have suggested it, but you
were able to figure it out! 8)
I still don't understand why Access comes up with this error in the first place ...
There is NO validation set in the table, no validation in the fields, no validation in the form.
I was thinking about corruption ? (already tried /decompile)

Are there any relationships anywhere that could be coming into play?

What about the data type that ends up going into the field? I've
sometimes had weird things happen because I'd forgotten exactly what the
data type of the field in the table was! Acutally, this happens to me
more times than I'm comfortable admitting, especially wiuth projects
that take a long time... 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Arno R]

Thanks again both Tim and MGFoster

Of course I tried compact & repair first, but to no avail.

I am using Access 2000 with SP3
I will check if the same issue is the case at the clients-side (Access XP).

Arno R