Tony Septav
iggy at nanaimo.ark.com
Mon Mar 1 08:16:40 CST 2004
Hey Jim
There are a lot of little gems buried in Access, it is just being able to find
them when you need them.
That is what makes this list so valuable.
Jim Dettman wrote:
> Tony,
>
> << STable = Rst(Me(MyCtl).ControlSource).SourceTable
> SField = Rst(Me(MyCtl).ControlSource).SourceField
> >>
>
> Been a long time since I found something in Access that I didn't know
> existed! I was shocked to realize this has been in DAO since version 2.0!
>
> That really makes what John is trying to do super easy.
>
> Good job!
>
> 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 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
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com