• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Access 2000 - Custom form functionality

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
starrynighter
Asked:
starrynighter
1 Solution
 
peter57rCommented:
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
 
stevbeCommented:
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
 
starrynighterAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now