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

VBA Run-time error 2046 'DeleteRecord'

I just converted a 3-yr old Access 2003 database to 2010 for testing.  I have a piece of VBA code I use often that checks temporary tables to make sure they were properly emptied after use and, if not, deletes all the records so they are ready for the next use.

In the 2003 version, the code looked like this and worked without any problems:

Dim db As Database
Dim table_RST As Recordset
Set db = CurrentDb
Set table_RST = db.OpenRecordset ("tbl_TempTable", dbOpenDynaset)

If (table_RST.RecordCount > 0) Then
  table_RST.MoveFirst
  Do While (Not table_RST.EOF)
    DoCmd.Delete
    table_RST.MoveNext
  Loop
End If

In Access 2010, I now get a error that the "DoCmd.Delete" command is not vaild.  So, I did a little looking around and tried replacing that line with the following two lines:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

When I try to run this, I get a Run-time error 2046 saying that the "command or action 'DeleteRecord' isn't available now."

All my forms are unbound and I use VB Scripting almost exclusively to move data from tables to forms, so I don't think it is a record lock problem.  But, I don't know how to correct it.  Any suggestions?  Thanks!
0
jmcclosk
Asked:
jmcclosk
  • 3
  • 2
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
That code does not even compile in A2003, so I do see how it ever worked.

Delete is not a Method of DoCmd.

I think you need

table_RST.Delete

mx
0
 
omgangCommented:
I agree with 'MX but why are you enumerating the recordset if you simply want to delete all the records.  For that matter, why even check if the table contains records first, simply execute a delete query (DELETE * FROM tbl_TempTable).
OM Gang
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Yes ... if your goal is to delete all records, all you need is one line of code:


    CurrentDb.Execute "DELETE tbl_TempTable.* FROM tbl_TempTable", dbFailOnError


mx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jmccloskAuthor Commented:
What a bonehead I am!  I copied some code to another form and didn't select everything, so I typed the rest from memory.  Obviously, my memory is not so good.  DatabaseMX got me back on track.  

However, omgang raises a good point, which may be a new question to write up.  I taught myself VBA.  I have no doubt "roughed out" some things that can be done better.  I tried entering that command you gave me, although it looks more like SQL than VBA and I got a compile error.  How could i wipe out all records in the table with a single command instead of going one by one?
0
 
omgangCommented:
See 'MX's second post.  That's the command you need.
OM Gang
0
 
jmccloskAuthor Commented:
Thank you for updating my self-learned skills to be more efficient!
0
 
omgangCommented:
You're only a bonehead if you don't continue to learn and improve.
OM Gang
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now