Recordcount Problem

[Guest]

I have a "search" form which is based on a query. There are several unbound
combo boxes where the user can select criteria, then click a button and the
resulting records are displayed in a continuous form below. I have managed to
figure out all the code to make it work (with help from Allen Browne's
excellent website) but this seemingly simple issue is driving me friggin nuts
(I'm probably over thinking it or something). I have an unbound text box that
uses the following code to display the number of records returned

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone
lngCount = rst.RecordCount

If rst.RecordCount = 0 Then

Me.txtCount.Visible = False
Me.lblRecordsFound.Visible = False

Else

Me.txtCount.Visible = True
Me.lblRecordsFound.Visible = True
Me.txtCount = lngCount

End If

Set rst = Nothing

It worked fine the first time I ran it, but now it always returns the same
number (13) regardless of how many records are actually there. I've tried the
code in the form's current and after update events to no avail. I can even
close and reopen the database and still get the same result. Maybe I'm using
the wrong approach. I tried using the DCount function, but I couldn't seem to
get that to work either.

I'm sure someone will reply "why don't you just do such and such" and then
I'll realize what a moron I am, but that's OK. Wouldn't be the first time.

 

[Dirk Goldgar]

In

I have a "search" form which is based on a query. There are several
unbound combo boxes where the user can select criteria, then click a
button and the resulting records are displayed in a continuous form
below. I have managed to figure out all the code to make it work
(with help from Allen Browne's excellent website) but this seemingly
simple issue is driving me friggin nuts (I'm probably over thinking
it or something). I have an unbound text box that uses the following
code to display the number of records returned

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone
lngCount = rst.RecordCount

If rst.RecordCount = 0 Then

Me.txtCount.Visible = False
Me.lblRecordsFound.Visible = False

Else

Me.txtCount.Visible = True
Me.lblRecordsFound.Visible = True
Me.txtCount = lngCount

End If

Set rst = Nothing

It worked fine the first time I ran it, but now it always returns the
same number (13) regardless of how many records are actually there.
I've tried the code in the form's current and after update events to
no avail. I can even close and reopen the database and still get the
same result. Maybe I'm using the wrong approach. I tried using the
DCount function, but I couldn't seem to get that to work either.

I'm sure someone will reply "why don't you just do such and such" and
then I'll realize what a moron I am, but that's OK. Wouldn't be the
first time.

Have you tried ensuring that the recordset has moved to last record?
Try this:

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

If rst.RecordCount = 0 Then

Me.txtCount.Visible = False
Me.lblRecordsFound.Visible = False

Else
rst.MoveLast
lngCount = rst.RecordCount
Me.txtCount.Visible = True
Me.lblRecordsFound.Visible = True
Me.txtCount = lngCount

End If

Set rst = Nothing

 

[Guest]

Thank you Dirk. I figured it would be something simple like that.