Determining if a Subform has no data. (1 Viewer)

[jstutz]

I have created a simple search form for my users to query the database. Once the user fills out the search criteria and runs the search, another form opens displaying the results. Currently, if there are no records that match their search criteria, the Search Results screen opens blank (ie. no records in it). If the query returns no data, I would like to have a MsgBox open instucting the user there was no records found and to change their search criteria, but I am having some trouble getting Access to do it.

I have tried using the OnOpen event to detect if the form doesn't have data using the HasData method, IsEmpty method, IsNull method, etc. without any success. Basically, each method I have tried returns a FALSE value regardless if there is info. in the form or not. I am sure I am just not coding it improperly, but I have tried several code configurations and have looked at the examples so much my head is spinning! Does anyone have any suggestions?

Thanks!
James Stutz

 

[Axis]

You don't want to look at the form, you want to look at the table. Essentially you create a query to search for the data you want, then use the DCount function to count the number of records returned by the query. If the query comes up empty, a message box pops up saying no records found. Here's some simple code:

========================================
Dim strWhat as String, inCnt as Integer
DoCmd.Hourglass True
strWhat = "(([SQL criteria]))"
intCnt = DCount("[Key Field]", "[Table Name]", strWhat)
If intCnt = 0 Then
MsgBox "There are no records that match this query.", 64, "Search Results"
DoCmd.Hourglass False
End If
========================================

For [SQL criteria] insert the SQL statement from your query, minus the "SELECT"
For [Key Field] insert the name of the key index field from your table
For [Table Name] insert the name of the name that's being queried