Using MS Access/Number of records processed dialog box
I have an Access database that contains name and addresses of employees (agents). I have a field that can either be Active or Inactive. When an agent leaves, I change the status to inactive so that they no longer appear on reports and such since all the reports look for Active agents only.
After a while though I like to delete the inactive agents from the main Addresses form. I created a form and query that first copies the inactive agents info to a duplicate form (which I call a morgue form), then deletes the info from the main Addresses form and any related forms. This way the info is never really removed from the database. I am trying to display a dialog box after the query deletes the records that displpays something like: 3 records were processed.
Or, if there are no Inactive records: There are no records to process.
I tried using SysCmd, but either i'm doing it wrong, or the process happens so fast I can't see it. And I can't seem to find an answer anywhere on line.
Can you help?
First you don't delete records from a form. A form is just a view of records in a table.
If you prefer to keep deleted records in an Archive table, that's fine. Personally I see no reason for it unless you have tens of thousands in records.
So I'm guessing that your morgue form has a recordsource of a query that is filtered for Inactive agents. And that you have a button on that form that copies those records into your Archive table and deletes them from the main table.
If so, then add this to the code behind the button:
Dim intCount As Integer
intCount = DCount("*","qryInactives")
Next, you run the query to Append the records to the Archive table and delete them from the main table. Finally you have the following line:
MsgBox "You have processed " & intCount & " records."
By the way, if you want to flag when there are no inactive records, you do that when you launch the Morgue form:
If DCount("*","qryInactives") = 0 Then
MsgBox "No currently inactive Agents."
If I have guessed wrong on what your process is, then please explain further.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA