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
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?
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
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:
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.
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