Clone, Findfirst, DAO vs ADO

[Guest]

I have the following code that works with DAO. This is used to retrieve
a record in a typical masterfile form.

Dim rs As Object

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

How would I do something similar using ADO?


Thanks in advance

George

 

[Guest]

Thanks!!!

Learnt a lot

George

 

[Albert D. Kallal]

First, in your code you REALLY REALLY REALLY should define your reocrdset
type:

Your code example IS ADO!!!!

There is NO find method in DAO, so, in fact, if your code is now working,
then you "object" actually IS a ADO reocrdset!!

Note that when you assign a recordset to a recortset var, it takes on the
type YOU defined. By using object, you have no idea, or clue as to what type
of object the recordset returns. So, in DAO, we get:


Dim rst As DAO.Recordset

set rst = me.RecordSetClone (note again, "recordsetclone" NOT
Reccord.Clone!!!!)

rst.FindFirst "[DbBoxId] = " & str(Nz(Me![ComXxFind], 0))
If rst.noMatch = False Then
Me.Bookmark = rst.Bookmark
End If

If you do not use recordSetClone, then you don't even have to use the book
mark. eg:

Dim rst As DAO.Recordset

set rst = me.RecordSet (in this case NO CLONE!)

rst.FindFirst "[DbBoxId] = " & str(Nz(Me![ComXxFind], 0))

Since we are executing directly on the forms recordset, then you don't even
have to use the bookmark.
Likey the above of set rst = me.RecordSet.Clone would also work. (but you
could try it..if the form does
not move..then you WOULD need the bookmark code).

And:

Dim rst As ADODB.Recordset

rst.Find "[DbBoxId] = " & str(Nz(Me![ComXxFind], 0))
If rst.Find.EOF = False Then
Me.Bookmark = rst.Bookmark
End If


As mentioned, DAO does NOT have a find method, but ADO does!....so, your
example code is ALREADY in ADO...

note that both recredset, and recordsetClone can be stuffed into a DAO, or
ADO reocrdset (the choice of ado, or dao being returned is going to be based
on how you define the reocrdset. And, even more amazing is the reverse is
true!! You can stuff a dao, or ado reocrdset into a forms source!!

set me.RecordSet = rstDaoRecordSet

 

[Dirk Goldgar]

First, in your code you REALLY REALLY REALLY should define your
reocrdset type:

Your code example IS ADO!!!!

There is NO find method in DAO, so, in fact, if your code is now
working, then you "object" actually IS a ADO reocrdset!!

Note that when you assign a recordset to a recortset var, it takes on
the type YOU defined. By using object, you have no idea, or clue as
to what type of object the recordset returns. So, in DAO, we get:


Dim rst As DAO.Recordset

set rst = me.RecordSetClone (note again, "recordsetclone" NOT
Reccord.Clone!!!!)

rst.FindFirst "[DbBoxId] = " & str(Nz(Me![ComXxFind], 0))
If rst.noMatch = False Then
Me.Bookmark = rst.Bookmark
End If

If you do not use recordSetClone, then you don't even have to use the
book mark. eg:

Dim rst As DAO.Recordset

set rst = me.RecordSet (in this case NO CLONE!)

rst.FindFirst "[DbBoxId] = " & str(Nz(Me![ComXxFind], 0))

Since we are executing directly on the forms recordset, then you
don't even have to use the bookmark.
Likey the above of set rst = me.RecordSet.Clone would also work. (but
you could try it..if the form does
not move..then you WOULD need the bookmark code).

And:

Dim rst As ADODB.Recordset

rst.Find "[DbBoxId] = " & str(Nz(Me![ComXxFind], 0))
If rst.Find.EOF = False Then
Me.Bookmark = rst.Bookmark
End If


As mentioned, DAO does NOT have a find method, but ADO does!....so,
your example code is ALREADY in ADO...

Well, but Albert, the code George posted (which is the sort of code
produced by the combo box wizard) is using the FindFirst method, not the
Find method -- and the FindFirst method is DAO, not ADO. On the other
hand, the code tests for rs.EOF to see if a match was found, and that's
ADO, not DAO. So what we can safely say is that the code,
wizard-generated though it may be, is erroneous for both DAO and ADO.

 

[Albert D. Kallal]

Well, but Albert, the code George posted (which is the sort of code
produced by the combo box wizard) is using the FindFirst method, not the
Find method -- and the FindFirst method is DAO, not ADO. On the other
hand, the code tests for rs.EOF to see if a match was found, and that's
ADO, not DAO. So what we can safely say is that the code,
wizard-generated though it may be, is erroneous for both DAO and ADO.

Actually, thanks for the heads up. When I read the code, my eyes saw
rs.FIND!!!

In other words, I read the message. While my points are helpful, ...I read,
and saw "find", and thus made my post on that assumption!!

So, in fact, my point about the original posters code being ADO is WRONG!

(sorry George!).

Thanks for the heads up!!

So, your original example was in fact DAO........I just read the text
wrong!!

Anyway, I did manage to post both examples..and my points about the forms
reocrdset returning the type based on the kind of recordset you define do
hold!!...but, I was wrong.

Thanks to eagle eyes Dirk for pointing this out!......and again my sorry to
George for being wrong!!

 

[Marshall Barton]

Well, but Albert, the code George posted (which is the sort of code
produced by the combo box wizard) is using the FindFirst method, not the
Find method -- and the FindFirst method is DAO, not ADO. On the other
hand, the code tests for rs.EOF to see if a match was found, and that's
ADO, not DAO. So what we can safely say is that the code,
wizard-generated though it may be, is erroneous for both DAO and ADO.

Now I'm confused too ;-\

rs.EOF is what I've always used to check for EOF in a DAO
recordset.

I believe the wizard code is actually correct.

 

[Dirk Goldgar]

Now I'm confused too ;-\

rs.EOF is what I've always used to check for EOF in a DAO
recordset.

I believe the wizard code is actually correct.

rs.EOF *is* what you use to check for EOF in a DAO recordset. However,
it is not what you use to check for a failure to find a match after
calling one of the "Find" methods. You have to use rs.NoMatch for that,
because a failed call to FindFirst, FindNext, etc., doesn't leave the
recordset positioned at EOF. (I'm actually sure you know this, but
didn't read the original message carefully.) The wizard code is just
wrong.

 

[Marshall Barton]

rs.EOF *is* what you use to check for EOF in a DAO recordset. However,
it is not what you use to check for a failure to find a match after
calling one of the "Find" methods. You have to use rs.NoMatch for that,
because a failed call to FindFirst, FindNext, etc., doesn't leave the
recordset positioned at EOF. (I'm actually sure you know this, but
didn't read the original message carefully.) The wizard code is just
wrong.

Right! I was reading it as checking for EOF, not checking
for FindFirst failing to find anything.

I do know that DAO EOF is irrelevant in this situation, I
just(?) have to read more carefully ;-(

Thanks for clearing up my befuddled thinking.