Solved

Error 3059 when ESC is pressed in Access 2007

Posted on 2010-09-21
15
1,462 Views
Last Modified: 2012-06-27
We have a main form/subform situation that I'm having a bit of trouble with...here's the processing sequence.

1. User enters some data on main form, then opens subform.
2. Enters data on subform and saves.   Data is saved to table #2, control returns to main form, data from subform is displayed on main form.  
3. User decides they don't want to keep that new data, and presses ESC.   In Form_KeyDown, we trap for the ESC key press and delete the data from table #2, the subform data is cleared from the main form.  

Or, that's the way it used to work in Access 2003.  Now, in 2007, when the user presses ESC, they get the error 3059, "Operation canceled by user".

We need to keep that processing to delete the saved data from the secondary table, so we can't ignore the ESC key press.  I've tried trapping for the 3059 error and resuming processing, but that doesn't seem to work, eiither.    

Any suggestions would be much appreciated.  Thanks!
0
Comment
Question by:L_Malchiodi
  • 6
  • 5
  • 4
15 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Post the code in the KeyDown event related to ESC.

mx
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Not that this is critical, but is this really a "subform" or is it a form that you open on top of your main form?

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Yes, actually ... not really clear on the process here.

"User decides they don't want to keep that new data,"
On the Main form ?  Or subform?

mx
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
It sounds like the OP wants a Cancel or Undo button on the main form that will undo changes made to both the main and "subform".  Unfortunatly, docmd.Undo will only undo changes in the current form.  However, I was doing some reading on transaction processing in the Access 2000 Developers Handbook just the other day, and a section in there implied that you could you can use transaction processing within a form to allow a rollback of all transactions not yet committed, to include changes made to data in a form/subform configuration.

I have not tried this, but it would come in handy for a project I'm working on.  I've always assumed that changes to the main form were not undoable once I had set the focus to a control in a subform, but this implies that if I get the BeginTrans, CommitTrans and Rollback methods located in the right events, I could rollback changes made in both the main and subforms.
0
 

Author Comment

by:L_Malchiodi
Comment Utility
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)


   On Error GoTo ErrorHandler
   
   'Declarations
   Const strProcedureName = "Form_KeyDown"
   
   Dim frmMe            As Form
   Dim Value   As String
   Dim intArrayIndex As Integer
   Dim dbCurrent As Database
   Dim strSQL  As String
   Set frmMe = Me
   Set dbCurrent = CurrentDb()
   
   Select Case KeyCode
      Case vbKeyEscape
         Select Case mstrRuleTypeMne
 
               
            Case RULE_TYPE_MNE_RFLTST 'JONES C6853 08/18/03
               For intArrayIndex = 1 To 6
                  If mudtOldRfltstEntries(intArrayIndex).lngAddlTestParmNo <> Val(TStr(frmMe("parm_val" & intArrayIndex + 7))) Then
                     strSQL = "DELETE dbo_addl_test_parm.* " _
                        & "FROM dbo_addl_test_parm " _
                        & "WHERE dbo_addl_test_parm.addl_test_parm_no = " & Val(TStr(frmMe("parm_val" & intArrayIndex + 7)))
                     dbCurrent.Execute strSQL
                  End If
               Next intArrayIndex

         End Select
   End Select
   
ExitRoutine:
   Set dbCurrent = Nothing
   On Error Resume Next
   Exit Sub
   
ErrorHandler:
   gobjLastError.Save Err, strProcedureName
   
   Select Case gobjLastError.Number

      Case Else
         gobjLastError.Show
         Resume ExitRoutine
   End Select
   
End Sub

Code is now attached....maybe my terminology is not correct, we call it a subform as it's opened from the main form.  We have a button on the main form (Rules) that opens the AddlTestParm form. The user enters some data on the Addl Test Parm form, clicks a save button on that form, data is saved to the addl_test_parm table.   The Addl Test Parm form then closes, and the data displays on the Rules form.  Under normal conditions, the user would enter other data on the main Rules form, then save, and that data is saved to the rules table.

I'm trying to handle the condition where the user enters some data on the AddlTestParm form, saves it, and then while back on the main Rules form, decides they don't want to continue and hits ESC to undo their work.

Because we've already saved some data to the addl_test_parm table at this point, we have to go back and delete it.

Hope that makes more sense...

Open in new window

0
 

Author Comment

by:L_Malchiodi
Comment Utility
Sorry, goofed the code post.  Not a good day here.  

Code is now attached....maybe my terminology is not correct, we call it a subform as it's opened from the main form.  We have a button on the main form (Rules) that opens the AddlTestParm form. The user enters some data on the Addl Test Parm form, clicks a save button on that form, data is saved to the addl_test_parm table.   The Addl Test Parm form then closes, and the data displays on the Rules form.  Under normal conditions, the user would enter other data on the main Rules form, then save, and that data is saved to the rules table.

I'm trying to handle the condition where the user enters some data on the AddlTestParm form, saves it, and then while back on the main Rules form, decides they don't want to continue and hits ESC to undo their work.

Because we've already saved some data to the addl_test_parm table at this point, we have to go back and delete it.

Hope that makes more sense...
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"decides they don't want to continue and hits ESC to undo their work."

Well, I would suggest then that ... do not allow opening of the AddlTestParm form *until* the data on the main form is saved.

mx
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:L_Malchiodi
Comment Utility
Sorry, that wouldn't work for us. The save of data on the AddlTestParm form generates an internal number that has to be saved with the main rule.

The AddlTestParm form is sort of a 'builder' form.  The Rules form consists of a number of fields that contain strings that are created by using the Addl Test Parm form.  Each of those strings is saved in the addl_test_parm table, and each of them is associated with an internal number (addl_test_parm_no).

When the user is done building all of the fields on the Rules form, what gets saved to the rules table is essentially a list of the addl_test_parm_no's for the various fields.   So, we have to save addl_test_parm first in order to get those numbers to send back to the Rules form.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
L_Malchiodi,

I'm going to look into the Transaction Processing I mentioned in my earlier post.  Will get back to you no this.

WRT the code you posted, where are the values for:mstrRuleTypeMne and RULE_TYPE_MNE_RFLTST
coming from?  My guess is that the reason you get the error is that RULE_TYPE_MNE_RFLTST <> 3  which is the value for the Access constant associated with the 'Escape' key (vbKeyCancel).  

The other possiblity is that you just need to set KeyCode = 0 prior to exiting the KeyDown event.  If you fail to do this, then Access will continue to attempt to execute the system procedue associated with the Escape key.

0
 

Author Comment

by:L_Malchiodi
Comment Utility
The value of RULE_TYPE_MNE_RFLTST is "RFLTST".

I just tried setting KeyCode=0 right before exiting the event, got the same 3059 error, unfortunately.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Where did you put the KeyCode = 0?  I would put it immediately following the line that reads:

Case vbKeyEscape

I would also move "Resume ExitRoutine" outside of the case statement in your error handler and would then put the KeyCode = 0 line.  I'm not entirely sure why you even have the case statement in the error handler since it isn't doing anything.

Have you tried putting a breakpoint in the code and stepping through it to see where it is actually breaking down?

BTW, I somehow missed the vbKeyEscape in your code segment, and my use of vbKeyCancel was incorrect.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"Have you tried putting a breakpoint in the code and stepping through it to see where it is actually breaking down?"

And/Or:

VBA editor >>Tools>>Options>>General Tab
Set Error Trapping to 'Break On All Errors'

This will cause a break on ANY error.  It can be a bit of a pain ... getting to the exact point you want ... depending if you have an On Error Resume Next statements, etc.

Be SURE to set this back after troubleshooting.

mx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Also, I was going to suggest cancelling ESC, however I don't recommend this 100% from a UI standpoint.

Typically, what I have is ...

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = vbKeyEscape Then
      KeyCode = 0
      Call MyCancelCodeHere  ' ****  typically a Cancel button would activate my cancel code
      Exit Sub
   End If
   ' other processing here

End Sub

I would move all of this Delete stuff into a special function by itself.  The if the user clicks a Cancel button (if you have one) OR hits ESC, the among other things ... you call the Delete code.

mx
0
 

Accepted Solution

by:
L_Malchiodi earned 0 total points
Comment Utility
Sorry to leave this hanging.  The resolution was to trap for errors 3059 and 2051, and continue processing.
0
 

Author Closing Comment

by:L_Malchiodi
Comment Utility
None of the solutions given resolved the problem; a colleague came up with this workaround.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now