ms-access: rowsource question

Question

i am a vb.net programmer, and i have done lots of VBA with excel. i am a beginner at access.

someone has given me an access database to debug and i am lost.

i succesfully made it connect to a local mysql database.

i have a listbox on a form that has a rowsource attached to a query called listbox. here's a picture of the properties of the listbox:

there are some issues with displaying the results of the query because the query is too complex and incorrectly structured. when there are fewer than a few results from the query, they display in the listbox without a problem, but when there are more than 4-5 results, it leaves it blank; however, i know that it returned the correct number because it left the correct number of rows (which are blank in the listbox). therefore, instead of trying to fix the huge messy query, i would like to feed all the rows in a string, and then feed them back into the listbox.

can someone help me with this? when you reply please put your responses in laymen's terms because as i said i am an access beginner.

this is what the listbox looks like when it doesnt want to display results:

Answer 1

If you're using ADO then you can use the .GetRows method to put the recordset into a two-dimensional array (matrix) and then manipulate it. Of course it'd be better if you could simplify your query so that Access would work normally with it.

Answer 2

I'm wondering if the Column Count property is correct. That is are there ten columns or fields in the query?

Also you should really put ten widths in the Column Widths property even if they are 0" width. I only see seven in there.

Is this Access 2003 with SP3? If so see this KB article. Combo box controls and list box controls display no value or incorrect values in Access 2003 after you install Office 2003 Service Pack 3

Answer 3

You could try something on these lines:

''Some notes on declarations for ADO recpdset and connection
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection

''Set the connection object to the access project
''connection. You may wish to use a connection string (see below)
Set cn = CurrentProject.Connection

''Open the recordset, change the sql string to whatever string is used
''to populate the listbox
rs.Open "SELECT * FROM Testaccess", cn

''Convert the recordset to a string delimited by row and column with ;
''This is the delimiter needed by Access for the listbox with value data
strlist = rs.GetString(, , ";", ";")

''Set the rowsource of the listbox to the string
Me.List0.RowSource = strlist

''It would be easiest to set these before the run
''None of this is necessary, it can all be set as properties 
''of the listbox before the code is run
''Column count, same as number of fields
Me.List0.ColumnCount = rs.Fields.Count
''List type is values
Me.List0.RowSourceType = "Value List"

''This is just a set of 1cms for each column
For i = 1 To rs.Fields.Count
    strWidths = strWidths & ";1cm"
Next

''here we go, all columns set to 1cm width
Me.List0.ColumnWidths = Mid(strWidths, 2)

connection string: http://www.connectionstrings.com/