Retuning No Records

[JamesJ]

Hi. I'm using a label 'lblCount' to display the number of records
returned in a recordset. I want the label to display "No Records"
if no records are returned.I've tried checking for a null value in
the Primary Index field 'ProductID' and have tried checking to see
if the RecordCount is >0. nothing seems to work. When I use
the following code the label displays "No Records even when there
are records. If I remove the If...RecordCount>0 If statement
and unremark out the If IsNull... the correct number of records are
displayed in the label but when no records are returned the
label simply displays the last recordcount.

Private Sub Form_Current()

Dim RecClone As DAO.Recordset
Dim intCount As Integer

Set RecClone = Me.RecordsetClone()

'If IsNull(Me!ProductID) Then

If RecordsetClone.RecordCount > 0 Then
Me!lblCount.Caption = "No Records"

Else

RecClone.MoveLast
intCount = RecClone.RecordCount
RecClone.Bookmark = Me.Bookmark
lblCount.Caption = intCount & " Item(s)"

'End If
End If

RecClone.Close
Set RecClone = Nothing

End Sub

Any help will be appreciated,

James

 

[SteveS]

Hi. I'm using a label 'lblCount' to display the number of records
returned in a recordset. I want the label to display "No Records"
if no records are returned.I've tried checking for a null value in
the Primary Index field 'ProductID' and have tried checking to see
if the RecordCount is >0. nothing seems to work. When I use
the following code the label displays "No Records even when there
are records. If I remove the If...RecordCount>0 If statement
and unremark out the If IsNull... the correct number of records are
displayed in the label but when no records are returned the
label simply displays the last recordcount.

Private Sub Form_Current()

Dim RecClone As DAO.Recordset
Dim intCount As Integer

Set RecClone = Me.RecordsetClone()

'If IsNull(Me!ProductID) Then

If RecordsetClone.RecordCount > 0 Then
Me!lblCount.Caption = "No Records"

Else

RecClone.MoveLast
intCount = RecClone.RecordCount
RecClone.Bookmark = Me.Bookmark
lblCount.Caption = intCount & " Item(s)"

'End If
End If

RecClone.Close
Set RecClone = Nothing

End Sub

Any help will be appreciated,

James

James,

I see several things that I would change. The main thing is the logic is backwards.

Just after the Dim statements, you need a line

Me!lblCount.Caption = "No Records" or
Me!lblCount.Caption = ""


You have "If Recordsetclone.RecordCount > 0 Then..." If the record count is
greater than 0, you have it displaying "No Records". Swap it with the Else clause.

Also, I think the line "If Recordsetclone.RecordCount > 0 Then..." should be
"If RecClone.RecordCount > 0 Then..."

Why are you checking a control on the form (that is using the Recordset not the
RecordsetClone) is null to check if there are records in the recordset?

And since (if) you are only checking the record count and updating a label
caption on the form, why use RecordsetClone? Just use Me.RecordCount.

This is air code, but you might try:
'*****************************
Private Sub Form_Current()

Dim intCount As Integer

Me!lblCount.Caption = ""

If Me.Recordset.RecordCount > 0 Then

'RecClone.MoveLast
intCount = Me.Recordset.RecordCount
'RecClone.Bookmark = Me.Bookmark
Me!lblCount.Caption = intCount & " Item(s)"

Else

Me!lblCount.Caption = "No Records"


End If

End Sub
'********************************

HTH

 

[JamesJ]

The air code doesn't want to display the "No Records", it displays the
previous value for the recordcount even though there are no records.
I was checking for a null in the ProductID field 'cause if it is null
then there are no records.
If it helps I'm filtering the records by selecting a value in a combobox
populated by another table with a field related to a field in this table.

James

 

[Douglas J. Steele]

You've instantiated RecClone as your RecordsetClone, yet you're checking

If RecordsetClone.RecordCount > 0 Then

The fact that Access isn't complaing about that being an undeclared variable
implies that you haven't told Access to Require Variable Declaration (on the
Module tab under Tools | Options).

 

[JamesJ]

Require Variable Declaration is checked and Option Explicit
is in the declarations of the module.

James

 

[JamesJ]

Ah. I had the AllowAdditions set to No, which is what I want.
I set the property to Yes and the air code works fine.
Don't know why there must be a blank new record for this to
work, if you know why please I'd like to know.

Thanks,
James

 

[Douglas J. Steele]

Did you not do a cut-and-paste, then?

 

[JamesJ]

I did cut and paste.

Did you not do a cut-and-paste, then?

 

[SteveS]

Ah. I had the AllowAdditions set to No, which is what I want.
I set the property to Yes and the air code works fine.
Don't know why there must be a blank new record for this to
work, if you know why please I'd like to know.

Thanks,
James

James,

I set up a label and pasted in the code on a test form. I have a couple of
buttons that sets or clears a filter.

Using the code below, it doesn't matter whether the "Allow Additions" property
is set to "Yes" or "No".... It works either way. Maybe there is some other code
that is interfering.

How are you changing the recordset? Setting a filter or changing query parameters?

I am using A2K on W2K.

Here is the code:
'**** beg code *******
Private Sub Form_Current()
Dim intCount As Integer

Me!lblCount.Caption = ""
If Me.Recordset.RecordCount > 0 Then
intCount = Me.Recordset.RecordCount
Me!lblCount.Caption = intCount & " Item(s)"
Else
Me!lblCount.Caption = "No Records"
End If
End Sub
'**** end code *******


Here is a shorter version using BOF and EOF:
'**** beg code 2 *******
Private Sub Form_Current()

Me!lblCount.Caption = ""

If Me.Recordset.BOF And Me.Recordset.EOF Then
Me!lblCount.Caption = "No Records"
Else
Me!lblCount.Caption = Me.Recordset.RecordCount & " Item(s)"
End If

End Sub
'**** end code 2 *******

Upon further testing, I found that if I set a filter like this:

Private Sub Command7_Click()
Me.Filter = "Paytype = 'steve'"
Me.FilterOn = True
End Sub

with the "Allow Additions" property was set to "NO" and the result was no
records, the FORM_CURRENT event was not called and the label was not updated..

With the "Allow Additions" property was set to "YES", the label was updated
properly.



I was able to get it to work like you wanted by doing this.

Delete the Sub Form_Current() event.

Paste in the following code:

'**** beg code *******
Public Sub CountRecs()
Me!lblCount.Caption = ""

If Me.Recordset.BOF And Me.Recordset.EOF Then
Me!lblCount.Caption = "No Records"
Else
Me!lblCount.Caption = Me.Recordset.RecordCount & " Item(s)"
End If

End Sub
'**** end code *******


In the FORM-LOAD() event, add

CountRecs


If you use buttons to change the filter, add the line

CountRecs

as the last line in each of the button click events.
If you are changing parameters for a query, put

CountRecs

as the last line in the sub that runs the query (or the button click).

HTH

 

[Douglas J Steele]

I don't understand, then, how your code can compile, given your code
includes

If RecordsetClone.RecordCount > 0 Then

You haven't declared RecordsetClone as a variable anywhere, and you'd need a
period in front of it to have that recognized as a RecordsetClone of the
form's Recordset.