|
|
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
|
|
"Wendy08" <Wendy08[ at ]discussions.microsoft.com> wrote in message
news:9253177A-D98E-4885-8291-82350E9957D8[ at ]microsoft.com...
[Quoted Text] >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)
|
|
Thank you for the sugestions. I dont think we anticipated that they would
have so many vendors they would want to select at one time. I will try your
suggestions, but should I have gone a different way with this?
"Dirk Goldgar" wrote:
[Quoted Text] > "Wendy08" <Wendy08[ at ]discussions.microsoft.com> wrote in message
> news:9253177A-D98E-4885-8291-82350E9957D8[ at ]microsoft.com...
> >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)
>
>
|
|
On Wed, 17 Dec 2008 09:10:22 -0800, Wendy08
<Wendy08[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >Thank you for the sugestions. I dont think we anticipated that they would
>have so many vendors they would want to select at one time. I will try your
>suggestions, but should I have gone a different way with this?
One way I've seen is to create a "selections" table with (in your case) a
field for the VendorID and a yes/no Selected field. Create a Subform based on
a query joining this table (empty, no records) one-to-one with the Vendors
table, using a Left Join so you see all the vendors. The user can go down the
list checking the checkbox (thereby creating a record in the selections
table).
Base your Report on a query joining (inner join this time!) the selections
table.
After the report runs, execute a Delete query to clear out the selections
table.
--
John W. Vinson [MVP]
|
|
"Wendy08" <Wendy08[ at ]discussions.microsoft.com> wrote in message
news:E820E454-9184-40FE-B656-4E9F8C255948[ at ]microsoft.com...
[Quoted Text] > Thank you for the sugestions. I dont think we anticipated that they would
> have so many vendors they would want to select at one time. I will try
> your
> suggestions, but should I have gone a different way with this?
If they need to select them one by one, while viewing all vendors, I don't
see a good alternative to some list-based mechanism, whether it's a list box
or a subform as John Vinson has suggested. On the other hand, if only a few
vendors are going to be selected at any one time, you could use a simple
subform based on a VendorsSelected table and use a combo box in that subform
to choose the vendor for each record. I'm sure there are other
possibilities, but it's hard to say what's best without knowing the expected
usage pattern: will they usually be selecting many/most of the vendors, or
only a few of them?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
|