Solved

Access 2000 - Custom form functionality

Posted on 2003-10-27
3
239 Views
Last Modified: 2008-02-26
I want to develop a bunch of forms based on individual tables such that each table X has parallel table X_audit with additional audit information (e.g.: auditTime, auditUser)
Each form will then call a public function when auditing is necessary.
E.g.: after_update  form trigger will call a function which will copy the current X row to X_audit and populate the 2 audit columns appropriately. (using insert into…select from…)
Triggers for "after insert", and "On delete" will existsimilarly.

Question: is their some “object-based” way to get this default behaviour into each form i.e.: without simply pasting the trigger code into each form? (and the resultant bug if the developer forgets to do this thoroughly!)
Might it use “WithEvent” ?

E.g.: it would be nice to define formX as a type of form which includes the default behaviours and then design forms of type formX with the form designer.

Also, if anyone can point me to existing packages that perform such auditing, that would be great.
0
Comment
Question by:starrynighter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9632125
There is nothing to stop you designing a template form which includes all the processing you are describing, and asking your developers to use that template as the starting point for all forms. But the problem is that the developer might just save the new design over the template. And you can't enforce the process anyway.
And what about the million and one ways that the records could be added/modified/deleted without user action and without a form ever being opened.  

Pete



0
 
LVL 39

Accepted Solution

by:
stevbe earned 150 total points
ID: 9641435
You could write a class module that gets instantiated in each form's open event and then using WithEvents capture the insert, delete, update events. When those events get fired in your class you can parse the bound recordsource to determine the base table and make queries of the same name with "_audit" which really are just pointer back to the base tables' parallel audit table. This approach limits the amount of code that needs to written in each form to the instatiation / termination of the class. Certainly Peter brings a good point that in order for this to suceed you need to make sure that all development follows these guidlines and that there are no other ways for the tables to be affected.

Steve
0
 

Author Comment

by:starrynighter
ID: 9645011
Good guide-thanks.
Thanks for your points Pete- of course triggers would solve this nicely; my initial concern is to capture form user-keyed changes.

I note that there seems to be little documentation (via Google) on "withEvents"- I presume it is fully functional in Access 2000.
I stumbled on it reading the book "Beginning Access 2002 VBA " and note it is not mentioned in the authors' previous book "Beginning Access 2000 VBA "

It looks like the way to go for many things including devloping unbound forms.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question