jmcclosk
asked on
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!
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!
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
OM Gang
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
See 'MX's second post. That's the command you need.
OM Gang
OM Gang
ASKER
Thank you for updating my self-learned skills to be more efficient!
You're only a bonehead if you don't continue to learn and improve.
OM Gang
OM Gang
Delete is not a Method of DoCmd.
I think you need
table_RST.Delete
mx