Form Filtering

[Sprinks]

I am having difficulty filtering a form.

I have made a criteria form to allow the user to enter one or more criteria
by which to filter a second form. The after update event of each control
calls a generic WriteFilterString procedure that loops through all the
relevant controls, and writes the filter string to a textbox
(Me!txtFilterString). It also writes a more meaningful string that can be
shown to the user on the form, replacing [Customer] = “45†with [Customer] =
“American Airlinesâ€, for example (Me!txtPrintFilterString).

I have used this strategy in the past successfully with the OpenReport
method. After the criteria is entered, the following code is executed:

If IsNull(Me!txtFilterString) Then
DoCmd.OpenReport strDocName, acViewPreview
Else
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If

I am now trying to use the same strategy to filter a form with the
OpenReport method, but it is not working.

‘ OpenForm with filter command button code
Dim stDocName As String
Dim stWhere As String
Dim stFilterLabel As String

stDocName = "frmItemsDisplay"
stWhere = Me![txtFilterString]
stFilterLabel = Me![txtPrintFilterString]
MsgBox stWhere

DoCmd.OpenForm stDocName, , , stWhere, , , stFilterLabel

If the above code is executed, I get an error dialog box stating “OpenForm
action was cancelled.â€

I also tried changing the stWhere assignment to:

stWhere = “’†& Me![txtFilterString] & “’â€

The before and after MsgBox displays were:

[MfrorProductLine] = 34
‘[MfrorProductLine] = 34’

The form opens with the latter code, but it is unfiltered. Can anyone tell
me what I’m doing wrong?

Below is the code for the WriteFilterString procedure.

Thank you.

Sprinks


‘WriteFilterString Procedure

' Reinitialize control
Me!txtFilterString = ""
Me!txtPrintFilterString = ""

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

If ctl.Tag = "Criteria" Then

‘ If it’s a combo box, use the exact value
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
If ctl.ControlType = acComboBox Then

Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & ctl.Value & " AND "

Me!txtPrintFilterString = Me!txtPrintFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & "'" & ctl.Column(1) & "'" & " AND "

Else

‘ If it’s a textbox, use it as a wildcard
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "] Like " & "'*" & ctl.Value & "*'" & " AND "

Me!txtPrintFilterString = Me!txtPrintFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]= " & "'*" & ctl.Value & "*'" & " AND "

End If

End If

End If

Next ctl

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