Joe Overman
asked on
Create a trap for no records found when user filters a form
I have a form tied to a table with no additions, deletions, or edits allowed. All of those tasks are handled by separate forms. The user has the ability to filter this form by right clicking on any field and selecting whichever filter the user wants. The issue is if the user selects a filter that returns zero results the form becomes blank and the user has to find the filter controls in the ribbon to unfilter the form (or close it and reopen). I need to know if there is an event where I can trap zero results so I can display a message box and then apply a different filter (the default) or remove it altogether. The onfilter and afterfilter events do not trigger when the user performs this action. MS Access 2013
how are you opening the form with filtered records?
ASKER
the form load event sets the record set then applies a default filter.
If CurrentProject.AllForms("f rmMainDisp lay").IsLo aded = True Then 'original data
DoCmd.ApplyFilter "", "(((lnktblRequestData.EndT imeDate) Is Null))"
If Me.Recordset.RecordCount = 0 Then
DoCmd.ApplyFilter "", "(((lnktblRequestData.EndT imeDate)>= Now()-7))"
End If
End If
If CurrentProject.AllForms("f
DoCmd.ApplyFilter "", "(((lnktblRequestData.EndT
If Me.Recordset.RecordCount = 0 Then
DoCmd.ApplyFilter "", "(((lnktblRequestData.EndT
End If
End If
try using the Open event of the form
private sub form_open(cancel as integer)
if me.recordset.recordcount=0 then
msgbox "No records"
cancel=true
end if
end sub
private sub form_open(cancel as integer)
if me.recordset.recordcount=0
msgbox "No records"
cancel=true
end if
end sub
ASKER
The form is open already so that won't work.
and the user has to find the filter controls in the ribbon to unfilter the form (or close it and reopen)How about you create a button that will remove the filter or assign default filter.
ASKER
I have a button on the form already. Problem is when no records are returned the form is blank and all tfhe controls/fields are hidden.
You can modify your filter activation to check if records exist and, if not, pop a message and disable the filter:
Me.Filter = "Id = -99" ' Apply your filter expression.
Me.FilterOn = True
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records for filter: " & Me.Filter, vbInformation, "Filter"
Me.FilterOn = False
End If
/gustav
ASKER
The code above governs the default filter just fine, that is not the issue. The issue is when the user right clicks and filters the form. I need to capture when I get zero records in that instance. What event can do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav,
Thanks for that. I tried this all day yesterday and could not get the applyfilter event to trigger. But it did this morning and all is well. I must of had something wrong.
Thanks for that. I tried this all day yesterday and could not get the applyfilter event to trigger. But it did this morning and all is well. I must of had something wrong.
You are welcome!
/gustav
/gustav