Hiding Sub form until search criteria made in main form 1

[mondeoman]

I have a standard search form with a sub form that is in datasheet view on load/open. I want this datasheet to be blank before the user enters a selection in the various search text boxes of the main form. However, I don't believe this is possible with a datasheet view. The next best thing would be to hide the sub form until a search criteria is made in the main form. How can I achieve this please. The code I am using in the search button is:

Private Sub btnSearch_Click()
 ' Update the record source
    Me.subfrm_InterimDataSearch.Form.RecordSource = "SELECT * FROM qry_Interim_Cert_Data " & BuildFilter
    
    ' Requery the subform
    Me.subfrm_InterimDataSearch.Requery
End Sub [code]

I have attached (ww.box.net url access below) a picture of the form with the sub form as it currently opens.

 

[kjv1611]

If you just want to hide the subform, then you'd need to set the .visible property for the subform to false when the main form loads, then reset it to .visible = True after the search criteria is entered.

Otherwise, if you want your datasheet to show, but just be empty, then you could try this:

Private Sub btnSearch_Click()
 ' Update the record source
    Me.subfrm_InterimDataSearch.Form.RecordSource = "SELECT * FROM qry_Interim_Cert_Data " & BuildFilter & "WHERE 1 = 2"
    
    ' Requery the subform
    Me.subfrm_InterimDataSearch.Requery
End Sub

Running that will return 0 records, b/c "1 = 2" is illogical. So, that way, your subform will be visible, but no data in the result set. THEN after the search query is entered, you simply change your SQL to reflect that, reset th recordsource, and requery the form/subform.

 

[mondeoman]

Thanks kjv1611 This just what I wanted and also helps me understand more how this works. Appreciated