Bookmark property works when stepping through code in debug, but not when run

[figaro]

In the code snippet below I'm attempting to set the .Bookmark property
of a Form to match the bookmark of an ADO recordset:

Private Sub Form_Load()
Dim rst As ADODB.Recordset
Dim mctlFieldName As Control
Set rst = New ADODB.Recordset
Set mctlFieldName = Forms!frmCDs!cboFiledUnder
rst.Open Me.RecordSource, CurrentProject.Connection, _
adLockReadOnly, adCmdTable
Set rst = rst.Clone(adLockReadOnly)
rst.Find "ID=" & mctlFieldName.Value
Me.Bookmark = rst.Bookmark

When the code is run by opening the Form, I get an error:

Not a valid bookmark.

The error occurs on the line that starts Me.Bookmark. The odd thing
is that when I step through the exact same code in Debug I do not get
the error and the form loads properly.

I'm using Office 2003 SP3 on Vista SP1. This code worked before I
changed all my recordsets from DAO to ADO, so perhaps that is a clue?

Thank you if you can shed some light.

John

 

[Stuart McCall]

In the code snippet below I'm attempting to set the .Bookmark property
of a Form to match the bookmark of an ADO recordset:

Private Sub Form_Load()
Dim rst As ADODB.Recordset
Dim mctlFieldName As Control
Set rst = New ADODB.Recordset
Set mctlFieldName = Forms!frmCDs!cboFiledUnder
rst.Open Me.RecordSource, CurrentProject.Connection, _
adLockReadOnly, adCmdTable
Set rst = rst.Clone(adLockReadOnly)
rst.Find "ID=" & mctlFieldName.Value
Me.Bookmark = rst.Bookmark

When the code is run by opening the Form, I get an error:

Not a valid bookmark.

The error occurs on the line that starts Me.Bookmark. The odd thing
is that when I step through the exact same code in Debug I do not get
the error and the form loads properly.

I'm using Office 2003 SP3 on Vista SP1. This code worked before I
changed all my recordsets from DAO to ADO, so perhaps that is a clue?

Thank you if you can shed some light.

John

Your last paragraph says it all. An Access form's recordset is a DAO
recordset. Since DAO and ADO recordsets are not compatible, you get the not
valid error. If you want to persue this technique, you'll have to recode for
DAO. Most likely not the answer you wanted, but...

 

[Albert D. Kallal]

Him, my best guess is that you using the conneciton object...

why not try:

Private Sub Form_Load()

Dim rst As ADODB.Recordset
Dim mctlFieldName As Control

Set rst = me.Recordset
Set mctlFieldName = Forms!frmCDs!cboFiledUnder
rst.Find "ID=" & mctlFieldName.Value
Me.Bookmark = rst.Bookmark

Try using the SAME recordset as the forms object. Access will actually
return a dao, or ado reocrdset depending on the type of reocrdset you stuff
it into. And, even better is if you move the recordset to a new record...the
form will follow (you actually don't need the bookmark code at all). If you
use reocrdSetClone (not me.clone, but me.ReocrdSetClone).

Of couse the real question at the end of the day is why convert everything
to ado? Ado is on it's way out and have been replaced by ado.net.

 

[figaro]

Your last paragraph says it all. An Access form's recordset is a DAO
recordset. Since DAO and ADO recordsets are not compatible, you get the not
valid error. If you want to persue this technique, you'll have to recode for
DAO. Most likely not the answer you wanted, but...

Thanks Stuart. I was not aware that forms still had native DAO behind
them.

 

[figaro]

Him, my best guess is that you using the conneciton object...

why not try:


Dim rst As ADODB.Recordset
Dim mctlFieldName As Control

Set rst = me.Recordset
Set mctlFieldName = Forms!frmCDs!cboFiledUnder
rst.Find "ID=" & mctlFieldName.Value
Me.Bookmark = rst.Bookmark

Try using the SAME recordset as the forms object. Access will actually
return a dao, or ado reocrdset depending on the type of reocrdset you stuff
it into. And, even better is if you move the recordset to a new record...the
form will follow (you actually don't need the bookmark code at all). If you
use reocrdSetClone (not me.clone, but me.ReocrdSetClone).

Of couse the real question at the end of the day is why convert everything
to ado? Ado is on it's way out and have been replaced by ado.net.

Thanks for the suggestion. And as to ado.net it seems I'm behind the
times, working as I am from an Access 2002 manual.

ado.net, gee whiz something new to learn, I was afraid I'd run out...
;-)

 

[Stuart McCall]

Try using the SAME recordset as the forms object. Access will actually
return a dao, or ado reocrdset depending on the type of reocrdset you
stuff it into.

In the hope of learning something new, how do you change a form's
RecordsetClone from DAO to ADO?

(not that I'd ever do that, just curious)

 

[Albert D. Kallal]

In the hope of learning something new, how do you change a form's
RecordsetClone from DAO to ADO?

(not that I'd ever do that, just curious)

Actually, I was not 100% clear. If you set the forms reocrdsource to an
adoReocrdset, then

me.ReocrdSet, and me.ReocrdSetClone and me.Recordset.Clone will all return
an ado reocrd set.

So, if your form is bound, or even unbound you can simply create a reocrdset
in code, and stuff it right into the form (by the way, since access 2002,
you can do this for a list/combo box also! - dao or ado works).

Anway, say in forms on load you go:

Dim rstAD As New ADODB.Recordset

rstAD.Open "tblAnswers", CurrentProject.AccessConnection

Set Me.Recordset = rstAD

At this point in time, me.ReocrdSet, me.RecordSetClone, me.recordset.Clone
will all return an ado reocrdset.

So, what I was saying if the form is based on a ado reocrdset, then the
standard properties such as me.ReocrdSet will return ado reocrdsets.

My post hints that you can stuff "me.ReocrdSetClone" into either type of
reocrdset (and access will change the reocrdset type on the fly -- this is
wrong and it does not convert on the "fly". However, the form **does**
return an ado reocrdset if that's what your form is based on. So a form can
return either type of reocrdset.

 

[Stuart McCall]

Actually, I was not 100% clear. If you set the forms reocrdsource to an
adoReocrdset, then

me.ReocrdSet, and me.ReocrdSetClone and me.Recordset.Clone will all return
an ado reocrd set.

So, if your form is bound, or even unbound you can simply create a
reocrdset in code, and stuff it right into the form (by the way, since
access 2002, you can do this for a list/combo box also! - dao or ado
works).

Anway, say in forms on load you go:

Dim rstAD As New ADODB.Recordset

rstAD.Open "tblAnswers", CurrentProject.AccessConnection

Set Me.Recordset = rstAD

At this point in time, me.ReocrdSet, me.RecordSetClone, me.recordset.Clone
will all return an ado reocrdset.

So, what I was saying if the form is based on a ado reocrdset, then the
standard properties such as me.ReocrdSet will return ado reocrdsets.

My post hints that you can stuff "me.ReocrdSetClone" into either type of
reocrdset (and access will change the reocrdset type on the fly -- this is
wrong and it does not convert on the "fly". However, the form **does**
return an ado reocrdset if that's what your form is based on. So a form
can return either type of reocrdset.

Well I did learn something new. I wasn't aware that you could do that (I
knew you could with DAO). As I said it's not something I'd ever do, being a
confirmed DAO user, but its certainly worth knowing. Thanks Albert.

 

[David W. Fenton]

And as to ado.net it seems I'm behind the
times, working as I am from an Access 2002 manual.

ADO.NET is not usable in Access, and probably never will be.

ADO was MS's generic data access interface, introduced into Access
2000 in 1999. MS went on a campaign to try to convince developers to
switch from ADO to DAO, but most developers were smart enough to
realize that for Jet data (and for data accessed via Jet, e.g., all
ODBC data sources), DAO was simply much better than ADO. In the end,
MS realized its mistake and reversed course. ADO is no longer the
preferred data interface, except for certain kinds of operations
with databases via OLEDB interfaces (ADO is a wrapper around OLEDB),
which mostly means SQL Server, and in Access Data Projects (which
work only with SQL Server).

In short, in 2000, there was no real justification for converting
ADO code to DAO, though MS was putting out a lot of propaganda to
make developers think there was some magical benefit to doing so.

In 2008, there is still no justification whatsoever, and it's really
not very wise, given that classic ADO exists only in Access and
classic ASP.

 

[figaro]

ADO.NET is not usable in Access, and probably never will be.

ADO was MS's generic data access interface, introduced into Access
2000 in 1999. MS went on a campaign to try to convince developers to
switch from ADO to DAO, but most developers were smart enough to
realize that for Jet data (and for data accessed via Jet, e.g., all
ODBC data sources), DAO was simply much better than ADO. In the end,
MS realized its mistake and reversed course. ADO is no longer the
preferred data interface, except for certain kinds of operations
with databases via OLEDB interfaces (ADO is a wrapper around OLEDB),
which mostly means SQL Server, and in Access Data Projects (which
work only with SQL Server).

In short, in 2000, there was no real justification for converting
ADO code to DAO, though MS was putting out a lot of propaganda to
make developers think there was some magical benefit to doing so.

In 2008, there is still no justification whatsoever, and it's really
not very wise, given that classic ADO exists only in Access and
classic ASP.

Thanks David for your clear and concise statement of some relevant and
interesting facts!

John