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

Max Wanadoo max.wanadoo at gmail.com
Sun Mar 8 13:44:44 CDT 2009


Hi John,
Yes, Gustav kindly gave me the answer which bears out your thinking too.

Thanks

Max



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

Max,

I know that doing a sql statement like that is fast if you are only updating
a single record.  In 
this case we are potentially appending anywhere from one to N records.  I
suspect that in that case 
it would be faster to open the rst and hold it open, then do the append.
Perhaps someone else would 
have an answer to that.

John W. Colby
www.ColbyConsulting.com


Max Wanadoo wrote:
>> 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