[RESOLVED] Commas in Combobox problem

[ChrisR873]

Here's a quick example of my problem.

(1) People's Lawyers
(2) Frank's Lawfirm
(3) Smith
Smith
Smith and Paul
(4) ABC Law

Here's how it should look

(1) People's Lawyers
(2) Frank's Lawfirm
(3) Smith, Smith, Smith and Paul
(4) ABC Law

The combobox is populated by code from a database, so it is set up as ValueList. What can I do to get around the issue of the listbox taking the comma and seperating the data? Heres my code for the AddItem routine.

Function PopulateOrganization()
    Dim db As Database
    Dim rstORG As DAO.Recordset
    Dim SQL
    Dim i As Long
    
    Set db = CurrentDb()
    SQL = "SELECT org_id, org_desc FROM ORGANIZATIONS ORDER BY org_desc ASC"
    
    Set rstORG = db.OpenRecordset(SQL)
    rstORG.MoveFirst
    rstORG.MoveNext
    
    i = 0
    Do While i < cmbOrganization.ListCount
        cmbOrganization.RemoveItem (i)
    Loop
    rstORG.MoveFirst
    Do While Not rstORG.EOF
        cmbOrganization.AddItem "(" & rstORG!org_id & ") " & (Trim(rstORG!org_desc))
        rstORG.MoveNext
    Loop
End Function

Any ideas would be greatly appriciated! Thanks in advanced.

~Chris

[salvelinus]

I haven't seen that problem with commas before. There are often errors with quotes. Try a Replace() on the commas with 2 commas. Just an idea.

[ChrisR873]

Well,
The replace function does work, but not the way you intended it to. I would have to use another character besides a comma in order for it to display. For example a period, but that doesn't look right and I would still prefer displaying a comma. Your idea of replaceing with two commas just makes more rows.

Anyone else have any suggestions on the issue?

EDIT: I ended up solving my own problem. What I ended up doing was utilizing the replace function to replace the comma with another comma. Except it was a different character. To help explain better heres my replacement code.

Do While Not rstORG.EOF
        orgTEXT = (Trim(rstORG!org_desc))
        If InStr(1, (Trim(rstORG!org_desc)), ",") > 0 Then
            orgTEXT = Replace(rstORG!org_desc, ",", Chr(130))
        End If
        cmbOrganization.AddItem (Trim(orgTEXT)) & " (" & rstORG!org_id & ")"
        rstORG.MoveNext
    Loop

What it does is replace the character 44 with character 130. Which are both commas. But because the combobox/listbox takes the character 44 as a row seperater, this is a workaround. Hope this helps anyone out.

Thanks,
~Chris

[Al42]

I just created a database and did what you did, but using ADO. I couldn't duplicate your results. The

Private Sub Form_Activate()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False"
  Set rs = New ADODB.Recordset
  rs.Open "Select * from Table1", cn
  Do While Not rs.EOF
    Combo1.AddItem (rs.Fields(0).Value) & "   (" & rs.Fields(1).Value & ")"
    rs.MoveNext
  Loop
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
  If Combo1.ListCount > 0 Then Combo1.ListIndex = 0

End Sub

[ChrisR873]

I don't think VB6 has that issue, which is why I was so confused. I am using MS Access 2002 to create my database app, and the data comes via DAO. But it didn't matter how the data came when populating the control, it did it no matter what. If you have access 2002 or 2000 try it there. Glad you looked into the issue tho. I did noticed when researching how to fix it before I posted that other people had the same issue, but none were pulling from a database and were able to fix it another way. My fix works and thats one problem out of the way.

~Chris