Filter by dialog

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've looked in the posts and didn't find exactly what I was looking for, hope
you can help :-). I have a form called quote. When you pull up that form I
would like the user to get a pop up dialog box with several of the fields
from my form that will allow them to filter. When they enter the information
(name, subdivision, county, quote number), or leave the fields blank, and
click a command button, I would like my quote form to come up with a filter
applied to show only the info they requested. Can someone tell me how to do
this?
 
Hi, Debra.

The way that I like to do it is to build a filter string in a textbox on the
dialog form, and use this string as the WHERE condition for the OpenForm (or
OpenReport) method.

Each field's AfterUpdate event procedure calls a general procedure
WriteFilterString that loops through all of the controls. If criteria has
been specified for the field, it appends this criteria to the filter string,
and when complete, writes it out to a textbox control.

The name of the field is determined by using a control naming convention, in
my case, a 3-character prefix followed by the field name. The code strips
off the prefix before writing out the criteria. For example, I might have a
combo box called cboCustomerID. If the user chooses a customer with ID = 6,
WriteFilterString intially writes out:

[CustomerID] = 6 AND

If the next control was say Year, and the user typed 2005, the string would
then be:

[CustomerID] = 6 AND [Year] = 2005 AND

After all controls have been dealt with the AND is stripped off, resulting in:

[CustomerID] = 6 AND [Year] = 2005

The code for each control's AfterUpdate event is simply:

Call WriteFilterString

The WriteFilterString code is:

‘ Code to write out filter string to a textbox on the form
‘ Leave visible while debugging; can make invisible to users once it’s working

Private Sub WriteFilterString()
Dim ctl As Control
On Error Resume Next

'Reinitialize the textbox
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add it to the filter
string
For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acComboBox, acTextbox

‘ Control naming convention of three-character prefix and fieldname
If (Nz(ctl.Value) <> 0 AND Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) -
3)) _
& "]=" & ctl.Value & " AND "

End If

‘ Place other Case statements that deal with other types of controls
here
‘ that need to be handled differently.
' For example, I often include fields where the user can supply a
range
‘ of dates or magnitudes.

End Select

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

A command button on your form then executes the openform method using the
textbox' value as the WHERE condition:

‘ Code for command button on filter form
Dim strDocName As String
Dim strFilter As String

strDocName = "YourForm"
strFilter = ""

' If no criteria selected, open the form with no criteria
If IsNull(Me!txtFilterString) Then
DoCmd.OpenForm strDocName, acNormal
Else
' otherwise use the textbox value as the filter string
strFilter = Me!txtFilterString
DoCmd.OpenForm strDocName, acNormal, , strFilter
End If

Hope that helps.
Sprinks
 
Back
Top