MultiSelect ListBox Error (1 Viewer)

[freidaf]

Hello,
I have a form that contains 2 multi select listboxes. The form opens a report filtered based on selections from the listboxes. I am trying to use a function by Duane Hookum that I found online but I am getting an error message: 2491: The action or method is invalid because the form or report isn’t bound to a table or query. The function is pulling the correct selections from the listbox according to the msgbox. The error occurs in this line of code: DoCmd.ApplyFilter Task. Any help would be greatly appreciated!

Function:
Option Compare Database
Option Explicit


Public Function BuildIn(lboListBox As ListBox, _
strFieldName As String, strDelim As String) As String

'send in
' List box control object
' Name of the field
' Delimiter which is typically a single quote for text
Dim strIn As String
Dim varItem As Variant

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & strFieldName & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn
End Function

Command Button Code:

Private Sub OK_Click()

DoCmd.SetWarnings False

Dim strSearch As String
Dim Task As String
strSearch = "1 = 1 "

' this code assumes both Model and Color are text/strings
strSearch = strSearch & BuildIn(Me.lstProduct, "QProductCode", "'")
strSearch = strSearch & BuildIn(Me.lstFunction, "QFA_Name", "'")
MsgBox (strSearch)

Task = "select * from qryRpt_ByProductMainDetail_Select where " & strSearch

DoCmd.ApplyFilter Task

DoCmd.OpenReport "rptChecklists_By_Product", acViewPreview, , strSearch

End Sub

 

[Ranman256]

I would stop using Multiselect lists. Too much programming.

Instead, use single click list, when dbl-clicked it adds the item to a tPickedList table.
That list joins to the main table and only those records pulled in the query.
No code, it can be done with 1 or 2 macros.

 

[bastanu]

Can you try:
Private Sub OK_Click()

DoCmd.SetWarnings False

Dim strSearch As String
Dim Task As String
strSearch = "1 = 1 "

' this code assumes both Model and Color are text/strings
strSearch = strSearch & BuildIn(Me.lstProduct, "QProductCode", "'")
strSearch = strSearch & BuildIn(Me.lstFunction, "QFA_Name", "'")
MsgBox (strSearch)

Task = "select * from qryRpt_ByProductMainDetail_Select where " & strSearch

'DoCmd.ApplyFilter Task

DoCmd.OpenReport "rptChecklists_By_Product", acViewPreview, , strSearch

End Sub

 

[freidaf]

Can you try:
Private Sub OK_Click()

DoCmd.SetWarnings False

Dim strSearch As String
Dim Task As String
strSearch = "1 = 1 "

' this code assumes both Model and Color are text/strings
strSearch = strSearch & BuildIn(Me.lstProduct, "QProductCode", "'")
strSearch = strSearch & BuildIn(Me.lstFunction, "QFA_Name", "'")
MsgBox (strSearch)

Task = "select * from qryRpt_ByProductMainDetail_Select where " & strSearch

'DoCmd.ApplyFilter Task

DoCmd.OpenReport "rptChecklists_By_Product", acViewPreview, , strSearch

End Sub

Thank you bastanu, but I am still getting the same error.

 

[freidaf]

I would stop using Multiselect lists. Too much programming.

Instead, use single click list, when dbl-clicked it adds the item to a tPickedList table.
That list joins to the main table and only those records pulled in the query.
No code, it can be done with 1 or 2 macros.

Thank you for the suggestion. I will give it a try. Could you share the macros in your example?
Thanks again!

 

[Pat Hartman]

DoCmd.ApplyFilter -- is only valid for a bound form. The error indicates that your form is not bound.

 

[bastanu]

If you commented out the Docmd.ApplyFIlter like I showed you on which line are you getting the error?

 

[freidaf]

If you commented out the Docmd.ApplyFIlter like I showed you on which line are you getting the error?

If you commented out the Docmd.ApplyFIlter like I showed you on which line are you getting the error?

bastanu, I commented out the ApplyFilter line and did NOT get the error. Must have done something wrong the first time because the line was not commented out. Problem solved. Thank you very much!

 

[freidaf]

DoCmd.ApplyFilter -- is only valid for a bound form. The error indicates that your form is not bound.

Thank you!

 

[bastanu]

Glad to hear you solved it!

Cheers,
Vlad

 

[moke123]

A little late in responding but I had this open on my office computer from yesterday and got called away before posting it.

Heres a function i use for multi-select listboxes which makes composing an In clause easier. It also helps shorten the coding needed.