When I use the VBA AddItem method to add an entry to a List Box, I get line breaks when the string contains a comma. Here is an example for the case of the some music artists, the contents of the list box appear like this:

Jackson Browne
The Beatles
Crosby
Stills
Nash and Young

instead of this:

Jackson Browne
The Beatles
Crosby, Stills, Nash and Young

This is a real inconvenience and I hope it is not an unavoidable limitation. Thanks.

What is the code you are using to post the names? If they are in a string variable, it should take the commas.



mmcdonal

Look it up at: http://wrox.books24x7.com

The line that adds to the list box is near the end. Right in front of it I write the same thing to the intermediate window. The problem I am describing does not occur when the string is written there.

Private Sub ScatterFields(objCD As CD)
' Copy values from a CD object out to the form's controls.
Dim iTrackCount As Integer
Dim iTrackIndex As Integer
Dim sFullDigits As String
If objCD.Load() Then
    Call ClearListBox(Me.lstSongList)
    With objCD
        Me!cboCDID.Value = .ID ' This is redundant when this call was a result of combo box, but is not redundant for all other cases
        Me!txtCDMusicCategory = .CDMusicCategory
        iTrackCount = objCD.Songs.Count
        For iTrackIndex = 1 To iTrackCount
            If iTrackIndex < 10 Then
                sFullDigits = "0" & iTrackIndex
            Else
                sFullDigits = iTrackIndex
            End If
            Debug.Print sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist
            lstSongList.AddItem (sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist)

        Next
    End With
End If
End Sub

The following appears in the Immediate Window for one of the CDS. Item 10 does not get extra lines...but it does when the item is added to my list box. I'm stumped.

01) Woman -- Brother Cane
02) Wheels Of Fortune -- The Doobie Brothers
03) Beginnings -- Chicago
04) Grey Seal -- Elton John
05) Your Gold Teeth II -- Steely Dan
06) Toulouse Street -- The Doobie Brothers
07) Lines On My Face -- Peter Frampton
08) Freaks -- Live
09) Lakini's Juice -- Live
10) Ohio -- Crosby, Stills, Nash and Young ................I cannot get this into my list box as it appears here...a new line is created at each comma.
11) A Stone's Throw Away -- Brother Cane
12) Too Rolling Stoned -- Robin Trower
13) Bridge of Sighs -- Robin Trower
14) Sweet Wine Of Love -- Robin Trower
15) Juke Box Hero -- Foreigner
16) You've Made Me So Very Happy -- Blood, Sweat & Tears................same thing...okay in intermed window but a new line is created at the comma in list box.

I would try this, since Access always has a problem with taking values at run-time:

Dim sArtist As String

Debug.Print sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist

sArtist = sFullDigits & ")" & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist

lstSongList.AddItem (sArtist)

Did that help?





mmcdonal

Look it up at: http://wrox.books24x7.com

mmcdonal, I fixed the problem by doing the code below; I don't fully understand why I had to do that but it works and I'm grateful. Thanks for your time. If you know why this works tell me....

Dim sFullName As String
sFullName = sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist
lstSongList.AddItem """" & sFullName & """"

If you try to pass a control to a runtime command, like DoCmd, then the command will try to interpret the values it is receiving from the control. I think in this case the command intepreted the data as an array or comma delimited data, and was going you a favor.

If you pass a string, then the command will have nothing to interact with, and just accepts the value.

Anyway, for data security, you should ALWAYS put control values in variables and pass the variables since you can do data validation that way if you need to.

Glad it worked.

mmcdonal

Look it up at: http://wrox.books24x7.com