Listbox Rowsouirce extension? 1

[ZOR]

I have a listbox that gets filled from an earlier listbox being mutiselect. The 1st listbox is for selecting a sport or sports. The second list fills with player names that are applicable. However the user wants 2 conditions. The first is that after selecting the sport/sports, he does not want list 2 to show records until a user starts typing into a search textbox below the list. How/If to do? I cannot hard code it in the list prior to running as a new rowsource gets put in.

List1 supplies rowsource to List 2 as=

Me.LPlayers.RowSource = "SELECT DISTINCT TXCLIPS.NNAME AS Player" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1 " _
& "WHERE (((TXMASTERS.SportorSports) In (" & Mid(strList1, 2) & ")));"

Thanks

 

[PHV]

The table is not a related table
But yes, by the keyword being in the comments !

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

 

[ZOR]

Thanks PHV. Do I need to go into table relationships to create a join between the field COMMENTS and the Keywords Table KEYWORDS field.

Would this cause problems to getting records where there is no link?

Can hardly keep eyes open at the moment. Thanks

 

[PHV]

Do I need to go into table relationships
NO.
Reread my post stamped 1 Jul 07 17:12 and try to make sense with it.

 

[ZOR]

You may join this table with the LIKE operator in the ON clause and the IN operator in the WHERE clause"

So if I create a join in the tables to the Keyword Table, I can persue the Like with IN? Am i correct?

I will be away until Tuesday night now, away from Internet, going to be grim 2 days. Will check for any further comments from you in the morning. Regards

 

[ZOR]

I have spent now days/hours getting nowhere.

Me.kw.RowSource = "SELECT TXCLIPS.NNAME AS Player,TXCLIPS.Comments, KEYWORDS.Keyword" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 AND TXCLIPS ON KEYWORDS.KEYWORD" _
& "WHERE TXCLIPS.Comments IN LIKE ( & " & Mid(strList2, 2) & ")"

Me.kw.Requery


Can anyone sort my SQL out before I jump off a bridge
please. Thanks

 

[Remou]

The Join is Like. You cannot say IN LIKE.

 

[PHV]

What about this ?
Me!kw.RowSource = "SELECT C.NNAME AS Player, C.Comments, K.Keyword" _
& " FROM (TXMASTERS AS M INNER JOIN TXCLIPS AS C ON M.ID1=C.ID1)" _
& " INNER JOIN KEYWORDS AS K ON C.Comments Like '*' & K.KEYWORD & '*' " _
& "WHERE K.KEYWORD IN ( & " & Mid(strList2, 2) & ")"

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

 

[ZOR]

Thanks Remou. I tried putting the LIKE inside ( & " & Mid(strList2, 2) & ")" . I am lost as to how the IN data or whatever can get changed so the query sees a *Goal* etc. I presume it would work this way?

Basically I am just trying to convert what would be a normal single text input that would go into a query and produce records that have that word (keyword) within the text of a record - TO a multiselect Listbox that contains a list of Keywords held in a seperate, unrelated table.

The SQL you see above is the remains of multiple strained attempts to make it work, having no knowledge in depth of SQL, and no reference to anything direct to what I am trying to do.

Thanks

 

[ZOR]

Thanks PHV, sorry your post came in while my last one went out. I tried it but it produced nothing. I debugged what the final rowsource was:

SELECT C.NNAME AS Player, C.Comments, K.Keyword FROM (TXMASTERS AS M INNER JOIN TXCLIPS AS C ON M.ID1=C.ID1) INNER JOIN KEYWORDS AS K ON C.Comments Like '*' & K.KEYWORD & '*' WHERE K.KEYWORD IN ( & 'Goal')

Any Help. Thanks

 

[PHV]

Sorry for the typo:
& "WHERE K.KEYWORD IN (" & Mid(strList2, 2) & ")"

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

 

[ZOR]

EUREEEEEKA! It works. 4 days of hell fixed in minutes. Thanyou PHV.

 

[PHV]

4 days of hell fixed in minutes
I disagree as you've got the solution at 1 Jul 07 17:12