Display message when no records on subform

[djphatic]

Hi

I currently have a main form (frmStandResults) and 2 subforms within tab controls.

There are 2 combo boxes (Contractors and Groups) in the main form header that when updated (AfterUpdate) they requery 2 subforms that are on different tabs using the below code:

Forms!frmStandResults!sfrmStandResults.Form.Requery
Forms!frmStandResults!sfrmStandResultsMissing.Form.Requery

What I would like to be able to do is display a MsgBox if no records are returned. The MsgBox should be displayed when:

a) The combo box is updated and the tab that is currently being displayed returns no records.
b) I switch onto the other tab and there are no records displayed.

I cannot get a) to work at all. b) I used the following code OnChange and OnClick on the tab.

If Forms!frmStandResults!sfrmStandResultsMissing.Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found"
End If

OnClick doesnt work. OnChange works but the message box appears if I switch to the other tab what has records visible.

Any suggestions on the change in code and the suitable event to place this in?

 

[Steve R.]

Below is what I use with a textbox in conjunction with a subform. You will note that I am using DCOUNT with a filter. A better approach may be using SQL to establish a recordset and use the DAO record count property.

Private Sub Command6_Click()
    Dim strCriteria As String
    strPermitNumber = Trim(Me.Text2)
    lonRecordCount = DCount("PermitNumber", "SiteInspRptQry", "PermitNumber LIKE '*" & strPermitNumber & "*'")
    If lonRecordCount = 0 Then
            TITLE1 = "     *** PERMIT NOT FOUND ***"
            MSG1 = Me.Text2 & " was NOT found as a permit." & Chr(13) & "Please check your entry."
            MsgBox MSG1, vbOKOnly, TITLE1
        Else
            strCriteria = "trim([PermitNumber]) like '*" & [strPermitNumber] & "*' "
            Me.Child0.Form.Filter = strCriteria
            Me.Child0.Form.FilterOn = True
    End If
End Sub