Unwanted ADO Error Messages -- Validation 
Author Message
 Unwanted ADO Error Messages -- Validation

I wrote my Form Level Validation Sub and inserted it as
indicated in the Code produced by the DFW.  My routine
check for zero-length fields and well as other checks.
If everything is valid, then the variable mbValPassed
is set to True.  Otherwise, it returns False.

It is my desire to trap and allow the user to correct
the input errors BEFORE any msg. is produced by routines
external to my code.  There are validation checks set in
the MDB Table (field can not be zero-length for one).  In
the events that occur, the Error Messages:

  Field "NameAddr.Name' can't be a zero-length string

and

  The change was cancelled during notification; no
  columns are changed.

(shouldn't that be "were changed"?) appears BEFORE my Sub
has had an opportunity to notify the user and allow the
user to correct the errors.  I can eleminate the errors
from the Database by changing the field attributes to
allow anything and everything.  Ideally, I would rather
change the order of events -- allow me a opportunity to
correct the errors and then use the Database as a final
check to ensure that the data is valid.

Is this feasible?

'--- Shows the Code Inserted -----------------------------------
Private Sub adoPrimaryRS_WillChangeRecord( _
            ByVal adReason As ADODB.EventReasonEnum, _
            ByVal cRecords As Long, _
            ByRef adStatus As ADODB.EventStatusEnum, _
            ByVal pRecordset As ADODB.Recordset)

  ' This is where you put validation code                
  ' This event gets called when the following actions occur

  Dim bCancel As Boolean

  '-- My Validation Code Added Here      <==
  ValidateForm                 ' Call My Validation Sub
  If Not mbValPassed Then
     '-- Now Do I prevent the ADO Error Message:    
     '--   "The change was cancelled during notification; no
     '--    columns are changed"
     adStatus = adStatusCancel         ' Notify ADO to Cancel
     Exit Sub
  End If

  Select Case adReason
     Case adRsnAddNew
     Case adRsnClose
     Case adRsnDelete
     Case adRsnFirstChange
     Case adRsnMove
     Case adRsnRequery
     Case adRsnResynch
     Case adRsnUndoAddNew
     Case adRsnUndoDelete
     Case adRsnUndoUpdate
     Case adRsnUpdate
  End Select
  ...



Wed, 04 Jul 2001 03:00:00 GMT  
 Unwanted ADO Error Messages -- Validation
A great number of events are called by ado when updating or cancelling a
record you must probably intercept the willChangeField (NOT WillChangeRecord
which is fired after (and DB controls are already firing errors)
first (something like : )

If mbCancelling Then Exit Sub    ' event called even during cancelupdate
processing
Select Case Fields(0).Name    ' Fields is a variant which map to Fields
collection it seems that just the first (index 0) is used
    Case "Code"
        If txtFields(0).Text = "" Then        ' unfortunately (bug?)
Fields(0).Value is not up to date
            MsgBox "Error encountered Null value is invalid"
            adStatus = adStatusCancel
            ' txtFields(0).SetFocus            ' unfortunately this one
doesn't work worse is to set a validate event on the textbox field
        End If
End Select

you must also trap the FieldChangeComplete, (such a way :)

If mbCancelling Then Exit Sub    ' see previous note
If adStatus = adStatusErrorsOccurred Then
    If Not pError Is Nothing Then    ' sometimes adstatus is 2 :
adstatusErrorsOccured but pError is not fired (??)
        MsgBox "Erreur dtecte en cours d'opration :
<FieldChangeComplete>. Code : " & pError.Number & vbCrLf & _
                pError.Description & vbCrLf & _
                "NativeError : " & pError.NativeError & " Source : " &
pError.Source & vbCrLf & _
                "SQLState : " & pError.SQLState, vbInformation,
"MoveComplete Erreur"
    Else
        MsgBox "Erreur en cours d'opration sur le champ : " &
Fields(0).Name & vbCrLf & _
                "Valeur actuelle : " & txtFields(0).Text                ' or
your proper error message
    End If
    adStatus = adStatusCancel

RecordChangeComplete must also be intercepted :

If mbCancelling Then Exit Sub
If adStatus = adStatusErrorsOccurred Then
    If Not pError Is Nothing Then
        MsgBox "Erreur dtecte en cours d'opration :
<RecordChangeComplete>. Code : " & pError.Number & vbCrLf & _
                pError.Description & vbCrLf & _
                "NativeError : " & pError.NativeError & " Source : " &
pError.Source & vbCrLf & _
                "SQLState : " & pError.SQLState, vbInformation,
"MoveComplete Erreur"
    End If
    adStatus = adStatusCancel
    End If

unfortunately it seems impossible to put the cursor back on the fields which
supports the error, not really very user-friendly

Quote:

>I wrote my Form Level Validation Sub and inserted it as
>indicated in the Code produced by the DFW.  My routine
>check for zero-length fields and well as other checks.
>If everything is valid, then the variable mbValPassed
>is set to True.  Otherwise, it returns False.

>It is my desire to trap and allow the user to correct
>the input errors BEFORE any msg. is produced by routines
>external to my code.  There are validation checks set in
>the MDB Table (field can not be zero-length for one).  In
>the events that occur, the Error Messages:

>  Field "NameAddr.Name' can't be a zero-length string

>and

>  The change was cancelled during notification; no
>  columns are changed.

>(shouldn't that be "were changed"?) appears BEFORE my Sub
>has had an opportunity to notify the user and allow the
>user to correct the errors.  I can eleminate the errors
>from the Database by changing the field attributes to
>allow anything and everything.  Ideally, I would rather
>change the order of events -- allow me a opportunity to
>correct the errors and then use the Database as a final
>check to ensure that the data is valid.

>Is this feasible?

>'--- Shows the Code Inserted -----------------------------------
>Private Sub adoPrimaryRS_WillChangeRecord( _
>            ByVal adReason As ADODB.EventReasonEnum, _
>            ByVal cRecords As Long, _
>            ByRef adStatus As ADODB.EventStatusEnum, _
>            ByVal pRecordset As ADODB.Recordset)

>  ' This is where you put validation code
>  ' This event gets called when the following actions occur

>  Dim bCancel As Boolean

>  '-- My Validation Code Added Here      <==
>  ValidateForm                 ' Call My Validation Sub
>  If Not mbValPassed Then
>     '-- Now Do I prevent the ADO Error Message:
>     '--   "The change was cancelled during notification; no
>     '--    columns are changed"
>     adStatus = adStatusCancel         ' Notify ADO to Cancel
>     Exit Sub
>  End If

>  Select Case adReason
>     Case adRsnAddNew
>     Case adRsnClose
>     Case adRsnDelete
>     Case adRsnFirstChange
>     Case adRsnMove
>     Case adRsnRequery
>     Case adRsnResynch
>     Case adRsnUndoAddNew
>     Case adRsnUndoDelete
>     Case adRsnUndoUpdate
>     Case adRsnUpdate
>  End Select
>  ...



Fri, 06 Jul 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Unwanted Message

2. Unwanted Security Message

3. macro gives unwanted message

4. Unwanted Setup Succeded message during MSI installation

5. Unwanted beep with message box

6. Insert into ms Access with ADO gives Error Message

7. Retrieving correct error messages with ado on SQL 2000

8. ADO messages and SQL SERVER 7 errors

9. ADO Application - Error messages and my application lost.

10. Requery Error Message (ADO)

11. Database ADO error message

12. Retrieving error message through ADO

 

 
Powered by phpBB® Forum Software