Listbox Qestions 2

[Eightball3]

I have a multi-select listbox. I have a query with criteria from two colums in the listbox. How would I loop through the listbox of selected items running the query for each one? I'm having trouble referencing the listbox columns.

 

[ssecca]

You can use this anywhere you need I placed it on the lost focus event of the list control. What it does is read all the selected items from the list box and print it to the debug window. All you need to do is decide where it should initiate from and what you wnat to happen to the resultant data.

<<Start CODE>>> next line

Private Sub List3_LostFocus()
Dim ctrl As Control
Dim str As Variant

Set ctrl = Me![List3]
For Each str In ctrl.ItemsSelected
Debug.Print ctrl.ItemData(str)
Next str

End Sub


<<<End Code>>> do not use this line

Good Luck
ssecca

 

[chrissie1]

something like this

For x = 1 To List1.ItemsSelected.Count
strsql = &quot;select * from table where field = '&quot; & (List1.Column(0, List1.ItemsSelected(x - 1)) & &quot;'&quot;
Next x Christiaan Baes
Belgium
&quot;What a wonderfull world&quot; - Louis armstrong

 

[Eightball3]

Hi ssecca, thanks for the reply. Does this print all the colums of the listbox or just the bound column? I just want to use the data from two of the colums as criteria for my query.

 

[ssecca]

What I gave you only refers to the bound column to get data from other columns use

Private Sub List3_LostFocus()
Dim ctrl As Control
Dim str As Variant

Set ctrl = Me![List3]
For Each str In ctrl.ItemsSelected
Debug.Print ctrl.Column(0)
Debug.Print ctrl.Column(1)
Next str

End Sub

 

[Eightball3]

Instead of placing the values of the two columns into the Debug window, can I put them into a couple of textboxes and then use the contents of the textboxes as my query criteria?

 

[ssecca]

My code was mearly an example of &quot;how to&quot; once you have a hold of the values you can put it anyplace you want. If you need help with that post back.

 

[Eightball3]

Would I do something like this?

For Each str In ctrl.ItemsSelected
Me![text1]= ctrl.Column(0)
Me![text2]= ctrl.Column(1)
'Run Query here
Next str

 

[Eightball3]

Anybody?

 

[ssecca]

Sorry for the delay, Once in a while I actually have to work. Can you beleive it?

Yes, what you did would place the value in a text box on the currently opened form.

 

[Eightball3]

Thanks for all you help ssecca! Although I don't have it working yet, I feel I'm close. I'm posting my code, can you look it over and let me know what I'm doing wrong.

Dim ctrl As Control
Dim str As Variant

If List0.ItemsSelected.Count > 0 Then
Set ctrl = Me![List0]
For Each str In ctrl.ItemsSelected
Me![Activity] = ctrl.Column(0)
Me![POC] = ctrl.Column(1)
DoCmd.OpenForm &quot;poc_form&quot;, acNormal, , , acFormEdit, acIcon
Forms![poc_form].RecordSource = &quot;Recall&quot;
If Forms![poc_form]![Recall] = &quot;y&quot; Then
DoCmd.OpenReport &quot;Recall&quot;, acViewNormal
End If
DoCmd.Close acForm, &quot;poc_form&quot;
Next str
Set ctrl = Nothing
Else
MsgBox &quot;You must first choose to whom the report(s) will be sent.&quot;
End If

If I select 3 poc's from list0 then 3 reports print but all of them are to the last poc I selected. Any Ideas?

 

[chrissie1]

Dim ctrl As Control
Dim str As Variant

If List0.ItemsSelected.Count > 0 Then
Set ctrl = [List0]
For x = 1 to List0.ItemsSelected.Count
[Activity] = ctrl.Column(0,List0.ItemsSelected(x - 1))
[POC] = ctrl.Column(1,List0.ItemsSelected(x - 1))
DoCmd.OpenForm &quot;poc_form&quot;, acNormal, , , acFormEdit, acIcon
Forms![poc_form].RecordSource = &quot;Recall&quot;
If Forms![poc_form]![Recall] = &quot;y&quot; Then
DoCmd.OpenReport &quot;Recall&quot;, acViewNormal
End If
DoCmd.Close acForm, &quot;poc_form&quot;
Next str
Set ctrl = Nothing
Else
MsgBox &quot;You must first choose to whom the report(s) will be sent.&quot;
End If


Christiaan Baes
Belgium
&quot;What a wonderfull world&quot; - Louis armstrong

 

[Eightball3]

Great!! Thanks Christiaan and ssecca! Looks like it's working. I've been struggling with this for over a week. Although I don't understand the two lines of code,

Me![Activity] = ctrl.Column(0,List0.ItemsSelected(x - 1))
Me![POC] = ctrl.Column(1,List0.ItemsSelected(x - 1))

that is the piece I've been missing.

I would like this to do one more thing, as the reports are printed, I would like the item in the list to become unselected. This would give the visual effect of it stepping through and the user would see where they're at in the process.

Thanks again both of you.

 

[chrissie1]

ill try to explain

ctrl.Column(0,List0.ItemsSelected(x - 1))

the 0 is the column

the list0.itemsselected(x-1) is the row but because itemsselected begins counting from zero you add a -1
hope that clears things up.


Christiaan Baes
Belgium
&quot;What a wonderfull world&quot; - Louis armstrong

 

[Eightball3]

The part I was confused about is:
ctrl.Column(0,List0.ItemsSelected(x - 1))

the ,List0.ItemsSelected confuses me

Can you help me with the second part of my question? I would like the item in the listbox to become un-selected after the report has printed.
I tried List0.Selected(x) = False but it doesn't work.

Thanks again.