RecodSetClone vs RecordSet

[JP]

I'm using the standard filter and sort functionality in a datasheet view.
I'm finding that both the RecordSet and RecordsetClone properties are the
same even after filtering or sorting. I thought one or the other would
represent the contents and order for the datasheet after it had been
filtered or sorted. Instead they both represent the data as the datasheet
appears with no filter or sort.



What I want is a recordset that represents the order and contents of the
datasheet after filtering.



TIA, JP

 

[Dirk Goldgar]

I'm using the standard filter and sort functionality in a datasheet
view. I'm finding that both the RecordSet and RecordsetClone
properties are the same even after filtering or sorting. I thought
one or the other would represent the contents and order for the
datasheet after it had been filtered or sorted. Instead they both
represent the data as the datasheet appears with no filter or sort.

What I want is a recordset that represents the order and contents of
the datasheet after filtering.

I don't find this to be the case, JP. As a test, I opened a form in
datasheet view, and then executed this statement in the Immediate
Window:

?forms(0).recordset.recordcount
11

Then I went to the form, and applied a filter, after whic I went back to
the Immediate Window and got this:

?forms(0).recordset.recordcount
3

Would you mind posting the code you're using that seems to be giving you
the result you describe?

 

[JP]

I'm calling the following function from teh DblClick property of the record
selector column.

Function DisplayRecordCount(FormName As String)
Dim F As Form
Dim RS As Recordset

' Get the form and its recordset.
Set F = Forms(FormName)
Set RS = F.Recordset

Debug.Print RS.RecordCount
Debug.Print F.RecordsetClone.RecordCount

End Function

The final objective is to perform an operation on multiple selected records
from the view after the user has applied whatever filter and sorting they
would like. The SelTop and SelHeight properties are accurate, but I'm not
finding the correct records.


JP

 

[JP]

I just checked using the immediate window and I still get the same
recordcount both before and after the filter is applied.

Does it make a differnce that I'm using an SQL DB view as my data source?

JP

 

[Dirk Goldgar]

I'm calling the following function from teh DblClick property of the
record selector column.

Function DisplayRecordCount(FormName As String)
Dim F As Form
Dim RS As Recordset

' Get the form and its recordset.
Set F = Forms(FormName)
Set RS = F.Recordset

Debug.Print RS.RecordCount
Debug.Print F.RecordsetClone.RecordCount

End Function

I don't get it. Of course the recordsets returned by the form's
RecordsetClone and Recordset properties will be identical under these
conditions -- the RecordsetClone is a clone of the Recordset. But where
does the Filter or OrderBy property come into it? At the moment you run
this code, each of the two recordset objects will reflect the current
filtering and ordering of the form.

The final objective is to perform an operation on multiple selected
records from the view after the user has applied whatever filter and
sorting they would like. The SelTop and SelHeight properties are
accurate, but I'm not finding the correct records.

I'm afraid you'll have to show more of the code you're really trying to
use.

 

[Dirk Goldgar]

I just checked using the immediate window and I still get the same
recordcount both before and after the filter is applied.

Does it make a differnce that I'm using an SQL DB view as my data
source?

I don't know. I suppose there could be a latency period, but if you're
doing it manually I wouldn't expect that to be a factor. Please give me
the exact steps necessary to reproduce your results.

 

[JP]

Thanks for looking at this. I've tried to eliminate as many variables as
possible by creating a new project on a demo database.

Using the Northwind demo database on SQL Server 8; I opened a new project.
Using the form wizard I created a datasheet from of the "Products by
Category" view. After opening the form I run
"forms(0).Recordset.RecordCount" in the immediate window. The returned
value is 69. I then right click in a "Beverages" cell in the "Category"
column and select "Filter by Selection". This trims the datasheet down to
11 rows, but "?forms(0).Recordset.RecordCount" still returns 69.

JP

 

[JP]

Thanks for looking at this. I've tried to eliminate as many variables as
possible by creating a new project on a demo database.

Using the Northwind demo database on SQL Server 8; I opened a new project.
Using the form wizard I created a datasheet from of the "Products by
Category" view. After opening the form I run
"forms(0).Recordset.RecordCount" in the immediate window. The returned
value is 69. I then right click in a "Beverages" cell in the "Category"
column and select "Filter by Selection". This trims the datasheet down to
11 rows, but "?forms(0).Recordset.RecordCount" still returns 69.

JP

I just did all of the above using the Jet Access DB as opposed to SQL server
DB and I get the correct results. This is a bother seeing as how all my
real data lives in SQL 8.
When using MS SQL engine do I need to do a requery after changing the Filter
or sort order?
JP

 

[Dirk Goldgar]

I just did all of the above using the Jet Access DB as opposed to SQL
server DB and I get the correct results. This is a bother seeing as
how all my real data lives in SQL 8.
When using MS SQL engine do I need to do a requery after changing the
Filter or sort order?

Sorry, JP, I don't know offhand, since I really haven't spent any time
working with ADPs. Unfortunately, I don't have the time to research
this right now -- I've too much "real work" to do. I'll try to get back
to it within the next few days. If you find a solution in the mean
time, I'd appreciate it if you would post it. You may want to take your
questions to the <microsoft.public.access.adp.sqlserver> and/or
<<microsoft.public.access.odbcclientsvr> newsgroups, to see if you can
get an answer from one of the denizens there.

 

[JP]

Sorry, JP, I don't know offhand, since I really haven't spent any time
working with ADPs. Unfortunately, I don't have the time to research
this right now -- I've too much "real work" to do. I'll try to get back
to it within the next few days. If you find a solution in the mean
time, I'd appreciate it if you would post it. You may want to take your
questions to the <microsoft.public.access.adp.sqlserver> and/or
<<microsoft.public.access.odbcclientsvr> newsgroups, to see if you can
get an answer from one of the denizens there.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I've looked at this some more. It definitely works with mdb projects but
not with adp. I used the function in article 294202 (How to enumerate
selected form records in Access 2002) to do testing.

Good suggestion on posting to <microsoft.public.access.adp.sqlserver> I'll
give it a try.

When I get this working I'll post the solution.



Thanks, JP