list box selection as filter

[okschlaps]

I need a little help understanding how filters work in ADP. I have a form
that worked in an mdb that allowed users to select multiple items from a list
box and have them applied as filters on a report.
When I try to run this in ADP i get "Error 207 - Invalid column name" and
then the value of the list box selection.
Below is the code (I believe I got it from an Allen Browne post) and I can't
find where it's misreading the filter as a column.

Private Sub cmdViewReport_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptWksht_byTRS"

With Me.lstTRS
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text
strDescription = strDescrip & """" & .Column(1, varItem) &
""","
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[T-R-S] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Township-Range,Section: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

Thanks

 

[Sylvain Lafontaine]

Try using the single quote (') instead of the double quote (") as your
string delimiter when working against SQL-Server in a ADP project. On
SQL-Server, the standard use for the double quote is to be used as a name
delimiter, ie., the same role as with the []; hence probably the source of
the error message about an invalid column name.

Also, using filters frequently won't work correctly for reports and forms
because these filters are executed on the SQL-Server and might not be
applied correctly (for example when the original source for the records is a
stored procedure). Take a look with the SQL-Server Profiler to see what's
going on between Access and the SQL-Server.