ADODC error trap - HELP!

[DrewDog_21]

Okay, my day was going great until I discovered this
ridiculous problem:

In the Error event of an ADODC, I have a Select Case series
to display more meaningful error messages to the user. The
problem here concerns the errors that occur when

1) the user tries to enter a record that would create a duplicate value in an indexed field, or
2) the user tries to enter a record that is entirely blank

Private Sub Adodc1_Error(ByVal ErrorNumber As Long, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, fCancelDisplay As Boolean)

On Error Resume Next

Select Case ErrorNumber
    Case 16389 'duplicate record
        MsgBox "The class '" & txtClass & "' already exists.", vbExclamation
        fCancelDisplay = True
    Case 3640 'reference to a deleted object
        MsgBox LoadResString(114) & _
          vbNewLine & LoadResString(115), vbExclamation
          
        fCancelDisplay = True
        With Adodc1
            .Recordset.CancelUpdate
            .Recordset.Requery
            .Refresh
        End With
'et cetera et cetera
End Select

This works fine to trap the Duplicate Record error IF the
user clicks on one of the Adodc control arrows. The
problem is that I have an AddRecord routine that is invoked
at various times. This routine also has an error handling
Select Case series.

Private Sub AddRecord()
'add new record

On Error GoTo Error_En_Base

'enable fields
If Adodc1.Recordset.RecordCount = 0 Then
    txtClass.Enabled = True
    txtClass.BackColor = vbWindowBackground
End If

txtClass.SetFocus

Adodc1.Recordset.Requery
Adodc1.Recordset.AddNew

Exit Sub

Error_En_Base:
    Select Case Err.Number
        Case 3021 'no active record
            Resume Next
        Case 3219 'action not permitted
            Resume Next
        Case -2147467259 'user has not entered all required fields
             MsgBox Err.Description, vbExclamation
'et cetera et cetera
End Select

End Sub

I just discovered that with an Adodc control, Err.Number
-2147467259 could mean one or more of at least 3 different
things.

1) that the entry will create duplicates in the index,
2) that the user has not entered a required field, or
3) that it is not possible to enter a blank record.

The above code does not pass control to the
Adodc1_Error routine, and I can't figure out how to make it
do so.

To an average user, the Microsoft Jet error messages are a
bunch of gibberish. How can I either

1) pass control to the Error routine and leave the AddRecord routine, or
2) determine which of the 3 or more situations represented by Err.Number -2147467259 actually caused the error?

Please, if SOMEONE can help, I will be forever grateful!

End Sub

[JHausmann]

Originally posted by DrewDog_21
Okay, my day was going great until I discovered this
ridiculous problem:

In the Error event of an ADODC, I have a Select Case series
to display more meaningful error messages to the user. The
problem here concerns the errors that occur when

1) the user tries to enter a record that would create a duplicate value in an indexed field, or
2) the user tries to enter a record that is entirely blank

<snip code>

I just discovered that with an Adodc control, Err.Number
-2147467259 could mean one or more of at least 3 different
things.

1) that the entry will create duplicates in the index,
2) that the user has not entered a required field, or
3) that it is not possible to enter a blank record.

The above code does not pass control to the
Adodc1_Error routine, and I can't figure out how to make it
do so.

To an average user, the Microsoft Jet error messages are a
bunch of gibberish. How can I either

1) pass control to the Error routine and leave the AddRecord routine, or
2) determine which of the 3 or more situations represented by Err.Number -2147467259 actually caused the error?

Please, if SOMEONE can help, I will be forever grateful!

End Sub

For #1 can you:

on error goto error_point.
{...}
error_point:
call adodc1_Error

For #2, do the easy stuff first:

Before you attempt an insert:
1) check that all fields have a len> 0
if there are fields with a lenght < 1 then check the fields that you predetermine to be required (any unique index fields and any field that doesn't allow nulls)
At this point, you should know if there's a blank record (or not) and if all required fields are filled in (or not). Make the user fix those things that need to be fixed before issuing a sql statement.

[DrewDog_21]

JHausmann you are right, a little bit of front-end
verification eliminates the possibility of a blank field or
missing required field error. And assuming that no MORE
errors are represented by error number -2147467259,
whenever that error occurs it will be because of a
duplicate record.

I am really curious as to why the Err object gives this
number for at least 3 different errors in the Adodc
control. I can't pass control to the Adodc1_Error routine
like you suggested, because I have to pass an error number,
and -2147467259 doesn't mean anything in that particular
event.

[JHausmann]

Nothing preventing you from setting the value of err.number...

[DrewDog_21]

That's true, I can set the error number to whatever and
pass it to the event. What has had me mind boggled is that
3 if not more different errors are covered by that number,
so when control is passed to my error handling routine, how
in the world can I figure out which specific error caused
it? I would have to know that in order to know what error
number and description to pass to the Adodc1_Error event so
that a meaningful error message is displayed to the user.

Okay, got the front-end verification going, so provided
that the apparently majestic number of -2147467259 doesn't
come up with any MORE errors, things should be okay. Now I
must go drink beer and mingle with Colombian ladies

[JHausmann]

Too much of either can lead to problems...

[chicho_chicho]

Hello,
Just Wanted you to know, I had the same problem with Access, what I ended with was just letting Access speak directly to the user.
Chicho

[Bregalad]

Actually, error -2147467259 can also mean
"Selected collating sequence not supported by the operating system."

[DrewDog_21]

Great! Yet another error covered by my favorite error number.

Seriously, I have now accepted my ridiculous -2147467259 fate and am ready to move on. But I would love to hear an explanation as to why now at least 4 errors are covered by the same number.

[JHausmann]

Originally posted by DrewDog_21
Great! Yet another error covered by my favorite error number.

Seriously, I have now accepted my ridiculous -2147467259 fate and am ready to move on. But I would love to hear an explanation as to why now at least 4 errors are covered by the same number.

I can get at least 5 different causes for a single error, if I use bound controls. Simplest explanation: it's far easier to use an existing error than to do the right thing an create a new one. Blame M$oft.