odbc--call failed: how to send a friendlier message to user?

[mrsbean]

I have Access front end with SQL back end. There are some constraints in the table which supports the form. If certain values are left blank (null), I receive an odbc--call failed error. I understand the error, but I want to make the interface a bit friendlier to the end user.

If the user starts entering a record and then clicks outside of the subform which holds these records, the ODBC error message appears. I tried some VBA code to check for null values on lost focus event, but it seems that the code I wrote there is completely ignored.

How can I change the error message which is displayed? Can somebody direct me to something which will show me how to customize what is displayed?

MrsBean

 

[CaptainD]

Seems to me there are a couple of ways to deal with the error. For one, have you stepped through the code and found where or what is the direct cause of the error?
My guess is you are trying to assign a Null to a variable. If so, then you can catch that and set a value in it's place.

Dim sMyString as string
sMyString = IIf(IsNull(YourValue), "", YourValue)

If you really want to catch an error then in your error trap you can use an If statement that checks the error number and send your own message.

MyErrTrap:
   If Err.Number = 1234 then
        MsgBox "Some Message", vbOkOnly, "Error Message"
   End If

I prefer to deal with issues so they don't throw errors myself.

 

[mrsbean]

I know that a value or values which are required in order to save the record was left null. I want to change the response -- get rid of the ODBC error message and replace it with one of my own, but so far, any VBA code I have tried to insert on any action has failed to fire. The ODBC error message happens first and then it ignores whatever else I told it to do.

MrsBean

 

[PHV]

A common way is to test all the validation rules in the BeforeUpdate event procedure of the bound form, playing with the Cancel argument.

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

 

[CaptainD]

Try something like this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrHandler
    txtRequiredField.SetFocus
If txtRequiredField.Text = "" Then
        MsgBox "You have left a required fields blank. Please Update the form", vbOKOnly, "Attention"
        txtRequiredField.SetFocus
        Cancel = True
Else
    Resume Next
End If


Exit Sub
ErrHandler:
    MsgBox "Error updating record. Error # " & Err.Number & ", " & Err.Description & " Please Update the form", vbOKOnly, "Attention"
    

End Sub

 

[CaptainD]

You may get by with just this.
(I removed the "resume next". Should not be needed.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrHandler

txtRequiredField.SetFocus

If txtRequiredField.Text = "" Then
        MsgBox "You have left a required fields blank. Please Update the form", vbOKOnly, "Attention"
        txtRequiredField.SetFocus
        Cancel = True
End If


Exit Sub
ErrHandler:
    MsgBox "Error updating record. Error # " & Err.Number & ", " & Err.Description & " Please Update the form", vbOKOnly, "Attention"
    

End Sub