Properly Displaying Special Characters in ListBox

[ohmydatabase]

Hey,

So I'm trying to properly display strings in a listbox and have run into an issue I can't find a clear definition on. When adding a string to a listbox, some care must be taken to make sure it displays correctly. My problem is with the hyphen character "-". When I insert a string that includes a hyphen into the listbox, the listbox assumes that the hyphen is a delimiter of some kind. It will break the string at the hyphen and display in remaining string in the next column. I've already added some logic that adds additional quotation marks to the string when there is a semicolon or comma present in the the string. (This works fine) However, I tried to add the same logic for a string that includes the hyphen and haven't had the same success. I haven't tried doubling the character "--", but I vaguely remember having to do that for the "&" character.

Does anyone know the proper way to get hyphens to display in the listbox? Are there any other special characters other than "; , -" that I should be concerned about and how should I go about handling them?



Thank you in advance.

[June7]

What do you mean by 'insert into listbox'? You are using ValueList for RowSourceType? These are strings for the RowSource property?

Not getting the break at hyphen.

I can't get comma and semi-colon accepted as literal characters.

Might have to build a table with these strings and use that as RowSource.

[Micron]

However, I tried to add the same logic for a string that includes the hyphen and haven't had the same success.

Aside from answering June7's questions, post what didn't work, because as a value list, this works: test;apple;orange;"ban;ana";"apple-pie"

[ohmydatabase]

The RowSourceType is set to ValueList. In VBA, I create an SQL Query and step through the desired recordset using DAO. While Not rs.EOF, I use the listbox.AddItem Method to add each of the values I want one by one. The Listbox is unbound, I add everything through VBA. Hope this clarifies my problem.

[Micron]

Hope this clarifies my problem.

For me, no. I thought I had the process figure out - which it turns out, I did

When adding a string to a listbox,

It's probably a matter of concatenating syntax, so I'm no further ahead.

[orange]

OhMy,

Perhaps you could post the code involved or a sample of the database.

[June7]

I don't get it. If you can build an SQL SELECT statement to open a recordset and loop through the recordset to add all those values as listbox items, why not just use VBA to set the listbox RowSource property to the SQL statement?

[ohmydatabase]

So I was advised to set the RowSourceType to a Query by someone (much better at Access than I am). I tried it, but got nit-picky about a certain value. When using the RowSourceType = Query everything was added (much easier), but the Boolean value I have for 'Ongoing' displayed as Yes, or No. I really wanted to say 'Progress' or 'Completed' so I went back to using the Value List. (Note: The AddEscapeCharacters2 Function adds quotation marks in the string if they are encountered)


Set db = CurrentDb()
i = 0
Me.listStatus.RowSourceType = "Value List"
rowSQLStr = "SELECT * FROM [" & tblStatus & "] WHERE Ongoing = " & varType & " "
rowSQLStr = rowSQLStr & "AND Author = '" & UserFullName & "' "
rowSQLStr = rowSQLStr & "ORDER BY Date2, Program, SubProgram;"
Set rs = db.OpenRecordset(rowSQLStr)
While Not rs.EOF
ReDim Preserve idArray(i)
ReDim Preserve progressArray(i)
'Add items one-by-one to listbox
programStr = rs!Program
subProgramStr = Nz(rs!SubProgram, "")
TaskStr = Nz(rs!Task, "")
dateStr = rs!DateField
statusStr = rs!BodyText
statusID = rs!ID
ongoingBool = rs!Ongoing
editDateStr = rs!editDate
If ongoingBool = True Then
ongoingStr = "In Progress"
Else
ongoingStr = "Complete"
End If
Dim tempStr As String
tempStr = statusStr
If InStr(tempStr, Chr(34)) > 0 Then
tempStr = AddEscapeCharacters2(tempStr)
End If
If InStr(tempStr, ",") > 0 Or InStr(tempStr, ";") > 0 Then
tempStr = """" & tempStr & """"
End If
With Me.listStatus
.AddItem statusID & ";" & programStr & ";" & subProgramStr & ";" & TaskStr & ";" & tempStr & ";" & ongoingStr & ";" & dateStr & ";" & editDateStr
End With
idArray(i) = statusID
progressArray(i) = ongoingBool
rs.MoveNext
i = i + 1
Wend

[June7]

Please post code within CODE tags to retain indentation and easier readability.

Consider:

"SELECT *, IIf([Ongoing], 'In Progress', 'Complete') AS Status FROM [" & tblStatus & "] WHERE Ongoing = " & varType & " "

[ohmydatabase]

Would the above query alter the order in which my values are added to the multi-column ListBox? The ordering of the tblStatus is very much different, so I suspect that "SELECT *" might cause some issues...

How would I manipulate my query to select the exact fields I wish, in the order I wish?

[June7]

The same way is done in any query.

"SELECT field1, field2, field3, IIf([Ongoing], 'In Progress', 'Complete') AS Status FROM [" & tblStatus & "] WHERE Ongoing = " & varType & " "

Add an ORDER BY clause if you want.

[ohmydatabase]

@June7
That worked great. Thank you!


I haven't used this way of setting RowSource before. Now that the entries are in the ListBox, how can I extract information about a given record? (Once it is selected that is.)

Do I need to open the same query that I used as the RowSource and step through the record set adding the values I want to an array? I did this to pass around the ID's of the records that were in that ListBox. Whenever a record was selected, I knew the index in the ListBox, which meant I knew which index in my IDArray() I currently had selected. I could then pull the exact ID for that entry, use it do whatever manipulation I needed with that record and re-define the ListBox/IDArray() as needed. (Hope that made sense..)

[June7]

Don't really understand what you are doing.

If you have selected a 'given record' why do you need a recordset and array?

Is the listbox set for multi-select?

[ohmydatabase]

why do you need a recordset and array?

You're right, I don't. I've figured this one out. Thank you so much for the help.
I have a single-select ListBox. It so happens that the first field of a selected record can be found using the following code:

x = Me.myListBox.ItemData(Me.myListBox.ListIndex)

It also turned out that the first field (the ID) was exactly what I needed!

[June7]

If you just want the value in the listbox box after an item is selected, simply reference the listbox.

x = Me.myListBox