[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2000 - Custom form functionality

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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