We help IT Professionals succeed at work.

How To Detect No Record Match After Applying Filter

davidsanderson
on
i'm looking for a clean way to detect that no records match the entered criteria after a user applies a filter.  i tried to put in some code in the on apply filter event like this:

    If IsNull(DLookup(1, Me.RecordSource, Me.Filter)) Then
        Cancel = True
        MsgBox "No records match the entered criteria."
    End If

but an automation error occurs if the user selected something from a combo box to filter on.

i also tried to detect if the form jumps to a new record in the on apply filter event but that doesnt work because the actions in event procedure occur before the filter is applied or removed.  also, that wouldnt work for users that don't have permissions to add new records.

i know about creating a seperate filter form but this would create way too much maintenance every time a new field is added to the form.

any suggestions?
Comment
Watch Question

Commented:
That's why I normally use a main form with a datasheet subform where the user can apply his filter(s).
After applying a filter not hitting a single row, that's really visible ;-)
Big advantage is the not needed message and OK click...

It also enables the user to filter on all fields visible and adding fields to the datasheet wouldn't involve extra coding...

Drop me a line at my nico5038 mailbox "at" yahoo.com and I'll mail a sample.

I'm a bit puzzled by the 1 in the DLOOKUP. I would just specify an existing fieldname within quotes...

Nic;o)
CERTIFIED EXPERT

Commented:
if i understand what you're looking for, how about using this:

MsgBox Me.RecordsetClone.EOF

(of course, use only the Me.RecordsetClone.eof part...)

dovholuk

Author

Commented:
dovholuk, where should i use Me.RecordsetClone.EOF?

nico, i emailed you for a sample.

if i just want to find out if a record exists in another table or query based on criteria, i just use a 1 in the dlookup and if it returns Null, it was not found, if 1 is returned then it was found.  there is no need to put in an actual field name when all you want to accomplish is finding out if a record exists or not.
CERTIFIED EXPERT

Commented:
put msgbox me.recordsetclone.eof into the form's current property... it should do what you want... (i  think)

dovholuk

Author

Commented:
me.recordsetclose.eof is no different than saying me.newrecord in this case.  it doesnt do any good for users that dont have permissions to add new records.

Commented:
David, check your mail ;-)

Nic;o)

Author

Commented:
nico, thank-you for the sample databases, thats some really sleak stuff!  i just dont have the time to implement those kinds of features for the myriads of forms already in the database... :-(

Commented:
I just use one mainform as a "stamp" and are able to create such selection forms within five minutes.
(Copy form, add subform from query or table, add OnCurrent event on subform and place correct txtKey field on mainform.)
As I wrote, it saves me much time afterwards, not having to write extra filter and sort code...

But it should indeed fit into the complete design. Perhaps something to think of next time ;-)

BTW where does your combo info comes from, when you use a SELECT DISTINCT of the values of the forms recordsource, there should always be a match...

Nic;o)

Author

Commented:
i need to re-examine your stuff then.  maybe i should read next time. :)  it still sounds like something i'll have to implement in my next database though.  it just seems like there should be a simple solution after applying filter to detect if there's no match, like the cool On No Data event for reports.

i'm not sure exactly what you're asking about where the combo box info comes from???
Commented:
The combobox can be based on a value list you added, or on a query.
When you base the query on the "field to be filtered" using e.g. a:
select distinct field2select from tblSubForm;

Then you'll always have rows to display as only distinct existing values are there in the combo.

See my point ?

Nic;o)

Author

Commented:
thats not what i was really after but its a solution that i can use in future access projects.

Author

Commented:
i finally found the article that provided the exact solution i needed:

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q210037

Set the OnApplyFilter property to the following event procedure:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
   Me.TimerInterval = 100
End Sub

Set the OnTimer property to the following event procedure:

Private Sub Form_Timer()
   Dim strfilter As String
   Me.TimerInterval = 0
   If IsNull(Me.Filter) Then Exit Sub
   strfilter = Me.Filter
   If Me.RecordsetClone.RecordCount = 0 Then
       MsgBox "The selected filter:" & Chr(13) & _
       strfilter & Chr(13) & _
       "returns no records. Try a less restrictive search."
       ' Form, Records, Filter, Filter By Form.
       DoCmd.DoMenuItem 0, 5, 0, 0, acMenuVer70
   End If
End Sub

Explore More ContentExplore courses, solutions, and other research materials related to this topic.