?
Solved

Access 2000 - Custom form functionality

Posted on 2003-10-27
3
Medium Priority
?
242 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 600 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

765 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