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

Max Wanadoo max.wanadoo at gmail.com
Sun Mar 8 11:24:53 CDT 2009


> This speeds things up by keeping the recordset open the whole time.

Would it be quicked to use an SQL statement ie,
Sql = "Insert into tblAudit fields(x,y,z) values (a,b,c)
Currentdb.execute(sql)

Rather than opening recordsets.  I don't know, so i am really asking the
question

Thanks

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: 08 March 2009 16:04
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] ...building, managing, and using a page favorites
list

...I like simple ...gives me more time in the hammock :)
...I'm not tracking you on "frmClass"
...in my lexicon that implies a new form ...or did you mean "clsForm" as a 
new class?

William

--------------------------------------------------
From: "jwcolby" <jwcolby at colbyconsulting.com>
Sent: Sunday, March 08, 2009 8:01 AM
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] ...building, managing, and using a page favorites 
list

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