Need help with making a label visible/not visible in a subform

[gojets1721]

I have a label in a subform that I only want to be visible if the recordset of the subform is 0. I used the below VBA in the on current event of the subform to achieve that.

Private Sub Form_Current()
    Me.lblNoRecords.Visible = Me.Recordset.RecordCount = 0
End Sub

It works in the parent form EXCEPT when the record set isn't 0. When moving to the next record in the parent form, the label doesn't update to be visible even if the subform's record set is zero.

Any suggestions on what I'm doing wrong?

 

[Pat Hartman]

Are you saying the field is in the subform??? or the mainform? It doesn't make much sense in the subform.

When a form loads initially, the subform is loaded first and then the mainform. Therefore, by the time the Current event of the main form runs, the subform has been loaded and so the recordcount is known. From that point on, as you scroll through mainform records, the count of the subform is not known at the time the current event runs.

You might try something like the following in the subform's Current event.

if  Me.Recordset.RecordCount = 0 then
    Me.Parent!lblNoRecords.Recalc
End If

 

[Ranman256]

rst.recordcount wont count unless you got to the end, so use DCount(...)

lbl.visible = Dcount("*",me.recordsource) = 0