Stuart Sanders
stuart at pacific.net.hk
Sun Feb 29 04:29:18 CST 2004
Having worked only recently with your classes, I don't see why it couldn't be done. In fact, if you use a separate table to name form and control/field name it should be possible to be dynamic such that the user can specify which fields they want logged. Much as you were doing with the security controls. On form load, it checks to see whether the form needs to be logged, and if so checks which fields. Use the before update event of the form to record before and after values but make sure it is after all validation has passed. Stuart -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby Sent: Sunday, 29 February 2004 12:29 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Watching data Jim, >With Access, everything needs to be done at form level and it will never be fool proof as anything done directly in a table will by pass your efforts. Understood. Fortunately "everything done in forms" describes the database. This thing is an insurance claim processing system. Users take calls, make calls, talk to doctors, investigators, claimants and so forth, and document everything using forms in the database. My client then advises the insurance company whether or not to pay the claim. As such I have "complete control". Users don't go in to tables to edit things. I use a handful of processes that automate receiving data from the Insurance company (payment records, new claim numbers etc.) where I take spreadsheets attached to daily emails from the insurance company and use them to update the database but nothing that I need to track here (I do track it in other ways of course). I have a very small number of forms that all personnel use to edit the database. As such I can "brute force it" if I wish. My first inclination was to do a class directly in the FE that I instantiated in each form that needs monitoring. The class is told the controls to watch and logs any changes to those controls. I may still do it that way, but as I have indicated I would prefer to embed this functionality in my framework so that I can offer this to any client using the framework, turn it on and off using SysVars at the FE or even the form level etc. We'll see. In order to do that I need to figure out the "drill down" thing - I believe that Drew was the one that assured me that it is possible. I just have to see if I can find the hints of how. It had to do with opening the query and inspecting the properties of the fields. If that is possible I may have a killer feature since my framework already supports form / control classes. It would be reasonably easy to add this functionality into those classes and go. I am thinking of adding a new DataLogging class to handle this stuff though. The reason is that it would be useful to be able to select groups of controls to monitor - as in this example "just the address portion". By designing a class, the form could load the DataLogger class and pass it controls (actually the controls' class) such that an instance of the class then performs the monitoring of a group of controls. In the event that several different groups of controls need monitoring, the form class just loads more instances of the class passing the specific controls for each group being monitored. If all I ever anticipated was a single group I could just do it in a single collection in the form class itself, but by building a class to do this, I can have more than one group of controls being monitored if necessary. The logger class knows how to poll its group of controls for changes and write the old/new/tbl/field info to the log table. I think that would be sweet, and would nicely encapsulate the process. John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman Sent: Saturday, February 28, 2004 3:27 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Watching data John, <<Make sense?>> Totally. Unfortunately, it's pretty rough to do in Access because there are no engine level triggers in JET. With Access, everything needs to be done at form level and it will never be fool proof as anything done directly in a table will by pass your efforts. In products like SQL Server, this type of feature is generally built-in and it falls outside your app. In others, like VFP, you have to do the work, but it's quite easy to do. For example, right now in VFP, I can very easily define a trigger at engine level for adds, deletes, or updates. On an update, I can compare .Oldvalue and current value on every field in a record and write a log as a separate table/file. I could also modify my base classes at the intermediate class level (I have 4: VFP, Framework, Intermediate Class, and App specific classes). This would be the approach your talking about. Another alternative is that there is a commercial product I can buy that hooks into the framework I use (but it uses the engine level triggers). BTW lack of control at the engine level is one of the reasons I've moved away from Access. But if the JET team had added them, it would have stepped on too many SQL Server toes<g>. Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W. Colby Sent: Friday, February 27, 2004 5:03 PM To: AccessD Subject: [AccessD] Watching data I need a system for watching specific data fields in specific tables for changes. For example, if the Policy holder address changes, the claimant address changes, the Payment location (address) changes etc. If ANY of these change then I need to gather the information and at the end of the day email a report to the client (the insurance company) spelling out the changes, what object the fields belonged to (Claimant, Policy Holder etc.). Make sense? Of course I could launch into building code in every form I can find where these objects are used and this info can be saved. However this seems like a "framework" kind of task. I envision a class (let's call it dclsMonitorCtlChg for now) in the framework that the form class loads if a form class method (perhaps MonitorCtlDataChg(ParamArray varCtls as variant) ) is called with controls specified. The form class already has a collection of the classes for each control's class. The form class MonitorCtlDataChg() could grab a pointer to each of the controls that this method says needs to be monitored and pass them to the dclsMonitorCtlChg which would place them in a collection. Then a form event or events (Before update, After update) could call a method of dclsMonitorCtlChg telling it to "look for changes in your control set". The class could raise an event or simply return a value to the form caller if any change was detected. Of course it would then be useful to know what controls (fields) were changed, the old value and the new value. This would allow the application to generate a report: Object Monitored (Claimant) Field: Addr1: OldValue: NewValue Zip: OldValue: NewValue IOW the claimant moved to a new location, but in the same city, just changed the address1 and the zip. So.... is anyone doing anything like this? If so any words of advice, things to look out for etc? John W. Colby www.ColbyConsulting.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com