Solved

error 2046, DoCmd.RunCommand acCmdSaveRecord - the real story

Posted on 2014-09-04
12
868 Views
Last Modified: 2014-10-03
Ok, this one has been batted around for over 10 years.  I searched last night and found a myriad of suggestions,  plenty of work arounds, but no definitive explanation and solution to the problem.

In my (inherited, legacy) application that i have converted to access 2013, it exhibits the same unpredictable failure on DoCmd.RunCommand acCmdSaveRecord.  Unfortunately, the app has lots of form_delete, before_delete_confirm, and after_delete_confirm functions that part of the overall delete function, so converting to a recordset based delete is not ideal and is not part of the question.

Currently there are both XP and 2013 clients running this app.  Users are in it all day long, and it works most of the time, but occasionally, the delete fails with a 2046 error.  Sometimes the users can hit delete repeatedly and get it to work.

The button calls some validation code and then executes a DoCmd.RunCommand acCmdSaveRecord.

  If Response = vbNo Then
          Exit Sub
       End If
       Me![Sales Order Number].SetFocus
       DoCmd.RunCommand acCmdDeleteRecord
       SendKeys "{ESC}"
    End If

Open in new window


The various responses and suggestion attach the problem from several angles.

Data Validation:
   If Me.Dirty = True Then DoCmd.RunCommand acCmdSaveRecord

Validation:
 
DoCmd.SetWarnings False
      DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings True

And finally, one suggestion that seems like it is on the right track.  And that is that the command really is not there like the error says.   The code looks to try to make the command available,  but unfortunately does not work for me.

' turn the echo off
DoCmd.Echo False
' show the db window
DoCmd.SelectObject acTable, , True
 
      DoCmd.RunCommand acCmdSaveRecord
 
' hide the db window again
Docmd.SelectObject acTable, , True
Docmd.RunCommand acCmdWindowHide
'turn the echo back on
DoCmd.Echo True


So my question is why is the command unavailable, and how could one restore it?  My currently dev platform is 2013 with custom and standard ribbons.  The standard ones are open during testing.

Press
0
Comment
Question by:pressMac
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40305015
I'm a bit confused.

You are talking about deletion events and your first code segment uses acCmdDeleteRecord, but the rest of the post talks about saving a record.

Which is it?
0
 

Author Comment

by:pressMac
ID: 40305049
Sorry, that was the cut and paste, it is delete, but issue occurs in the same manner for save also.
0
 
LVL 75
ID: 40305141
For Saving a record, all you need is this:
(command button)

Private Sub btnSave_Click ()
    Me.Dirty =False 'This saves the record
End Sub


Do *not* ... use SetWarnings True/False for anything ... very dangerous .

mx
0
 
LVL 75
ID: 40305142
Also using Echo is very dangerous because if Echo True is not set back, your app will for all practical purposes be hung up ...
0
 

Author Comment

by:pressMac
ID: 40305748
Once again for clarification, it is the doCmd.runcommand ANYTHING that becomes unavailable at times.  As if the interface or collection is not there or available.  Its the environment, not the actual syntax.  The syntax works 90% of the time.  Not that it matters, but i really did intend the topic to be about Delete, not Save, but the issue is the same either way.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40305784
The runcommands are only "available" at times where those actions would be available if you were you interacting with the records in a table or a query.

If no record has the focus, then commands that would save or delete the record will not be "available".  If you are trying to delete a record in a query, where you would not be able to delete the record in query view, you might also get that error.

The runcommand methods also only work on the currently selected object, which is why I generally avoid them in favor of other VBA techniques.  Do delete a record, I generally use a delete query which lets me specify exactly which record I want to delete from which table.  When saving a record, I tend to use the

me.Dirty = False

technique, with error handling to ensure that I can properly process an error encountered during that step.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:PatHartman
ID: 40305974
I've been working with Access since the beginning of time and I recently encountered this bug using A2013 so I changed the code to use the Me.Dirty = False trick and the problem cleared up.  Then it came back as a different error so I went back to the standard save method and the problem went away again.  I'm stumped.  I added code to trap both errors and try the alternate method.  I also added a counter because I didn't want to end up in an infinite loop.

Before I force save a record, I always check to see if it is dirty so in theory, the command to save should be available if the record is dirty.   I would blame the problem on A2013 (which is seriously unstable on my main development PC) but many folks have had the problem with earlier versions so it is a bug that is ongoing.  I'm going to guess that it is not related to the situation at hand but to something that happened previously such as some error that was improperly handled causing Access to loose its place.

In my very first assignment creating CICS transactions many moons ago, my group was experiencing random failures that we could not track down for weeks.  Transactions that were working fine suddenly failed.  Eventually we discovered the problem one day when only two people were active on the system and it came to me the trainee - in one of my Transactions I had returned control to CICS but failed to provide a return code so the value was null.  The very next transaction that started would fail so the failures were completely random.  My program ended successfully.  It was the next guy that got clobbered.  So it depended on who was testing and what they were testing.  I think this is that kind of bug.
0
 
LVL 75
ID: 40306294
"with error handling to ensure that I can properly process an error encountered during that step."
Yes ...
I was just giving the simplest version.  If say ... you have validation code in the Form Before Update event, and you end of cancelling that event (based on validation fail), then the error "You cancelled the previous operation" will occur in the command button code below - on the Me.Dirty = False like. So ... you need to have error trapping to cover this, and any other potential errors that might occur during Access's attempt to save the record.

Private Sub btnSave_Click ()
    On Error Goto btnSave_Click_Error

    Me.Dirty =False 'This saves the record
   
btnSave_Click_Exit:
    ' possible code
    Exit Sub

btnSave_Click_Error:
    ' error trapping code
    ' ....
     Resume btnSave_Click_Exit

End Sub
0
 

Author Comment

by:pressMac
ID: 40307131
Hello,

i think fyed (Dale Fye) is onto something of the issue.  We are talking about delete here.  I am not sure how the record could not have the focus as the calling button is on the form where the delete is taking place.  There are no calls to other functions to move the focus away.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40307418
the question is, what other code is involved, either in the click event of that button or some other event, that may have changed the focus?
0
 

Author Closing Comment

by:pressMac
ID: 40358390
The best that i can see is that the code somehow, only some of the time takes the focus away from row that needs to be deleted.  So that appears to be why docmds dont always work.  So i'll in DAO to delete the records.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40359730
Keep in mind that if you use DAO/ADO to delete a record that is visible on the form, the fields will show #deleted# unless you requery the form after the delete.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

22 Experts available now in Live!

Get 1:1 Help Now