Currently I'm using Access to try and take 4 different listbox fields and combine them on the back end into one listbox. The problem I'm having is that for some reason when I'm doing this, I'm ending up in a situation where instead of giving me back the values contained in my listbox fields (that are visible to the user) it's giving me back a SQL statement.



Now, I'm really confused, because it's not just putting out one SQL statement once. It's putting out the same SQL statement in the listbox over and over and over again. Like a solid 10 or so times with large amounts of space in between.



Is there something I'm doing wrong that I just can't see?

Thanks!

First Suggestions:
- Clean up your code, you have alot of malformed For statements.
for instance:

For listHolder = 0 To listHolder = NoQDM
  QDM.SetFocus
  Storage(listHolder) = QDM.RowSource
Next

how is it you plan on the code actually going through this without some potential problems? Listholder=0 to listholder= NOQDM? So what your saing is NoQDM=0 to NoQDM? or is it listholder=0 to NoQDM?

Get those parts of your code cleaned up first and you will resolve some residual problems.

Second Suggestion:
- Make sure you are using a consistent format when plugging items into a listbox.
for instance:

Storage(listHolder) = QDM.RowSource
.
.
.
Storage(listHolder) = QOM(listHolder).Value

You want to know why your spitting out alot of SQL styled queries into the listbox....QDM.RowSource is your problem.

Lastly,
Are your listbox mutli-selectable or single-select. I assume that since your using a listbox instead of a combobox that you have the multi-select option enabled. As such where are you validating if a item has been selected or not? Also, why not add the items to a Selected event to push them to your consolidation listbox?

I think I need to take a step back for a second and maybe explain how I got to where I am. The code is kind of a mess because it didn't work how I expected it to, and I've been piling on solution after solution. Which of course means I should probably explain what I expected it to do, or want it to do.

Basically, breaking it down into Front and Back End.

Front End

1. User selects multiple choices from multiple fields.
2. User clicks button to run commands to receive desired data.

The front end seems like a pretty easy concept, and the back end is where I start going like this

Back End

Basic Concept: Using a unique identifier assigned to each value in each respective listbox, use a loop a to cycle through and determine whether or not the value is selected. If the value is selected, run the command listed in the case statement.

Now, I thought that was pretty easy, but I haven't done this sort of thing with an Access database, and my VBA background is kind of weak. [I'm willing to learn though ]

So maybe in pseudocode I saw this working like this.



So where part of my difficulty arose, was because I don't understand how Access is getting the displayed information, I'm unsure how to use the values which I already have defined in the tables as unique identifiers. This is why I have the loop in the structure currently trying to assign everything to an array (which was because the term .AddItems was confusing me, and I couldn't get it to work, based on what the debugger was telling me, I assumed it needed a string input).

So potentially, if I could just check the individual listboxs for what is selected, I could just use the numbers I have and cut out a lot of this work.

Would a reasonable way to do this possibly be:



So in summation:

1. What is the correct way to get a value from Access? (do I have to use SQL?)
2. When you say clean up my code, I know it's only pseudocode, but does it make more sense, and what am I doing wrong?

Notes:

Well, the reason I went with RowSource was because when I tried to use .value, the system responded that it was Null. I guess I don't understand where Access is pulling the value displayed in the user interface from. So while I can remove rowsource, I'm really unsure what to replace it with.

Thank you!

Your Psuedocode is really good. If i remember right (as i have been away from the VBA language) there should be a ForEach loop structure that would be perfect for what you are doing.

Also, i assume each of the listboxes are multi-selects.

What you can do, and i have done in the past in Access is drop a hidden field on the form, make it a list box also. Each time they click on a value in a list box enter it into the hidden list. Once its all said and done, you simply iterate through the hidden list box and wham you got it.

Catch with the hidden list box. You need to incorporate code to validate what has already been selected to make sure you are not adding already selected items to the hidden list box. Its not that hard but you still need to do it. Ill see if i can dig up some example code for this.

Lastly, if you simply take your psuedocode diagram it out and then program according to your diagram, your answer will be right in front of you. All you have to do is simply turn on the flash light and walk the path through the fog of VBA.

Word of Advise:
I dont care if the language you are programming in ignores white space or not. Its always best practice to keep segments of code together in a nice pack. I try to group variables together in a nice packed area, seperate decision/loops from the rest of a sub or function with 1 white space above and below regardless of where it is. Lastly, keep it organized and WELL COMMENTED, so in the event someone else looks at it, they dont say "What the hell was he/she thinking". Well commented code regardless of the design structure will get your point across more than simply keeping it (what i call) "white-space clean".

All around you did better job describing your problem the second time around