Panick - Change list select query to multiselect 2

[ZOR]

I have a query derived from someone clicking on 2 listboxes. All worked okay, but now the boss wants multiple selections from each listbox. I have done a search but my time of existance on this planet terminates tommorow unless I can fix it. Is there an easy way to change things? Many thanks

WHERE (((TXMASTERS.SeriesName) Like [FORMS]![MasterQuery2b].[LNAME1].[CAPTION]

I need to gather the multiselected data from the list into a string which replaces the primitive label caption part, being the present selection of a listbox.

 

[PHV]

You have to build dynamically an "IN ('name1','name2',...,'nameX')" list playing with the ItemsSelected collection of the ListBox.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

A few notes.

Private Sub lstMultiSimple_AfterUpdate()
  For Each Item In Me.lstMultiSimple.ItemsSelected
     strItems = strItems & Item
     'Numbering starts at 0
     strList = strList & Me.lstMultiSimple.Column(1, Item)
     Me.txtSimpMClick = strItems
     Me.txtSimpMClickCol = strList
  Next Item

End Sub

 

[ZOR]

Many thanks, the noose is lifting. The selections from the listbox go into strItems, how do I change my display results listbox query which has in the relevant column of the query -

WHERE (((TXMASTERS.SeriesName) Like [FORMS]![MasterQuery2b].[LNAME1].[CAPTION]


WHERE (((TXMASTERS.SeriesName) Like

I don't know how the syntax goes for an IN statement. Sorry, not being lazy, it's just racing against the clock. Thanks again

 

[PHV]

WHERE TXMASTERS.SeriesName IN ('name1','name2',...,'nameX')

FYI, you can't use the Like operator with a list.
If you insist on the Like you have to build something like this:
WHERE (TXMASTERS.SeriesName Like 'name1' OR TXMASTERS.SeriesName Like 'name2' OR ... OR TXMASTERS.SeriesName Like 'nameX')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

As PHV said

" ... WHERE TXMASTERS.SeriesName IN ( " & strList & " )"

Have another look at the code, strList is the selected item; just change the column number to zero, or the relevant column number:

Me.lstMultiSimple.Column(0, Item)

 

[ZOR]

Thanks PHV, however I cannot figure how I get from strItems to ('name1','name2',...,'nameX')

Regards

 

[PHV]

If Me!lstMultiSimple.ItemsSelected.Count = 0 Then
  MsgBox "NO SeriesName selected !"
  Exit Sub
End If
strList = ""
For Each Item In Me!lstMultiSimple.ItemsSelected
  strList = strList & ",'" & Replace(Me!lstMultiSimple.ItemData(Item), "'", "''") & "'"
Next Item
strWhere = "TXMASTERS.SeriesName IN (" & Mid(strList, 3) & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ZOR]

Many thanks both for your help. I will try it out early tomorrow when I get in. Appreciate the fast assistance. Best regards

 

[ZOR]

Had a rough day today, could not get things to work. When I open the form it's asking for a value of strList. If I click an item in the multiselect list, again it asks for strList. Nothing happens in the final list.

Query in multiselect list
SELECT DISTINCT UCase([SportorSports]) AS Sport
FROM TXMASTERS
WHERE (((UCase([SportorSports])) Like [FORMS]![MasterQuery2b].[L4L].[CAPTION] & "*"))
ORDER BY UCase([SportorSports]);

Query in final display list.
SELECT TXMASTERS.SportorSports AS Sport
FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
WHERE ((("TXMASTERS.SportorSports IN (" & Mid([strList],3) & ")")<>False));


Event click on multilist
Private Sub List471_Click()
Dim strList As String
Dim Item As Variant ' items in listbox

strList = ""
For Each Item In Me.List471.ItemsSelected
strList = strList & ",'" & Replace(Me.List471.ItemData(Item), "'", "''") & "'"
Next Item

Me.showit.Caption = strList

Me.List473.Requery

End Sub

Any ideas where its failing? Thanks

 

[PHV]

Replace this:
Me.List473.Requery
with this:
Me.List473.RowSource = "SELECT M.SportorSports AS Sport" _
& " FROM TXMASTERS M INNER JOIN TXCLIPS C ON M.ID1=C.ID1" _
& " WHERE M.SportorSports IN (" & Mid(strList,3) & ")"

And in the Load event procedure of the form:
Me.List473.RowSource = ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ZOR]

Thanks PHV, however the list List473 shows nothing when clicking on the multiselect listbox.

 

[Remou]

What does strList contain?

 

[ZOR]

Its:

SELECT M.SportorSports AS Sport FROM TXMASTERS M INNER JOIN TXCLIPS
C ON M.ID1=C.ID1 WHERE M.SportorSports IN (Cricket')


or if two selections made IN(Football','Freestyle Skiing')

Thanks

 

[PHV]

OOps, sorry for the typo :~/
Replace this:
Mid(strList,3)
with this:
Mid(strList,[!]2[/!])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ZOR]

Many thanks again PHV, that woke it up!!!

 

[ZOR]

I am trying to expand the number of multiselect lists to generate a final query. It worked when it was one list, but it's dead with two. The code below gets used if either listboxes are clicked on. The code does not error?

Public Sub ResetLists()
Dim strList1, strList2 As String
Dim Item1, Item2 As Variant ' items in listbox

strList1 = ""
For Each Item1 In Me.List471.ItemsSelected
strList1 = strList1 & ",'" & Replace(Me.List471.ItemData(Item1), "'", "''") & "'"
Next Item1

strList2 = ""
For Each Item2 In Me.List475.ItemsSelected
strList2 = strList2 & ",'" & Replace(Me.List475.ItemData(Item2), "'", "''") & "'"
Next Item2


Me.TP.RowSource = "SELECT distinct TXCLIPS.NName AS Player" & " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1" _
& " WHERE TXMASTERS.SportorSports IN (" & Mid(strList1, 2) & ") and TXMASTERS.NName IN (" & Mid(strList2, 2) & ") ORDER BY TXCLIPS.NName"

Me.TP.Requery

End Sub

 

[Remou]

What is the content of strList1 and strList2?

 

[ZOR]

Hi Remou. I thought I had fixed it as I had a syntax error,

TXMASTERS.NName IN (" & Mid(strList2, SHOULD BE TXCLIPS.NName

However its still broke.

strList1 = ,'Boxing'
strList2 = ,'SALSARO'

Thanks

 

[ZOR]

Found another error, but still sick

Me.TP.RowSource = "SELECT distinct TXCLIPS.NName AS Player, TXMASTERS.SportorSports AS Sport" & " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1" _
& " WHERE TXMASTERS.SportorSports IN (" & Mid(strList1, 2) & ") and TXCLIPS.NName IN (" & Mid(strList2, 2) & ") ORDER BY TXCLIPS.NName"

I had left out the Sport data. Now if I click the first list List471, nothing happens, but If I also click on the second list, the column titles appear in List TP, but no records