Error Handler Mystery!

[sanan]

Hi there
I am a little confused on Error Handler in ADO recordset.
Following codes Work. (so do not be intimitate by the size of it)
Basically How does the Error handler work? I have some Idea about the concept of it, But I am not quite sure “for example, In a situation that we are trying to find out the Duplicate Entries”, does the Error Handler checks for all Updatable Fields or Picks any particular one?

In bellow codes, The Error Handler checks for Duplicate only for “OrderID” field, and does not mind the other ones, Otherwise one can Enter Duplicates in any other fields except the “OrderID”

Private Sub Accept_Click()
On Error GoTo ErrHandler
Dim demoConn As New ADODB.Connection
Dim sqlcmd As New ADODB.Command
Dim r2 As New ADODB.Recordset
Dim demoPath As String
Dim v_OIDID As Integer
Dim v_QIDID As Integer
Dim Tax1 As Double
Dim Tax2 As Double
Dim Total As Double
Dim error_fld As String
Dim msg_warning As String

error_fld = ""
'check for entered values
If IsNull(Me!OID) And IsNull(Me!QID) Then
error_fld = "OIDID and QIDID"
ElseIf IsNull(Me!OID) Then
error_fld = "OIDID"
ElseIf IsNull(Me!QID) Then
error_fld = "QIDID"
Else
v_OIDID = Me!OID
v_QIDID = Me!QID
Tax1 = Me!Tax1
Tax2 = Me!Tax2
Total = Me!Total
End If
If Len(error_fld) > 0 Then
msg_warning = "you have to give a value for " & error_fld & "."
MsgBox msg_warning
Else
demoPath = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=TT2"
demoConn.ConnectionString = demoPath
demoConn.Open
Set sqlcmd.ActiveConnection = demoConn
sqlcmd.CommandText = "Select * From OrdersOrder;"
r2.CursorLocation = adUseClient
r2.Open sqlcmd, , adOpenKeyset, adLockOptimistic
r2.AddNew
r2("OrderID") = v_OIDID
r2("QouteID") = v_QIDID
r2("CustomerID") = CustomerID.Value
r2("CompanyName") = ShipName.Value
r2("OrderDate") = OrderDate.Value
r2("tax1") = Tax1
r2("tax2") = Tax2
r2("Total") = Total
r2.Update
r2.Close
demoConn.Close
Set r2 = Nothing
Set demoConn = Nothing
Set sqlcmd = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
Case 3219 'operation not allowed
Resume Next
Case -2147217900 'duplicate record
MsgBox "Order ID Has Been Issued Already"
Resume Next
Case Else 'unknown error
MsgBox "Unknown error:" & Err.Number & " " & Err.Description
Resume Next
End Select
End If
End Sub

Best regards
Sanan

 

[TomCologne]

VB HelP: Err Object
Contains information about run-time errors.

run-time errors:
An error that occurs when code is running. A run-time error results when a statement attempts an invalid operation.
-------------------------------------------------------

If "OrderID" is Primary Key in the table and the entry would produce a duplicate or a Null value then your "Private Sub Accept_Click()" would cause a run time error because it would result in a violation of the rules for primary keys.

In other words, you only need to check for a valid entry in the control if "OrderID" is a simple text field with no specific properties.

Another example:

If your procedure is supposed to modify data in a field and comes across a null value that you didn't consider,
without the handler you'd end up in the VBE window, a line in your code highlighted.

The handler however allows you to, eg display an alert instead (MsgBox Err.Description) and determine what happens next (Resume), like "Exit" or go to next record etc.

The error handling created by the wizard is just one way to deal with errors.
If an alert doesn't make sense you could leave the MsgBox part out.

I hope this helps,

TomCologne

 

[sanan]

Hi TomCologne
Thanks so much for your comment.
But I should say that my “OrderID” is not a Primary Key. But it checks for Duplicate entries of “OrderID” field.
Let me ask you this question; Could you check my Latest Thread on this issue.
This is another technique of using ADO recordset; thread 705-965848
In that thread, I have a similar error Handler Issue.
The codes work and Data to my Table, But Only and Only when All the Fields have a Value for them and there is no Null value, If a user misses one not so important Control, the Data entry does not happen.
It is a little strange for me. Could you explain that?

Best regards
Sanan

 

[TomCologne]

Try stepping through your code to see what's going wrong.

My suggestion for the Null value issue would be a validation rule and/or a default value in the form control.

TomCologne

 

[tstuetzer]

Hi,
i dont understand why your "err handler" is in the "If else". On Error the Sub has to be terminated.
So just check then isnull() and raise n error and handle evrything in the errorhandle. Why ist the Id n Primary Key? and should be new entered by the user, get a function therefor.

 

[sanan]

Hi TomCologne, tstuetzer
Thanks so much for the comments.
I should say again that the codes work. They do exactly what they supposed to do, And that is my question, I tried the exact codes(or very similar) in another situation, They work again But only the “Checking for duplicate part of it does not work properly”, And that is what is puzzling me.

Also, TomCologne; I solved the problem of my other thread. Adding only this solved it;
“On error Resume next”

Let my summarize my Question, how do you use following in a Practical situation?

public sub AnExample()

On Error goto ErrHandler:

' Code here that sets up an ADO Command for execution
...
...
...

' Following lines of code attempt to execute the ADO command
errTimeoutRetry:
On Error GoTo errTimeout
adoCMD.Execute
On Error GoTo ErrHandler:

' Rest of the procedure here
...
...
...

Exit Sub

ErrHandler:
strMsg = "The sytem has returned an error. " & _
"The error message was: " & _
Err.Description, vbOKOnly + vbExclamation
MsgBox strMsg
Exit Sub

errTimeout:
If Err.Description = "Timeout expired" Then
strMsg = "A timeout error occured. Would you like to try again?")
If msgbox(strMsg,vbYesNo) = vbYes Then
goto errTimeoutRetry
Else
Exit Sub
End if
Else
GoTo ErrHandler:
End If

End Sub

Basically, I do not know where to put “errTimeoutRetry:” in my original Sub.

Best regards
Sanan