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]

In the Change event procedure of the search textbox:

Me!LPlayers.RowSource = "SELECT DISTINCT M.NNAME AS Player" _
& " FROM TXMASTERS M INNER JOIN TXCLIPS C ON M.ID1=C.ID1 " _
& "WHERE M.SportorSports In (" & Mid(strList1, 2) & ")" _
& " AND M.NNAME Like '" & Me![search textbox].Text & "*'"

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

 

[ZOR]

Thanks a lot, works very well. Just had to alter the code a bit, as I think there were letter references wrong to TXMASTERS AND TXCLIPS. Another star and thanks.

 

[PHV]

there were letter references wrong to TXMASTERS AND TXCLIPS
M and C was alias to TXMASTERS and TXCLIPS respectivly, and thus I'd don't qualify them as wrong ...

 

[ZOR]

Hi PHV. I put your code back in. I selected a sport, and then started typing into my search box. As soon as I did it produced a dialogue box - Enter Parameter Value
I entered Mike Tyson and clicked okay. One row appeared in my listbox saying Mike Tyson. When I removed all reference to M,N and C, replacing them with TXMASTERS. and TXCIPS. It all works perfectly, mystery?

 

[PHV]

all reference to M,[!]N[/!] and C
Where is this N coming from ?

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

 

[ZOR]

Forgot to put in references to help solve it.

The Sports in TXMASTERS and the NNAME is in TXCLIPS

Thanks again

 

[PHV]

Ah, I see, sorry for the typo:

Me!LPlayers.RowSource = "SELECT DISTINCT C.NNAME AS Player" _
& " FROM TXMASTERS AS M INNER JOIN TXCLIPS AS C ON M.ID1 = C.ID1 " _
& "WHERE M.SportorSports In (" & Mid(strList1, 2) & ")" _
& " AND C.NNAME Like '" & Me![search textbox].Text & "*'"

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

 

[ZOR]

Thanks PHV. Put your code in and works perfectly. Appreciate the help once again. Having a bad time this end, don't know where the N came from myself, I will be seeing all sorts of things soon at this rate.

 

[ZOR]

Thank heaven this site is back up!!!

I am using this code which works fine, filling a listbox with narrowed down records against the text inputted in Text490 textbox.

Me!LPlayers.RowSource = "SELECT DISTINCT C.NNAME AS Player" _
& " FROM TXMASTERS AS M INNER JOIN TXCLIPS AS C ON M.ID1 = C.ID1 " _
& "WHERE M.SportorSports In (" & Mid(strList1, 2) & ")" _
& " AND C.NNAME Like '" & Me![Text490].Text & "*'"

I am sending the same rowsource to amother listbox, but in this listbox it displays another field in the records. I have been trying all day to find a way to requery that listbox against a multiselected keywords in another list.

The other list has the code:
strList2 = ""
For Each Item2 In Me.List506.ItemsSelected
strList2 = strList2 & ",'" & Replace(Me.List506.ItemData(Item2), "'", "''") & "'"
Next Item2

Is it possible to do what I want? Thanks

 

[ZOR]

I have been trying this code in the listbox containing keywords. It errors, highlighting all the Rowsource code saying it needs to have focus. Any help greatly appreciated.


Private Sub List506_Click()
strList2 = ""
For Each Item2 In Me.List506.ItemsSelected
strList2 = strList2 & ",'" & Replace(Me.List506.ItemData(Item2), "'", "''") & "'"
Next Item2

Me.kw.RowSource = "SELECT TXCLIPS.NNAME AS Player,TXCLIPS.Comments" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& "WHERE TXMASTERS.SportorSports In (" & Mid(strList1, 2) & ")" _
& " AND TXCLIPS.NNAME Like '" & "*" & Me![Text490].Text & "*'& TXCLIPS.Comments In (" & Mid(strList2, 2) & ")"

Me.kw.Requery

End Sub

 

[ZOR]

Set focus to Text490 before inserting rowsource, that fixed the error, but still no results. !!!!!!!

 

[PHV]

& is the concatenation operator, not a boolean one ...

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

 

[ZOR]

Thanks PHV. I've tried as many permutaions as I could, done searches, and this site has been down most of the day.

I thought I would have progressed further by the morning but not so. I get no clues when it errors, as all the code gets highlighted.

I have removed the part:

& "WHERE TXMASTERS.SportorSports In (" & Mid(strList1, 2) & ")" _
& " AND TXCLIPS.NNAME Like '" & "*" & Me![Text490].Text & "*'& TXCLIPS.Comments In (" & Mid(strList2, 2) & ")"

and tried putting it back in various order etc. I just want to pass the previous rowsource into another list but showing the comments field of the records in the set. I am then trying to filter the list down further by searching for words selected from a multilist.

I had done most of the work until my boss wanted multiple selections. Would appreciate any clearer direction on resolving it. Regards

 

[PHV]

Double check your where clause to discover which & you have to replace with AND

 

[ZOR]

Thanks PHV. I reduced my code to try and get part of it done, and then probably spend hours finding how to add the
Text490 in.

I reduced my where down to:

"WHERE TXCLIPS.Comments In (" & Mid(strList2, 2) & ")"

Nothing showed in the resultant output to another list. I debugged the rowsource getting produced and it gave:

SELECT TXCLIPS.NNAME AS Player,TXCLIPS.Comments FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1WHERE TXCLIPS.Comments In ('Goal')


I cannot find any help on searches how to include *Goal* as the word Goal is part of the fields (comments) content

Any guidance appreciated.

 

[PHV]

You can't use Like with the In operator.
I'm afraid you have to build a list of OR criterias.

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

 

[ZOR]

Never rains but it pours. Any idea where I can search and find how to do that? The keywords are in a table when then get put into a listbox for selection. So if the Like statement cannot be used with the IN statement, is there SQL syntax to do it in my query, or do I have to do it in VB. Last question for tonight, Thanks

 

[PHV]

The keywords are in a table
Good news: You may join this table with the LIKE operator in the ON clause and the IN operator in the WHERE clause.

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

 

[ZOR]

Thanks PHV. The table is not a related table, would this be a problem. Its just a table of words. Glad its good news, but I'm still in the dark with pressure to fix it. Regards