jwcolby
jwcolby at colbyconsulting.com
Sun Mar 8 07:01:34 CDT 2009
William,
You couldn't get much simpler.
Now, as an exercise to you:
1) Place the WriteAuditUpdate and WriteAuditAddDelete in frmClass.
2) Hook BeforeUpdate of the form and build an event sink
3) Build a new function ScanCtlsForAuditTrail() that iterates the controls collection looking for
controls where OldValue <> NewValue
4) Call ScanCtlsForAuditTrail from BeforeUpdate
5) Test for changed data being stored.
I will discuss optimizations in the next email. They basically involve moving the Dim DB and Dim Rs
up into ScanCtlsForAuditTrail, then passing the RS into WriteAuditUpdate.
This speeds things up by keeping the recordset open the whole time.
I will write this up into a lecture but you can do it William, I have faith in you! ;)
John W. Colby
www.ColbyConsulting.com
William Hindman wrote:
> ...you're a mite touchy these days :)
>
> tblAudit
> ID
> MachineName
> User
> TableName
> RecordPrimaryKey
> FieldName
> OriginalValue
> NewValue
> dateTimeStamp
>
> basAudit
> Option Compare Database
> Option Explicit
>
> Sub WriteAuditUpdate(txtTableName, lngRecordNum, txtFieldName, OrgValue,
> CurValue)
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Set db = CurrentDb
> Set rs = db.OpenRecordset("AuditTable")
>
> rs.AddNew
> rs!TableName = txtTableName
> rs!RecordPrimaryKey = lngRecordNum
> rs!FieldName = txtFieldName
> rs!MachineName = ComputerName
> rs!User = UserID
> rs!OriginalValue = OrgValue
> rs!NewValue = CurValue
> rs!DateTimeStamp = Now()
> rs.Update
> rs.Close
> db.Close
> End Sub
>
> Sub WriteAuditAddDelete(txtTableName, lngRecordNum, txtFieldName, OrgValue,
> CurValue)
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Set db = CurrentDb
> Set rs = db.OpenRecordset("AuditTable")
>
> rs.AddNew
> rs!TableName = txtTableName
> rs!RecordPrimaryKey = lngRecordNum
> rs!MachineName = ComputerName
> rs!User = UserID
> rs!NewValue = CurValue
> rs!DateTimeStamp = Now()
> rs.Update
> rs.Close
> db.Close
> End Sub
>
> Typical Form cb
>
> Option Compare Database
> Option Explicit
>
> Const txtTableName = "tblCompany"
>
> Private Sub cboCountry_BeforeUpdate(Cancel As Integer)
> On Error GoTo Err_cboCountry_BeforeUpdate
>
> WriteAuditUpdate txtTableName, Me.CompanyID, "cboCountry",
> Me.cboCountry.OldValue, Me.cboCountry.Value
>
> Exit_cboCountry_BeforeUpdate:
> Exit Sub
>
> Err_cboCountry_BeforeUpdate:
> MsgBox "Error # " & Str(Err.Number) & " was generated by " & Err.Source
> & Chr(13) & Err.Description
> Resume Exit_cboCountry_BeforeUpdate
>
> End Sub
>
> William
>
> --------------------------------------------------
> From: "jwcolby" <jwcolby at colbyconsulting.com>
> Sent: Saturday, March 07, 2009 10:21 PM
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] ...building, managing, and using a page favorites
> list
>
>> I provided code for automatically iterating the controls collection. I
>> provided classes for two of
>> the data aware controls.
>>
>> What is your code for the audit trail, writing the old values to the audit
>> table? I haven't done
>> that but if you have, it should be trivial to add it to the form class.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> William Hindman wrote:
>>> ...I don't NEED classes for anything ...anything except pain avoidance in
>>> doing the same thing over and over
>>> ...yes I can iterate the forms and have ...but then I add a new control
>>> and
>>> have to remember to do it again
>>> ...classes SHOULD put that into the auto category ...but like you, I'm
>>> waiting on the conductor :)
>>>
>>> William
>>>
>>> --------------------------------------------------
>>> From: "Max Wanadoo" <max.wanadoo at gmail.com>
>>> Sent: Saturday, March 07, 2009 6:33 PM
>>> To: "'Access Developers discussion and problem solving'"
>>> <accessd at databaseadvisors.com>
>>> Subject: Re: [AccessD] ...building, managing, and using a page favorites
>>> list
>>>
>>>> You don't need classes for that. Just a global Boolean and one line of
>>>> code
>>>> on entry to function/sub and one when you leave function/sub.
>>>>
>>>> On Entry
>>>> If bgLogMe then call pfLogEntry(me,True)
>>>>
>>>> On exit
>>>> If bgLogMe then call pfLogEntry(me,False)
>>>>
>>>> It would be a simple matter to write some code to run though all modules
>>>> and
>>>> insert that line of code.
>>>>
>>>> As I said to JWC, I do (sometimes) use Classes but the real reason I
>>>> don't
>>>> is that I have never came across any situation where I NEED them.
>>>>
>>>> But, Like you, I am looking forward to the Missive (Don't know if I will
>>>> live that long though - he really is an arse-dragger).
>>>>
>>>> Max
>>>>
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: accessd-bounces at databaseadvisors.com
>>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
>>>> Hindman
>>>> Sent: 07 March 2009 23:25
>>>> To: Access Developers discussion and problem solving
>>>> Subject: Re: [AccessD] ...building, managing, and using a page favorites
>>>> list
>>>>
>>>> ...I do audit trail logging on key forms ...but the code I'm using
>>>> requires
>>>> that I mod every control in each form ...pita it is ...one of the things
>>>> I'm
>>>>
>>>> hoping to get from jc's class system is a way to automatically include
>>>> that
>>>> feature with the code in a single place.
>>>> William
>>>>
>>>> --------------------------------------------------
>>>> From: "Max Wanadoo" <max.wanadoo at gmail.com>
>>>> Sent: Saturday, March 07, 2009 1:51 PM
>>>> To: "'Access Developers discussion and problem solving'"
>>>> <accessd at databaseadvisors.com>
>>>> Subject: Re: [AccessD] ...building, managing, and using a page favorites
>>>> list
>>>>
>>>>> William,
>>>>>
>>>>> I do *something* similar but I put mine on hot keys, Ctl-M for Max's
>>>>> Menu,
>>>>> Ctl-xyz for others. The code within the loading checks the person who
>>>>> has
>>>>> logged in. Generically I can allow the OnLoad code if the currentuser
>>>>> has
>>>>> an access level <= that required for this operation.
>>>>>
>>>>> Not in Treeview though, just normal forms.
>>>>>
>>>>> Also, all my Forms, Reports can be *logged* so that I can go back and
>>>>> see
>>>>> who done-what and when they done it.
>>>>>
>>>>>
>>>>> HTH
>>>>>
>>>>> Max
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: accessd-bounces at databaseadvisors.com
>>>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
>>>>> Hindman
>>>>> Sent: 07 March 2009 17:47
>>>>> To: Access Developers discussion and problem solving
>>>>> Subject: [AccessD] ...building, managing, and using a page favorites
>>>>> list
>>>>>
>>>>> ...I want to create a user specific favorites list for an a2k3 mdb fe
>>>>> ...such that when joe blow is logged in, in addition to my normal
>>>>> treeview
>>>>> menu, he'll also see a mini menu with up to 5 page selects that he has
>>>>> chosen and can go directly to with a single click.
>>>>>
>>>>> ...has anyone built something like this before ...any ideas, samples,
>>>>> etc
>>>>> much appreciated
>>>>>
>>>>> William
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> 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
>>
>