[AccessD] ...building, managing, and using a page favorites list

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
>>
> 



More information about the AccessD mailing list