issues with listbox and loop

[Kevin_S]

Hey Gang - need some help...

I am working on setting up a form that will allow "power users" in one of my applications to create their own queries "on-the-fly" by selecting tables and subsequent fields from two listboxes. I am having issues building the criteria for my "fields" listbox as the criteria in the loop is erroring. Heres the setup:

3 tables many-to-many relationship between 'tblTables' and 'tblFields' based on a junction table, 'tblTablesFieldsJNX' The list box for the 'Fields' selection is based off of a query and has 3 columns:

- objTableName
- objFieldName
- objFieldAlias

in the fields listbox the first two columns are hidden and only the alias field is displayed. In the 'On_Click' event of the list box I have the following code:

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me.lstFields

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Me.lstFields.Column(0) & "." & Me.lstFields.Column(1)
Else
Criteria = Criteria & ", " & Me.lstFields.Column(0) & "." & Me.lstFields.Column(1)
End If
Next Itm

Me.txtFields = Criteria

Basically, the list box is set to multi-select and as the user selects/unselects items in the listbox I want to generate a string that shows the table name and the field name as displayed in SQL for queries. The problem I am running into is that when I use" Me.lstFields.Column(0) & "." & Me.lstFields.Column(1)" to build the string it changes each selection...

i.e. I click 3 different fields in the listbox but the string ends up replicating the same field name and table name 3 times like:

dbo_BAS_TABLEA.FieldA, dbo_BAS_TABLEA.FieldA, dbo_BAS_TABLEA.FieldA,

instead of what it should like:

dbo_BAS_TABLEA.FieldA, dbo_BAS_TABLEB, FieldX, dbo_BAS_TABLEC, FieldZ, etc....

any thoughts?

Thanks in Advance,
Kev

 

[dcx693]

There's basic problem with this structure:

For Each Itm In ctl.ItemsSelected
  If Len(Criteria) = 0 Then
    Criteria = Me.lstFields.Column(0) & "." & Me.lstFields.Column(1)
  Else
    Criteria = Criteria & ", " & Me.lstFields.Column(0) & "." & Me.lstFields.Column(1)
  End If
Next Itm

The ItemsSelected collection is being looped through, no problem. However, when does the Itm variable ever get called inside the loop?

 

[Kevin_S]

dcx693 - thanks for the interest

I guess I'm confused on what you mean by:

when does the Itm variable ever get called inside the loop?

First, let me say that looping structures are not my strong point so I may need a little hand holding here. I'm missing what you mean by item variable? What I am trying to accomplish is to create a collection of all of the selected items in the listbox. Then, loop through this collection of selected item with ctl.ItemsSelected and for each thats selected add to the criteria...

Are you saying I need something such as

if ctl.itemselected = true in the code? I was under the assumption this was qualified by ctl.ItemSelected??

Thanks in Advance,
Kev

 

[dcx693]

OK, let's try to translate your code:

For Each Itm In ctl.ItemsSelected
  If Len(Criteria) = 0 Then
    Criteria = Me.lstFields.Column(0) & "." & Me.lstFields.Column(1)
  Else
    Criteria = Criteria & ", " & Me.lstFields.Column(0) & "." & Me.lstFields.Column(1)
  End If
Next Itm

into pseudo-English so we can see what's going on:

For each item that's selected in the multiselect listbox do the following
   'Do something with the criteria
Go to the next item

What you want is, as the loop goes through different values of the Itm variable, is for the middle part of that loop to do something on each Itm. Your code is doing the exact same thing no matter which Itm the loop is up to.

For example, here's a loop that is not working as intended. It's trying to sum up all chosen values in the listbox and display it in a Msgbox window:

Dim Itm As Variant
Dim intSum As Integer

For Each Itm In Me.lstNumbers.ItemsSelected
    intSum = intSum + Me.lstNumbers.Column(0)
Next Itm

MsgBox intSum

Here is a version of the code that works. Can you see the difference?

Dim Itm As Variant
Dim intSum As Integer

For Each Itm In Me.lstNumbers.ItemsSelected
    intSum = intSum + Me.lstNumbers.ItemData(Itm)
Next Itm

MsgBox intSum

 

[Kevin_S]

dcx693 -

I see what you are saying and I implemented the changes as you suggested however I am still having the same problem as the criteria is being generated as one item. I now realize what the problem is, however, as the selected item (last selected by the cursor and highlighted with the dashed line around the item) is the one being replicated. The criteria loops the correct number of times depending on the number of selected items BUT - it doesn't insert the different values only the one highlighted as I described above...

Any ideas?

Would a mock-up db be more helpful to clarify what I mean? If so let me know and I will post one...

Thanks,
Kev

 

[dcx693]

Try this version of your loop:

For Each Itm In ctl.ItemsSelected
  If Len(Criteria) = 0 Then
    Criteria = Me.lstFields.Column(0,Itm) & "." & Me.lstFields.Column(1,Itm)
  Else
    Criteria = Criteria & ", " & Me.lstFields.Column(0,Itm) & "." & Me.lstFields.Column(1,Itm)
  End If
Next Itm

 

[Kevin_S]

THAT GOT IT

Thanks alot for the help I appreciate it!

Kev