Multiple List Box Question

[boredguy2000]

All,
I am attempting to use a list box with multiple possible entries to populate a filtered form. As of now I have dependent combo boxes that work perfectly fine but for this permutation I want the user to be able to pick let's say 1-6 states, have the filtered form(s) open with the header displaying the chosen state(s) and the detail section being the... well details. I have found code that seems perfect:

DIM varItem As Variant
DIM strWhere As String

For Each varItem In Me![YourListBoxName].ItemsSelected
strWhere =strWhere & Me![YourListBoxName].Column(0,varItem)
Next varItem

I am just not quite getting it to fit correctly, and seemingly don't have it doing much of anything. I have changed the one Combo Box to a list box and with multi not selected it works just as before but with multiple the form opens up as if it is getting passed nothing at all.

Sorry so long, I have never posted here and thought the most details possible would garner the best results. Thanks in advance everyone. I have been reading this forum for quite some time and have used literraly 50-100 of you guru's help tips!



Brandon

 

[boredguy2000]

***Sorry not dependent Combo's but two Combo's that pass critereia to a Query that the Form is based upon. THX again!

 

[MajP]

assume the following items were selected:
Cat
Dog
Bird

Your code
strWhere =strWhere & Me![YourListBoxName].Column(0,varItem)
would build the string

"CatDogBird"

Since it is unlikely that "CatDogBird" is an item in your table no records are returned

Depending on how you use strWhere depends on the syntax of the where statement. Post rest of code.

 

[boredguy2000]

The code that I found that seems related was:


Dim varItem As Variant
Dim strWhere As String

For Each varItem In Me![YourListBoxName].ItemsSelected
strWhere = strWhere & "YourUniqueIDField =" _
& Chr(39) & Me![YourListBoxName].Column(0, varItem) & Chr(39) & " Or "
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "

DoCmd.OpenForm "YourFormName", , , strWhere

This is only an example. the names of the objects need to be the names of your objects.

Also the Multi Select property for the listbox needs to be set to Simple or Extended.




I am at best decent with VB so my real problem is understanding how to incorporate this into my existing framework.

"I have changed the one Combo Box to a list box and with multi not selected it works just as before but with multiple selected the form opens up as if it is getting passed nothing at all."

"two Combo's that pass critereia to a Query that the Form is based upon" <-- This is the crux of what I am trying to do, just with multiple selections as a possibility.

Thank you so much for even trying to help me!!!

Brandon

 

[Remou]

The first of these should be what you want, the second may be of interest:

thread702-1382849
thread702-1383271

 

[boredguy2000]

That does seem to be exactly what I was missing. Thank you again everybody. 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?

Really quickly I have a query full of State data, with fields: State, Metric, and then 40 quarters of data. Right 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

so 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.

Brandon

 

[boredguy2000]

Just hoping for a little more help to help me tie things together from the last two posts. Thank you again for any help.

Brandon

 

[Remou]

To get a query that works with a mutiselect listbox, you must use code. The first link I posted shows the code that you need. I do not understand what the problem is after that.

 

[boredguy2000]

Remou,
That code was certainly what I need, and thank you again, I guess my problem is just the where/how of applying that code to what I currently have (hopefully well explained on: 29 Jun 07 15:12).

 

[boredguy2000]

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



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]

a macro attached that opened a form (State_Lookback) with the query mentioned
So, use VBA to open the form and have a look at the 4th argument of the DoCmd.OpenForm method.

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