DAO to ADODB Listbox conversion. Upsizing to SQL

[boyleyc]

Hi all

I have written a database in access and used ADODB recordsets all the
way through. The only recordsets that are not ADODB are the listbox
navigation code automatically generated by access 2003 as follows :

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![lstclient], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I have tried doing the above by putting the following code into an
ADODB

Function ADODB_Goto_Record(frm As Form, fieldname As String, val As
Variant)

Dim Clone_RST As ADODB.Recordset

Set Clone_RST = frm.RecordsetClone
With Clone_RST
If Not (.EOF And .BOF) Then
Do Until .EOF
'Records Found So Check for 'strCriteria'
.Find "[" & fieldname & "] = " & val

'When found Goto record
'Forms!FormName.Bookmark = .Bookmark
frm.Bookmark = .Bookmark
'Moves Record set to next one
'.MoveNext - not sure if i need this as i cant get in to
test it
Loop
Else
'No Records Found,
End If
.Close
End With

Set Clone_RST = Nothing
Now this gives a 'type mismatch' error at the recordset.clone line.

Now i understand that the form will try to insert a
DAO.recordeset.clone into Clone_Rst which is obviously the cause of
the error.

I have changed the record source of the form to an SQL statement but i
still cant clone the current form recordset in ADODB and i've spent
ages on this

Can anyone HELP????

Someone out there must have successfully upsized to an SQL backend
using list boxes to navigate with.

Thanks in advance

[Lyle Fairfield]

bo*****@gmail.com wrote in news:1172064363.189384.259710
@m58g2000cwm.googlegroups.com:

What is the recordsource of the form? How did you make the form's
recordsource an ADODB recordeset?

After we know that, we could be more helpful in suggesting how you could
use a list box to identify a record on the form and to move to it.

[boyleyc]

On 21 Feb, 13:54, Lyle Fairfield <[e-mail]:

[e-mail] wrote in news:1172064363.189384.259710
@m58g2000cwm.googlegroups.com:

What is the recordsource of the form? How did you make the form's
recordsource an ADODB recordeset?

After we know that, we could be more helpful in suggesting how you could
use a list box to identify a record on the form and to move to it.

Hi i didnt make it an ADODB recordset. I was just saying that the
recordsetclone is a mismatch as the forms recordsource is DAO.

I cant clone the forms recordset in an ADODB function.

All i did was change the frm.recordsource property from "invoice" to
SELECT * FROM Invoice;

Which didnt change anything really

Just need to know how to use bookmarks and recordsetclone to navigate
using a listbox in ADO

I tried on a blank database and still couldnt get it to work

[Lyle Fairfield]

On Feb 21, 9:51 am, [e-mail] wrote:

On 21 Feb, 13:54, Lyle Fairfield <[e-mail]:

[e-mail] wrote in news:1172064363.189384.259710
@m58g2000cwm.googlegroups.com:

What is the recordsource of the form? How did you make the form's
recordsource an ADODB recordeset?

After we know that, we could be more helpful in suggesting how you could
use a list box to identify a record on the form and to move to it.

Hi i didnt make it an ADODB recordset. I was just saying that the
recordsetclone is a mismatch as the forms recordsource is DAO.

I cant clone the forms recordset in an ADODB function.

All i did was change the frm.recordsource property from "invoice" to
SELECT * FROM Invoice;

Which didnt change anything really

Just need to know how to use bookmarks and recordsetclone to navigate
using a listbox in ADO

I tried on a blank database and still couldnt get it to work

Hmmm

The forms recordset is DAO?

And you want to set an ADODB recordset to the form recordset's clone?

as in:
Dim Clone_RST As ADODB.Recordset
Set Clone_RST = frm.RecordsetClone

And it gives you a type mismatch error?

I suggest you use

Dim Clone_RST As DAO.Recordset

and use FindFirst and NoMatch to locate the record.

[boyleyc]

On 21 Feb, 15:07, "Lyle Fairfield" <[e-mail]:

On Feb 21, 9:51 am, [e-mail] wrote:

On 21 Feb, 13:54, Lyle Fairfield <[e-mail]:

[e-mail] wrote in news:1172064363.189384.259710
@m58g2000cwm.googlegroups.com:

What is the recordsource of the form? How did you make the form's
recordsource an ADODB recordeset?

After we know that, we could be more helpful in suggesting how you could
use a list box to identify a record on the form and to move to it.

Hi i didnt make it an ADODB recordset. I was just saying that the
recordsetclone is a mismatch as the forms recordsource is DAO.

I cant clone the forms recordset in an ADODB function.

All i did was change the frm.recordsource property from "invoice" to
SELECT * FROM Invoice;

Which didnt change anything really

Just need to know how to use bookmarks and recordsetclone to navigate
using a listbox in ADO

I tried on a blank database and still couldnt get it to work

Hmmm

The forms recordset is DAO?

And you want to set an ADODB recordset to the form recordset's clone?

as in:
Dim Clone_RST As ADODB.Recordset
Set Clone_RST = frm.RecordsetClone

And it gives you a type mismatch error?

I suggest you use

Dim Clone_RST As DAO.Recordset

and use FindFirst and NoMatch to locate the record.- Hide quoted text -

- Show quoted text -

Hi thats the way it is at the minute but I need to upsize to an SQL
backend which doesnt support DAO

Therefor i need the code (not just for this database but any DB) that
allows me to do the code you suggested but using the ADODB method NOT
the DAO method

[Lyle Fairfield]

bo*****@gmail.com wrote in
news:11*********************@v33g2000cwv.googlegro ups.com:

On 21 Feb, 15:07, "Lyle Fairfield" <[e-mail]:

>On Feb 21, 9:51 am, [e-mail] wrote:

On 21 Feb, 13:54, Lyle Fairfield <[e-mail]:

[e-mail] wrote in news:1172064363.189384.259710
@m58g2000cwm.googlegroups.com:

What is the recordsource of the form? How did you make the form's
recordsource an ADODB recordeset?

After we know that, we could be more helpful in suggesting how
you could use a list box to identify a record on the form and to
move to it.

Hi i didnt make it an ADODB recordset. I was just saying that the
recordsetclone is a mismatch as the forms recordsource is DAO.

I cant clone the forms recordset in an ADODB function.

All i did was change the frm.recordsource property from "invoice"
to SELECT * FROM Invoice;

Which didnt change anything really

Just need to know how to use bookmarks and recordsetclone to
navigate using a listbox in ADO

I tried on a blank database and still couldnt get it to work

Hmmm

The forms recordset is DAO?

And you want to set an ADODB recordset to the form recordset's clone?

as in:
Dim Clone_RST As ADODB.Recordset
Set Clone_RST = frm.RecordsetClone

And it gives you a type mismatch error?

I suggest you use

Dim Clone_RST As DAO.Recordset

and use FindFirst and NoMatch to locate the record.- Hide quoted text
-

- Show quoted text -

Hi thats the way it is at the minute but I need to upsize to an SQL
backend which doesnt support DAO

Therefor i need the code (not just for this database but any DB) that
allows me to do the code you suggested but using the ADODB method NOT
the DAO method

How will you connect to the backend? If ODBC then I suspect that you will
have to do nothing to your original code at all.
I'm not sure where you got the notion "an SQL backend doesn't support
DAO." SQL Server is a database engine. It doesn't "support" any
connection technology. Connection technologies may have drivers that
allow them to connect to various Databases. DAO does not connect directly
to SQL Server. But neither does ADO; it uses an OLEDb layer.

Of course, if you going to move to an ADP application then you will not
be using DAO. But in that case your Form recordset will be an ADO
recordset, and so there will not be a type mismatch error as you report.

IMO you are solving a problem which is unlikely tocome up.

[David W. Fenton]

bo*****@gmail.com wrote in
news:11*********************@v33g2000cwv.googlegro ups.com:

I need to upsize to an SQL
backend which doesnt support DAO

Um, bookmark navigation via the Recordsetclone doesn't involve the
back end.

And if you're using an MDB to your SQL Server back end, then Jet is
involved, which means DAO is appropriate.

I wouldn't use ADO at all. Ever.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[boyleyc]

On 21 Feb, 19:43, "David W. Fenton" <[e-mail]>
wrote:

[e-mail] wrote innews:11*********************@v33g2000cwv.googleg roups.com:

I need to upsize to an SQL
backend which doesnt support DAO

Um, bookmark navigation via the Recordsetclone doesn't involve the
back end.

And if you're using an MDB to your SQL Server back end, then Jet is
involved, which means DAO is appropriate.

I wouldn't use ADO at all. Ever.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thanks

I was under the assumption that DAO couldnt be used with a connection
to the SQL server.

I tested the theory and DAO navigation works fine when connecting to
an SQL backend, so thanks to you all as i seem to be creating problems
that dont need solving :-)

regards