[AccessD] Watching data

Hey John

You will have to be careful if the developer uses field names like
Account Number rather than Account_Number

You will have to insert [] brackets around the sourcefield

 Set Rst2 = Db.OpenRecordset("select Logger from " & STable & "
where [" & SField & "] = " & HasChng & ";", dbOpenDynaset)

"John W. Colby" wrote:

> 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.
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com