Can't reference to subform when record count is 0

[Lukael]

Hi,




I want to perform some action when Userform has 0 record count on Main Form load event. It's not working, whatever reference I use for subform

Here It is (here I tried only to set Dataentry to true, so that fields of subform would appear If there are no records - otherwise It returns me only labels of fields...continous subform, tabular design):

Private Sub Form_Load()


If Me.MySubform.Form.Recordset.RecordCount = 0 Then

Me.MySubform.Form.DataEntry = True


End If


End Sub

However, this Main form is opened from another form via cmdbutton (simple Docmd.OpenForm, but with desired .Recordsource), maybe that is problem in reference ?

[JeroenMioch]

I have this statement in my form wich show the subform when the recordcount is greater then zero.
I have only editted the form names etc for my own discretion.

Maybe you can edit the code to your situation.

If Me.MyCheckMark = True Then
    With Me![MySubForm].Form
        .Visible = (.RecordsetClone.RecordCount > 0)
    End With
Else
Me![MySubForm].Form.Visible = False
End If

I know you have no checkmark but again, maybe you can edit a little to make it work.

[Lukael]

thanks for response,

but It's not working. I tried this :

Private Sub Form_Load()


  With Me![MySubform].Form
    If (.RecordsetClone.RecordCount > 0) Then
    Me![MySubform].Form.Visible = False
    End If
 End With


End Sub

MySubform is not visible, whether has records or not. Any idea on how to change this code ?

[rpeare]

how about providing a sample database with some junk data in it.

[Lukael]

Sure,

here It is. Form1 is "search" form - based on criteria Form2 opens, with subform. Check VBA for that, and Subfom_Load event. On Form2 I also added explanation of what exactly I'm trying to achieve.

[Lukael]

Solved. Code from post#1 is ok, I just need to put It in separate module and call It right after SQL for subform recordsource executes. This way I can set Textbox to visible, and If there are no records in Subform I can display "No records" in Textbox.