VBA: Escaping double quotes, and commas while building string to use as combobox rowsource

[anavagomez]

Hello,

I am trying to build a string programmatically to use as the rowsource property of a combobox.

I got the idea from this page: Persistent Caching Of Combo Boxes

The code that I put together almost works as I needed to work, having only a couple of issues. In one of the xml files that are saved from the recordset there is a value that contains double quotes that looks like this:

<z:row PublisherName='&#x22;A&#x22; Easy Street, LLC'/>

When the combobox is the values, this specific value gets truncated, and only an A is visible, no double quotes, and the rest of the text after the A is lost.
The other issue is that a second xml file there is a value that contains a comma inside parenthesis:

<z:row Category='9' ProductDescription='Easy Play (Piano, Guitar) or short PD'/>

When the value is put in the combobox, the text gets splitted into 2 columns like:

Column 1              Column 2
Easy Play (Piano    Guitar) or short PD

The comma is removed and what should be one whole value, is now two.
I do have, in the code an if statement that checks if the string value contains either double quotes or commas, but for some reason even though when I add a watch or in the immediate window I see that the value looks as it should, when it gets passed to the combobox, they get truncated.
I am now going to include how I call the function, and the contents of the function. I hope someone can share a light here and help me solve this small couple of issues.
Function call:

cboBlockReason: combobox being passed
qstrcBlockReason: is the query
1: is the number of columns to use in the for iteration
"BlockingDescription": name of the xml file to be saved and then read from.


BlockReason.RowSource = CacheRecordSet(cboBlockReason, qstrcBlockReason, 1, "BlockingDescription")

Function contents:

Function CacheRecordSet(pCombo As ComboBox, _
                        pQuery As String, _
                        pCols As Variant, _
                        pDataSource As String) As Variant


    Dim s As String 
    Dim c As Integer    
    Dim rs As ADODB.Recordset


    If Dir(CacheDir, vbDirectory) = "" Then
        Shell ("cmd /c mkdir """ & CacheDir & """")
    End If


    On Error Resume Next
    Kill CacheDir & pDataSource & FileType


    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = CurrentProject.Connection
        .Open pQuery
        .Save CacheDir & pDataSource & FileType, adPersistXML
    End With


    Do Until rs.EOF

        For c = 0 To pCols


            If InStr(rs(c), """") > 0 _
            Or InStr(rs(c), ",") > 0 Then
                rs(c) = "'" & rs(c) & "'"
            End If


            s = s & rs(c) & ";"


        Next c


        rs.MoveNext


        pCombo.AddItem (s)


    Loop


    ' Now return the string as the RowSource to be used
    CacheRecordSet = pCombo.RowSource


eofit:


    On Error Resume Next


    rs.Close


    Exit Function


errhandler:


    z = ErrorFunction(Err, Err.Description, Erl, "CacheRecordSet", , True)


    Err = 0


    Select Case z
        Case 0: Resume Next
        Case 1: GoTo eofit
    End Select


End Function

[aytee111]

Haven't a clue what you are doing here, but does it compile? The function CacheRecordSet has four parameters listed and you are only passing it three.

[anavagomez]

Yes it compiles. The missing parameter was a mistake I made when editing the post. I have now updated that part.

[anavagomez]

Yes it compiles. The missing parameter was a mistake I made when editing the post. I have now updated that part.

I am saving a recordset to an xml file and then reading the xml to use its values as the values for the combobox.

[Micron]

There might be some issues for us in deciphering your post, such as

there is a value that contains double quotes that looks like this:
<z:row PublisherName='&#x22;A&#x22; Easy Street, LLC'/>

I don't see double quotes (") only 2 singles. Never bothered to learn xml, but I can guess that it should be
<z:row PublisherName="'" &#x22;A&#x22; Easy Street, LLC & "'"/>
However, in Access the ampersand is a concatenation character whereas I suppose it is an escape character in xml as in html. Therefore, depending on which program is dealing with the strings at a given time, you may need to use Ascii character codes instead of literal text. So a single quote may be chr(39) on the Access side, and &#39 on the other side.

Some of the statements you make don't define if the point is a problem or not

the text gets splitted into 2 columns like:
The comma is removed and what should be one whole value, is now two.

My Access/html experience is limited to building an html email message body through concatenation of all the necessary html code. I seem to recall using chr(##) when I had to deal with quotes and some other characters.

[anavagomez]

Hi thank you for your reply.

The double quotes are the hex value &#x22; around the A.

the second issue, it seems like the comma inside the parenthesis impacts how the string is put together before being passed to the combobox.

This is the entire value: 'Easy Play (Piano, Guitar) or short PD'

When it gets placed in the combobox the value shows as:
column 1 column 2
'Easy Play (Piano Guitar) or short PD'

When it should be:

column1
'Easy Play (Piano, Guitar) or short PD'

[Micron]

did you try selecting alternate characters for the "Separator Characters" property?
I can get #x22;A&#x22;Easy Street;LLC' all on one combo box row if I choose comma as a list separator. Not sure if that helps, since I don't really grasp what you're attempting to do. AFAIK, Access will not interpret #x22 as a comma, nor would you want the <'s or >'s (tag delimiters) as part of the combo box row values. Or do you? I don't get the purpose of displaying

<z:row PublisherName='&#x22;A&#x22; Easy Street, LLC'/>

as a row value for a combo. Maybe you should elaborate more clearly if none of the separator character property choices help. Maybe explain what the output of pCombo.RowSource is while you're at it.