Unanswered: how to notify when query returns no records
hi, I have a form that has 22 drop downs that allows a user to search data. There are circumstances where 2 (or more) of the drop downs selected in conjunction will return no data. When this happens...the form goes blank...and it looks as though the database freezes. This can be corrected by clicking the "unfilter" icon...but most of my users aren't that Access savy.
Behind the scenes my code looks like:
Private Sub Set_Filter_Click()
Dim strSQL As String, intcounter As Integer
'Build SQL String
'chr(34) is an open or close quotation mark
For intcounter = 1 To 22
If Me("Filter" & intcounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intcounter).Tag & "] " & " = " _
& Chr(34) & Me("Filter" & intcounter) & Chr(34) & " And "
If strSQL <> "" Then
'strip last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'set the filter property
Forms![frmsupervisorssearch].Filter = strSQL
Forms![frmsupervisorssearch].FilterOn = True
My question is...how can I create a message box that pops up that tells the user there is no data. Also...how can I prevent the form from going blank. I was thinking I could sort of pre-process the query and not set the filter if the record count was 0...but I haven't had luck with that.
You can use code like this in the OnCurrent event to test to see if there are no records. If the norecord condition is found, then you can change the filterOnto be False, or whatever you would like to do at that point.