3 Replies - 1136 Views - Last Post: 23 June 2009 - 07:33 PM Rate Topic: -----

#1 Fullchaos  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 30-May 08

Adding data from multiple listboxes to one listbox

Posted 22 June 2009 - 07:29 AM

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.

Dim i As Integer
Dim listHolder As Integer


Dim listTotal As Integer
Dim commandNo As Boolean

Dim Storage(0 To 50) As String

Dim NoQDM As Integer
Dim NoQOM As Integer
Dim NoProd As Integer
Dim NoListOther As Integer

NoQDM = QDM.ListCount
NoQOM = QOM.ListCount
NoProd = Prod.ListCount
NoListOther = ListOther.ListCount

listTotal = QDM.ListCount + QOM.ListCount + Prod.ListCount + ListOther.ListCount





For listHolder = 0 To listHolder = NoQDM

QDM.SetFocus



Storage(listHolder) = QDM.RowSource

Next



For listHolder = NoQDM + 1 To listHolder = NoQOM

Storage(listHolder) = QOM(listHolder).Value

Next




For listHolder = NoQOM + 1 To listHolder = NoProd

Storage(listHolder) = Prod(listHolder).Value

Next




For listHolder = NoProd + 1 To listHolder = NoListOther

Storage(listHolder) = ListOther(listHolder).Value

Next



For i = 0 To listTotal
CombiningBox.AddItem (Storage(i))
Next



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.

SELECT [List Box].Cat
[List Box].[KPI#]
[List Box].KPI FROM [List Box] WHERE (([List Box]!Cat="Q - D")) ORDER BY [List Box].[KPI#]



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

Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Adding data from multiple listboxes to one listbox

#2 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: Adding data from multiple listboxes to one listbox

Posted 22 June 2009 - 08:55 AM

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?
Was This Post Helpful? 0
  • +
  • -

#3 Fullchaos  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 30-May 08

Re: Adding data from multiple listboxes to one listbox

Posted 23 June 2009 - 06:46 AM

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 :D]

So maybe in pseudocode I saw this working like this.

Dim i As Integer

ListTotalLength = Sum of Length of Lists

For i is 0 To when i is equal to ListTotalLength

If current listbox element Is selected
Then 

Switch
Select i

Case Is 1
  Do Cmd
Case Is 2
  Do Cmd

...

Case Is (last number, manual defined)

End Select

End If

Next



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:

Dim i As Integer
Dim j As Integer
Dim GotSelected (0 - 50) As Integer

For 0 To ListBoxNameHere.ListCount
If current element is selected
Then GotSelected (j) = SelectedItem.Value
		j = j + 1
End If
Next

For 0 To ListBoxNameHere2.ListCount

If current element is selected
Then GotSelected (j) = SelectedItem.Value
		j = j + 1
End If
Next

'repeat twice more for the other listboxes

For 0 to j
i = GotSelected (j)
Select i
Case Is ...




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!
Was This Post Helpful? 0
  • +
  • -

#4 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: Adding data from multiple listboxes to one listbox

Posted 23 June 2009 - 07:33 PM

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 ;)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1