Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 4th, 2008, 11:20 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
Default Is a comma allowed in a List Box?

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.


  #2 (permalink)  
Old June 5th, 2008, 09:25 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
  #3 (permalink)  
Old June 5th, 2008, 10:42 AM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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.
  #4 (permalink)  
Old June 5th, 2008, 10:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
  #5 (permalink)  
Old June 5th, 2008, 02:39 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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 & """"




  #6 (permalink)  
Old June 6th, 2008, 06:28 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a list box from comma separated values CoderNH Access VBA 3 October 20th, 2008 08:37 AM
multi-column list box values moved to 2nd list box sbmvr Access VBA 1 May 14th, 2007 01:58 PM
comma separated list MunishBhatia SQL Server 2000 11 March 21st, 2007 03:56 PM
comma separated list of attributes rjonk XSLT 3 September 27th, 2006 11:52 AM
Sort by a value that's a comma seperated list medic XSLT 0 June 7th, 2006 09:52 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.