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.

 

[ZOR]

If I rem out the last part of the SQL, the first list works, showing 2 columns of data.

" WHERE TXMASTERS.SportorSports IN (" & Mid(strList1, 2) & ")"


Remmed this bit out
' and TXCLIPS.NName IN (" & Mid(strList2, 2) & ") ORDER BY TXCLIPS.NName"

 

[Remou]

Are you sure that there is a result for this query? Try pasting the sql (debug.print) into the query design window.

 

[PHV]

the first list works, showing 2 columns of data
With a row containing 'SALSARO' in the first column ?

 

[ZOR]

Hi PHV. On remming out the last part of SQL, clicking the first list shows 2 columns. The 1st column is all the Names of Players (ie all, not the selected SALSARO as its dead after remming it out. The second column shows all records having the name of the selected sport in them.

 

[PHV]

My question was:
do you see SALSARD in the resultset when you comment out the NName filter ?

 

[PHV]

In other words:
Is SALSARD play Boxing ?

 

[ZOR]

How embarrasing, you have hit it on the head PHV. SALSARO is not a boxer. I need to requery the second lists contents against the selected sport. Thankyou to you and Remou, I will balance up my debt on stars. So pleased you sussed it, glad it was a weekend. Best regards

 

[ZOR]

Trying to do a variation, this time having a listbox containing several columns where the criteria gets derived from clicked on listboxes. I have one user select listbox (List471) which has the code

Private Sub List471_Click()
Dim strList1 As String
Dim Item1 As Variant ' items in listbox

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

Me.SPTL.Caption = strList1
Me.List487.Requery
End Sub

It produces strList1 data okay. However I get no data showing up in my multicolumn listbox have the SQL inside as


SELECT DISTINCT TXMASTERS.Barcode, TXMASTERS.EpisodeTitle, TXMASTERS.Competition, TXCLIPS.Comments
FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
WHERE (((TXMASTERS.SportorSports) In (" & Mid(strList1, 2) & ")));


It does nor error, all I get is the list column headings

Any ideas, thanks

 

[boredguy2000]

All,
I am trying to use all of this amazing helpfulness on a similar problem I am having.

When I run the first code (27 Jun 07 16:59) I get an error saying: Me.txtSimpMClick Compile Error: Method or Data member not found. I have another post sort of going asking basically the same question (actually less advanced, but I have to keep trying!) Thanks again.

 

[Remou]

Have you a control called txtSimpMClick? You must edit the code to suit your application. Please post your reply with the edited code in your original thread. Please post a reference to that thread here.

 

[boredguy2000]

*****
Really quickly I have a QUERY full of State data, with the following fields:: State, Metric, and then 40 quarters of data.

As of now I have State and Metric criteria filled with [Forms]![Economic Data Menu]![State_Level_Drop];
[Forms]![Economic Data Menu]![Metric_Level_Drop]
These are populated from my (at least for now) two combo boxes

Now for the bad part: without sounding too terribly out of my element: if I put the code from that link in the AfterUpdate of my ListBox, is the rest of my overall plan just to leave the criteria fields in the original query basically untouched?

TO THIS POINT there was no need for any VB that is why I am having a little trouble picturing how to tie it all together.***


SO Remou I basically took your code stuck it in the AfterUpdate of the listbox and now sit stumped (but I think on the right path)

 

[Remou]

This is getting quite difficult. I have already said that it is not possible to do what you wish without code. I have said that you should post the code, as edited to suit your application. I have requested that yoy post it in your original thread. What else can I say?

 

[boredguy2000]

I apologize for the difficulty, certainly not my intent.

I understand that what I want to do cannot be done without VB. I just don't really have any code to post up to this point.

I also understand that you cannot help me with my code issues without the code to fix.

So let me please ask this in a new way: I have a form with: One list box (State_Drop) that I want multiple choices to be possible (using the code you provided, with some alterations of course) to hopefully feed to a criteria field in a pre-existing query.
One other combo box (Metric_Drop) that feeds to that same pre-existing query
and One command button (State_Click) that did just have a macro attached that opened a form (State_Lookback) with the query mentioned above (State_Data_Query).

I guess at this point I need a little bit more of a "Big Picture" help rather than just a code fix.

I really am sorry this has proven so difficult, but thank you for sticking with me.

 

[PHV]

boredguy2000, please stop hijacking ZOR's thread and go back to yours (as already said 2 times by Remou):
thread705-1383426

 

[boredguy2000]

Alright now I am even more sorry! I did not catch that was what Remou was saying.. truly sorry Remou, PHV, and ZOR.