Solved

Access 2000 - Custom form functionality

Posted on 2003-10-27
3
240 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

696 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