davidsanderson
asked on
How To Detect No Record Match After Applying Filter
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?
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?
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
MsgBox Me.RecordsetClone.EOF
(of course, use only the Me.RecordsetClone.eof part...)
dovholuk
ASKER
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.
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.
put msgbox me.recordsetclone.eof into the form's current property... it should do what you want... (i think)
dovholuk
dovholuk
ASKER
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.
David, check your mail ;-)
Nic;o)
Nic;o)
ASKER
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... :-(
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)
(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)
ASKER
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???
i'm not sure exactly what you're asking about where the combo box info comes from???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thats not what i was really after but its a solution that i can use in future access projects.
ASKER
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.RecordCo
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
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.RecordCo
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
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)