Invite to Debug vs. Error Message

[athyeh]

Hello,



I have a rule embedded in one of my fields that does not allow the user to enter duplicate values. I have a lot of VBA code running behind that, and when a user accidentally enters in duplicate information into that field, Access produces a VBA error and invites him/her to Debug it.

Is there a way to replace this invitation with an error message of my creating? Barring that, what are my options here, in terms of getting rid of that message?

Thanks!

[pbaldy]

Add error trapping to the procedure:

http://www.baldyweb.com/ErrorTrap.htm

[athyeh]

Perfect. Thread solved. Thank you.

[pbaldy]

Happy to help!

[athyeh]

Hello,

I tried to re-integrate this back into my DB but for some reason it is not functioniong again.

What have I done wrong?

Private Sub LID_AfterUpdate()
On Error GoTo ErrorHandler
Const cQuote = """"
Me!Operator.DefaultValue = cQuote & Me!Operator.Value & cQuote
Me!Process.DefaultValue = cQuote & Me!Process.Value & cQuote
Me!Goal.DefaultValue = cQuote & Me!Goal.Value & cQuote
ExitHandler:
Exit Sub
Select Case Err
Case 3205
MsgBox "LID has already been recorded. Please use alternative LID."
DoCmd.Hourglass False
Resume ExitHandler
Case Else
MsgBox Err.Description
DoCmd.HourglassFalse
Resume ExitHandler
End Select
DoCmd.RunCommand acCmdSaveRecord
Me![qrySubForm subform].Requery
Me.Requery
If Hour(Now()) > Nz(Me.tbxHour, -1) Then
Me.tbxCount = 0
Me.Goal = 35
Me.tbxHour = Hour(Now())
End If
Me.tbxCount = Me.tbxCount + 1
Me.Requery
End Sub

[pbaldy]

There is no line to tell it where the error handler is:

ErrorHandler:

[athyeh]

Private Sub LID_AfterUpdate()
On Error GoTo ErrorHandler
    Const cQuote = """"
        Me!Operator.DefaultValue = cQuote & Me!Operator.Value & cQuote
        Me!Process.DefaultValue = cQuote & Me!Process.Value & cQuote
        Me!Goal.DefaultValue = cQuote & Me!Goal.Value & cQuote
ExitHandler:
    Exit Sub
ErrorHandler:
    Select Case Err
        Case 3205
            MsgBox "LID has already been recorded. Please use alternative LID."
            DoCmd.Hourglass False
            Resume ExitHandler
        Case Else
            MsgBox Err.Description
            DoCmd.Hourglass False
            Resume ExitHandler
        End Select
    Exit Sub
DoCmd.RunCommand acCmdSaveRecord
Me![qrySubForm subform].Requery
    If Hour(Now()) > Nz(Me.tbxHour, -1) Then
        Me.tbxCount = 0
        Me.Goal = 35
        Me.tbxHour = Hour(Now())
    End If
    Me.tbxCount = Me.tbxCount + 1
Me.Requery
End Sub

Still does not let me save and requery the form.

[pbaldy]

Might help to know what "does not work" means exactly. For starters, the code starting here will never run:


DoCmd.RunCommand acCmdSaveRecord

[athyeh]

Might help to know what "does not work" means exactly. For starters, the code starting here will never run:


DoCmd.RunCommand acCmdSaveRecord

I apologize, realized what I was doing was more than ambiguous and wanted to clarify:

The Form will not save or requery.

Why won't the DoCmd.RunCommand work?

[pbaldy]

I didn't say it wouldn't work, I said it wouldn't run.

You have that stuff below the exit and error handlers, both of which end with "Exit Sub", which code will never progress beyond. You want all code that should run above the exit handler. Back to my link, in the area noted with:

'Your code here

[athyeh]

Gosh darn it. You are just great.

[pbaldy]

As the joke goes, I'm a legend...in my own mind.