• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Changing error code description

Experts,
In my table tblBonusEntered I created this unique index:
Index Name
Data Entry
Filed Names
StoreID
MonthID
YearID
My objective is to keep a bonus that has already been entered from being entered again. So I used this code to bypass a system error code 3022:
Err_cmdSave_Click:
    If Err.Number = 3022 Then
    MsgBox "Bonus's for this Store for the Month and Year selected have already been entered.!"
       Me.DataEntry = True
       Exit Sub
    End If
    MsgBox Err.Description
    Resume Exit_cmdSave_Click

Open in new window


Well I'm still get the error?
0
Frank Freese
Asked:
Frank Freese
  • 13
  • 10
  • 2
  • +1
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Error 3022 is rendered in the Form Error event, and consequently needs to be trapped there.  See example below:


Private Sub Form_Error(DataErr As Integer, Response As Integer)
    'http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26898496.html#a35173481 - more discussion
   
    Response = acDataErrContinue
    Select Case DataErr
       
        Case 3022 'Dup Value
            MsgBox "Bonus's for this Store for the Month and Year selected have already been entered.!"
       
        Case 3197, 7787, 7878   'Write conflict errors ...
             MsgBox "Another user is currently updating this record. Please go to happy hour and try later."
        Case Else
            MsgBox "An unexpected error occurred " & DataErr & "  " & AccessError(DataErr)
    End Select
End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And remove the 3022 code from your Save button.

mx
0
 
Frank FreeseAuthor Commented:
mx: I placed the code in the Form_Error (didn't know about that) and removed the 3022 code from the cmdSave. The same system error message appears and never got to the Form_Error?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Show me the code you have for the Save button ..

And did you use the code I posted ?

mx
0
 
Frank FreeseAuthor Commented:
Here;s the code for the Save button:
On Error GoTo Err_cmdSave_Click

 UserResponse = MsgBox("Were there ANY adjustments to this Stores commission? If you click Yes, the Bonus Adjustment button will appear! Click on Bonus Adjustment to Record any Adjustment(s).", vbYesNo, "Adjustments?")
    If UserResponse = vbYes Then
    Beep
    Me.cmdBonusAdj.Visible = True
    Me.cmdBonusAdj.SetFocus
    Exit Sub
    Else
    End If
 DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 MonthID.SetFocus
Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
   
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

Open in new window

Here's the code for the Form_Err:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
   
    Response = acDataErrContinue
    Select Case DataErr
        
        Case 3022 'Dup Value
            MsgBox "Bonus's for this Store for the Month and Year selected have already been entered.!"
        
        Case 3197, 7787, 7878   'Write conflict errors ...
             MsgBox "Another user is currently updating this record."
        Case Else
            MsgBox "An unexpected error occurred " & DataErr & "  " & AccessError(DataErr)
    End Select
End Sub

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is it possible to upload a db that does this ?

mx
0
 
Frank FreeseAuthor Commented:
You bet....the db opens to the form in question.
A record already exists for:
Month: September
Year: 2011
Store: # 2
Once those have been select simply got to the Save button. There are no addtional charges.
StoreBonus.mdb
0
 
Michael VasilevskySolutions ArchitectCommented:
It works for me on the cmdSave_Click event. I don't think you need Form_Err...
Try the attached code.
HTH,

MV
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
    Dim UserResponse

 UserResponse = MsgBox("Were there ANY adjustments to this Stores commission? If you click Yes, the Bonus Adjustment button will appear! Click on Bonus Adjustment to Record any Adjustment(s).", vbYesNo, "Adjustments?")
    If UserResponse = vbYes Then
    Beep
    Me.cmdBonusAdj.Visible = True
    Me.cmdBonusAdj.SetFocus
    Exit Sub
    Else
    End If
 DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 MonthID.SetFocus
 
Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
   Select Case Err
        
        Case 3022 'Dup Value
            MsgBox "Bonus's for this Store for the Month and Year selected have already been entered.!"
        Case 3197, 7787, 7878   'Write conflict errors ...
             MsgBox "Another user is currently updating this record."
        Case Else
            MsgBox "An unexpected error occurred " & DataErr & "  " & AccessError(DataErr)
    End Select

    'MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

Open in new window

0
 
Michael VasilevskySolutions ArchitectCommented:
I think the problem was simply that you have "MsgBox Err.Description" outside the if statement, so on 3022 you see your message, and then the default message.

Err_cmdSave_Click:
    If Err.Number = 3022 Then
    MsgBox "Bonus's for this Store for the Month and Year selected have already been entered.!"
       Me.DataEntry = True
       Exit Sub
    End If
    MsgBox Err.Description
    Resume Exit_cmdSave_Click

Correct is:

Err_cmdSave_Click:
    If Err.Number = 3022 Then
       MsgBox "Bonus's for this Store for the Month and Year selected have already been entered.!"
       Me.DataEntry = True
       Exit Sub
    Else
      MsgBox Err.Description
    end if
    Resume Exit_cmdSave_Click
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I will be back in a few ...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... working on this ... not quite sure what is going on ... something odd ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
mvasilevsky:  You code @ http:#37002327 does trap the error and display the message.  At this point the user should make the corrections.  Meantime, the record is still Dirty.  However ... IF ... You attempt to Close the Form  (not using the Close button with does an Undo) ... Now ... Error 3022 occurs again but this time in the Form Error event ... so, there is still a problem. In other words, when the form is still dirty, try switch to Design view ... this *will* trigger the Form Error event.

For some odd reason, this is not happening on the initial Save attempt ...

mx

0
 
Frank FreeseAuthor Commented:
Folks,
Does any of this have to do with the Unique Index I set up at the beginning of this post?
At one point I consider this in the Private Sub cmdSave_Click() event:

If DCount("*", "tblBonusEntered", "[StoreID]= " & Me.StoreID) And DCount("*", "tblBonusEntered", "[MonthID]= " & Me.MonthID) _
And DCount("*", "tblBonusEntered", "[YearID]= " & Me.YearID) > 0 Then
  Beep
 MsgBox "Bonus's for this Store for the Month and Year selected have already been entered.!"
   Me.DataEntry = True
   Me.Form.Undo
   Cancel = True
   Exit Sub
End If

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Does any of this have to do with the Unique Index I set up at the beginning of this post? "
Well ... that is the idea ... create a U.I. ... and let the Form Error event trap the duplicate case. I've used this *many* times, but something odd is going on here.  I will look at it more tonight.

mx
0
 
Frank FreeseAuthor Commented:
mx: Thanks
0
 
mbizupCommented:
What was the trouble with the DCount method that you have commented out?  This is what I generally use to avoid duplcates and find it very reliable and straight-forward.
0
 
mbizupCommented:
Clarification - I use that type of code without composite Unique Indexes.
0
 
Frank FreeseAuthor Commented:
mbizup: I wanted to clear the form and when I used Me.DataEntry=True I kept getting a warning messasge. I just went back and removed that bit of code and it appears using Dcount as I first thought is working.
mx: any thoughts here? I know you continue to work on the other problem but if Dcount is OK with you I'll close this out and split the points.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Hey ... whatever it takes to get this going.  

I'm mystified at the moment. Worked on this last night and cannot see WHY this is not work.  Again, it's odd that if you try to close the form in the context I noted above, THEN 3022 occurs in the Form Error event - as expected.  It SHOULD happen as soon as a Save is attempted.

Compound Unique Indexes do make it more 'relationally' correct.

mx
0
 
Frank FreeseAuthor Commented:
mx: thanks. I'll spilt the points. another question follows
0
 
Frank FreeseAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for fh_freese's comment http:/Q_27407141.html#37006690
Assisted answer: 250 points for DatabaseMX's comment http:/Q_27407141.html#37003011
Assisted answer: 250 points for mbizup's comment http:/Q_27407141.html#37006172

for the following reason:

thanks to all
0
 
Frank FreeseAuthor Commented:
incorrectly closed the question
0
 
Frank FreeseAuthor Commented:
thanks
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And oddly enough (using the code I posted) ... IF ... you execute a Save by doing a Shift+Enter, then the Form Error event DOES trigger - as expected !...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
AND ... if you turn on the Nav buttons, and then try to nav to a new record - which forces an automatic save, you get the expected results.  Seems only when doing an explicit save with the button this fails ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Damn strange that NONE of these:


 'Me.Dirty = False
 DoCmd.RunCommand acCmdSaveRecord
 'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

work ... does not cause error 3022 to render in Form Error event at that moment.  It happens in the Save button code ... but that doesn't help.  Trapping there will not solve the problem, because trying to close (w/o undo) will still result in Form Error triggering ... as it should.

WEIRD!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And so ... this DOES work ... Form Error event is triggered correctly:

Private Sub cmdSave_Click()
'On Error GoTo Err_cmdSave_Click
    'Dim UserResponse

'' UserResponse = MsgBox("Were there ANY adjustments to this Stores commission? If you click Yes, the Bonus Adjustment button will appear! Click on Bonus Adjustment to Record any Adjustment(s).", vbYesNo, "Adjustments?")
''    If UserResponse = vbYes Then
''    Beep
''    Me.cmdBonusAdj.Visible = True
''    Me.cmdBonusAdj.SetFocus
''    Exit Sub
''    Else
''    End If
 
 'Me.Dirty = False
 'DoCmd.RunCommand acCmdSaveRecord
 'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  SendKeys "+{ENTER}"
 MonthID.SetFocus

GO Figure !!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 13
  • 10
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now