[AccessD] Watching data

Tony,

The following is what I ended up with:

'
'This function iterates the mfrm's control collection looking up the
control's table and field names
'
'Since I sometimes open the form unbound, I need to do all this in a
separate function after binding the form
'to a table.
'
Public Function CtlDataSrc()
On Error GoTo Err_CtlDataSrc
Dim rst As DAO.Recordset
Dim ctl As Control
Dim strSrcTbl As String
Dim strSrcFld As String

    'Get a copy of the form's Recordset
    Set rst = mfrm.RecordsetClone
    'Iterate the controls collection for the form
    For Each ctl In mfrm.Controls
        Debug.Print ctl.Name
        'attempt tp get the source table name for the field the control is
bound to.
        err.Clear
        On Error Resume Next
        strSrcTbl = rst(ctl.ControlSource).SourceTable
        If err = 0 Then 'If there is no error (the field is bound to a field
and the source table is found)
            mobjChildren(ctl.Name).BoundTbl = strSrcTbl 'look up the class
in mobjChildren collection and pass the class the table name
        End If
        'Do the same stuff for the source field name
        strSrcFld = rst(ctl.ControlSource).SourceField
        If err = 0 Then
            mobjChildren(ctl.Name).BoundFld = strSrcFld
        End If
    Next ctl

Exit_CtlDataSrc:
On Error Resume Next
    If Not (rst Is Nothing) Then rst.Close: Set rst = Nothing
Exit Function
Err_CtlDataSrc:
        MsgBox err.Description, , "Error in Function dclsFrm.CtlDataSrc"
        Resume Exit_CtlDataSrc
    Resume 0    '.FOR TROUBLESHOOTING
End Function

This assumes a pair of private string variables in each data aware class:

'
'Used for change logging
'
Private mstrBoundTbl As String              'The Table that this control is
bound to
Private mstrBoundFld As String              'The field in the table that
this control is bound to

and corresponding property get/let statements to access the variables:

'
'Properties for the bound field / table (properties of the control class)
'
Public Property Get BoundTbl() As String
    BoundTbl = mstrBoundTbl
End Property
Public Property Let BoundTbl(strTblName As String)
    mstrBoundTbl = strTblName
End Property
Public Property Get BoundFld() As String
    BoundFld = mstrBoundFld
End Property
Public Property Let BoundFld(strFldName As String)
    mstrBoundFld = strFldName
End Property

So far it all appears to be working flawlessly.  I added the variables and
properties to all of my control classes that are for bound controls and I'm
off and running.

That takes care of each control knowing it's table / field.

Next I have to do the code to tell each such control to store the old value
and return the various old / new  values.  I do think I'll write a
DataLogger class to handle the issue of storing sets of controls to log data
for as well as the logic of when to save the old values, when to return the
various pieces for logging, handling the log table etc.

I think this is going to work nicely though.

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.