John W. Colby
jwcolby at colbyconsulting.com
Sun Feb 29 18:50:00 CST 2004
Tony,
>
Set Db = CurrentDb()
Set Rst = Db.OpenRecordset(Me.RecordSource, dbOpenDynaset)
STable = Rst(Me(MyCtl).ControlSource).SourceTable
SField = Rst(Me(MyCtl).ControlSource).SourceField
That is awesome and exactly what I need to do.
I have to wonder though whether the form's RecordsetClone couldn't be
assigned to the rst and do exactly the same thing. I just tested and:
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
Debug.Print rst(txtCat.ControlSource).SourceTable
Debug.Print rst(txtCat.ControlSource).SourceField
(actual controls on a form) produces:
tblCatWrk
CW_Cat
My form class has a function that iterates the form's control collection
instantiating a control class for each control found. In the form class (in
this FindControls function) I can do this process, then as each control is
found, have the form class look up the tablename / fieldname and pass that
in to the control class as it instantiates the control's class. Thus each
control "knows" what table / field it is bound to and can report that back
later.
This is starting to look easy! I'll keep you informed.
John W. Colby
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Tony Septav
Sent: Sunday, February 29, 2004 3:22 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Watching data
Hey John
Tried a quick test with a bound form to a query.
Query contained 2 tables. My test field in the query had an alias
eg. Tblname Account_Number, Qryname AcctNum
For the sake of the test the text box on the form was named with an alias
ActNo, control source from query Acctnum
In Before Update
Dim Db As Database, Rst As Recordset
Dim Rst2 As Recordset
Dim MyCtl As String
Dim STable As String, SField As String
Dim Ctl As Control
Set Ctl = Me.ActiveControl
MyCtl = Ctl.Name
'check to see if value has changed (variable HasChng assigned OnEnter)
'if changed
Set Db = CurrentDb()
Set Rst = Db.OpenRecordset(Me.RecordSource, dbOpenDynaset)
STable = Rst(Me(MyCtl).ControlSource).SourceTable
SField = Rst(Me(MyCtl).ControlSource).SourceField
'Assumes all tables have a Logger field
' Find the record and tag it
Set Rst2 = Db.OpenRecordset("select Logger from " & STable & " where "
&
SField & " = " & HasChng & ";", dbOpenDynaset)
Rst2.Edit
Rst2!Logger = "Yes"
Rst2.Update
rst2.close
set Rst2=nothing
Rst.Close
set Rst=nothing
'endif
As I say this was a quick test but "Yes" did appear in the appropriate table
and
record.
"John W. Colby" wrote:
> >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
>
> --
> _______________________________________________
> 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