Multiple Criteria Search Form

[michaelg51]

Hi all, I'm hoping for an answer to search form that's been puzzling me for a
few weeks now. I'm trying to make a search form where users can enter
multiple criteria to search through a number of fields in a table.
I dl'd a demo that seemed to do exactly what I want
(http://allenbrowne.com/ser-62.html) but I keep getting prompted to enter a
parameter(like in a parameter query) even though there is no query that is
being called anywhere in the code. I tried commenting out everything
unnecessary and searching on only one field with no change.
Below is the code that I've modified, I'm getting prompted for the parameter
between 'Me.Filter = strWhere' and 'Me.FilterOn = True'

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim lngLen As Long
'Const conJetdate = "\#mm\/dd\/yyyy\#"

'If Not IsNull(Me.txtIRsrch) Then
' strWhere = strWhere & "([IR] = """ & Me.txtIRsrch & """) and "
'End If

If Not IsNull(Me.txtDeptsrch) Then
strWhere = strWhere & "([Department] = """ & Me.txtDeptsrch & """)"
'and "
End If

'If Not IsNull(Me.txtSYSsrch) Then
' strWhere = strWhere & "([System] = """ & Me.txtSYSsrch & """) and "
'End If

'If Not IsNull(Me.txtDESCsrch) Then
' strWhere = strWhere & "([Description] like ""*" & Me.txtDESCsrch &
"*"") and "
'End If

'Trim trailing characters(" and ")
'Trim trailing characters(", ")
lngLen = Len(strWhere) - 5

'If lngLen <= 0 Then 'no trailing characters
' MsgBox "No Search criteria entered Dolt!", vbInformation, "Nothing
here, move along."
'Else
' strWhere = Left$(strWhere, lngLen) 'trim trailing characters
Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True

'End If

End Sub

Any help at all would be appreciated,
Thanks!

 

[Armen Stein]

Below is the code that I've modified, I'm getting prompted for the parameter
between 'Me.Filter = strWhere' and 'Me.FilterOn = True'

To diagnose this, add a breakpoint right before your error. Create a
query using the form's recordsource, then tack on the Where clause you
are building in your code. When you run the query, you should see
where the problem is - maybe a typo in a field name.

I'm also concerned with your field name Description. See list of
reserved words at http://support.microsoft.com/kb/286335.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[michaelg51]

I added two breakpoints, one on 'Me.Filter = strWhere' and one on
'Me.FilterOn = True'. The parameter is being requested after the first line
but before the second, so it's after the string is applied to the filter but
before the filter is turned on(if I'm understanding it correctly).
I can create a query specifying as criteria, whichever department with no
errors.
The where clause should simply be the department name.

As for 'Description' the actual field name in the table is 'txtDesc' so I
think that at least should be ok.

 

[Armen Stein]

I added two breakpoints, one on 'Me.Filter = strWhere' and one on
'Me.FilterOn = True'. The parameter is being requested after the first line
but before the second, so it's after the string is applied to the filter but
before the filter is turned on(if I'm understanding it correctly).
I can create a query specifying as criteria, whichever department with no
errors.
The where clause should simply be the department name.

I suggest that you use the Immediate window (Ctrl-G) at the first
breakpoint to display the actual Where clause you built in code. Then
use the actual recordsource of your form in a new query and tack on
that Where clause using the SQL view of that new query. This will
confirm whether the Where clause you are building is formed correctly.
I suspect it isn't.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com