Problem with Error Handler using ADO Recordset

[sanan]

Hi there
I have a question about Following codes;
These Below codes as you can see update a table by Name of Tablesales using ADO recordset
And they work for most Part, But as you can see these codes check for the
Error (# -2147217900, 3219, and any Unknown error) for all my Records, but I want that they check only “for example” the InvoiceID record and basically do not mind the other records, I know I should be able to do it by adding a If statement,
But I just cannot figure it out, Any suggestions?


Private Sub Command166_Click()
'telling the routine what to do if an error raises
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_InvoiceID As Integer
Dim v_ReffrenceID As Integer
Dim v_combcust As String
Dim error_fld As String
Dim msg_warning As String
error_fld = ""
'check for entered values
If IsNull(Me!InvoiceID100) And IsNull(Me!ReffrenceID) And IsNull(Me!ComboCustomer) Then
error_fld = "InvoiceID and ReffrenceID and CustomerID"
ElseIf IsNull(Me!ComboCustomer) Then
error_fld = "customerID"
ElseIf IsNull(Me!InvoiceID100) Then
error_fld = "InvoiceID"
ElseIf IsNull(Me!ReffrenceID) Then
error_fld = "ReffrenceID"
Else
v_CustomerID = Me!ComboCustomer
v_InvoiceID = Me!InvoiceID100
v_ReffrenceID = Me!ReffrenceID
End If
If Len(error_fld) > 0 Then
msg_warning = "you have to give a value for " & error_fld & "."
MsgBox msg_warning
Else
'create dns-lessconnection string
demoPath = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=TT2;Initial Catalog=TT2"
'Set connection string
demoConn.ConnectionString = demoPath
'Open connection
demoConn.Open
'Set active connection
Set sqlcmd.ActiveConnection = demoConn
'Set sql statement
sqlcmd.CommandText = "Select * From TableSales;"
'set cursorlocation
r2.CursorLocation = adUseClient
'open recordset for editing
r2.Open sqlcmd, , adOpenKeyset, adLockOptimistic
'adding new record
r2.AddNew
'filling records
r2("CustomerID") = v_CustomerID
r2("InvoiceID") = v_InvoiceID
r2("ReffrenceID") = v_ReffrenceID
'update record
r2.Update
'closing recordset
r2.Close
'closing connection
demoConn.Close
'destroing Set's
Set r2 = Nothing
Set demoConn = Nothing
Set sqlcmd = Nothing
'Exit sub needed here so Errhandler is skipped
Exit Sub
'Handling the errors
ErrHandler:
Select Case Err.Number
Case 3219 'operation not allowed
Resume Next
Case -2147217900 'duplicate record
MsgBox "Invoice 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

 

[PHV]

You may play with the 2 instructions below:
On Error GoTo ErrHandler
On Error Resume Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244