VBA: Listbox with 1 record

**hendre** · 07-23-2009, 02:12 PM

In some cases an Access DB query will return only one record (by design).

When this happens, and only when this happens, my listbox displays each of the values in a seperate row instead of just on one row.

i.e. My query will return 8 values per record. If there is only 1 record returned, the listbox shows each value on a seperate row instead of on one row in 8 columns.

The listbox works as intended when there are 2 or more records. I am now stuck. Pardon the ugly code. "questionaires" is the name of the listbox. "record_array" is the variant array containing the records.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim record_array As Variant


With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=reporting_tool_db.mdb;"
    .Open
End With

With rs
    .Open "SELECT Business_Unit,Inquiry_Type,Rec_Date,Comp_Date,Author,Hours,Path,ID FROM Questionaire WHERE [Client_Name] = '" & selected_client & "'", cn
    record_array = .GetRows
    .Close
End With

cn.Close

With Me.questionaires
    .Clear
    .list = Application.Transpose(record_array)
    .ListIndex = -1
    .ColumnWidths = "1.5 in, 0.7 in, 1 in, 0.8 in, 0.8 in, 0.7 in, 0.1 in"
End With

Any help/suggestions are appreciated, thanks!

**antoka05** · 07-24-2009, 02:33 AM

Try to add this code:

Me.questionaires.ColumnCount = <fieldNum>

Regards,
Antonio

**romperstomper** · 07-24-2009, 04:51 AM

See this thread (as long as you are not using a Decimal data type!)

**Chance2** · 07-24-2009, 12:45 PM

You could try adding a dummy record and then deleting it from the listbox...

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim record_array As Variant


With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=reporting_tool_db.mdb;"
    .Open
End With

With rs
    .Open "SELECT '','','','','','','' FROM Questionaire UNION " & _
             "SELECT Business_Unit,Inquiry_Type,Rec_Date,Comp_Date,Author,Hours,Path,ID " & _
             "FROM Questionaire WHERE [Client_Name] = '" & selected_client & "'", cn
    record_array = .GetRows
    .Close
End With

cn.Close

With Me.questionaires
    .Clear
    .list = Application.Transpose(record_array)
    .RemoveItem (0)    .ListIndex = -1
    .ColumnWidths = "1.5 in, 0.7 in, 1 in, 0.8 in, 0.8 in, 0.7 in, 0.1 in"
End With

The first SELECT statement must contain the same number of fields as the second for the UNION to work and by using REMOVEITEM(0) you'll always remove that first (empty) record.