Using Getrows on RecordsetClone

[cefrancke]

I would like to get a certain number of rows of data from a subform
datasheet.

I figured using Me.Forms.RecordsetClone.GetRows() to get the data in a
variant.


Access 2000 help shows....

Dim rs As Recordset
Set rs = Me.Form.RecordsetClone

I get a "Data type mismatch" error, even if I use ADODB.Recordset.
Why is the help wrong, or is it?

Continuing...

I used the following...

Dim aID as Variant
With Me.RecordsetClone

aID = .GetRows(10)

end with


This worked, but it gives me all the fields, starting at what seems to
be a random row.
I would like to get say 10 records starting from the current record or
even an actual record number...

So I tried....

aID = .GetRows(10, Me.Bookmark)

but I get "invalid argument", or even....

aID = .GetRows(10, , Fields:=Array(2)) 'Args are optional

Near straight out of a Ken Getz reference, "Named arg not found"


What's going on here?


TIA

 

[Dirk Goldgar]

I would like to get a certain number of rows of data from a subform
datasheet.

I figured using Me.Forms.RecordsetClone.GetRows() to get the data in a
variant.


Access 2000 help shows....

Dim rs As Recordset
Set rs = Me.Form.RecordsetClone

I get a "Data type mismatch" error, even if I use ADODB.Recordset.
Why is the help wrong, or is it?

The help is not wrong, but unless you're working in an ADP, the form's
recordset is a DAO.Recordset, not an ADODB.Recordset. So you need to
declare rs accordingly:

Dim rs As DAO.Recordset

If you haven't already done so, making such a declaration requires that
you set a reference to the Microsoft DAO 3.6 Object Library.

Continuing...

I used the following...

Dim aID as Variant
With Me.RecordsetClone

aID = .GetRows(10)

end with


This worked, but it gives me all the fields, starting at what seems to
be a random row.
I would like to get say 10 records starting from the current record or
even an actual record number...

Synchronize the recordset's bookmark with that of the form:

With Me.RecordsetClone

.Bookmark = Me.Bookmark

aID = .GetRows(10)

End With

So I tried....

aID = .GetRows(10, Me.Bookmark)

but I get "invalid argument", or even....

aID = .GetRows(10, , Fields:=Array(2)) 'Args are optional

Near straight out of a Ken Getz reference, "Named arg not found"


What's going on here?

Those arguments apply to the GetRows method of an ADODB Recordset, but
the GetRows method of a DAO recordset doesn't have them. It has only
the one argument for specifying the number of rows.

 

[cefrancke]

I would like to direct you to the help reference from my Access 2000
installation.
This is where I got that crazy idea about RecordsetClones being similar
to an ADODB.Recordset

Thanks for your advice.


Bookmark Property Example

To test the following example with the Northwind sample database, you
need to add a command button named cmdFindContactName to the Suppliers
form, and then add the following code to the button's Click event. When
the button is clicked, the user is asked to enter a portion of the
contact name to find. If the name is found, the form's Bookmark
property is set to the Recordset object's ADO Bookmark property, which
moves the form's current record to the found name.

Private Sub cmdFindContactName_Click()
Dim rst As adodb.Recordset, strCriteria As String
strCriteria = "[ContactName] Like '*" & InputBox("Enter the first
few letters of the name to find") & "*'"

Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "No entry found"
Else
Me.Bookmark = rst.Bookmark
End If
End Sub

 

[Dirk Goldgar]

I would like to direct you to the help reference from my Access 2000
installation.
This is where I got that crazy idea about RecordsetClones being
similar to an ADODB.Recordset

Thanks for your advice.


Bookmark Property Example

To test the following example with the Northwind sample database, you
need to add a command button named cmdFindContactName to the Suppliers
form, and then add the following code to the button's Click event.
When the button is clicked, the user is asked to enter a portion of
the contact name to find. If the name is found, the form's Bookmark
property is set to the Recordset object's ADO Bookmark property, which
moves the form's current record to the found name.

Private Sub cmdFindContactName_Click()
Dim rst As adodb.Recordset, strCriteria As String
strCriteria = "[ContactName] Like '*" & InputBox("Enter the first
few letters of the name to find") & "*'"

Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "No entry found"
Else
Me.Bookmark = rst.Bookmark
End If
End Sub

I'm afraid that example from the help file is just plain wrong. In the
Northwind.mdb sample database, that code won't even compile unless you
remove the "adodb." qualifier from the declaration of rst. That's
because an ADODB recordset has no FindFirst method or NoMatch property.
Those belong to the DAO recordset. If you do remove the "adodb."
qualifier, or replace it with "DAO.", the code will compile and work
fine, because the form's recordset is a DAO recordset.

I'm sorry, but you've been misled by an erroneous help entry.

 

[cefrancke]

Thanks for clearing that up. One can go crazy (I did for 4 hours)
wondering what they did wrong.

I guess there's no way of "casting" that RecordsetClone to an
ADODB.Recordset? No?

Thanks again

P.S. Are there any plans (by MS) to get all Access recordsets, Clones
and all, to an ADODB state?

 

[Douglas J. Steele]

P.S. Are there any plans (by MS) to get all Access recordsets, Clones
and all, to an ADODB state?

I doubt it. ADO is a dead technology: it's been replaced by ADO.Net which,
despite the name, has very little in common with ADO.

DAO was developed specifically for Jet databases, while ADO was a more
generic approach. Despite earlier appearances, Microsoft still uses DAO
internally.

Of course, the above is only my interpretation of the situation, and
shouldn't be taken as official!

 

[Dirk Goldgar]

Thanks for clearing that up. One can go crazy (I did for 4 hours)
wondering what they did wrong.

The help file is full of good information, but the occasional error is
extremely annoying.

I guess there's no way of "casting" that RecordsetClone to an
ADODB.Recordset? No?

Not in any practical way, I think. Why would you want to? Just to get
those extra arguments in the GetRows method? It doesn't seem worth it
to me. I don't know of anything that can be done to a Jet database with
ADO that can't be done with DAO.

P.S. Are there any plans (by MS) to get all Access recordsets, Clones
and all, to an ADODB state?

I think when MS wrote the help file for Access 2000, they were really
thinking that ADO would eventually replace DAO. That hasn't happpened,
and isn't going to happen now. So I wouldn't concern yourself with it.
Maybe at some point in the future, the data interface layer in Access
will be replaced with something else -- ADO.Net, or some even later
object library -- but it won't be "classic" ADO, and it won't happen
very soon.

 

[cefrancke]

Thanks gentlemen,
For all the insight. I enjoy the theoretical talk.

getrows, ADO style, lets you specify what fields and what record to
start from when it retrieves the records.
getrows DAO, gives you all the fields and you have to set the bookmark
of the Clone to match the subform bookmark.
With DAO I have to know the ordinal of the ID field and ensure it is
there. With ADO, I can explicitly name it, no worries.

I'm working on a method to allow highlighting/selecting records and
being able to print the highlighted records in a subform.
Starting to look good so far, but all it takes is one "hitch" to throw
it off.

I'm globally saving the Sel info on the "mouse up" (after selecting
rows) event and then when they click the print button, it knows where
to start printing from and how many records, etc.

No one has said it cant be done, so onward I go....

Cheers

 

[Dirk Goldgar]

Thanks gentlemen,
For all the insight. I enjoy the theoretical talk.

getrows, ADO style, lets you specify what fields and what record to
start from when it retrieves the records.
getrows DAO, gives you all the fields and you have to set the bookmark
of the Clone to match the subform bookmark.

Settig the bookmark strikes me as so trivial as not to be worth
mentioning. I guess being able to specify only certain columns to get
may be handy, but it seems to me you can either get them all and just
ignore the columns you don't want or, if it's really that important to a
particular application, loop through the recordset and load the array
manually.

With DAO I have to know the ordinal of the ID field and ensure it is
there. With ADO, I can explicitly name it, no worries.

I'm not following you here. Could you explain what you mean?

I'm working on a method to allow highlighting/selecting records and
being able to print the highlighted records in a subform.
Starting to look good so far, but all it takes is one "hitch" to throw
it off.

I'm globally saving the Sel info on the "mouse up" (after selecting
rows) event and then when they click the print button, it knows where
to start printing from and how many records, etc.

No one has said it cant be done, so onward I go....

It seems pretty straightforward. What do you need the array for?

 

[cefrancke]

Thank you for making me think about what I was doing.
I assume you are wondering why didn't I just use the RecordSet clone.

I just didn't realize I could manipulate the Clone.

Then can you tell me, if I know a certain record position I want to go
to, say the 5th record, how do I use the bookmark of the Clone to get
there,
or should I use AbsoluteProperty(help recommends the bookmark over this
one)?
That is to say, how do I move to a particular record position using
Bookmark?

TIA

 

[Dirk Goldgar]

Thank you for making me think about what I was doing.
I assume you are wondering why didn't I just use the RecordSet clone.

I just didn't realize I could manipulate the Clone.

Then can you tell me, if I know a certain record position I want to go
to, say the 5th record, how do I use the bookmark of the Clone to get
there,
or should I use AbsoluteProperty(help recommends the bookmark over
this one)?
That is to say, how do I move to a particular record position using
Bookmark?

Thinking about records in terms of their positions is *generally* a bad
idea, because the position of a record in a recordset or on a form is
not inherent in the record. However, in this case all you have to work
with is the position of the record as given by the form's SelTop
property, while at the same time you can count on the form's
RecordsetClone to have the same records, in the same order, as the form
itself. So in this case, I think the AbsolutePosition property is the
one to use. Just bear in mind that for SelTop, the form's first record
is 1, while for a recordset the first record has an AbsolutePosition of
0.

 

[cefrancke]

Thanks Dirk,
That's exactly what I did.
Positioning seems to me to be important here because of the "Selection"
rectangle.

Thanks for all the nudging, confirmations (or is it affirmations?) and
advice.

Cheers,

Christopher