terry w said:
Dirk: thanks for a quick response.
a) If I use the form's recordset property as you suggest,
Actually, I said to use the RecordsetClone property, not the Recordset
property. In recent versions of Access, you can also use the Recordset
property for this sort of thing, but I wasn't talking about that.
does this mean
there is no need to use a statement like my current 'Set rs =
Me.Recordset.Clone'?
Yes, there is no need to define and set a separate recordset object.
Also, is there still a need for any statements to
replace my current statements 'rs.close' or 'Set rs = Nothing'? I'm
just
puzzled because I see these used in so many examples from this site.
No, there is no need for those statements. In the code I posted, the With
statement creates the reference to the RecordsetClone, so you don't need to
Dim a Recordset object; you don't need to (and shouldn't) close the
RecordsetClone; and the scope of the With statement defines the lifetime of
the procedure's reference to the recordset, so there's no object variable
that needs to be set to Nothing.
You *could* accomplish the same thing using an object variable, but still
using the form's RecordsetClone property rather than creating a fresh clone
of the form's recordset:
'------ start of alternate code ------
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst strSearch
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
'------ end of alternate code ------
But there's no reason, in my view, to use the extra code.
b) You mention that if using .mdb (I am), "...the form's RecordsetClone
property is better for this sort of thing." For my own understanding of
the
situation, can you tell me why it is better?
It's better because the RecordsetClone property returns a reference to a
hidden clone of the form's recordset that is created the first time you
refer to the property, and then remains in existence for as long as the form
is open. Thus, the clone is created the first time you use it, and the
overhead of creating the clone is incurred only once during the lifetime of
the form instance. If your code explicitly clones the recordset (as
originally written: "Set rs = Me.Recordset.Clone"), then that overhead will
be incurred every time the code is called.
The code you so often see, using "Me.Recordset.Clone", is the code that the
wizards in Access 2000-2003 (if not 2007) build for finding records. I
believe they introduced that specifically for ADP support (ADPs use ADO
recordsets, not DAO recordsets), but I'm not sure why they felt the need to,
and other parts of the wizard code are just plain wrong.
Some people do feel the need to declare an object variable for every object
they intend to manipulate, but I don't see the point when a well-chosen With
statement will do the job, and usually be more efficient, too.
