Wendy08 said:
I have a Multi Select List box on a form. The user selects the items in the
list and a report is generated. If the user selects more than 100 items in
the list box we receive a 7769 error...The filter operation was canceled.
The
filter would be too long. Is there a way to allow the user to select more
than 100 items? Thanks.
Private Sub OK_Click()
Dim varItem As Variant
Dim strInClause As String
If Me!List39.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one Vendor.")
Else
strInClause = "[VendorName] IN ("
For Each varItem In Me!List39.ItemsSelected
strInClause = strInClause & """" & Me!List39.Column(0, varItem) &
"""" & ","
Next varItem
strInClause = Left(strInClause, Len(strInClause) - 1) & ")"
End If
DoCmd.OpenReport "rptF3", acViewPreview, , strInClause
End Sub
That's a lot of items to select in a list box! But if you want to do it
that way, there are a couple of ways you could manage it.
One way is to use code to insert all the selections as records in a table,
and refer to that table in the where-condition using an "In" clause:
DoCmd.OpenReport "rptF3", acViewPreview, , _
"VendorName In (SELECT VendorName FROM VendorsSelected)"
Another way, which would not run very efficiently but may be adequate for
your needs, is to use a function like this in your where-condition:
'------ start of code ------
Function IsSelectedInListbox( _
pListboxRef As String, _
pValue As Variant) _
As Variant
' Accepts a string that can be interpreted as a reference to a list box,
' and a value. Returns True if the value is selected in the list box,
' False if not. Note that it's the bound column of the list box that is
' tested for the value, not necessarily the displayed column.
'
' Arguments:
'
' pListboxRef -
' This is a string of the form "FormName!ListboxName", or
' "FormName!SubformName!ListboxName",
' or "Formname!SubformName!SubSubFormName!ListboxName", etc.
' Note that subform names must be provided as the names of the
' subform *controls* on their parent forms, as in normal
' form/subform references.
'
' pValue -
' This is the value that will be sought in the selected items
' of the list box.
'
' Copyright © Dirk Goldgar, 2007-2009
' Permission: You may use this function in your applications and
' distribute it freely, provided that the copyright notice
' remains unchanged.
On Error GoTo Err_Handler
Dim frm As Access.Form
Dim lst As Access.ListBox
Dim astrObjectName() As String
Dim varRow As Variant
Dim strValue As String
Dim I As Integer
IsSelectedInListbox = False
If IsNull(pValue) Then Exit Function
astrObjectName = Split(pListboxRef, "!")
I = LBound(astrObjectName)
Set frm = Forms(astrObjectName(I))
I = I + 1
While I < UBound(astrObjectName)
Set frm = frm(astrObjectName(I)).Form
I = I + 1
Wend
Set lst = frm(astrObjectName(I))
strValue = CStr(pValue)
For Each varRow In lst.ItemsSelected
If lst.ItemData(varRow) = strValue Then
IsSelectedInListbox = True
Exit For
End If
Next varRow
Exit_Point:
Set lst = Nothing
Set frm = Nothing
Exit Function
Err_Handler:
IsSelectedInListbox = CVErr(Err.Number)
Resume Exit_Point
End Function
'------ end of code ------
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
