Delete a record

[Marc]

I've opened a Query with the following commands:

*******************************************
Set Wsp = DBEngine.Workspaces(0)

Set Dbs = CurrentDb

Set rstList = Dbs.OpenRecordset("qryList", dbOpenForwardOnly)
' I've tried also with dbOpenDynaSet.

Do While Not rstList.EOF
If SomeCondition Then
DoCmd.RunCommand acCmdDeleteRecord
Endif
rstList.MoveNext
Loop
*******************************************

This doesn't work. I get a message: "The command or action DeleteRecord is
not available"



How can I delete the current record?


Marc

 

[Douglas J. Steele]

To delete the current record from a recordset, use rstList.Delete

Note, though, that it's almost always faster to use a SQL statement than to
loop through a recordset. Since I don't know what SomeCondition is, I can't
guarantee that it's possible in this case, but something like:

strSQL = "DELETE FROM MyTable WHERE SomeCondition"
Dbs.Execute strSQL, dbFailOnError

 

[Marshall Barton]

I've opened a Query with the following commands:

*******************************************
Set Wsp = DBEngine.Workspaces(0)

Set Dbs = CurrentDb

Set rstList = Dbs.OpenRecordset("qryList", dbOpenForwardOnly)
' I've tried also with dbOpenDynaSet.

Do While Not rstList.EOF
If SomeCondition Then
DoCmd.RunCommand acCmdDeleteRecord
Endif
rstList.MoveNext
Loop
*******************************************

This doesn't work. I get a message: "The command or action DeleteRecord is
not available"

Most DoCmd methods operate on the object that was previously
selected with the mouse. Since you're trying to operate on
a recordset, which is not even visible on the screen, you
have no idea what would be deleted (if you hadn't received
that error message). A good general rule is to avoid DoCmd
wheneveryou can find another way to do the job.

In this case, the proper way is to use the recordset .Delete
method.

OTOH, cycling through all the records in code is rarely the
most efficient approach. If you can set things up so that
your SomeCondition expression can be evaluated in the SQL
environment, then you should execute a query to perform the
job all in one fell swoop.

Set Dbs = CurrentDb()
Dbs.Execute "DELETE * FROM qryList WHERE SomeCondition"