No Entries in a Query

[sc5502]

Background:
Front End: MS Access 2010/VBA
Back End: MS SQL Server 2008

I have a form called Accounts. Accounts has a sub form that list all Items (sub form name Items example attached). In the example there are no items in the query. How do I display in the query the words "No Items to Display" when there is nothing to display? Thanks.

[jforbes]

Hey sc5502,

I would create a Label that says "No Items to Display" as the Caption. I would make it the same size at the SubForm and place it in the same place on the Form as the SubForm.

Then in the OnCurrent Event of the Form I would perform a DCount on the Table that the SubForm is based on with the same criteria of the SubForm. If the Count is 0 make the New Label Visible and make the SubReport non-visible, if the Count is greater than 0, make the SubReport visible and the Label non-visible. (You can also create a Clone of the SubForms recordset to find out the Record Count... up to you.)

[sc5502]

How do I code your example when the form/sub form is set up like in the attached?

[jforbes]

I mocked this up in a test database that was doing something similar. Most of the code was already there, I just added the Label. This code Filters a SubForm of Companies based on what is supplied in a TextBox. I'm guessing what you end up with would be something similar:

Private Sub cmdApplyFilter_Click()
    Dim sSearch As String
    sSearch = Nz(Me.txtSearch.Value, "")
    If Len(sSearch) > 0 Then
        Me.CompanySub.Form.Filter = "Company LIKE '*" & sSearch & "*'"
        Me.CompanySub.Form.FilterOn = True
    Else
        Me!CompanySub.Form.FilterOn = False
    End If
 
    Call Form_Current
End Sub
Private Sub Form_Current()
    Dim bSubRecords As Boolean
    bSubRecords = (DCount("CompanyID", "Company", Me.CompanySub.Form.Filter) <> 0)
    Me.CompanySub.Visible = bSubRecords
    Me.lblNoRecords.Visible = Not bSubRecords
End Sub

Maybe, instead of Me.CompanySub.Form.Filter for the Criteria, it would be something like: "idAcounts=" & Me.frmIDAccounts.Value