Escaping Semicolons in a Multi-Column Access ListBox

I received the following article request from reader Hemant (used with permission; surname withheld as requested):

Let’s say we have two textbox, submit button and datagrid view in MS Access Form. Now datagrid is not connected to any table. Here what I am really trying to achieve is whenever End user will input data in both text box’s and click on submit button then entry will go datagrid(In two different columns) one after another. Here, we need to build something like disconnected architecture. Data will be stored virtually till form is in running state.

Hemant, the way I would approach this is to use an unbound List Box control with no RowSource property. I would then use the control's AddItem method to add the two items when the user clicks on the Submit button.

Some things to be aware of:

The list box's Row Source Type must be "Value List"
Items can only be added as Strings (i.e., you give up some compile-time type safety)
To create multiple columns, join the item values with a semicolon

Starting Simple

I created a new form and added the following controls:

Text Box (Text0)
Text Box (Text2)
Button (btnSubmit)
List Box (List5)

I made the following changes to the List Box control:

Column Count: 2
Row Source Type: Value List

I added the following code behind the form:

Private Sub btnSubmit_Click()
    Me.List5.AddItem Me.Text0.Value & ";" & Me.Text2.Value
End Sub

Private Sub Form_Unload(Cancel As Integer)
    'Print the loaded items to the Immediate Window when the form closes
    Dim i As Integer
    For i = 0 To Me.List5.ListCount - 1
        Debug.Print Me.List5.Column(0, i), Me.List5.Column(1, i)
    Next i
End Sub

Here's what it looks like when I type my name into the boxes and click Submit:

When I close the form, it prints the values to the immediate window. I did this simply to demonstrate how you go about retrieving the values from the list box:

Dealing with Embedded Semicolons

So far so good, but what happens when we try to add a value with an embedded semicolon?

It's a List Box Injection Attack! The software world's least dangerous hack.

Oh no! The embedded semicolon is being treated as a column separator.

There must be some way to escape the semicolon character so that you can add it to the list box, right? Wrong. Or, at least, I could not find one. None of the usual suspects worked:

Double semicolons ;;
Backslash \;
Backtick `;

So I had to get creative.

The Greek Question Mark to the Rescue

I thought to myself, "Self, surely there's some wacky Unicode character out there that looks like the semicolon character without actually being the semicolon character."

Self replied, "Are you really not familiar with the Greek question mark symbol?"

The Greek question mark looks like ;. It appeared around the same time as the Latin one, in the 8th century. It was adopted by Church Slavonic and eventually settled on a form essentially similar to the Latin semicolon. In Unicode, it is separately encoded as U+037E ;GREEK QUESTION MARK, but the similarity is so great that the code point is normalized to U+003B ;SEMICOLON, making the marks identical in practice.

Inserting Unicode Characters in VBA

To embed the Greek question mark in our code, we need a way to access Unicode characters. Since you can't type Unicode characters directly into the VBA IDE, we had to use the wide character function, ChrW.

For consistency, I used the &h number token to pass the hexidecimal value of the Greek question mark character: ChrW(&H37E).

Of course, if we're going to escape the semicolons going in, we should unescape them going out. This does mean that if the user intentionally entered a Greek question mark into the text box that we would be changing it to a semicolon character. That's a chance I'm willing to take.

The Code

Here's the code behind the form:

Private Sub btnSubmit_Click()
    Me.List5.AddItem EscapeSemiColon(Me.Text0.Value) & ";" & _
                     EscapeSemiColon(Me.Text2.Value)
End Sub

Private Function EscapeSemiColon(Text As String) As String
    'Replace embedded semicolons with the visually-indistinguishable
    '   Greek question mark character (U+037E)
    'https://en.wikipedia.org/wiki/Question_mark#Greek_question_mark
    EscapeSemiColon = Replace(Text, ";", ChrW(&H37E))
End Function

Private Function UnescapeSemiColon(Text As String) As String
    UnescapeSemiColon = Replace(Text, ChrW(&H37E), ";")
End Function

Private Sub Form_Unload(Cancel As Integer)
    'Print the loaded items to the Immediate Window
    Dim i As Integer
    For i = 0 To Me.List5.ListCount - 1
        Debug.Print Me.List5.Column(0, i), Me.List5.Column(1, i)
    Next i
End Sub

It works! Thanks, Greeks!

That's a Latin semicolon, not a Greek question mark. You know, in case you were curious.