Generic Filter Dialogs

Posted on 2005-04-13
Medium Priority
Last Modified: 2012-06-21
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?
Question by:Mig-O
  • 3
  • 3
  • 2
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 600 total points
ID: 13774954
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 & ";"

Author Comment

ID: 13774983
Good start. Let's look what the others come up with.
LVL 51

Expert Comment

by:Steve Bink
ID: 13775145
Sure thing.  If you need more information on my proposed solution, just ask.  :)
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 10

Accepted Solution

perkc earned 900 total points
ID: 13775414
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.

LVL 10

Expert Comment

ID: 13775433
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


Author Comment

ID: 13780048
This looks good, I will give it a try. But how do you deal with other datatypes, like numbers and dates?
LVL 10

Expert Comment

ID: 13781430
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.


Author Comment

ID: 13891160
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! :)

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question