Generic Filter Dialogs

Hi you all out there,

I need a nice filter Dialog for my application and are too lazy to write one on my own ;). No, I did one, but it sucks a bit, and I wanted to know if someone of you has written a filter dialog for your application before.

It is okey, if I have to customize it for every form (like adding the filterable fields and fieldtypes).

It should be something like choosing a value, thatn the operator, like "=", "<=" or "contains" ,and then a value that is given to the function.

Maybe someone has a download link for me?
Who is Participating?
perkcConnect With a Mentor Commented:
I use a form/subform combination. The form contains the filtering section and code.

I add a listbox named 'lstfields' on the main form.

I add a option group named 'frmsearch' on the main form. This contains the following options:

Contains - Value = 1
Begins With - Value = 2
Equals - Value = 3

I add a textbox named 'txtsearch' on the main form.

I then add the following code to the main form's on load event:

Private Sub Form_Load()
On Error GoTo load_err
Dim cntl As Control
Dim tmplist As String
Let Forms!mainformname![subformcontrolname].Form.Filter = ""
Let Forms!mainformname![subformcontrolname].Form.FilterOn = False
Me.lstfields.RowSource = ""
searchstring = ""

For Each cntl In Forms!mainformname.subformcontrolname.Form.Controls
    If cntl.ControlType = acTextBox Then
    tmplist = tmplist & ";" & cntl.Properties(1) & ";" & cntl.Properties(11)
    End If
Next cntl

Me.lstfields.RowSource = Right(tmplist, Len(tmplist) - 1)
Me.lstfields.DefaultValue = "'" & Left(Right(tmplist, Len(tmplist) - 1), InStr(1, Right(tmplist, Len(tmplist) - 1), ";") - 1) & "'"
Exit Sub
MsgBox "The following error was encountered:" & Chr(13) & Err.Number & ": " & Err.Description, vbOKOnly, "Database Name"
Exit Sub
End Sub

I then add the following code to the on change event of the main form's 'txtsearch' textbox:

Private Sub txtsearch_Change()
On Error GoTo txtsearch_err
plsearchstring = Me.txtsearch.Text
Let Forms!mainformname![subformcontrolname].Form.Filter = plfilter()
Let Forms!mainformname![subformcontrolname].Form.FilterOn = True
Exit Sub
MsgBox "The following error was encountered:" & Chr(13) & Err.Number & ": " & Err.Description, vbOKOnly, "Database Name"
Exit Sub
End Sub

I then make sure that all of the subforms I use with this main form contains the following:

All textboxes have the name and control source the same.
All textboxes contains a description entered in the 'Status Bar Text' property.

Hope that helps.

Steve BinkConnect With a Mentor Commented:
I do not know of any pre-made libraries to use for this kind of thing, but they no doubt exist...perhaps one of the other experts can point you towards one.  The problem being that each application is different, so it would be a little difficult to create a "plug-n-play" type of library.

One idea you can try is to put an unbound combobox on the form to hold your form's fields.  Also include one for the comparison operators, and a textbox for a value.  To populate the combobox, simply use the form's OnLoad event.  Something like this:

Me.ComboBox.RowSource = ""
For each x in Me.Recordset.Fields
    Me.ComboBox.RowSource = Me.ComboBox.RowSource & x.Name & ";"
Mig-OAuthor Commented:
Good start. Let's look what the others come up with.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Steve BinkCommented:
Sure thing.  If you need more information on my proposed solution, just ask.  :)
Oh, it would help if I gave you the plfilter code.

This code just goes in the forms module:

Public Function plfilter() As String
On Error GoTo plfilter_err

Dim tmpfilter As String

tmpfilter = "[" & Me.lstfields.Value & "]"

Select Case Me.frmsearch.Value
    Case 1
    tmpfilter = tmpfilter & " LIKE '*" & searchstring & "*'"
    Case 2
    tmpfilter = tmpfilter & " LIKE '" & searchstring & "*'"
    Case 3
    tmpfilter = tmpfilter & " Like '" & searchstring & "'"

End Select

plfilter = tmpfilter

Exit Function
plfilter = ""
Exit Function

End Function

Mig-OAuthor Commented:
This looks good, I will give it a try. But how do you deal with other datatypes, like numbers and dates?
I was only using it on string values.

You could store a value in the 'tag' property of the subform controls(n-number,t-text...)

Then when building the listbox values from the controls, make the name start or end with something to indicate the data type. This indicator would then be used when creating the filter.

Let me know if this is the method you want to go and need help modifying the code.

Mig-OAuthor Commented:
Thank you for your comments.

I'll rate it B and share the points, because you showed me a direction what to do and could both give the one or other good idea, but its far from a complete solution (I will ask smaller questions the next times :). I hope I didn't dissapoint anyone.

Btw. sorry for not answering that long, I cancelled creation of this dialog for some time, if I finish it, you will both get a copy! :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.