Full Version: Error Trapping For Filter
UtterAccess Forums > Microsoft® Access > Access Forms
DAS108
Hi all-
Quick question. I have a function running (code below) to filter a form based on a text box that matches against a few different fields. It works great, but is there a way to trap and stop the filter if it is going to return no records? It's freezing up the form when it returns no records, and I'd like to be able to display a message box when the record count is going to be zero and just end the function.
Thanks!
Private Function SetFormFilter()
'Declare MFilter as base part of string
Dim mFilter As String
mFilter = ""
'Checks if string built for text fields is blank, and if not adds it to running string
If Not IsNull(Me.SearchStringHolder) Then mFilter = "(( [RecordGroup] Like '*" & Me.SearchStringHolder & "*' OR [Series] Like '*" & Me.SearchStringHolder & "*' OR [Group] Like '*" & Me.SearchStringHolder & "*' OR Title] Like '*" & Me.SearchStringHolder & "*' OR [Notes] Like '*" & Me.SearchStringHolder & "*' ))"
'Checks if entire string built is blank, and if not activates filter
If Len(mFilter) > 0 Then
Me.Filter = mFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If
'Requeries Form To Display Newly Found records
Me.Requery
End Function
fredrisg
I might use a DCount on the same qry and use a If then test if it's less than 1 . . . then display your message and exit the function.
teve
DAS108
That would work great...where do I put it in the code to dcount the filter before i turn it on?
And what code do I use? Can i DCOUNT(Me.Filter) ?
DAS108
Here is my attempt...its throwing an "invalid use of Null"
Declare MFilter as base part of string
Dim mFilter As String
mFilter = ""
'Checks if string built for text fields is blank, and if not adds it to running string
If Not IsNull(Me.SearchStringHolder) Then mFilter = "(( [RecordGroup] Like '*" & Me.SearchStringHolder & "*' OR [Series] Like '*" & Me.SearchStringHolder & "*' OR [SubSeries] Like '*" & Me.SearchStringHolder & "*' OR [FolderTitle] Like '*" & Me.SearchStringHolder & "*' OR [Notes] Like '*" & Me.SearchStringHolder & "*' ))"
'Checks if entire string built is blank, and if not activates filter
If Len(mFilter) > 0 Then
Me.Filter = mFilter
Me.FilterOn = True
If DCount(Me.folder, 0) = 0 Then
MsgBox ("No Records. Returning You To Form")
Me.FilterOn = False
Else
End If

Else
Me.FilterOn = False
End If
'Requeries Form To Display Newly Found records
Me.Requery
End Function
fredrisg
Here's the link to the MS site for DCount: DCount
The syntax: DCount(expr, domain [, criteria] )
Odon't believe you can use it in the manner you tried . . . but can include the main table or qry you're searching and use the criteria for the SQL string . . . \
My only observation and this is puzzeling to me is that my 2002 VBA help file won't search for DCount anymore . . . strange . . . but I did find it on the MS site.
Anyway, take a peek at the web page . . . and it should explain fully how to use the DCount.
I personally don't use filters very much if at all . . . and I'm not sure if a method exist to test for the count of a filter . . . but DCount is something I do use.
Steve
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.