Add records to a multicolumn ListBox

[tony]

Hi,
I am having problems when I try to add records to a multicolumn
listbox. These are the problems:
1. If there is a comma in any of the values that is added to the
listbox then it will split that value into two columns. I understand
why that hapens, I just don't know how to get around it.
2. If the string used to populate the listbox is too long, I get the
following message:
"Run-Time error 2176" The setting for this property is too long.

I am using a combo box and two list boxes for my selections. Then I use
a function to generate a recordset, that I would like to attach to my
list. I am new to access, so any alternatives to accomplish this would
be greatly appreciated. Here is my code:

Private Sub lstAuthors_Click()
Dim i As Integer
Dim strList As String
Dim strPublisher As String
Dim strBookType As String
Dim strAuthor As String

strPublisher = ""
strBookType = ""
strAuthor = ""

strPublisher = cboPublishers.Value

i = lstBookTypes.ListIndex
If lstBookTypes.ItemData(i) <> "" Then
strBookType = lstBookTypes.ItemData(i)
End If

i = lstAuthors.ListIndex
If lstAuthors.ItemData(i) <> "" Then
strAuthor = lstAuthors.ItemData(i)
End If

GetBooks strPublisher, strBookType, strAuthor
strList = "Year,Title,Printing,Book #;"
rstBooks.MoveFirst
Do While Not rstBooks.EOF
strList = strList & rstBooks(0) & "," & Replace(rstBooks(1),
",", " ") & "," & Replace(rstBooks(2), ",", " ") & "," & rstBooks(3) &
";"
rstBooks.MoveNext
Loop
lstBooks.RowSource = ""
lstBooks.RowSource = strList
End Sub

Public Function GetBooks(strPublisher As String, strBookType As String,
strAuthor As String)
Dim strQuery As String
strQuery = "Select distinct Publishing_year,title,printing,book_no

From books where Publisher='" & strPublisher & "'"

If strBookType <> "" Then
strQuery = strQuery & " and Book_Type='" & strBookType & "'"
End If
strQuery = strQuery & " and Author='" & Replace(strAuthor, "'",
"''") & "'"
Set rstBooks = New ADODB.Recordset
rstBooks.CursorLocation = adUseClient
rstBooks.Open strQuery, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
End Function

 

[Guest]

Hi Tony,

you may find this link helpfull: http://www.mvps.org/access/forms/frm0049.htm
It describes the use of callback function to fill up a multicolumn listbox.
Using this function (which i think can alo be found in access help) you will
not hit problem 'source string is too long'

HTH

Trias H

 

[tony]

For those that might be interested, I solved the problem with commas. I
needed to enclose my data in double quotes. This is the code:
strList = strList & rstBooks(0) & ",""" & rstBooks(1) & """,""" &
rstBooks(2) & """," & rstBooks(3) & ";"

Note that only rstBooks(1) and rstBooks(2) had this problem.

Trias, I am not yet sure of how to apply the function in this example.
Tony