on error resume next problem

[jeba]

I have a project that I'm changing from DAO 3.51 jet to ADO

The on error resume next doesn't work. In the old access when I did an object.addnew then object.update, I could set on error resume next and check the error code after the update to determine if a duplicate key was found.

Now, it gives a fatal run-time error on the update method and doesn't continue to the next statement. What is up?

I'm using VB6 connecting to an Access 2000 .mdb

The table has a primary key and 2 other indexes.

The connection string is:

accessconnect = "Provider=MSDASQL.1;" & _
"Persist Security Info=False;" & _
"Extended Properties=" & """" & _
"DSN=MS Access Database;" & _
"DBQ=" & fulldsn & ";" & _
"DefaultDir=" & currdrivepath & ";" & _
"UID=admin;PWD=;" & _
"DriverId=25;FIL=MS Access;" & """"

Here's the code. It errors before executing the "if err.number" statement saying I am trying to create duplicate key. I want to trap that myself.

nixdtbl.Close
Set nixdtbl = New ADODB.Recordset
nixdtbl.Open "nixdates", Umpmain.Conn1, adOpenDynamic, adLockOptimistic, adCmdTableDirect

nixdtbl.AddNew

nixdtbl("NixUmp_id&quot = umptbl("Umpnum&quot
nixdtbl("NixDate&quot = CDate(datelist.Text)
nixdtbl("Nixgame&quot = 0
nixdtbl("Nixfield&quot = 0
On Error Resume Next
nixdtbl.Update

If Err.Number = 3022 Then
MsgBox "Already blocked this date."
Err.Clear
nixdtbl.CancelUpdate
Exit Sub
End If

 

[tcoffey]

You might try using the on error statement at the top of the code.

What happens when you step thru the code and at what point does it give you the error?

Usually when I am using the on error statement I place the code at the top of the routine. You could be getting an error before that time. The other option is to place the code in a stored procedure and have all the work done there.

Good Luck.

 

[jeba]

I called Microsoft support. They said it should work. I sent them my sample project and it worked on their machine, but not mine. He was using Windows 2000, I am using Windows ME.

Microsoft is going to get back to me. The guy was stumped.

I haven't tried it on my Windows 98 machine. I'll do that and let you know. I'll also let you know what MS comes back with.

 

[tcoffey]

Looking forward to see what they say. It could be something with the ME OS.

 

[jeba]

They are supposed to get back to me today, but I discovered something strange.

It fails in the IDE (debug "run&quot mode, but after I do a "make" (compile) and execute the .exe it WORKS!

Also, it does NOT fail on my Windows 98 machine in either mode. So, something is wrong with the Windows ME VB6 IDE mode.

I'll let you know what the resolution is.

BTW you can put an ON ERROR anywhere in a procedure. You can even have more than one. It's best not to mix methods. If you like the ON ERROR GOTO then use that throughout your project. If you prefer ON ERROR RESUME NEXT (as I do) then you should use that throughout. With the RESUME NEXT you need more code however. You need to check the err.number after any instruction that could cause an error. But, with this method you can identify the specific error and handle it.

 

[jeba]

Well the answer is embarrassing.

Under VB "tools/options/general" I had the "break on all errors" button checked. This traps the error before any error handling.

The MS tech was embarrassed also, but his expertise was in the mdb area which I thought was the original problem.