John W. Colby
jwcolby at colbyconsulting.com
Sun Feb 29 14:18:18 CST 2004
>I just thought of another one; what if you assign the forms recordsource to a recordset variable in code so you can do transactions? I've never looked at runtime to see what that looks like to a form object. AFAIK this isn't possible in A2K. 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: Sunday, February 29, 2004 2:37 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Watching data John, <<No, by "drilling down" I mean discovering what the SOURCE of the data is by (I think) opening the query that the form is bound to, looking up the field that the control is bound to, and looking at the properties of that field in the query to discover what table / field the data comes from.>> Thought I had missed the boat on that one<g>. <<Thus when it's time to log data and we want "table / field name / oldval / new val" how do we discover the Tablename / Fieldname portion?>> I used the brute force approach in the past; I coded the table and field name that I wanted logged in the tag property for each control. Without doing that, you'd need to grab the form's recordsource and figure out what it is. It could be a table name, query def, or SQL statement. The first would be easy, as from that point you'd only need the controls record source. The second you can figure out easy enough, but parsing it would be a problem, especially if it in itself is based on sub queries. That applies to querydef's as well. I'm not sure what Drew was thinking of, but I don't see any easy answers there. I just thought of another one; what if you assign the forms recordsource to a recordset variable in code so you can do transactions? I've never looked at runtime to see what that looks like to a form object. 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: Sunday, February 29, 2004 12:38 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Watching data >but I'm assuming what you mean by "drilling down" is looping through all the controls I do control collection iteration collection all the time, starting with my form class which has a control scanner (as I call it) which iterates the controls collection loading classes for each control found. No, by "drilling down" I mean discovering what the SOURCE of the data is by (I think) opening the query that the form is bound to, looking up the field that the control is bound to, and looking at the properties of that field in the query to discover what table / field the data comes from. When a form / field is bound to data, what the data is can be non-trivial to discover. For example the form could be bound to a query. The developer decides that rather than leaving the field names of that query alone, he is going to rename CL_Addr1 to Address1 using field name aliases. Further the query could be two or more tables linked together in the query (and still editable) so that fields from the claimant and a specific claim are all bound to controls on a form. Now when the data logger tries to log the data, what table is the data coming from, the claimant or the claim? Further is Address1 the actual field name in the table or is it an alias? Thus when it's time to log data and we want "table / field name / oldval / new val" how do we discover the Tablename / Fieldname portion? 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: Sunday, February 29, 2004 11:15 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Watching data John, <<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.>> I missed earlier parts of this discussion, but I'm assuming what you mean by "drilling down" is looping through all the controls, then yes it certainly can be done. Any of the code floating out there to resize forms contains the logic required. If you don't have access to something like that, it's a pretty straight forward matter. Starting at the form level, you'd use the controls collection to iterate through all the controls for an object. Just be aware that you'll need to handle container controls (i.e. page frames and subforms), so it will be a series of nested loops. You might want to try and make it a recursive procedure. Performance wise, you also might want to restrict which controls are looked at. For example, if you don't place controls in anything other then the detail section of forms, then no need to check the header and footer. Once you locate a control, you use the TypeOf function to figure out what it is and if you need to deal with it. I would not bother checking the .enabled or .locked properties, but simply check .oldvalue vs .value, as it should be faster that way. This would all be done in the BeforeUpdate event after your sure your not going to cancel for any reason. As far as the change log, I've always used the approach of one record per field change: tblLogID - Autonumber -PK LogDateTime - D/T LogType - String - "A"dd, "D"elete, "U"pdate TableName - String RecordPK - String FieldName - String TypeOf - Numeric - Indicates where "NewValue" is stored NewValue1 - String NewValue2 - Integer NewValue3 - Long NewValue4 - Single etc.... I don't bother to hold the .oldvalue because you'd be duplicating it in the table. You can also see that I don't bother to try and convert the .oldvalue into a common type (i.e. a string), but rather store the actual value. If I had access to the raw binary data, then I'd do so, but without that, this seemed to be the cleanest way. I've used two different types of logic in working with the log file: Roll forward - used to recreate a database at a specific point in time. 1. Check point (backup) 2. Apply log changes in a "roll forward" to a specific date and time. Auditing: 1. Log file records are maintained as long as possible. I've primarily used this in HR type apps or others where it was required to recreate the data at a specific point in time. I haven't had to use logging/auditing in quite some time, but it was workable. I went with strings for the table name and field names, but I suppose you could come up with some type of internal tracking system to use a numeric ID of some type, but that is probably overkill. The only gotcha there is if you rename a table or field. Generally, that doesn't happen too often for me. HTH, Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -- _______________________________________________ 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