Error handler and ADO recordset

[sanan]

Hi there
I have the following codes in on_Click event of a Button. And as you can see it’s job is to Add data to a Table in my DataBase, And It works.
But here is the little problem that I have with it, In order for it to work, all the Fields in my “tableSales” must have a value for them, other wise if any Control in my “sales” form has a Null value, The Button does not work and it does not Update my “Tablesales” anymore.
I think I have a Error Handler Problem.
Actually I like it to check for Fields “InvoiceId” and “reffrenceID” for Null Values, But for the rest of the Fields, It does not matter, and they can have a Value of Null.
Could you tell me how can I design my Error Handler Codes?

Private Sub TablesalesAdd_Click()

Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
Dim IV As Integer
Dim RR As Integer
Dim rq As Date
Dim cmbCus As String
Dim sName As String
Dim sAdd As String
Dim scity As String
Dim scount As String
Dim sregi As String
Dim spos As String

cmbCus = Me.ComboCustomer
IV = Me.InvoiceID100
RR = Me.ReffrenceID
rq = Me.RequiredDate
sName = Me.ShipName
sAdd = Me.ShipAddress
scity = Me.ShipCity
scount = Me.ShipCountry
sregi = Me.ShipRegion
spos = Me.ShipPostalCode
myCn.ConnectionString = "DSN=TT2"
myCn.Open
myRs.Open "INSERT INTO TableSales (CustomerID, InvoiceID, reffrenceID, RequiredDate, shipname, shipAddress, ShipCity, shipcountry, shipRegion, shipPostalcode) VALUES ('" & cmbCus & "', " & IV & "," & RR & ",'" & rq & "','" & sName & "','" & sAdd & "','" & scity & "','" & scount & "','" & sregi & "','" & spos & "');", myCn, adOpenDynamic

End Sub

Best Regards
sanan

 

[cmmrfrds]

The editing can be done up front before sending the insert. Something like.

If isnull(Me.InvoiceID100) then
msgbox "The invoice id was not entered"
docmd.cancel
exit sub
End if

 

[sanan]

Hi
Thank you for your Comment.
Yes, I know I can do that, But I like to use the other technique.
Some thing like;
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

 

[cmmrfrds]

I believe "resume next" will pick up where the error occurred. Please look up in Help. I don't think you need to use the goto.

 

[PHV]

Replace this:
goto errTimeoutRetry
By this:
Resume errTimeoutRetry

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