You are here:

Using MS Access/Number of records processed dialog box

Advertisement


Question
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?

Thanks.

Answer
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."
Else
    DoCmd.OpenForm "morgue"
End If

If I have guessed wrong on what your process is, then please explain further.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.