Convert this DAO bookmark code to ADO

[DougP]

Dim SyncCriteria As String
Dim f As Form, rs As Recordset

'Define the from object and recordset object for the AutoCAD form
Set f = Forms(Screen.ActiveForm.FormName)
Set rs = f.RecordsetClone

' define the criteria used for the sync
SyncCriteria = "[ProjectNumber]='" & Me![Combo2] & "'"

' find the corresponding record in the Parts table
rs.Find SyncCriteria
f.Bookmark = rs.Bookmark
-----------------------
I have used this forever and it works great if I use DAO.
but in Access 2000 and beyond ADO is the default.

Two stubling blocks come up "Recordsetclone" gives error
Set rs = f.RecordsetClone <<<< Error "Type mismatch"
and there is a problem on the last two lines
rs.Find SyncCriteria
f.Bookmark = rs.Bookmark

Any ideas
TIA



DougP, MCP, A+

 

[RoyVidar]

You are correct that ADO is the default library for recordset manipulation in 2000+ versions, but at least in 2000 and xp, the form recordsets are still DAO, unless it's a SQL backend (or as I believe we discussed in a previous thread) the the form recordset is set at runtime thru assigning an ADO recordset to the forms recordset.

The error assigning the recordset migth (at least) have two reasons.

1 - not explicitly declared recordset variable - will assume whatever is highest in the reference list, if both ADO and DAO is present

2 - this recordset is DAO, and you're assigning it to a ADO recordset.

Try checking what kind of recordset is used, for instance thru something like this:

[tt]dim rs as object
set rs=me.recordsetclone
if typeof rs is dao.recordset
msgbox "DAO"
else if typeof rs is ado.recordset then
msgbox "ADO"
else
msgbox "Don't know"
end if[/tt]

And act accordingly (note - for this to work, the DAO library must be checked).

- similar code as you've posted works on my setup with ADO recorsets.

- I use DAO for all form manipulation, has the DAO library ckecked, but use ADO for other recordset manipulation. And use explicit declarations:

[tt]dim rsDao as DAO.recordset
dim rsADO as ADODB.recordset[/tt]

Roy-Vidar

 

[RoyVidar]

Hmmmm - might perhaps disreagard most of the above, and change from recordsetclone to recordset. (Booted up my a2k machine, and it doesn't like assigning the recordsetclone to rs)

Roy-Vidar

 

[DougP]

There is just a "Clone" item in ADO

But what I really need is to have a ADO bookmark
Set a forms recordsource to an ADO Bookmark
Or is this not possible
so I can use the above code but with ADO

DougP, MCP, A+

 

[RoyVidar]

Yes, the recorsetclone seems available in xp, but not a2k.

The following code works in my a2k setup, as long as the form recordset really is ADO:

[tt]dim rs as adodb.recordset
' 1 - using recordset
set rs = me.recordset
rs.find SyncCriteria
if not rs.eof
me.bookmark = rs.bookmark
end if

' 2 - using the clone method of the recordset
set rs = me.recordset.clone
rs.find SyncCriteria
if not rs.eof
me.bookmark = rs.bookmark
end if[/tt]

If neither of the above works - are you really sure you have an ADO form recordset?

Type mismatch error on the recordset assigning can indicate several things, including rs being a ADO recordset variable and the forms recordset being DAO. Test it with the previous given code.

If the form recordset is DAO you cannot assign it to an ADO rcordsetvariable, you'll need a DAO recordset variable (or you could do as is often done by the wizards, declare rs as object).

Roy-Vidar