Multi List Box Filter

[fipp]

I appreciate your help in advance. I am new to most of this and I need a little assistance. I have searched the web and found some articles etc... I am just not sure how to apply it to mine because I am unfamiliar with some of the wording etc.. I understand a very little of what people are saying but I can't quite put it all together.

I have a form called "reportfilterfrm" in this form I have 3 list box's. On 2 of the list box's multi select is selected to "None" and I have them bound to the criteria field in my query and that works perfect. The 3rd list box multi select is set to "Simple" and I am not sure how to get the query to refrence this.

The query is called "reportfilterqry"

listbox #3 is called [opponent] I would like it to be the criteria for [opponent] in the "reportfilterqry" It is a text field.

Thanks again for your help. I really appreciate it!

 

[boblarson]

A multiselect listbox can't be referenced in the same way as a non-multiselect one. For a multiselect, you need to iterate through the items selected property to get the selected values, so it won't work in the normal way with a query. You need to loop through and dynamically create your where clause.

 

[fipp]

Is there any way you can help me with that?
I really appreciate your help.

 

[boblarson]

I don't have the mental capacity at the moment (I'm still at work) to work on that one right now, but I've sent myself the link to this post as a reminder and in about 4-5 hours or so, when I get home, I will attempt to come up with something, if you (or someone else who does feel so inclined) don't come up with something first.

 

[fipp]

I appreciate your help

I appreciate you taking the time to help me. It will be a major problem solved for me.

Thanks again.

 

[boblarson]

How are you using the query? Is it for a report? I hope so as I can deal with that better than just trying to open a query.

 

[fipp]

It is for multiple reports.

 

[bwyrwitzke]

I’ve got a list box based on a query generated on the fly (code below) that allows me to sort data in a number of ways. Right now, the list box does not support multi-select. I can open either one of the records or all. I really need to be able to pick and choose individual records. Bob mentioned a while back that this required some iterative code, but nothing else was posted. Any help on this would be greatly appreciated.

Thanks.

Bruce

Function BuildSQLstring(strSQL As String) As Boolean

  Dim strSELECT As String
  Dim strFROM As String
  Dim strWHERE As String
  Dim strORDER As String

strSELECT = ""
strFROM = ""
strWHERE = ""
strORDER = ""

strSELECT = "DISTINCTROW tblBids_Detailed.*, qryContacts.* "
strFROM = " (tblBids_Detailed INNER JOIN qryContacts ON tblBids_Detailed.Contact_ID=qryContacts.Contact_ID) "

If ckProduct_Search = True And cboProduct > 0 Then
  strFROM = strFROM & "INNER JOIN qryBids ON tblBids_Detailed.Bid_Detailed_ID = qryBids.Bid_Detailed_ID "
  strWHERE = strWHERE & " AND qryBids.tblBids.Product_ID = " & Chr$(34) & cboProduct & Chr$(34)
End If

If ckCompany = True And cboCompany > 0 Then
 strWHERE = strWHERE & " AND qryContacts.tblContacts.Reseller_ID = " & cboCompany
End If

If frameOrder = 1 Then
 strORDER = " tblBids_Detailed.Bid_EndUser, tblBids_Detailed.Bid_Date "
End If

If frameOrder = 2 Then
 strORDER = " tblBids_Detailed.Bid_Expiration, tblBids_Detailed.Bid_EndUser "
End If

If frameOrder = 3 Then
 strORDER = " tblBids_Detailed.Bid_Updated, tblBids_Detailed.Bid_EndUser "
End If

If ckLost = True Then
 strWHERE = strWHERE & " AND " & " tblBids_Detailed.Bid_Status=" & Chr(34) & "lost" & Chr(34)
End If

If ckOpen = True Then
 strWHERE = strWHERE & " AND " & " tblBids_Detailed.Bid_Status=" & Chr(34) & "open" & Chr(34)
End If

If ckWon = True Then
 strWHERE = strWHERE & " AND " & " tblBids_Detailed.Bid_Status=" & Chr(34) & "won" & Chr(34)
End If

If ckClosed = True Then
 strWHERE = strWHERE & " AND " & " tblBids_Detailed.Bid_Status=" & Chr(34) & "closed/lost" & Chr(34)
End If

'------------------------------
'Build SQL String
'------------------------------

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM

If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
strSQL = strSQL & " ORDER BY " & strORDER

'MsgBox strSQL

BuildSQLstring = True

End Function

To view all applicable records, I use:

Private Sub cmdAllBids_Click()
    
    UpdateQuery
    
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmBids"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Forms!frmBids.cmdPrevious.Visible = True
    Forms!frmBids.cmdNext.Visible = True
        
End Sub

And to view a single record, I’m using:

Private Sub lstBids_DblClick(Cancel As Integer)
    
    Dim strDocName As String
    Dim strLinkCriteria As String
    
    strDocName = "frmBids"
    strLinkCriteria = "[Bid_Detailed_ID]=" & Me![lstBids]
    DoCmd.OpenForm strDocName, , , strLinkCriteria
            
End Sub

 

[bwyrwitzke]

I found a work around...

but I know this can be done better.

I added the following code to re-query based on the multiple items selected in the lst box

'------------------------------------------------------------
If gblMultiSelect = True Then
  Dim strMultiSelect As String
  
  Set lst = Me.lstBids
   
  For Each varItem In lst.ItemsSelected
    strMultiSelect = strMultiSelect & " OR " & " (tblBids_Detailed.Bid_Detailed_ID=" & Nz(lst.Column(0, varItem)) & ")"
  Next varItem

  If strMultiSelect <> "" Then 
    strWHERE = strWHERE & " AND (" & Mid$(strMultiSelect, 5) & ")"
  End If

End if
'-------------------------------------------------------------

A side effect is that my list box is re-queried to show the items from the multi-select. Ideally, I would like the list box to not change (no re-query) and only the selected records are opened. Any thoughts?

Thanks.

Bruce