ADP issue with navigating to record using rs.Bookmark

[Danny J. Lesandrini]

The following code works with a standard MDB to navigate
to a particluar record (with a DAO recordset, of course)
but it's giving me problems in an ADP I'm working on.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[ContractID]=" & lngContractID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I must site the Heisenberb Uncertainty Principal here, as it
works fine while I "observe the system" ... stepping through
the code using F8. But, when I let the code run on it's own
the ADO recordset is at EOF.

So long as I create an artificial pause, by stepping through,
the recordset gets populated, but not when it runs at full speed.

The data is in SQL Server. Could the network retrieve time
be doing it? Is there a different way to navigate to a record
when in an ADP?

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast

[Lyle Fairfield]

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in news:bt81vj$371s4$1
@ID-82595.news.uni-berlin.de:

The following code works with a standard MDB to navigate
to a particluar record (with a DAO recordset, of course)
but it's giving me problems in an ADP I'm working on.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[ContractID]=" & lngContractID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I must site the Heisenberb Uncertainty Principal here, as it
works fine while I "observe the system" ... stepping through
the code using F8. But, when I let the code run on it's own
the ADO recordset is at EOF.

So long as I create an artificial pause, by stepping through,
the recordset gets populated, but not when it runs at full speed.

The data is in SQL Server. Could the network retrieve time
be doing it? Is there a different way to navigate to a record
when in an ADP?

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast

Usually we set the row position explicitly with a .MoveFirst before
"Finding" in ADO but I do not know if this has anything to do with what you
report here.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Danny J. Lesandrini]

Well, that was a thought. Thanks for the quick reply.

I test for BOF and EOF before doing the find and they
are both FALSE ... so there are records. I do the MoveFirst
as you suggested, performed the find, get this result
BOF = False
EOF = True
Ok, so I add another If Not rs.BOF Then rs.MoveFirst after
the Find operation, but still no go.

Again, if I step through, it works, but if I let it run, there
is no record set as the current record after the Find.

Odd stuff.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Lyle Fairfield" <Mi************@Invalid.Com> wrote ...

Usually we set the row position explicitly with a .MoveFirst before
"Finding" in ADO but I do not know if this has anything to do with what you
report here.

--
Lyle

[Steve Jorgensen]

You didn't say where you are calling this from, but if it's from Form_Open or
Form_Load, I've seen what you're describing. It appears that when a form
opens a recordset, it opens it asynchronously. Depending how long you wait,
you'll see zero records, then 50, then 100, etc. until the actual number of
records is reached. Even a move-last doesn't force it to go all the way to
the end, only to the last record retrieved so far.

After fighting with that for some time, I finally decided that it is best not
to design any part of an ADP app to need to move to a specific record on a
form on-open. There's just no reliable way to do it. Figure out a design
that doesn't need to do that.

On Sat, 3 Jan 2004 20:41:32 -0700, "Danny J. Lesandrini"
<dl*********@hotmail.com> wrote:

The following code works with a standard MDB to navigate
to a particluar record (with a DAO recordset, of course)
but it's giving me problems in an ADP I'm working on.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[ContractID]=" & lngContractID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I must site the Heisenberb Uncertainty Principal here, as it
works fine while I "observe the system" ... stepping through
the code using F8. But, when I let the code run on it's own
the ADO recordset is at EOF.

So long as I create an artificial pause, by stepping through,
the recordset gets populated, but not when it runs at full speed.

The data is in SQL Server. Could the network retrieve time
be doing it? Is there a different way to navigate to a record
when in an ADP?

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast

[Danny J. Lesandrini]

Right on both counts.

It didn't matter whether I put it in the Open or Load events. In fact,
I noticed behavior that I thought was odd. I thought the sequence
was Open ==> Load ...like you would do with a washing machine.
The ADP seemed to fire Load ==> Open.

Anyhow, I decided to use a filter instead of a find. It changes the
design somewhat and requires I provide a way to restore all the
filtered records, but it works reliably.

Thanks for the comments, though. Makes me feel confident about
my decision.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Steve Jorgensen" <no****@nospam.nospam> wrote in message news:bm********************************@4ax.com...

You didn't say where you are calling this from, but if it's from Form_Open or
Form_Load, I've seen what you're describing. It appears that when a form
opens a recordset, it opens it asynchronously. Depending how long you wait,
you'll see zero records, then 50, then 100, etc. until the actual number of
records is reached. Even a move-last doesn't force it to go all the way to
the end, only to the last record retrieved so far.

After fighting with that for some time, I finally decided that it is best not
to design any part of an ADP app to need to move to a specific record on a
form on-open. There's just no reliable way to do it. Figure out a design
that doesn't need to do that.

On Sat, 3 Jan 2004 20:41:32 -0700, "Danny J. Lesandrini"
<dl*********@hotmail.com> wrote:

[Steve Jorgensen]

On Sun, 4 Jan 2004 08:21:10 -0700, "Danny J. Lesandrini"
<dl*********@hotmail.com> wrote:

Right on both counts.

It didn't matter whether I put it in the Open or Load events. In fact,
I noticed behavior that I thought was odd. I thought the sequence
was Open ==> Load ...like you would do with a washing machine.
The ADP seemed to fire Load ==> Open.
I've also seen that behavior. I don't recall the exact specifics, but it
turns out that the events are not truly reversed, but certain actions in
Form_Open code will force the Form_Load to occur immediately, and prior to the
completion of the Form_Open code.

Anyhow, I decided to use a filter instead of a find. It changes the
design somewhat and requires I provide a way to restore all the
filtered records, but it works reliably.

Thanks for the comments, though. Makes me feel confident about
my decision.

[David W. Fenton]

dl*********@hotmail.com (Danny J. Lesandrini) wrote in
<bt************@ID-82595.news.uni-berlin.de>:

The following code works with a standard MDB to navigate
to a particluar record (with a DAO recordset, of course)
but it's giving me problems in an ADP I'm working on.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[ContractID]=" & lngContractID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I have never understood why people would use a new recordset
variable for navigating a recordset that already exists. Why not:

With Me.RecordsetClone
.Find "[ContractID]=" & lngContractID
If Not .EOF Then Me.Bookmark = .Bookmark
End With

Then you don't have to either set the variable or clean up after
yourself.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Danny J. Lesandrini]

What? you can do that? :-)
--
Danny J. Lesandrini

"David W. Fenton" <dX********@bway.net.invalid> wrote ...

I have never understood why people would use a new recordset
variable for navigating a recordset that already exists. Why not:

With Me.RecordsetClone
.Find "[ContractID]=" & lngContractID
If Not .EOF Then Me.Bookmark = .Bookmark
End With

[Steve Jorgensen]

On Mon, 05 Jan 2004 00:10:37 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

dl*********@hotmail.com (Danny J. Lesandrini) wrote in
<bt************@ID-82595.news.uni-berlin.de>:

The following code works with a standard MDB to navigate
to a particluar record (with a DAO recordset, of course)
but it's giving me problems in an ADP I'm working on.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[ContractID]=" & lngContractID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I have never understood why people would use a new recordset
variable for navigating a recordset that already exists. Why not:

With Me.RecordsetClone
.Find "[ContractID]=" & lngContractID
If Not .EOF Then Me.Bookmark = .Bookmark
End With

Then you don't have to either set the variable or clean up after
yourself.

Just to clarify...
If you're saying that a With block is a good substitute for an explicit
variable declaration, I heartily agree for simple cases like this one (and
most cases are simple if the code is well organized).

If what you're saying, though, is that Me.RecordsetClone should be treated as
a variable, so you should not need your own, I disagree since it is never safe
to assume that a class member reference refers to the same thing each time you
access it. In this case, it likely does -not- refer to the same thing, but
generates a new clone for each call just as CurrentDB generates a new Database
object instance each time it is called. A With block essentially does act
like a temporary variable, so that's safe, but multiple independent references
to .RecordsetClone might not be.

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<89********************************@4ax.com>:

On Mon, 05 Jan 2004 00:10:37 GMT, dX********@bway.net.invalid
(David W. Fenton) wrote:

dl*********@hotmail.com (Danny J. Lesandrini) wrote in
<bt************@ID-82595.news.uni-berlin.de>:

The following code works with a standard MDB to navigate
to a particluar record (with a DAO recordset, of course)
but it's giving me problems in an ADP I'm working on.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[ContractID]=" & lngContractID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I have never understood why people would use a new recordset
variable for navigating a recordset that already exists. Why not:

With Me.RecordsetClone
.Find "[ContractID]=" & lngContractID
If Not .EOF Then Me.Bookmark = .Bookmark
End With

Then you don't have to either set the variable or clean up after
yourself.

Just to clarify...
If you're saying that a With block is a good substitute for an
explicit variable declaration, I heartily agree for simple cases
like this one (and most cases are simple if the code is well
organized).

If what you're saying, though, is that Me.RecordsetClone should be
treated as a variable, so you should not need your own, I disagree
since it is never safe to assume that a class member reference
refers to the same thing each time you access it. In this case,
it likely does -not- refer to the same thing, but generates a new
clone for each call just as CurrentDB generates a new Database
object instance each time it is called. A With block essentially
does act like a temporary variable, so that's safe, but multiple
independent references to .RecordsetClone might not be.

Not the same object instance? Of course, they'll be the same
*object* instance. That doesn't mean between two contexts the
recordset referred to by Me.Recordsetclone will be in the same
state. Isn't that what you mean here?

In the context in question, navigating via bookmarks, there's no
need for a variable, and never has been such a need.

Other contexts have different requirements, so worrying about where
a WITH structure has problems seems like worrying about whether an
asteroid will strike in the next 10 minutes. It's possible, yes,
but not very likely that we wouldn't know about it ahead of time.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Mon, 05 Jan 2004 15:35:49 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

....

Not the same object instance? Of course, they'll be the same
*object* instance. That doesn't mean between two contexts the
recordset referred to by Me.Recordsetclone will be in the same
state. Isn't that what you mean here?

In the context in question, navigating via bookmarks, there's no
need for a variable, and never has been such a need.

Other contexts have different requirements, so worrying about where
a WITH structure has problems seems like worrying about whether an
asteroid will strike in the next 10 minutes. It's possible, yes,
but not very likely that we wouldn't know about it ahead of time.

I can't tell if we're agreeing ot not. I agree what you get should be a clone
of the same form's same recordset, but is it safe to assume that it's the same
clone? You could be getting a different clone each time, and that would lose
all state information between refernces. For instance, it would not have the
same current record pointer, the same BOF/EOF state, and would not have the
same instance of Fields so that continuing, say, a For Each loop of Fields
after the clone was out of scope could be an unsafe operation that could cause
a crash.

Now, I just did a quick experiment showing that, in an MDB in Access 2002,
RecordsetClone actually does return the same clone instance for consecutive
calls, I'm just not comfortable relying on this to be a guaranteed fact.

Code
==========
Private Sub cmdTest_Click()
Dim rstClone1 As DAO.Recordset
Dim rstClone2 As DAO.Recordset

Set rstClone1 = Me.RecordsetClone
Set rstClone2 = Me.RecordsetClone

Debug.Print ObjPtr(rstClone1), ObjPtr(rstClone2)

Set rstClone2 = Nothing
Set rstClone1 = Nothing

End Sub
==========

Output
----------
2108324 2108324

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<j0********************************@4ax.com>:

On Mon, 05 Jan 2004 15:35:49 GMT, dX********@bway.net.invalid
(David W. Fenton) wrote:

...

Not the same object instance? Of course, they'll be the same
*object* instance. That doesn't mean between two contexts the
recordset referred to by Me.Recordsetclone will be in the same
state. Isn't that what you mean here?

In the context in question, navigating via bookmarks, there's no
need for a variable, and never has been such a need.

Other contexts have different requirements, so worrying about
where a WITH structure has problems seems like worrying about
whether an asteroid will strike in the next 10 minutes. It's
possible, yes, but not very likely that we wouldn't know about it
ahead of time.
I can't tell if we're agreeing ot not. I agree what you get
should be a clone of the same form's same recordset, but is it
safe to assume that it's the same clone? You could be getting a
different clone each time, and that would lose all state
information between refernces. . . .

I thought we already knew that Me.RecordsetClone would not be the
same as Me.RecordsetClone after a Me.Requery. So, what's the
difference? You know that the data represented in the clone may or
may not represent the data in the underlying data tables exactly at
the moment you are using it, unless you requery just before you do
whatever it is you want to use. Caching bookmarks, for instance, as
always been deprecated as a programming technique (you use PKs to
identify records, not the bookmark).
. . . For instance, it would not have
the same current record pointer, the same BOF/EOF state, and would
not have the same instance of Fields so that continuing, say, a
For Each loop of Fields after the clone was out of scope could be
an unsafe operation that could cause a crash.
Why would you do *that*?

Within a WITH structure, the RecordsetClone will be reliable,
unless you requery the underlying form (or maybe the clone itself)
during your WITH structure. Of course, if you did that, you'd know
you were invalidating everything, so you wouldn't attempt to cache
a bookmark, requery, then use the cached bookmark. Right?
Now, I just did a quick experiment showing that, in an MDB in
Access 2002, RecordsetClone actually does return the same clone
instance for consecutive calls, I'm just not comfortable relying
on this to be a guaranteed fact.
Why does it matter?

When would you ever use it in a fashion that would require it to be
the same? What is the recordsetclone good for, other than
navigation? And why would you ever navigate using any structures
that were not reliable?
Code
==========
Private Sub cmdTest_Click()
Dim rstClone1 As DAO.Recordset
Dim rstClone2 As DAO.Recordset

Set rstClone1 = Me.RecordsetClone
Set rstClone2 = Me.RecordsetClone

Debug.Print ObjPtr(rstClone1), ObjPtr(rstClone2)

Set rstClone2 = Nothing
Set rstClone1 = Nothing

End Sub
==========

Output
----------
2108324 2108324

Shouldn't this stand to reason, as you're talking about a property
of a form?

To raise another question, maybe the things you're thinking about
should be done with Me.Recordset instead of with Me.RecordsetClone?

And in any event, I just can't conceive of anything you'd be doing
with other recordset where it would matter. If it *does* matter,
then I think you're mis-using it.

No?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Mon, 05 Jan 2004 22:07:19 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

no****@nospam.nospam (Steve Jorgensen) wrote in
<j0********************************@4ax.com>:

On Mon, 05 Jan 2004 15:35:49 GMT, dX********@bway.net.invalid
(David W. Fenton) wrote:

...

Not the same object instance? Of course, they'll be the same
*object* instance. That doesn't mean between two contexts the
recordset referred to by Me.Recordsetclone will be in the same
state. Isn't that what you mean here?

In the context in question, navigating via bookmarks, there's no
need for a variable, and never has been such a need.

Other contexts have different requirements, so worrying about
where a WITH structure has problems seems like worrying about
whether an asteroid will strike in the next 10 minutes. It's
possible, yes, but not very likely that we wouldn't know about it
ahead of time.
I can't tell if we're agreeing ot not. I agree what you get
should be a clone of the same form's same recordset, but is it
safe to assume that it's the same clone? You could be getting a
different clone each time, and that would lose all state
information between refernces. . . .

I thought we already knew that Me.RecordsetClone would not be the
same as Me.RecordsetClone after a Me.Requery. So, what's the
difference? You know that the data represented in the clone may or
may not represent the data in the underlying data tables exactly at
the moment you are using it, unless you requery just before you do
whatever it is you want to use. Caching bookmarks, for instance, as
always been deprecated as a programming technique (you use PKs to
identify records, not the bookmark).

The difference is when you are expecting state to be preserved between
statements in the same procedure referencing the clone, such as While Not
rst.EOF ... rst.MoveNext ... Wend. If one did not use a variable or a With
block, and if .RecordsetClone returned a new clone each time, the loop would
reference the first record repeatedly and never complete.

. . . For instance, it would not have
the same current record pointer, the same BOF/EOF state, and would
not have the same instance of Fields so that continuing, say, a
For Each loop of Fields after the clone was out of scope could be
an unsafe operation that could cause a crash.

Why would you do *that*?

How about For Each fld in Me.RecordsetClone.Fields? If .RecordsetClone
returned a new instance which propmtly falls back out of scope, then the With
block holds a hidden instance of Fields, but the Recordset object behind it
might already be gone - boom. Again, my experiment shows that this will
probably not happen because the Recordset instance between subsequent calls to
RecordsetClone is preserved. Again, I just try not to rely on such things.
Within a WITH structure, the RecordsetClone will be reliable,
unless you requery the underlying form (or maybe the clone itself)
during your WITH structure. Of course, if you did that, you'd know
you were invalidating everything, so you wouldn't attempt to cache
a bookmark, requery, then use the cached bookmark. Right?
Absolutely. I'm not arguing with using a With block for RecordsetClone, in
fact I would advocate it. I'm just arguing with assuming that a direct
reference to Me.RecordsetClone is the same thing as a variable or With block
that preserves a single instance resulting from a single reference to
Me.RecordsetClone.

Now, I just did a quick experiment showing that, in an MDB in
Access 2002, RecordsetClone actually does return the same clone
instance for consecutive calls, I'm just not comfortable relying
on this to be a guaranteed fact.

Why does it matter?

See above.
When would you ever use it in a fashion that would require it to be
the same? What is the recordsetclone good for, other than
navigation? And why would you ever navigate using any structures
that were not reliable?
Well, even assuming you only use it for navigation, you need to know that it
will have the same record pointer after a FindFirst and in the next statements
where you ask for .NoMatch and .Bookmark. In the case of the With block, it's
safe no matter what. I just wouldn't want anyone to think you should rely on
saying Me.RecordsetClone.FindFirst, then expecting a subsequent
Me.RecordsetClone.Bookmark to have the expected value.

Code
==========
Private Sub cmdTest_Click()
Dim rstClone1 As DAO.Recordset
Dim rstClone2 As DAO.Recordset

Set rstClone1 = Me.RecordsetClone
Set rstClone2 = Me.RecordsetClone

Debug.Print ObjPtr(rstClone1), ObjPtr(rstClone2)

Set rstClone2 = Nothing
Set rstClone1 = Nothing

End Sub
==========

Output
----------
2108324 2108324
Shouldn't this stand to reason, as you're talking about a property
of a form?

You can never assume a property is just a property. It could well execure a
function and return a value dynamically rather than referring to a simple
property variable. That is, in fact and as you know, just the issue that
happens with CurrentDB. CurrentDB is not actually defined as a property, but
it is given a name that misleads one into thinking it is, and the issue can
just as well happen with a genuine property.

Code
==========
Public Sub Test()
Dim dbs1 As DAO.Database
Dim dbs2 As DAO.Database

Set dbs1 = CurrentDb
Set dbs2 = CurrentDb

Debug.Print ObjPtr(dbs1), ObjPtr(dbs2)

Set dbs2 = Nothing
Set dbs1 = Nothing
End Sub
==========

Output
----------
1827760 1830872

People write code thinking that CurrentDB is CurrentDB, when actually a
separate instance is returned for each call. When they use this code, they
get crashing, resource leaks, etc. That's why it's good policy never to
assume that a property of an object for which you don't control the source
code is a single thing, and not really just a function generating a value
dynamically each time it is referenced.

Another way of saying this is, if you expect your code to be dealing with the
same object at 2 different points, you should use something in your own code
to make sure this will be true and not assume that some external code you
didn't write will provide this behavior for you.

In Access 97, I also ran into cases where separate references to the same form
or form control no not return True for obj1 Is obj2 because they are separate
instances of some wrapper object. Things like this have tought me the lesson
that it's not safe to assume. Instead, write code that assumes the worst and
will be safe no matter what.
To raise another question, maybe the things you're thinking about
should be done with Me.Recordset instead of with Me.RecordsetClone?
A good point actually. If it doesn't have to run in Access 97, it's simpler
to navigate using Me.Recordset - "Look Ma, no bookmarks".
And in any event, I just can't conceive of anything you'd be doing
with other recordset where it would matter. If it *does* matter,
then I think you're mis-using it.

No?

Again, I agree and disagree. To me, using a With block or an explicit
variable is doing it properly. Using separate references to the property is
relying on a behavior you don't control, and that's not so good.

And yes, there are cases where it is necessary to use a Recordset Clone for
something more convoluted that what you would use a simple With block for does
come up. This happens, for instance, in the code to trap ODBC error messages
that occur during updates through a bound, continuous form. You do the update
through code via the RecordsetClone, so the change is reflected in the form
afterward without a requery. You can't use the <form>.Recordset because you
need to preserve the current record and dirty state of the form of the update
in code fails with an error. Another example is where you want to loop and
aggregate the rows in the form's snapshot recordset consistently with that
recordset even if records may have been added by another user that your
recordset is not aware of.

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<qj********************************@4ax.com>:

On Mon, 05 Jan 2004 22:07:19 GMT, dX********@bway.net.invalid
(David W. Fenton) wrote:

no****@nospam.nospam (Steve Jorgensen) wrote in
<j0********************************@4ax.com>:

On Mon, 05 Jan 2004 15:35:49 GMT, dX********@bway.net.invalid
(David W. Fenton) wrote:
[]
Now, I just did a quick experiment showing that, in an MDB in
Access 2002, RecordsetClone actually does return the same clone
instance for consecutive calls, I'm just not comfortable relying
on this to be a guaranteed fact.
Why does it matter?

See above.

When would you ever use it in a fashion that would require it to
be the same? What is the recordsetclone good for, other than
navigation? And why would you ever navigate using any structures
that were not reliable?

Well, even assuming you only use it for navigation, you need to
know that it will have the same record pointer after a FindFirst
and in the next statements where you ask for .NoMatch and
.Bookmark. In the case of the With block, it's safe no matter
what. I just wouldn't want anyone to think you should rely on
saying Me.RecordsetClone.FindFirst, then expecting a subsequent
Me.RecordsetClone.Bookmark to have the expected value.

I think it's quite obvious that, unless MS introduces a bug in
future versions of Access, the whole thing was designed from the
very beginning to always return the exact same recordset (in
whatever state). Remember that the FIND RECORD wizard uses direct
references to Me.RecordsetClone, no object variable, no WITH
structure, and it always has done so. That indicates to *me* that
Microsoft intends for Me.RecordsetClone to always return the same
recordset.

I don't see any reason why you'd ever think otherwise, or worry
that MS would change the behavior of a form property that is pretty
transparent in what it does and what it is intended for. Why would
you ever imagine that a property like that would not return the
same object each time you referred to it? How would that assumption
be consistent with the behavior of any other property that Access
forms have ever possessed?

Code
==========
Private Sub cmdTest_Click()
Dim rstClone1 As DAO.Recordset
Dim rstClone2 As DAO.Recordset

Set rstClone1 = Me.RecordsetClone
Set rstClone2 = Me.RecordsetClone

Debug.Print ObjPtr(rstClone1), ObjPtr(rstClone2)

Set rstClone2 = Nothing
Set rstClone1 = Nothing

End Sub
==========

Output
----------
2108324 2108324

Shouldn't this stand to reason, as you're talking about a
property of a form?

You can never assume a property is just a property. It could well
execure a function and return a value dynamically rather than
referring to a simple property variable. . .

Except that, quite obviously, it does not, and that's clearly by
design.
. . . That is, in fact and as
you know, just the issue that happens with CurrentDB. . .
CurrentDB is a FUNCTION -- it is not a property of an object.
. . . CurrentDB
is not actually defined as a property, but it is given a name that
misleads one into thinking it is, and the issue can just as well
happen with a genuine property.
I've never considered CurrentDB() to be anything other than a
function, and that's why I've always used the () with it, to keep
it clear in my mind that I'm use a function.
Code
==========
Public Sub Test()
Dim dbs1 As DAO.Database
Dim dbs2 As DAO.Database

Set dbs1 = CurrentDb
Set dbs2 = CurrentDb

Debug.Print ObjPtr(dbs1), ObjPtr(dbs2)

Set dbs2 = Nothing
Set dbs1 = Nothing
End Sub
==========

Output
----------
1827760 1830872

People write code thinking that CurrentDB is CurrentDB, when
actually a separate instance is returned for each call. . . .
If they do that, then they haven't read the documentation for
CurrentDB(). Period.
. . . When they
use this code, they get crashing, resource leaks, etc. That's why
it's good policy never to assume that a property of an object for
which you don't control the source code is a single thing, and not
really just a function generating a value dynamically each time it
is referenced.
There's a clear differentiation here between a function
(CurrentDB()) and a property of a form (Me.RecordsetClone). I can't
see why anyone who has read the help file on CurrentDB() would
think it's a property.
Another way of saying this is, if you expect your code to be
dealing with the same object at 2 different points, you should use
something in your own code to make sure this will be true and not
assume that some external code you didn't write will provide this
behavior for you.
It's a good basic principle, I agree.

But not because of a fear that a form property will suddenly be
changed by Microsoft to start behaving like a function. That would
majorly break all sorts of code, including the code written by
Access's wizards.
In Access 97, I also ran into cases where separate references to
the same form or form control no not return True for obj1 Is obj2
because they are separate instances of some wrapper object.
Things like this have tought me the lesson that it's not safe to
assume. Instead, write code that assumes the worst and will be
safe no matter what.
Well, controls are not properties. My uninformed guess is that this
has something to do with the hWnd issue, and is an artifact of the
resource limitations that controlled the basic design of Access
back in the days when a machine with 16MBs of RAM was more than
adequate to run Windows. I didn't use . syntax for my tests, but
used the form's Controls collection explicitly. I also tested with
Screen.PreviousControl (in A97 -- ActiveControl would accomplish
the same thing in A2K), and all returned different object pointers.

Yes it's inconsistent, but I don't see why that should lead you to
distrust Me.RecordsetClone.

To raise another question, maybe the things you're thinking about
should be done with Me.Recordset instead of with
Me.RecordsetClone?

A good point actually. If it doesn't have to run in Access 97,
it's simpler to navigate using Me.Recordset - "Look Ma, no
bookmarks".

But why don't you worry about the same problem with that? Seems to
me that it's much more obvious that this will be the same object
each time

And in any event, I just can't conceive of anything you'd be
doing with other recordset where it would matter. If it *does*
matter, then I think you're mis-using it.

No?

Again, I agree and disagree. To me, using a With block or an
explicit variable is doing it properly. Using separate references
to the property is relying on a behavior you don't control, and
that's not so good.

I think it's inefficient, but I don't see that it's a problem. It's
inefficient to do something like:

Me!FirstControl.Enabled = [some Boolean test]
Me!SecondControl.Enabled = Me!FirstControl.Enabled
Me!ThirdControl.Enabled = Me!FirstControl.Enabled
Me!FourthControl.Enabled = Me!FirstControl.Enabled
Me!FifthControl.Enabled = Me!FirstControl.Enabled
[etc.]

You could use an object variable so the reference to the controls
collection doesn't have to be repeatedly resolved. And, of course,
more efficient still is a Boolean variable that you use in all
cases.

Using WITH (obviously superior to an object variable, in my
opinion) is efficient, because the reference is resolved once and
only once, which makes for efficient, reliable code.
And yes, there are cases where it is necessary to use a Recordset
Clone for something more convoluted that what you would use a
simple With block for does come up. This happens, for instance,
in the code to trap ODBC error messages that occur during updates
through a bound, continuous form. You do the update through code
via the RecordsetClone, so the change is reflected in the form
afterward without a requery. You can't use the <form>.Recordset
because you need to preserve the current record and dirty state of
the form of the update in code fails with an error. . . .
Is that use of the .RecordsetClone Kosher? It sounds like a case
where you really want a transaction, instead, no? And where a
..Refresh would update the data display?

That kind of thing sounds not quite right to me, but I don't do
that kind of development.
. . . Another
example is where you want to loop and aggregate the rows in the
form's snapshot recordset consistently with that recordset even if
records may have been added by another user that your recordset is
not aware of.

Why would it make a difference if you use the .RecordsetClone or
the .Recordset? Because of updates made by another user that will
eventually show up in the .Refresh interval?

Sounds dangerous all around. I'm glad I never have to use the
..RecordsetClone in that manner, but my reservations have nothing to
do with fears that it won't be the same recordset. Indeed, the two
methods you've outlined could not possibly work unless it were the
case that, by design, Me.RecordsetClone always returned the same
recordset.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Tue, 06 Jan 2004 01:11:04 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

....

I think it's quite obvious that, unless MS introduces a bug in
future versions of Access, the whole thing was designed from the
very beginning to always return the exact same recordset (in
whatever state). Remember that the FIND RECORD wizard uses direct
references to Me.RecordsetClone, no object variable, no WITH
structure, and it always has done so. That indicates to *me* that
Microsoft intends for Me.RecordsetClone to always return the same
recordset.

I don't see any reason why you'd ever think otherwise, or worry
that MS would change the behavior of a form property that is pretty
transparent in what it does and what it is intended for. Why would
you ever imagine that a property like that would not return the
same object each time you referred to it? How would that assumption
be consistent with the behavior of any other property that Access
forms have ever possessed?
Well, my response could simply be because I'm not as familliar with all the
ways that Microsoft's own code has relied on this behavior. Based on your
experience, it sounds as if my warning is way overstated. I would still
submit that it's a good GP to minimize dependencies on behaviors you don't
control. If you try to always follwo that rule, it won't hurt you when it's
not necessary, but it may save you lots of headaches in the other cases.

....

Shouldn't this stand to reason, as you're talking about a
property of a form?

You can never assume a property is just a property. It could well
execure a function and return a value dynamically rather than
referring to a simple property variable. . .

Except that, quite obviously, it does not, and that's clearly by
design.

Perhaps so, it was not so obvious to me.

. . . That is, in fact and as
you know, just the issue that happens with CurrentDB. . .

CurrentDB is a FUNCTION -- it is not a property of an object.

. . . CurrentDB
is not actually defined as a property, but it is given a name that
misleads one into thinking it is, and the issue can just as well
happen with a genuine property.

I've never considered CurrentDB() to be anything other than a
function, and that's why I've always used the () with it, to keep
it clear in my mind that I'm use a function.

I'm one of those folks who made the mistake early on, and learned the hard
way. IMO, it's not valid to assume this kind of issue will not occur with
properties simply because they are properties. Since object properties are
not, fundamentally guaranteed not to generate new object instances for each
call, I advocate not assuming that they won't do so.

....

Another way of saying this is, if you expect your code to be
dealing with the same object at 2 different points, you should use
something in your own code to make sure this will be true and not
assume that some external code you didn't write will provide this
behavior for you.

It's a good basic principle, I agree.

But not because of a fear that a form property will suddenly be
changed by Microsoft to start behaving like a function. That would
majorly break all sorts of code, including the code written by
Access's wizards.

Not being familliar with this evidence, I did not come to that conclusion. Do
you agree that simply being a property is not sufficient evidence that an
object property will not return a new instance each time it is referenced, and
that should need to have seen the other evidence that MS would not do that
before trusting the assumption?

In Access 97, I also ran into cases where separate references to
the same form or form control no not return True for obj1 Is obj2
because they are separate instances of some wrapper object.
Things like this have tought me the lesson that it's not safe to
assume. Instead, write code that assumes the worst and will be
safe no matter what.

Well, controls are not properties. My uninformed guess is that this
has something to do with the hWnd issue, and is an artifact of the
resource limitations that controlled the basic design of Access
back in the days when a machine with 16MBs of RAM was more than
adequate to run Windows. I didn't use . syntax for my tests, but
used the form's Controls collection explicitly. I also tested with
Screen.PreviousControl (in A97 -- ActiveControl would accomplish
the same thing in A2K), and all returned different object pointers.

Yes it's inconsistent, but I don't see why that should lead you to
distrust Me.RecordsetClone.

It is similar because it's another case where 2 object references that refer
to the same object in all ways visible to the Access programmer do not refer
to the same instance. No, it's not a direct parallel, but it goes to pattern.

To raise another question, maybe the things you're thinking about
should be done with Me.Recordset instead of with
Me.RecordsetClone?

A good point actually. If it doesn't have to run in Access 97,
it's simpler to navigate using Me.Recordset - "Look Ma, no
bookmarks".

But why don't you worry about the same problem with that? Seems to
me that it's much more obvious that this will be the same object
each time

Because the definition of the property is more clear to me. With regard to
the clone, what I know for sure is that it's a clone, and not when the clone
was made. With regard to the .Recordset property, it's defined as being the
exact recordset instance the form is using to the extent that any actions on
that Recordset (such as navigation) actually affect the form directly and
immediately. There is no recordet instance that shares a current record
pointer with another, so they truly must be the same by definition. I guess
you would say something similar about RecordsetClone, but my consciousness of
the definition of that property did not include information that made the same
guarantee.

And in any event, I just can't conceive of anything you'd be
doing with other recordset where it would matter. If it *does*
matter, then I think you're mis-using it.

No?

Again, I agree and disagree. To me, using a With block or an
explicit variable is doing it properly. Using separate references
to the property is relying on a behavior you don't control, and
that's not so good.

I think it's inefficient, but I don't see that it's a problem. It's
inefficient to do something like:

Me!FirstControl.Enabled = [some Boolean test]
Me!SecondControl.Enabled = Me!FirstControl.Enabled
Me!ThirdControl.Enabled = Me!FirstControl.Enabled
Me!FourthControl.Enabled = Me!FirstControl.Enabled
Me!FifthControl.Enabled = Me!FirstControl.Enabled
[etc.]

You could use an object variable so the reference to the controls
collection doesn't have to be repeatedly resolved. And, of course,
more efficient still is a Boolean variable that you use in all
cases.

Using WITH (obviously superior to an object variable, in my
opinion) is efficient, because the reference is resolved once and
only once, which makes for efficient, reliable code.

Not only that, but it makes the code more immune to changes in the object type
of the item used in the With block, even without breaking early binding in
many cases.
And yes, there are cases where it is necessary to use a Recordset

Clone for something more convoluted that what you would use a
simple With block for does come up. This happens, for instance,
in the code to trap ODBC error messages that occur during updates
through a bound, continuous form. You do the update through code
via the RecordsetClone, so the change is reflected in the form
afterward without a requery. You can't use the <form>.Recordset
because you need to preserve the current record and dirty state of
the form of the update in code fails with an error. . . .
Is that use of the .RecordsetClone Kosher? It sounds like a case
where you really want a transaction, instead, no? And where a
.Refresh would update the data display?

Actually, I agree it's not Kosher, but it's the solution provided on the MS
Support site, and it has been the only way for me to wriggle out of some very
sticky problems in a couple of situations.
That kind of thing sounds not quite right to me, but I don't do
that kind of development.

. . . Another
example is where you want to loop and aggregate the rows in the
form's snapshot recordset consistently with that recordset even if
records may have been added by another user that your recordset is
not aware of.
Why would it make a difference if you use the .RecordsetClone or
the .Recordset? Because of updates made by another user that will
eventually show up in the .Refresh interval?

It has nothing to do with that. You use RecordsetClone for navigation so as
not to mess up the current record pointer and scroll position in the form.
Any navigation using .Recordset actually navigates on the form itself.
Sounds dangerous all around. I'm glad I never have to use the
.RecordsetClone in that manner, but my reservations have nothing to
do with fears that it won't be the same recordset. Indeed, the two
methods you've outlined could not possibly work unless it were the
case that, by design, Me.RecordsetClone always returned the same
recordset.

Except that my code never makes more than one direct call to Me.RecordsetClone
within a procedure, so I'm not relying on that behavior. I agree that you've
made the case that it would be reliable to rely on it, though I think we both
agree performance would be better using the With block or variable than
reusing the property.

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<ah********************************@4ax.com>:

On Tue, 06 Jan 2004 01:11:04 GMT, dX********@bway.net.invalid
(David W. Fenton) wrote:

[]

Another way of saying this is, if you expect your code to be
dealing with the same object at 2 different points, you should
use something in your own code to make sure this will be true
and not assume that some external code you didn't write will
provide this behavior for you.

It's a good basic principle, I agree.

But not because of a fear that a form property will suddenly be
changed by Microsoft to start behaving like a function. That
would majorly break all sorts of code, including the code written
by Access's wizards.

Not being familliar with this evidence, I did not come to that
conclusion. Do you agree that simply being a property is not
sufficient evidence that an object property will not return a new
instance each time it is referenced, and that should need to have
seen the other evidence that MS would not do that before trusting
the assumption?

What property of a control does the control example test? I don't
see any property in use.

In Access 97, I also ran into cases where separate references to
the same form or form control no not return True for obj1 Is
obj2 because they are separate instances of some wrapper object.
Things like this have tought me the lesson that it's not safe to
assume. Instead, write code that assumes the worst and will be
safe no matter what.

Well, controls are not properties. My uninformed guess is that
this has something to do with the hWnd issue, and is an artifact
of the resource limitations that controlled the basic design of
Access back in the days when a machine with 16MBs of RAM was more
than adequate to run Windows. I didn't use . syntax for my tests,
but used the form's Controls collection explicitly. I also tested
with Screen.PreviousControl (in A97 -- ActiveControl would
accomplish the same thing in A2K), and all returned different
object pointers.

Yes it's inconsistent, but I don't see why that should lead you
to distrust Me.RecordsetClone.

It is similar because it's another case where 2 object references
that refer to the same object in all ways visible to the Access
programmer do not refer to the same instance. No, it's not a
direct parallel, but it goes to pattern.

You're mixing things up. A RecordsetClone is an object that is a
property of a form, explicitly referred to. The object references
that don't match with controls are not generated from a property of
controls, except implicitly. If you set varVariable = Me!MyControl
you get the value of the control, but if you set ctlControl =
Me!MyControl, you get a pointer of control type. That is implicit
behavior that you have no control of, as you haven't chosen which
kind of data to return. In other words, you can't pick the property
used to return data that is assigned to your variable.

Me.RecordsetClone is, quite clearly, very different from that.

[]

And in any event, I just can't conceive of anything you'd be
doing with other recordset where it would matter. If it *does*
matter, then I think you're mis-using it.

No?

Again, I agree and disagree. To me, using a With block or an
explicit variable is doing it properly. Using separate
references to the property is relying on a behavior you don't
control, and that's not so good.

I think it's inefficient, but I don't see that it's a problem.
It's inefficient to do something like:

Me!FirstControl.Enabled = [some Boolean test]
Me!SecondControl.Enabled = Me!FirstControl.Enabled
Me!ThirdControl.Enabled = Me!FirstControl.Enabled
Me!FourthControl.Enabled = Me!FirstControl.Enabled
Me!FifthControl.Enabled = Me!FirstControl.Enabled
[etc.]

You could use an object variable so the reference to the controls
collection doesn't have to be repeatedly resolved. And, of
course, more efficient still is a Boolean variable that you use
in all cases.

Using WITH (obviously superior to an object variable, in my
opinion) is efficient, because the reference is resolved once and
only once, which makes for efficient, reliable code.

Not only that, but it makes the code more immune to changes in the
object type of the item used in the With block, even without
breaking early binding in many cases.

And that's a justification for using good practices that has
nothing to do with worries about some change in behavior in the
future.

[]

. . . Another
example is where you want to loop and aggregate the rows in the
form's snapshot recordset consistently with that recordset even
if records may have been added by another user that your
recordset is not aware of.

Why would it make a difference if you use the .RecordsetClone or
the .Recordset? Because of updates made by another user that will
eventually show up in the .Refresh interval?

It has nothing to do with that. You use RecordsetClone for
navigation so as not to mess up the current record pointer and
scroll position in the form. Any navigation using .Recordset
actually navigates on the form itself.

I thought you were talking about edits? Navigation is necessary
only if you jump from record to record to do your edits. Right?
Couldn't you create a filtered recordset from Me.Recordset, operate
on that, and the changes would appear in the oiginal recordset,
without any effect on current record pointer?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

OK, let's undo this tangled mess we've managed to work ourselves into. I can
no longer easily work out what parts of the message follow from what, so...

Here's where I stand at this point.

1. I get that there is sufficient evidence to suggest that it is safe to
reuse <form>.RecordsetClone within a procedure, though we both seem to agree
it's not efficient or good style.
2. I want to know if you agree that simply being a property is not sufficient
grounds to assume an object-tyep class member will not generate a new object
instance each time it is referenced. I feel that since there's noting about a
property that guarantees anything about its implementation, one should not
make this assumption.
3. Regarding the uniqueness of form and control references having anything to
do with the behavior of properties, I was simply trying to show that there are
places where things one might assume referred to the same object instance do
not do so. I was not saying there were properties involved in that case, just
that it is another case in which, like a property, one would expect to see the
same thing be referred to by the same object instance, and it isn't. I would
not be surprised or dismayed to find a property demonstrating similar
behavior.
4. I think we are both in agreement that good not to make access the same
object property of an object multiple times in the same procedure on style and
performance grounds alone, whether it is technically safe or not in a given
case. I think, besides the style issue, it's best to treat all such cases as
unsafe (just to be safe).

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<03********************************@4ax.com>:

[]

2. I want to know if you agree that simply being a property is
not sufficient grounds to assume an object-tyep class member will
not generate a new object instance each time it is referenced. I
feel that since there's noting about a property that guarantees
anything about its implementation, one should not make this
assumption.
I don't know that you've presented any examples of properties that
are not reliable. Your one example is a case of generating a return
from a control that is implicitly coerced by VBA to the appropriate
type for the variable you're storing it in. You have no control
there, so I would say, yes, it's got some unreliability going on
there. But it only *matters* if you need to do something that
assumes obj1 = obj2, and that's that the *pointer* is equal, that
the two structures are indentical, not that what you're *operating
on* is identical. It's only the pointer that differs in your
example, and I can't think of a circumstance in general,
run-of-the-mill Access programming where I give a rat's ass about
that.

I don't know of any actual *properties* of any kind of object that
are unreliable. Do you?
3. Regarding the uniqueness of form and control
references having anything to do with the behavior of properties,
I was simply trying to show that there are places where things one
might assume referred to the same object instance do not do so. .
.
But you can operate on those two object variables as though they
were the same *if* you only care about what they point *to*, not
what the pointers *are*. There is no issue as there is with a
recordset.
. . . I
was not saying there were properties involved in that case, just
that it is another case in which, like a property, one would
expect to see the same thing be referred to by the same object
instance, and it isn't. I would not be surprised or dismayed to
find a property demonstrating similar behavior.
Well, yes, I was suprised that the object pointers were not the
same. But I can't conceive of a real programming situation where it
would cause a problem. When would I really need to compare the
*pointers* to a control? The control is the object that matters,
and the pointer is a structure. The fact that I get different
pointers does not change that they still point to the same thing
insofar as *anything significant I'd ever want to do with that
specific thing*.

Recordsets are independent structures, independent of the
underlying data. There it definitely matters. But it's pretty clear
that Me.RecordsetClone and Me.Recordset are going to always return
the exact same object pointer, and will always be the same.
4. I think we are both in agreement that good not to make access
the same object property of an object multiple times in the same
procedure on style and performance grounds alone, whether it is
technically safe or not in a given case. I think, besides the
style issue, it's best to treat all such cases as unsafe (just to
be safe).

I don't see a reason for worrying about it being unsafe.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc