ke wenzel


I am using a ListBox in MS Access 2003 with multiple columns that may have commas or semicolons are part of the data. I understand that when using the AddItem method that I need to use double quotes to surround the data before delimiting the values with semicolons. The problem is that when a semicolon exists inside a value, the ListBox drops the last column.


Code Block

Dim strRowItem As String
strRowItem = """" & "one;one" & """;""" & "two" & """;""" & "three" & """"

List1.RowSourceType = "Value List"
List1.ColumnCount = 3
List1.RowSource = ""
List1.AddItem strRowItem

In this case, the example row in the ListBox List1 will have "one;one" in the first column, "two" in the second column, and the third column is blank (and any subsequent AddItems will be off by one because it will start with the first empty column).

I played with it a little bit and discovered that this behavior does not happen with commas, just semicolons. Is there a way to work around this, or am I doing it wrong


Re: ListBox.AddItem drops value for last column if semicolon is in data

ke wenzel


If I go to the open form and right click on the ListBox to get to its properties, then I can manually change the Row Source so that is is "one;one";"two";"three", and it works fine.

The only trouble is when I tried to do this from VBA code with the AddItem method.

Also, if I add more semicolons to the data from the AddItem method, more columns disapear. If I have more semicolons than there are columns, then the ListBox truncates the data in the offending value, and the ListBox remembers the extra semicolons on future AddItems, making future columns disappear. It is as if even though the ListBox understands that double quotes surround each value, it ignores that when it counts semicolons for determining the number of values.


Dim strItem1 as String
Dim strItem2 as String

strItem1 = """" & "one;one;one" & """;""" & "two" & "";"" & "three" & """"
strItem2a = """" & "one;one;one;neverseethis" & """;""" & "two" & "";"" & "three" & """"

strItem2b = """" & "four" & """;""" & "five" & """;""" & "six" & """"

List1.RowSource = List2.RowSource = ""
List1.ColumnCount = List2.ColumnCount = 3
List1.RowSourceType = List2.RowSourceType = "Value List"

List1.AddItem strItem1
List2.AddItem strItem2a

List2.AddItem strItem2b

List1 will show "one;one;one" in the first column, and nothing in the second or third columns. Right clicking on the form to see its Row Source reveals that only "one;one;one" is stored.

List2 will show "one;one;one" in the first column, "five" in the second, and "six" in the third column. Right clicking on the form to look at its Row Source shows that its data is "one;one;one;"four";"five";"six", which is even more bizarre because the value "four" doesn't appear on the form. (Note that the double quotes don't follow the end of the first value).

Is the ListBox not supposed to be used this way The application that I'm working on allows the user to store information in a ListBox before commiting it to the database, and sometimes semicolons will be part of the data. If this isn't possible, then I'm going to have to rethink how this app works. Help