Delete Query Vs Tagging in MS Access

Introduction

When we develop our applications in Microsoft Access we always take into account the intended objectives of the app. Mainly we want to ensure that the business integrity is not violated at all. Now taking into account the business ethics and requirements of financial auditors, we may consider dropping the idea of using the Ms Access Delete action queries to delete incorrect transactions, so we should consider using tagging methods instead. 

The danger with this kind of queries is that it does not give an audit trail for the financial auditors to follow. For example, once a document is deleted from the tables as long as a printed copy is not reserved, then that is it, it can never be recovered, then we have to face the auditors about the missing documents since our sequence will have some gaps.

Discussion

Instead of using this delete query, the best method is to avoid it altogether and simply use the tagging method.

We use the tagging method by simply creating a control in those tables which we want to filter the affected transactions, for example in a sales invoice table, we can add a control called rejected invoices. We can update our rejected invoices control by using an update query either through the built-in update Ms Access queries or VBA code with some criteria. 

Once the update query is done and working, then to filter the rejected invoices we simply reference that control in our query. For example, if our update query is pasting a “2” in a table control called tblsalesinvoice.reject, then at query level we can quarantine those invoices by adding this <>”2” in the criteria row of a query under that control.

Below is a simple update query:

UPDATE tblsalesinvoice

SET tblsalesinvoice.reject = 2

WHERE tblsalesinvoice.ID = '1';

As long as the customer report is bonded to the filtered query like the one described above, I can guarantee the readers that the rejected invoices will never ever be part of the report, but can be printed for audit purposes.

If you want you can use a form with a combo to help you select the required criteria. But you have to run this query from a form, however, to do that you have to create a click button and reference the query update name. It’s also possible to set off and on the query warning messages or replace them with your own if you so wish.

Why is the tagging method better than delete function?

Below are the reasons:

1. A proper audit trail is kept for the financial auditors and other managers
2. The integrity of the system will also be appreciated by all
3. It reduces the chances of fraud
4. Easy to generate exceptional reports for those rejected transactions for scrutiny