List box can't handle large stirngs for a filter.

[Rocky5]

I have this code that selects the records to print, however from the string
when I select more than 300 records or so the report will not launch, but it
will on stand alone for over 2000 records.

The error message I get is : " The filter operation was canceled. The Filter
would be too long"


Here is the code. the list box contains the results/ Records I want to
print. Is there a way around that? waht is the limitation on the filter.

Thanks for your help in advance !
..........................................................................................................................................................................................................................................................
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
Dim varItem As Variant
Dim strSelected As String

strSelected = ""

For Each varItem In Me.lstInstrIssue.ItemsSelected
'If the selected item is numeric, uncomment the following line
strSelected = strSelected & Me.lstInstrIssue.Column(0, varItem) & ","

'If the selected items are textual, uncomment the following line
' strSelected = strSelected & """" & Me.List2.Column(0, varItem) &
""","
Next varItem

'Get rid of the trailing comma
'If the selected item is numeric, uncomment the following line
If Len(strSelected) > 0 Then
strSelected = "(" & Left(strSelected, Len(strSelected) - 1) & ")"
Else
msgg:
MsgBox "You must Select at least one record", vbCritical, "My
Application"
GoTo endit
End If
'If the selected items are textual, uncomment the following line
' strSelected = "(" & Left(strSelected, Len(strSelected) - 2) & """)"
If Len(strSelected) < 0 Then
GoTo msgg
Else
End If

Debug.Print strSelected
'MsgBox "You Selected Record(s) Number " & strSelected

DoCmd.OpenReport "C2 Profibus Loop", acViewPreview, , "CMPNT_ID IN " &
strSelected
endit:
Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

 

[Douglas J. Steele]

One approach is to store the details of the desired records in a temporary
table, and then join that table to your existing one so that only the
matching records appear on the report.

If you're going to use a temporary table, you might consider putting it in a
temporary database, to reduce the amount of database bloat. Tony Toews has a
sample at http://www.granite.ab.ca/access/temptables.htm

 

[pietlinden]

I have this code that selects the records to print, however from the string
when I select more than 300 records or so the report will not launch, butit
will on stand alone for over 2000 records.

The error message I get is : " The filter operation was canceled. The Filter
would be too long"

Here is the code. the list box contains the results/ Records I want to
print. Is there a way around that? waht is the limitation on the filter.

Thanks for your help in advance !
............................................................................................................................................................................................................................................................
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
   Dim varItem As Variant
   Dim strSelected As String

   strSelected = ""

   For Each varItem In Me.lstInstrIssue.ItemsSelected
       'If the selected item is numeric, uncomment the following line
       strSelected = strSelected & Me.lstInstrIssue.Column(0, varItem) & ","

       'If the selected items are textual, uncomment the following line
       '   strSelected = strSelected & """" & Me.List2.Column(0, varItem) &
""","
   Next varItem

   'Get rid of the trailing comma
   'If the selected item is numeric, uncomment the following line
   If Len(strSelected) > 0 Then
      strSelected = "(" & Left(strSelected, Len(strSelected) - 1)& ")"
    Else
msgg:
    MsgBox "You must Select at least one record", vbCritical, "My
Application"
    GoTo endit
    End If
   'If the selected items are textual, uncomment the following line
   '   strSelected = "(" & Left(strSelected, Len(strSelected) - 2) & """)"
   If Len(strSelected) < 0 Then
   GoTo msgg
   Else
   End If

    Debug.Print strSelected
   'MsgBox "You Selected Record(s) Number " & strSelected

   DoCmd.OpenReport "C2 Profibus Loop", acViewPreview, , "CMPNT_ID IN" &
strSelected
endit:
Exit_Command10_Click:
    Exit Sub

Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click

End Sub

sounds like you need a new way to filter them... you're hitting a hard
limit, I think. What if you were to write the records to a temporary
table, and then inner join that table to your original source table?
Then you could delete the records after the report printed.

 

[Guest]

Another way to do it is to include a user-defined-function
written in VBA as part of the criteria for the source for
the combo box.

function selected(id)
if eval(id & " in (" & strFilter & ")" ) then
selected = true
endif
end function

There are many ways to write functions like that, any
kind of code for selecting records will work.

Another way to do it is use a user-defined-function
written in VBA as the recordsource, which allows
you complete control over which records are displayed,
but that is seldom used.

(david)