Hi all: Hoping someone can help me debug here. The below code is throwing an error when the filter returns zero records. What I'm getting is that
1) it gives me teh desired feedback message of "No Records Match This Search"
2) THEN however, it errors out to my generic "On Error" message...the problem seems to be in the section I've marked 'Nested If Routine To Weed Out Filters With No Results but I can't figure it out since its giving me the first error message properly.


Private Function SetFormFilter()

On Error GoTo Err_SetFormFilter

'Declare MFilter as base part of string, SearchStringPassThrough
Dim mFilter As String
Dim SearchStringPassThrough As String
mFilter = ""


'Checks if string built for text fields is blank, and if so displays message and ends function
If IsNull(Me.SearchStringHolder) Then
MsgBox ("You Didn't Search For Anything. Returning To All Records.")
Me.FilterOn = False
SearchStringHolder.SetFocus

Else
'Trims Search String For leading/trailing spaces
SearchStringPassThrough = Trim(Me.SearchStringHolder)
Me.SearchStringHolder = SearchStringPassThrough

'builds Filter string from search string
mFilter = "(( [RecordGroup] Like '*" & SearchStringPassThrough & "*' OR [Series] Like '*" & SearchStringPassThrough & "*' OR [SubSeries] Like '*" & SearchStringPassThrough & "*' OR [FolderTitle] Like '*" & SearchStringPassThrough & "*' OR [Notes] Like '*" & SearchStringPassThrough & "*' ))"

'Checks if entire string built is blank, and if not activates filter
If Len(mFilter) > 0 Then
Me.Filter = mFilter
Me.FilterOn = True
'Nested If Routine To Weed Out Filters With No Results
If DCount("FolderTitle", "Files") = 0 Then
MsgBox ("No Records Match This Search")
Me.FilterOn = False
Me.SearchStringHolder = Null
SearchStringPassThrough = Null
Else
End If
'End of If portion which activates filter
End If

'End of outermost If statement starting with If Is Null
End If

'Requeries Form To Display Newly Found records
Me.Requery


Exit_SetFormFilter:
Exit Function

'General Error Trap
Err_SetFormFilter:
MsgBox ("ERROR: ")

Resume Exit_SetFormFilter


End Function

You realize that without a criteria, your DCount will count all records in the table? I'd expect you to use the filter string in the criteria argument.

How would I structure that? Like this?
DCount("FolderTitle", "Files", mfilter)

I don't think thats the problem though because it is properly giving me my error message that I wan't (no records found) when i search for something i know isnt there (like a string of junk like fdsfsdekjlsd)

Thoughts on if something else is calling my other error. Should have mentioned before, before I had the generic ONERROR setup with no other changes, this was throwing an "invalid use of null"

Well this will throw an error:

SearchStringPassThrough = Null

because a string variable can't be set to Null. You'd either have to make that a variant or set it to a ZLS (""). I still don't see how the DCount can work for you though.

How would you recommend changing the DCOUNT so it runs more safely/efficienctly/accurately?

changing SearchStringPassThrough = Null to SearchStringPassThrough = "" did solve the original problem btw. so thanks!

Maybe I'm making a bad assumption. I'm assuming that the domain in the function is the data table, which would contain all the records. If that's accurate, then without a criteria, the function will return the total count of records in the table. That would mean it could never return zero, but you're saying it can, so I guess my assumption is bad. My expectation was that the DCount would use mFilter in the criteria, to see if that particular filter returned records or not. Can I assume "Files" is not the data table?

I don't use Dcount much so I am probably not using it properly.
FolderTitle is a mandatory field, Files is a table (and the table that the form is based on)

I only picked FolderTitle (or Dcount generally) to use this to stop the function when the filter is going to return zero records.

If there is a better way to do it I'd love to learn. How do i use mfilter as criteria for Dcount ?

I missed this earlier:

How would I structure that? Like this?
DCount("FolderTitle", "Files", mfilter)

The answer is yes, that's what I expected, though I would do this:

DCount("*", "Files", mfilter)

My curiosity is up though, because I can't figure out how the original version:

If DCount("FolderTitle", "Files") = 0 Then

ever returned zero to get to your message. Were there records in the table at the time? I guess if it's working for you now, leave well enough alone. BTW the method is fine; it working without a criteria baffled me.

There were records in teh table...it clearly was, in effect, running off the filtered set of records, even if I didn't do it right...Underlying table's data almost never changes in this application...running different searches against the table using the filter was giving me the expected results -- a search i knew wouldn't find anything did trigger my message even though there were other non-matching records in the table.

Regardless I changed it to DCount("*", "Files", mfilter) and it still works so mine as well do it properly

Thanks fo rhte late Friday pm assist!