DCOUNT question?

[jdbegg]

Hi all,

I'm asking for some help on this simple question. I have a form with a button that runs a query based on user's selection. Sometimes the query returns no records but leaves the query window up. The user has to manually close the window. I want to add the feature that if the query returns no records, a msgbox tells them there are no records to display and the query window closes. Would DCOUNT work here?

Private Sub btnApplyFilter_Click()
On Error GoTo Err_btnApplyFilter_Click

ColToSearch = Me.CboCol.Value
Select Case ColToSearch
    Case "ID"
        DoCmd.OpenQuery "IDNumberQuery", acViewNormal, acReadOnly
 '       DoCmd.Close acForm, "IDNumberQuery"
    Case "Issue"
        DoCmd.OpenQuery "Words in Issue Query", acViewNormal, acReadOnly
 '       DoCmd.Close acForm, "Words in Issue Query"
    Case "Discussion"
        DoCmd.OpenQuery "Words in Discussion Query", acViewNormal, acReadOnly
 '       DoCmd.Close acForm, "Words in Discussion Query"
    Case "Recommendation"
        DoCmd.OpenQuery "Words in Recommendation Query", acViewNormal, acReadOnly
 '       DoCmd.Close acForm, "Words in Recommendation Query"
End Select
   
Exit_BtnApplyFilter_Click:
    Exit Sub
   
Err_btnApplyFilter_Click:
    MsgBox Err.Description
    Resume Exit_BtnApplyFilter_Click
   
End Sub

 

[pbaldy]

Yes, a DCount() should work fine in that situation.

 

[jdbegg]

Almost working

Thanks Paul,

Part of it is working, the MsgBox part is showing, so I know I'm into the If statement, but the form is still not closing. The standard datasheet view is what is showing and what I want to close. I don't have an actual named form bound to a query. So, would I still close a form or maybe close the query? Would DoCmd.CloseQuery close the datasheet view? Updated code is below.

Case "ID"
        DoCmd.OpenQuery "IDNumberQuery", acViewNormal, acReadOnly
        If DCount("*", "IDNumberQuery") = 0 Then
            MsgBox "There are no records to display"
            DoCmd.Close acForm, "IDNumberQuery"
        End If

Jim

 

[pbaldy]

You're closing a form rather than the query. However, what I would do is not even open the query if its empty (it doesn't need to be open for the DCount). This type of thing, in pseudo code:

If DCount = 0
msgbox
else
open query
end if

 

[jdbegg]

Yep, that worked!

Thanks again Paul,

To me, logically, there had to be records for the DCOUNT to count which would come after the query ran. I didn't realize this this wasn't the case. I'm trying to learn a new skill-set in Access and VBA. Thanks much.

Jim