Filter Command Button

  • Thread starter Thread starter Jenny
  • Start date Start date
J

Jenny

HELP Please! I have a continuous form based on a qry called qry2009PO. I
want to be able to filter the records based on State (cboState) and County
(lstCounty) -which lstCounty is populated via an after update on cboState.
Both are text fields. Here is the code that I have. I get Runtime error
'2580' The record source 'Select * FROM 2009PO WHERE [State] like "CO*'"
specified on this form or report does not exist.
(CO is the state I chose in cboState).

*********code start**************
Private Sub cmdSearch_Click()
Dim strSQL As String

'If both State(cboState) and County (lstCounty) have data entered then
restrict using both.
If Not IsNull(Me!cboState) And Not IsNull(Me!lstCounty) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM 2009PO "
strSQL = strSQL & "WHERE [State] Like """ & Me!cboState & "*"""
strSQL = strSQL & " AND [County] Like """ & Me!lstCounty & "*"""

Me.RecordSource = strSQL
'If State(cboState) has data then restrict by State.
ElseIf Not IsNull(Me!cboState) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM 2009PO "
strSQL = strSQL & "WHERE [State] Like """ & Me!cboState & "*"""

Me.RecordSource = strSQL
End If
' Empty out the search criteria
Me!cboState = Null
Me!lstCounty = Null

End Sub
******************************************************
 
'Select * FROM 2009PO WHERE [State] like "CO*'"

the quotes and double quotes don't seem to match up, so that could be your
problem - or just a typo in your post.

Put breakpoints on the 2 lines: Me.RecordSource = strSQL
Run the code. When the breakpoint is reached, type..

? strSQL

...in the Immediate window of the VB editor. Does the result look right? Can
you paste the result into SQL view of the query designer and get it to run?
 
I've tried going a different route. Now I get it to filter on the state, but
not the county as well. Here is the code.

********************
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State] = """ & Me.cboState & """) AND "
End If

If Not IsNull(Me.lstCounty) Then
strWhere = strWhere & "([County] = """ & Me.lstCounty & """) AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
***************

George Nicholson said:
'Select * FROM 2009PO WHERE [State] like "CO*'"
Click to expand...

the quotes and double quotes don't seem to match up, so that could be your
problem - or just a typo in your post.

Put breakpoints on the 2 lines: Me.RecordSource = strSQL
Run the code. When the breakpoint is reached, type..

? strSQL

...in the Immediate window of the VB editor. Does the result look right? Can
you paste the result into SQL view of the query designer and get it to run?

--
HTH,
George


Jenny said:
HELP Please! I have a continuous form based on a qry called qry2009PO. I
want to be able to filter the records based on State (cboState) and County
(lstCounty) -which lstCounty is populated via an after update on cboState.
Both are text fields. Here is the code that I have. I get Runtime
error
'2580' The record source 'Select * FROM 2009PO WHERE [State] like "CO*'"
specified on this form or report does not exist.
(CO is the state I chose in cboState).

*********code start**************
Private Sub cmdSearch_Click()
Dim strSQL As String

'If both State(cboState) and County (lstCounty) have data entered then
restrict using both.
If Not IsNull(Me!cboState) And Not IsNull(Me!lstCounty) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM 2009PO "
strSQL = strSQL & "WHERE [State] Like """ & Me!cboState & "*"""
strSQL = strSQL & " AND [County] Like """ & Me!lstCounty & "*"""

Me.RecordSource = strSQL
'If State(cboState) has data then restrict by State.
ElseIf Not IsNull(Me!cboState) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM 2009PO "
strSQL = strSQL & "WHERE [State] Like """ & Me!cboState & "*"""

Me.RecordSource = strSQL
End If
' Empty out the search criteria
Me!cboState = Null
Me!lstCounty = Null

End Sub
******************************************************
Click to expand...
Click to expand...
 
Back
Top