Multiselect Listbox

[MrTBC]

Access 2003

Hi.
I'm trying to loop through all of the selected records in a multiselect listbox and return their email addresses separated by a semi-colon - but i'm not getting the right results.

                If MsgBox(strMessage, vbYesNo) = vbYes Then
                    intRow = 0
                    Do
                        If Me.lstInvites.Selected(intRow) = True Then
                            If intRow > 0 Then
                                strEmail = strEmail & " ;"
                            End If
                            Me.lstInvites.Selected(intRow) = True
                            strEmail = strEmail & Me.lstInvites.Column(3)
                        End If
                        intRow = intRow + 1
                        MsgBox strEmail
                    Loop Until intRow = intTotal
                    DoCmd.OpenForm strForm, , , , , , strEmail
                End If

Any ideas please guys?

 

[traingamer]

You need to reference the row (in addition to the column):

strEmail = strEmail & Me.lstInvites.Column(3, [b]intRow[/b])

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MrTBC]

Thanks - I'm still not getting the right email addresses though.

 

[dhookom]

Do you realize the columns are numbered beginning with 0?

You should also reply with a little more information than "I'm still not getting the right email addresses". How about describing what you are getting and what you have done to trouble-shoot?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

And why not using the ItemsSelected collection ?

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

 

[MrTBC]

Sorry for not providing more info.

The result is supposed to be a String, containing the email address stored in a particular column, for each selected row separated by ";". I have successfully done this elsewhere for all records in the listbox, but am having problems achieving the same result for only the selected records.

I have two test records, lets says the first is "test1@test.com" and the last is "test2@test.com".

If I highlight record 1 only I get "test2@test.com" = wrong email address
If I highlight record 2 only I get ";test1@test.com" = wrong email address and has an extra semi-colon
If I highlight both records I get "test1@test.com;test1@test.com" = the first email address twice

Thanks very much.

 

[traingamer]

The leading semi-colon problem is caused by your code telling it to put a semi-colon unless you're on the first row. And

Me.lstInvites.Selected(intRow) = True

is not at all what you want. Try removing it.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MrTBC]

Thanks - yes the ";" problem I can see now. I'll fix that.

I've removed the line "Me.lstInvites.Selected(intRow) = True" but I'm still getting the wrong email addresses:

If I highlight record 1 only I get "test2@test.com" = wrong email address
If I highlight record 2 only I get ";test1@test.com" = wrong email address
If I highlight both records I get "test1@test.com;test1@test.com" = the first email address twice

 

[PHV]

A starting point:

For Each varItm In Me.lstInvites.ItemsSelected
  strEmail = strEmail & ";" & Me.lstInvites.Column(3, varItm)
Next
Debug.Print Mid(strEmail, 2)

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

 

[MrTBC]

Thanks very much PHV. That works perfectly except the ";" which I'll fix and is really nice, concise code.

 

[PHV]

except the ";"
Really ?
What is displayed in the debug window with my code ?

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

 

[MrTBC]

It displays ";test1@test.com;test2@test.com" so I added this after the For Each:

strEmail = Right(strEmail, Len(strEmail) - 1)

Is there any way I can adapt your piece of code for all items in the listbox (rather than just those that are selected) please?

 

[PHV]

What is YOUR actual code ?