William Benson (VBACreations.Com)
vbacreations at gmail.com
Tue Feb 7 17:46:28 CST 2012
This will surprise no one but me I am sure, but the recordset object variable does not seem to persist when a form is sorted. I originaly declared Rst as a Static object, and I was getting errors the second time I clicked the button I set up for testing. Then I thought I would declare Rst at the module level, and that would "guarantee" long life for the variable's scope. As if. As soon as I either sorted or filtered the form, the object variable became "invalid or no longer set". Note that it didn't become Nothing, but rather like a Range variable in Excel when you delete the row it is in. I don't know what to call this... its not out of scope, and it's not Nothing, its in purgatory? Lol. Anywy, I answered my own question about setting an object variable. Unless you are going to use Rst right away, seems one should get rid of it ASAP...? Option Compare Database Option Explicit Private rst As DAO.Recordset Dim dbLocal As DAO.Database Private Sub Command8_Click() Set dbLocal = CurrentDb If rst Is Nothing Then Set rst = Me.RecordsetClone MsgBox rst.Fields(0) Else MsgBox rst.Fields(0) End If End Sub -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Tuesday, February 07, 2012 5:54 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] RecordsetClone and Bookmark >> The code may have been structured that way if it was older, because up >> until A2000... That history certainly makes great sense - and it explains why so many coding samples make use of the clone and bookmark method. Old habits often prevent you from understanding what's going on... Thank you for pointing out! / Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Jim Dettman Sendt: 7. februar 2012 23:33 Til: 'Access Developers discussion and problem solving' Emne: Re: [AccessD] RecordsetClone and Bookmark <<So if I just want to jump to a specific record, no more, then the Me.RecordSet FindFirst method seems the most simple - does it have any performance drawbacks?>> None that I can think of. In fact it would be a tad faster. The code may have been structured that way if it was older, because up until A2000, a forms Recordset wasn't exposed and using the clone was the only way in which you could do a Findfirst. But if you do multiple Find/Moves on the recordset, the forms cursor will move and the form will react (ie. fire OnCurrent, etc), where as if you do it on the clone, nothing happens until you match bookmarks. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Tuesday, February 07, 2012 05:06 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] RecordsetClone and Bookmark Agree on this... If for instance I would consider looping the recordset to change some values, then using RecordsetClone and Bookmark would be more efficient than using Me.Recordset because the form wouldn't have to be updated for each move. (But actually I wouldn't use a recordset Edit/Update but a SQL Update for this...). So if I just want to jump to a specific record, no more, then the Me.RecordSet FindFirst method seems the most simple - does it have any performance drawbacks? / Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Jim Dettman Sendt: 7. februar 2012 22:17 Til: 'Access Developers discussion and problem solving' Emne: Re: [AccessD] RecordsetClone and Bookmark <<the finds certainly make the form show the wanted records>> Once you do: Me.Bookmark = rst.Bookmark But until then, all the manipulation in rst won't be visible to the form or the user. That's the whole point. You can jump forward and backwards all you want, but until you set the forms bookmark property, none of what you do affects the form. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Tuesday, February 07, 2012 04:11 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] RecordsetClone and Bookmark Disagree - the finds certainly make the form show the wanted records, no matter if I use rst.FindFirst or Me.Recordset.FindFirst / Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Jim Dettman Sendt: 7. februar 2012 21:27 Til: 'Access Developers discussion and problem solving' Emne: Re: [AccessD] RecordsetClone and Bookmark You don't move the forms cursor. Your finds are invisible to the form and the user. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Tuesday, February 07, 2012 10:29 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] RecordsetClone and Bookmark Having a listbox on a form for navigation to a specific record I've always used RecordsetClone and Bookmark like this: Private Sub lstOrderID_AfterUpdate() Dim rst As DAO.Recordset Set rst = Me.RecordsetClone rst.FindFirst "OrderID = " & Me.lstOrderID Me.Bookmark = rst.Bookmark End Sub Now I notice that the code apparently might as well be just: Private Sub lstOderID_AfterUpdate() Me.Recordset.FindFirst "OrderID = " & Me.lstOrderID End Sub Can't remember the reason for using RecordsetClone and Bookmark in this case. Any suggestions? / Asger -- 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