Error Handling issue

[Dave14867]

Hello all,



I am trying to create error handlers to better instruct users and to enhance my skills and I have run into yet another problem. In the attached database if you open frmPartNumbers and go to the third record it shows "Part #" as 12345a, if you change that to 12345 it will create an error because the first record has that PN.

My issue is that when I try to handle the error I get an error number of "0" when it should be error number "3022". I also want the user to just see the message I have put in there and not the default Access error message.

What am I doing wroog to capture the actual error number and how do I stop the default Access Error Message?


Error Handling.zip


Thanks in Advance

Dave

[Welshgasman]

I get error 3022 ?
Plus some annoying messages that image file is not where it is supposed to be.

You need the OnError statement before any code executes?

Plus you are using the Form Error event, so choose one or the other.? I have never used the Form Error event, so not sure how it behaves.

[Dave14867]

Welshgasman,

The message about the Image File not being where it is supposed to be is because of the Image Path Field has a path where it expects to find an image file which you don't have on your system, so that error is working correctly.
I changed it so the OnError statement is before any code executes I believe.

Private Sub MfrPartNumber_AfterUpdate()
On Error GoTo ErrHandler
If Me.Dirty Then    'to check if any data has changed.
        DoCmd.RunCommand acCmdSaveRecord
End If


Dim ErrorNumber As Integer




Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "The PN Entered is a Duplicate PN and is not allowed, Please enter a different PN"""
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Dave's Database"
'Help = "DEMO.HLP"
'Ctxt = 1000
Response = MsgBox(Msg, Style, Title, , Ctxt)
If Response = vbYes Then    ' User chose Yes.
    MyString = "Yes"    ' Perform some action.
Else    ' User chose No.
    MyString = "No"    ' Perform some action.
End If




Exit_ErrorHandler:
    Exit Sub


ErrHandler:
Dim intErrNum As Integer
intErrNum = Err
If intErrNum = 3022 Then
 
        DoCmd.SetWarnings False
        ErrorNumber = Err.Number
        'MsgBox "The PN Entered is a Duplicate PN and is not allowed, Please enter a different PN", , "Daves MiniMRP"
        MsgBox "The PN Entered is a Duplicate PN and is not allowed, Please enter a different PN" & _
        vbCrLf & ErrorNumber, , "Daves MiniMRP"
        Me.PartImage.Picture = ""
        Me.txtImagePath = ""
        Me.MfrPartNumber.SetFocus
End If


End Sub

It still isn't working as I would like though, now it goes back to the PN field but if you hit "Tab" it just goes to the next field rather that forcing the user to change the PN. That is probably because of the Me.Dirty Statement correct?

[Welshgasman]

TBH I do not know why it allows you to move to the description.
I've found this link http://www.databasedev.co.uk/duplicates.html so see how they do it.

Seems it should also be the Before_Update even t as well?
https://stackoverflow.com/questions/...ving-a-textbox

HTH

Welshgasman,

The message about the Image File not being where it is supposed to be is because of the Image Path Field has a path where it expects to find an image file which you don't have on your system, so that error is working correctly.
I changed it so the OnError statement is before any code executes I believe.

Private Sub MfrPartNumber_AfterUpdate()
On Error GoTo ErrHandler
If Me.Dirty Then    'to check if any data has changed.
        DoCmd.RunCommand acCmdSaveRecord
End If


Dim ErrorNumber As Integer




Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "The PN Entered is a Duplicate PN and is not allowed, Please enter a different PN"""
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Dave's Database"
'Help = "DEMO.HLP"
'Ctxt = 1000
Response = MsgBox(Msg, Style, Title, , Ctxt)
If Response = vbYes Then    ' User chose Yes.
    MyString = "Yes"    ' Perform some action.
Else    ' User chose No.
    MyString = "No"    ' Perform some action.
End If




Exit_ErrorHandler:
    Exit Sub


ErrHandler:
Dim intErrNum As Integer
intErrNum = Err
If intErrNum = 3022 Then
 
        DoCmd.SetWarnings False
        ErrorNumber = Err.Number
        'MsgBox "The PN Entered is a Duplicate PN and is not allowed, Please enter a different PN", , "Daves MiniMRP"
        MsgBox "The PN Entered is a Duplicate PN and is not allowed, Please enter a different PN" & _
        vbCrLf & ErrorNumber, , "Daves MiniMRP"
        Me.PartImage.Picture = ""
        Me.txtImagePath = ""
        Me.MfrPartNumber.SetFocus
End If


End Sub

It still isn't working as I would like though, now it goes back to the PN field but if you hit "Tab" it just goes to the next field rather that forcing the user to change the PN. That is probably because of the Me.Dirty Statement correct?

[davegri]

I've handled this problem before using this method.
In the after update event of the textbox holding the PN, use DLookup (or DCount) to see if the PN already exists. If so, display your error message and UnDo the PN.

[Micron]

In the after update event of the textbox holding the PN, use DLookup (or DCount) to see if the PN already exists. If so, display your error message and UnDo the PN.

Did you mean BeforeUpdate? AFAIK, AfterUpdate has no Cancel, thus no Undo either. Usually I validate data before attempting to write it, and often it is enough to simply trap the Access error number and present your own message.

[orange]

As micron said--- use the BeforeUpdate event to do validation. That's the last event before record is saved.