Pull row count from action query

Question

I have a form button that runs a delete query and an append query. I would like to be able to show the user how many rows will be deleted and how many will be appended and store them on the form. The queries already exist outside the VBA for the button. So I am just calling the queries with DoCmd.OpenQuery. I turned off the warnings so the popup doesn't happen that says "Do you really want to delete this many rows?" But I would still like to find out how many rows will be deleted or appended and store that number as a variable somewhere so the user can see it. How can I make that happen?

I tried a DCount function but because these are action queries it won't work. I would also like to avoid putting the SQL for the queries in my VBA. I like that they are separate from the VBA and can be edited on their own.

Fionnuala Fionnuala · Accepted Answer · 2014-09-03T15:48:54

Assuming ACE / Jet, you can use an instance of currentdb to run the queries and then look at the RecordsAffected property.

Dim db As Database
Set db = CurrentDb

db.Execute "Delete From Table1 Where Id = 1"
Debug.Print db.RecordsAffected

Or with a saved query

Dim db As Database
Set db = CurrentDb

db.Execute "ADelete"
Debug.Print db.RecordsAffected

Pull row count from action query

1 Answers