Hide label for null results

[programmingrookie]

I've coded a label to become visible based on the number of results in a
filtered form. The problem I run into is I can't set the label's visible
property to "no" if the query is null.

Private Sub Form_Current()
Dim Terms As Integer
Dim rst As Object

Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0

Terms = rst.RecordCount

If Terms > 1 Then
Form_frmApplicants.Label60.Visible = True
ElseIf IsNull(Terms) Then
Form_frmApplicants.Label60.Visible = False
Else: Form_frmApplicants.Label60.Visible = False
End If
End Sub


Any Ideas?

 

[Dirk Goldgar]

In

I've coded a label to become visible based on the number of results
in a filtered form. The problem I run into is I can't set the
label's visible property to "no" if the query is null.

Private Sub Form_Current()
Dim Terms As Integer
Dim rst As Object

Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0

Terms = rst.RecordCount

If Terms > 1 Then
Form_frmApplicants.Label60.Visible = True
ElseIf IsNull(Terms) Then
Form_frmApplicants.Label60.Visible = False
Else: Form_frmApplicants.Label60.Visible = False
End If
End Sub

If there are no records, the form's Current event won't fire unless it's
possible to add a new record with the form -- AllowAdditions must be
yes/true, and the form's recordsource must be updatable. Is that the
case here? If your form doesn't allow records to be added, you can't
use the Current event to test for the "no data" condition. I think
you'll need to use two events for this: Load and ApplyFilter.

In those events, you can use code like this:

With Me.RecordsetClone
If Not .EOF Then .MoveLast
Forms!frmApplicants.Label60.Visible = (.RecordCount > 1)
End With

I'm assuming that Label60 is on a different form from the one this code
is running on, since your original code named it instead of using
"Me!Label60". Note that, in your original code, it's not possible for
Terms ever to be Null, so there's no point in testing that.

 

[OldPro]

I've coded a label to become visible based on the number of results in a
filtered form. The problem I run into is I can't set the label's visible
property to "no" if the query is null.

Private Sub Form_Current()
Dim Terms As Integer
Dim rst As Object

Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0

Terms = rst.RecordCount

If Terms > 1 Then
Form_frmApplicants.Label60.Visible = True
ElseIf IsNull(Terms) Then
Form_frmApplicants.Label60.Visible = False
Else: Form_frmApplicants.Label60.Visible = False
End If
End Sub

Any Ideas?

First of all, the test "Terms > 1 " won't work if Terms is NULL. Test
for NULL first. Second of all, find a way to make the label invisible
from the same form in which it resides.

 

[programmingrookie]

My recordset is based on a query which is not updateable, so this is where my
problem starts. I'm thinking the only way to make the recordset updateable
would be to create a table from the query. Is this correct?

 

[OldPro]

My recordset is based on a query which is not updateable, so this is where my
problem starts. I'm thinking the only way to make the recordset updateable
would be to create a table from the query. Is this correct?

Egads NO! The source of a recordset has nothing to do with whether a
label is visible. How will making a table out of a recordset help you
to achieve anything? Did you try my earlier suggestions?

 

[Dirk Goldgar]

In

My recordset is based on a query which is not updateable, so this is
where my problem starts. I'm thinking the only way to make the
recordset updateable would be to create a table from the query. Is
this correct?

No, that would be taking the wrong approach. You don't need to make
your form's recordset updatable just to make this label visible or
invisible; you just need to use the correct form events -- not the
Current event. (If you need to make the form updatable for some other
reason, that's a different matter, and should be addressed separately.)

Please reread my original response, and if there's anything in it you
don't understand, just ask.

 

[Dirk Goldgar]

In

First of all, the test "Terms > 1 " won't work if Terms is NULL.

Actually, this logic would work, if the form's Current event could be
counted on to fire even when there are no records. In an If statement
where the "If" condition is Null, the Else block will execute. However,
as I pointed out in my response, it is impossible for Terms ever to be
Null, given this code.