Strange List Box Behavior

[JvC]

Background
Access 2003. MDB Front End.
I am populating a list box with the names of queries in the system. I
am searching for strings in the query names and then creating a value
list for users to select from.

Problem
My users occasionally use commas in query names. When I move the value
list to the RowSource of the list box, the commas in the names are
being interpreted as list separators. If my RowSource is:
Comma, delimited, query;SecondQuery

In the list box I should get:

Comma, delimited, query
SecondQuery

What I actuall get is:
Comma
Delimited
Query
SecondQuery

Is this a bug, or some fascinating quirk that I can work around?

Thanks!

John

[Stuart McCall]

"JvC" <jo******@earthlink.netwrote in message
news:k4******************@newsfe06.iad...

Background
Access 2003. MDB Front End.
I am populating a list box with the names of queries in the system. I am
searching for strings in the query names and then creating a value list
for users to select from.

Problem
My users occasionally use commas in query names. When I move the value
list to the RowSource of the list box, the commas in the names are being
interpreted as list separators. If my RowSource is:
Comma, delimited, query;SecondQuery

In the list box I should get:

Comma, delimited, query
SecondQuery

What I actuall get is:
Comma
Delimited
Query
SecondQuery

Is this a bug, or some fascinating quirk that I can work around?

Thanks!

John

It's a fascinating quirk. The comma is used as a row delimiter as you found.
No way to switch this off (AFAIK).

What you can do for a workaround is use the replace function to transform
all commas into (say) slashes:

strTemp = "Comma, delimited, query;SecondQuery"
Listbox.RowSource = Replace(strTemp, ",", "/")

Then, following user selection, use Replace again to transform them back
into commas.

[JvC]

I got so frustrated I wrote it out to a table. I need the users to be
able to double click on the query and run it. Simpler to do if the name
is correct!

Thanks for the input!

John

It happens that Stuart McCall formulated :

"JvC" <jo******@earthlink.netwrote in message
news:k4******************@newsfe06.iad...

>Background
Access 2003. MDB Front End.
I am populating a list box with the names of queries in the system. I am
searching for strings in the query names and then creating a value list for
users to select from.

Problem
My users occasionally use commas in query names. When I move the value list
to the RowSource of the list box, the commas in the names are being
interpreted as list separators. If my RowSource is:
Comma, delimited, query;SecondQuery

In the list box I should get:

Comma, delimited, query
SecondQuery

What I actuall get is:
Comma
Delimited
Query
SecondQuery

Is this a bug, or some fascinating quirk that I can work around?

Thanks!

John

It's a fascinating quirk. The comma is used as a row delimiter as you found.
No way to switch this off (AFAIK).

What you can do for a workaround is use the replace function to transform all
commas into (say) slashes:

strTemp = "Comma, delimited, query;SecondQuery"
Listbox.RowSource = Replace(strTemp, ",", "/")

Then, following user selection, use Replace again to transform them back into
commas.

[Salad]

JvC wrote:

I got so frustrated I wrote it out to a table. I need the users to be
able to double click on the query and run it. Simpler to do if the name
is correct!

Thanks for the input!

John

It happens that Stuart McCall formulated :

>"JvC" <jo******@earthlink.netwrote in message
news:k4******************@newsfe06.iad...

>>Background
Access 2003. MDB Front End.
I am populating a list box with the names of queries in the system. I
am searching for strings in the query names and then creating a value
list for users to select from.

Problem
My users occasionally use commas in query names. When I move the
value list to the RowSource of the list box, the commas in the names
are being interpreted as list separators. If my RowSource is:
Comma, delimited, query;SecondQuery

In the list box I should get:

Comma, delimited, query
SecondQuery

What I actuall get is:
Comma
Delimited
Query
SecondQuery

Is this a bug, or some fascinating quirk that I can work around?

Thanks!

John

It's a fascinating quirk. The comma is used as a row delimiter as you
found. No way to switch this off (AFAIK).

What you can do for a workaround is use the replace function to
transform all commas into (say) slashes:

strTemp = "Comma, delimited, query;SecondQuery"
Listbox.RowSource = Replace(strTemp, ",", "/")

Then, following user selection, use Replace again to transform them
back into commas.

Have you considered a UDF function to populate the listbox? Instead of
ValueList, enter
FillList0
in the rowsource type row of the property sheet. Then copy the
following code into your module. To test, I placed a comma in the 4th
position of each query listed. Remove that code after you test it.

Option Compare Database
Option Explicit

Private Type ListQ
Qname As String
End Type
Private Function FillList0(fld As Control, ID As Variant, row As
Variant, col As Variant, Code As Variant) As Variant
On Error Resume Next

'DLE = Directory List External

Static strRows() As ListQ
Static Entries As Integer

Dim ReturnVal As Variant
Dim qdf As QueryDef

ReturnVal = Null

Select Case Code
Case acLBInitialize ' Initialize.
'Docmd.Hourglass true
Dim rstFilter As Recordset

Entries = 0
ReDim Preserve strRows(Entries)

strRows(Entries).Qname = "Query Name"

For Each qdf In CurrentDb.QueryDefs
If Left(qdf.Name, 1) <"~" Then
Entries = Entries + 1
ReDim Preserve strRows(Entries)
strRows(Entries).Qname = Left(qdf.Name, 3) & "," & _
Mid(qdf.Name, 4)
End If
Next qdf
ReturnVal = True
Case acLBOpen ' Open.
ReturnVal = Timer ' Generate unique ID for control.
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries + 1
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' Column width.
ReturnVal = -1 ' -1 forces use of default width.
Case acLBGetValue ' Get data.
Select Case col
Case 0
ReturnVal = strRows(row).Qname
End Select
Case acLBEnd ' End.
Erase strRows
End Select
FillList0 = ReturnVal
End Function

[JvC]

Very nice!

Salad used his keyboard to write :

JvC wrote:

>I got so frustrated I wrote it out to a table. I need the users to be able
to double click on the query and run it. Simpler to do if the name is
correct!

Thanks for the input!

John

It happens that Stuart McCall formulated :

>>"JvC" <jo******@earthlink.netwrote in message
news:k4******************@newsfe06.iad...

Background
Access 2003. MDB Front End.
I am populating a list box with the names of queries in the system. I am
searching for strings in the query names and then creating a value list
for users to select from.

Problem
My users occasionally use commas in query names. When I move the value
list to the RowSource of the list box, the commas in the names are being
interpreted as list separators. If my RowSource is:
Comma, delimited, query;SecondQuery

In the list box I should get:

Comma, delimited, query
SecondQuery

What I actuall get is:
Comma
Delimited
Query
SecondQuery

Is this a bug, or some fascinating quirk that I can work around?

Thanks!

John
It's a fascinating quirk. The comma is used as a row delimiter as you
found. No way to switch this off (AFAIK).

What you can do for a workaround is use the replace function to transform
all commas into (say) slashes:

strTemp = "Comma, delimited, query;SecondQuery"
Listbox.RowSource = Replace(strTemp, ",", "/")

Then, following user selection, use Replace again to transform them back
into commas.

Have you considered a UDF function to populate the listbox? Instead of
ValueList, enter
FillList0
in the rowsource type row of the property sheet. Then copy the following
code into your module. To test, I placed a comma in the 4th position of each
query listed. Remove that code after you test it.

Option Compare Database
Option Explicit

Private Type ListQ
Qname As String
End Type
Private Function FillList0(fld As Control, ID As Variant, row As Variant, col
As Variant, Code As Variant) As Variant
On Error Resume Next

'DLE = Directory List External

Static strRows() As ListQ
Static Entries As Integer

Dim ReturnVal As Variant
Dim qdf As QueryDef

ReturnVal = Null

Select Case Code
Case acLBInitialize ' Initialize.
'Docmd.Hourglass true
Dim rstFilter As Recordset

Entries = 0
ReDim Preserve strRows(Entries)

strRows(Entries).Qname = "Query Name"

For Each qdf In CurrentDb.QueryDefs
If Left(qdf.Name, 1) <"~" Then
Entries = Entries + 1
ReDim Preserve strRows(Entries)
strRows(Entries).Qname = Left(qdf.Name, 3) & "," & _
Mid(qdf.Name, 4)
End If
Next qdf
ReturnVal = True
Case acLBOpen ' Open.
ReturnVal = Timer ' Generate unique ID for control.
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries + 1
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' Column width.
ReturnVal = -1 ' -1 forces use of default width.
Case acLBGetValue ' Get data.
Select Case col
Case 0
ReturnVal = strRows(row).Qname
End Select
Case acLBEnd ' End.
Erase strRows
End Select
FillList0 = ReturnVal
End Function

[Salad]

JvC wrote:

Very nice!

Thanks. I found some of my more complex queries would take a couple of
seconds to requery or present themselves initially. Using a UDF speeded
up the process dramatically. They're a bit more work but at times worth
the effort...especially the speed gains.

Value lists are useful for small sets of data but one can rapidly go
beyond the string length limit of a value list.

BTW, I set it up in my code to use a column heading as my first row.

>
Salad used his keyboard to write :

>JvC wrote:

>>I got so frustrated I wrote it out to a table. I need the users to be
able to double click on the query and run it. Simpler to do if the
name is correct!

Thanks for the input!

John

It happens that Stuart McCall formulated :

"JvC" <jo******@earthlink.netwrote in message
news:k4******************@newsfe06.iad...

Background
Access 2003. MDB Front End.
I am populating a list box with the names of queries in the system.
I am searching for strings in the query names and then creating a
value list for users to select from.
>
Problem
My users occasionally use commas in query names. When I move the
value list to the RowSource of the list box, the commas in the
names are being interpreted as list separators. If my RowSource is:
Comma, delimited, query;SecondQuery
>
In the list box I should get:
>
Comma, delimited, query
SecondQuery
>
What I actuall get is:
Comma
Delimited
Query
SecondQuery
>
Is this a bug, or some fascinating quirk that I can work around?
>
Thanks!
>
John

It's a fascinating quirk. The comma is used as a row delimiter as
you found. No way to switch this off (AFAIK).

What you can do for a workaround is use the replace function to
transform all commas into (say) slashes:

strTemp = "Comma, delimited, query;SecondQuery"
Listbox.RowSource = Replace(strTemp, ",", "/")

Then, following user selection, use Replace again to transform them
back into commas.

Have you considered a UDF function to populate the listbox? Instead
of ValueList, enter
FillList0
in the rowsource type row of the property sheet. Then copy the
following code into your module. To test, I placed a comma in the 4th
position of each query listed. Remove that code after you test it.

Option Compare Database
Option Explicit

Private Type ListQ
Qname As String
End Type
Private Function FillList0(fld As Control, ID As Variant, row As
Variant, col As Variant, Code As Variant) As Variant
On Error Resume Next

'DLE = Directory List External

Static strRows() As ListQ
Static Entries As Integer

Dim ReturnVal As Variant
Dim qdf As QueryDef

ReturnVal = Null

Select Case Code
Case acLBInitialize ' Initialize.
'Docmd.Hourglass true
Dim rstFilter As Recordset

Entries = 0
ReDim Preserve strRows(Entries)

strRows(Entries).Qname = "Query Name"

For Each qdf In CurrentDb.QueryDefs
If Left(qdf.Name, 1) <"~" Then
Entries = Entries + 1
ReDim Preserve strRows(Entries)
strRows(Entries).Qname = Left(qdf.Name, 3) & "," & _
Mid(qdf.Name, 4)
End If
Next qdf
ReturnVal = True
Case acLBOpen ' Open.
ReturnVal = Timer ' Generate unique ID for
control.
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries + 1
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' Column width.
ReturnVal = -1 ' -1 forces use of default
width.
Case acLBGetValue ' Get data.
Select Case col
Case 0
ReturnVal = strRows(row).Qname
End Select
Case acLBEnd ' End.
Erase strRows
End Select
FillList0 = ReturnVal
End Function