jwcolby
jwcolby at colbyconsulting.com
Sun Mar 8 20:04:07 CDT 2009
Other problems I see:
1) The AuditTable needs an "Edit type" field. We need to know whether the data in the table was an
edit to the field, a new record, or a delete.
2) The table that the data comes from... how do I determine that? Am I drilling down into the DAO
under the control? What happens if this is a complex query? Even worse a complex SQL statement?
3) The field name can be determined from the control source, but it may be an alias if the form's
recordsource is a query or SQL Statement.
4) Recording full on text fields for the table name, field name and computer name is going to be
very expensive. If you have a form with 40 fields and you are performing an add record...
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
>>
>