Record Count on a filtered .adp form?
Jane
the life of me figure out how to count the number of records in the
filtered recordset. With DAO, you could just use the RecordsetClone
to do it, but that's no good with ADO. The RecordCount property just
shows the total number of records returned, whether filtered or not.
Still, it must be possible--if you use the built-in Navigation
Buttons, it will show something like '1 of 6 (filtered)' when the
filter is on, '1 of 241' when it's off. So there's got to be a way
for me to figure out there are 6 records after the filter is applied.
Any gurus out there who've figured it out?
L Wells
Hi Jane,
There may be a much better way to achieve this (and hopefully someone
else will chime in if that's the case), but you can make use of an ADO
recordset's Filter property to mirror the filter you currently have on
the form from which the recordset has been 'cloned.'
The only downside, from my limited experience, is depending on how you
applied the filter to the form, you may have to pull apart the value
returned by the Form's filter property to build the value to pass to
the Recordset's filter, because the form's filter probably refers to
the underlying table or query / view / stored procedure etc, while the
recordset's filter won't recognise this as a valid reference.
For example, I currently have a form which I filtered via FIlter By
Selection that is based on a Stored Procedure in an AccXP ADP with an
SQL Server 2000 backend.
When I retrieve the form's filter property, it returns (without the
outside quote marks):
"((sp_alldbresources.obtype="Character"))"
sp_alldbresources is the Stored Procedure on which the form is based,
obtype is the field I filtered on and "Character" was the value I
filtered by.
If I wanted to pass this as a filter to an ADO recordset I created as
a recordset clone of this form, I would have to (to the best of my
knowledge) manipulate this value so the string I passed looked like:
"obtype='Character'"
This is because the recordset Filter would return an error if I tried
to pass it "sp_alldbresources.obtype='Character'".
It shouldn't be _too_ difficult to build a routine that pulls apart a
single form filter criteria to rebuild it as an appropriate recordset
filter criteria, but it gets a little more complicated if you are
facing the possibility of a form being filtered on more than one
criteria, which is certainly possible using Filter By Form.
Certainly it wouldn't be impossible to do, and it would then probably
be a good thing to approach as a public function to which you pass a
reference to the form or subform in question and from which you return
a valid recordset filter string value to pass to the recordset's
filter property.
And, just to round out the picture, once filtered using the same
criteria as used to filter the form from which the recordset was
cloned, recordset.RecordCount should return the same number of records
as the form is displaying in its filtered state.
Let me know by reply in the newsgroup if you're not (yet) up to
writing a function to cut up a form's Filter string and rebuild it as
a recordset's filter string and I'm sure me or some other helpful (and
hopefully more knowledgable) soul will almost certainly do what I / we
can to help.
All the best,
L Wells
Jane
what I want to do. This was supposed to be a quick-and-dirty little
db for in-house use, tying into our Help Desk software, and I decided
to do it in Access for the built-in filtering and the ability to
switch easily from form to datasheet view, so I may just wind up using
the built-in nav bar, junky as it is. I guess I was hoping for some
quick-and-easy property I'd overlooked. But if I have the time, I'll
try what you suggest 'cause it would certainly work. Thanks!
L Wells <urban...@ozemail.com.au> wrote in message news:<c73m0v4a03knqv3l6...@4ax.com>...
L Wells
>Makes sense to me, though it seems a little resource intensive to do
>what I want to do. This was supposed to be a quick-and-dirty little
>db for in-house use, tying into our Help Desk software, and I decided
>to do it in Access for the built-in filtering and the ability to
>switch easily from form to datasheet view, so I may just wind up using
>the built-in nav bar, junky as it is. I guess I was hoping for some
>quick-and-easy property I'd overlooked. But if I have the time, I'll
>try what you suggest 'cause it would certainly work. Thanks!
Hi Jane,
I agree it seems like a lot of work by comparison to what you can
apparently expect of the same operation using DAO.
Of course, you could get a little lateral about the situation, which
might make things a little easier for you.
For example, if your user(s) are not particularly married to the
process of right-clicking for Filter By Selection etc, then one way of
making your life easier would be to create an unbound form and place
your record details form as a subform in the unbound parent form. On
the parent form put an unbound text box with a label called "Filter:"
in which your user's can type a value with a button next to it. When
the user clicks on the button, you apply the value as both a filter to
the subform, and the recordset you derive from it, ensuring the
easiest possible way of creating the filter strings for both.
So your button's click code would look something like:
Dim rst as New ADODB.Recordset
Dim FilterString as String
FilterString = "myfield='" & me.txtFilterValue & "'"
Me.Filter = FilterString
Me.FilterOn = True
Set rst = Me.RecordSetClone
rst.Filter = FilterString
etc..
This example assumes you're filtering by an alphanumeric field.
Perhaps not the most elegant approach, but it might make your task
simpler.
Jane West
Well, I figured if I had time to post a question and follow the thread,
I had time to try out your first suggestion. Works well, though there
were the usual nuisances. For example, when you filter excluding
something, the filter string is Not Name = 'Fred,' which of course you
can't use to apply the filter: that has to be Name <> 'Fred'.
But one big problem I can't figure out. When I have, say, 10 records,
click to add a new one, the count goes to 11. If I then decide not to
add anything, it goes back to 10. But if I apply a filter and do this,
it stays at 11, so Me.Recordset returns the wrong value. I'm using an
Undo button to discard a new record: if nothing's been done to it, I
issue the undo command and go back to the record where I was when I
first tried to add. Or, if I'm filtering, back to the first record.
The Undo turns me.newrecord off either way, but the record count is
wrong only if I tried adding with a filter on. I've tried a number of
things trying to get the record count back to where it should be, but
nothing works. Pity I can't just say Me.recordcount = 10. Well, I CAN,
but it won't do much good on a read-only property.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!