Hide the subform when it's empty

Question

Hi all,

I have a form in datasheet mode who contains a sub form.

The sub form is always visible, even if it is empty, under all the lines of the main form (as well as the + sign in front of the lines).

I would like to hide the subform when it is empty.

I know that it's possible because I have another project that does that (I didn't created it). But despite of this "template", I am unable to find the right property or code.

FYI, I am using Access 2010 and the "template" project uses Access 2003 (!)

Thanks for your help,

Emphyrio

Answer 1

Hi,

Not familiar with the template but try going to the design view of the subform in question and set the Allow Additions property to No.

Hope it helps...

Answer 2

Say for example, the subform name = SubForm

and the subforms Record Source = SubFormQuery

Insert the following VBA code using the Main Forms On Open Event:

If DCount("*","SubFormQuery")=0 Then

Me.SubForm.Visible = False

Else

Me.SubForm.Visible =True

End If

That way if the subform is not empty, it will be visible and you can enter data into it. If it is empty, it will be invisible. Substitute the subforms actual Record Source for 'SubformQuery' and the subforms actual name for 'SubForm'.

Answer 3

Hi emphyrion,

below is an alternative approach.

Private Sub Form_Current()
    With Me![SubformName].Form
        .Visible = (.RecordsetClone.RecordCount > 0)
    End With
End Sub

Reference:

Hide a subform if no records present

you can also refer link below , but you need to modify the code as per your requirement.(you can only refer it to understand the logic)

Hide a Subform if the Main Form Contains No Records

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].