Using RecordsetClone.RecordCount and Form Filter
franc sutherland
I'm using A2003 in Windows XP.
I have a search form which uses unbound text boxes and check boxes to
build a string to be applied as a form filter, as in Allen Browne's
Search database example.
I am using me.recordsetclone.recordcount to give a record count in a
label box in the header when different filters are applied and then
removed.
This is working fine apart from when I 'reset' the form. It takes two
or three clicks for the number returned to be correct. When I first
click 'reset', it says there are "13" records, when there's over 400.
If I wait a second or two and click again, it gets the number right.
The code in the reset button is:
Me.Filter = ""
Me.FilterOn = False
Dim record_count As Long
record_count = Me.RecordsetClone.RecordCount
Me.lbl_sub_title.Caption = record_count & " records - Reset"
'Adapted from allenbrowne.com
'Purpose: Clear all the search boxes in the Form Header, and show
all records again.
Dim ctl As Control
'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Is the recordcount occuring too soon? Do I need to put in a delay?
Cheers,
Franc.
Tom van Stiphout
<franc.su...@googlemail.com> wrote:
Perhaps. I would place a DoEvents line after resetting the filter.
This may be one of those rare cases where we need to allow Access to
finish processing.
-Tom.
Microsoft Access MVP
Salad
Maybe put in a breakpoint to step thru your code. Start at Me.Filter
line and see what values are assigned.
franc sutherland
Hi Salad,
I put the breakpoint in on the me.filter line and stepped through each
line.
The recordcount value assigned to the variable was correct, at 402.
I tried the breakpoint a little lower, down at the beginning of the
For loop, and this time the recordcount variable had been assigned as
13.
Any ideas?
Cheers,
Franc.
franc sutherland
> On Mon, 11 May 2009 06:58:52 -0700 (PDT), franc sutherland
>
Hi Tom,
I tried the DoEvents before and after and in between the two filter
lines, but with no success. It was still showing a recordcount of 13.
Just to check with you that I was doing it right, I put:
Me.Filter = ""
Me.FilterOn = False
DoEvents
etc....
Cheers,
Franc.
Salad
I don't know why it'd get reset unless another event executed that
changed the count. Maybe do the forloop first, clear out the variables
and then get the count. You might want to try something like this.
For loop code to clear out filter values here
Me.Filter = ""
Me.FilterOn = False
Dim r as dao.recordset
set r = Me.Recordsetclone
r.movelast
labelname = r.recordcount " Reset"
set r = Nothing
David W. Fenton
news:b22d20d4-52f5-44d5...@s31g2000vbp.googlegroups.co
m:
> This is working fine apart from when I 'reset' the form. It takes
> two or three clicks for the number returned to be correct. When I
> first click 'reset', it says there are "13" records, when there's
> over 400. If I wait a second or two and click again, it gets the
> number right.
Do a Me.RecordsetClone.MoveLast before you check the .RecordCount.
The .RecordCount for all DAO recordsets will at least 1 record if
there are records, but because of Rushmore technology, the
.RecordCount property is available before all the records have been
loaded. An explicit .MoveLast forces the whole recordset to load so
that the .RecordCount property will be accurate.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jim Devenish
wrote:
My usual approach is to place a TextBox at the top of the form with
its Control Source set to =Count(*)
Works for me.
Jim