how to make columns in ListBox

[LUTINO]

Dear All,

I googled how to populate list box (with columns) but could not succeed..

Situation:
I have a recordset with 3 fields. I want to populate listbox (add Columns) in following style. (Dashes or - is just to maintain symtry)
ID------Name--------AGE
1-----Alice-------------22
2-----Chris-------------21
3------Asha------------23

But I am getting results like this

1
Alice
22
2
Chris
21
3
Asha
23


I just simplified above mentioned scenioro for understanding..actual code is following.

Dim Sql_Search As String
Dim RowStr As String



Sql_Search = "select tbl_Company.cmp_id , tbl_Company.cmp_id_char,tbl_Company.cmp_short_name , tbl_Company.cmp_full_name, tbl_Company.cmp_address, tbl_Company.cmp_city , tbl_Company.cmp_fone, tbl_Company.cmp_fax, tbl_Company.cmp_limit, tbl_Company.cmp_status, tbl_Company.cmp_purchaser, tbl_Company.cmp_Purchaser_mobile, tbl_Company.cmp_accounts, tbl_Company.cmp_accounts_mobile from Tbl_company where tbl_company.cmp_id_char = '" & Txt_Cmp_Char & "' "

Set Rst = Dbs.OpenRecordset(Sql_Search)





If (Rst.RecordCount <> 0) Then
Do While Not Rst.EOF
' show Fields in 1 row
RowStr = Rst.Fields(0).Value & " " & Rst.Fields(1).Value & " " & Rst.Fields(2).Value & " " & Rst.Fields(3).Value
Lst_Search.AddItem (RowStr)
Rst.MoveNext

Loop
End If




End Sub

[ranman256]

In the listbox propery,
set COLUMNS to 3
set COLUMWIDTHS to 0;1;0.5

[apr pillai]

Besides setting the Column Count and Column Widths you need some correction in the following expression in your Code:

RowStr = Rst.Fields(0).Value & " " & Rst.Fields(1).Value & " " & Rst.Fields(2).Value & " " & Rst.Fields(3).Value

Add a semicolon ( between column items. The column count property value will ensure that each line of items is in group of 3.

Set Rst = Dbs.OpenRecordset(Sql_Search)
    


If (Rst.RecordCount <> 0) Then
          Do While Not Rst.EOF
                 ' show Fields in 1 row 
                RowStr = RowStr & Rst.Fields(0).Value & ";" &  Rst.Fields(1).Value & ";" & Rst.Fields(2).Value & ";"  & Rst.Fields(3).Value & ";"
                Lst_Search.AddItem (RowStr)
                Rst.MoveNext
           
           Loop
End If
'Next statement removes the extra semicolon at the end of the string
RowStr = Left(RowStr,Len(RowStr)-1) 

'Replace Combobox with your combo box name
Me.Combobox.RowSource = RowStr 
Me.Combobox.ReQuery
End Sub

[ssanfu]

Why the VBA code? All you need is a query...

[ItsMe]

Why the VBA code?...

Because this is the programming forum.

[ssanfu]

Because this is the programming forum.

Now THAT's funny..... ROTFLMAO


Thanks!! I needed that.

[apr pillai]

Corrections:

Set Rst = Dbs.OpenRecordset(Sql_Search)
    

Lst_Search.RowSourceType = "Value List"
If (Rst.RecordCount <> 0) Then
          Do While Not Rst.EOF
                 ' show Fields in 1 row 
                RowStr = RowStr & Rst.Fields(0).Value & ";" &  Rst.Fields(1).Value & ";" & Rst.Fields(2).Value & ";"  & Rst.Fields(3).Value & ";"
           'Lst_Search.AddItem RowStr
                Rst.MoveNext
           
           Loop
        rst.close
End If
'Next statement removes the extra semicolon at the end of the string
RowStr = Left(RowStr,Len(RowStr)-1) 

Me.Lst_Search.RowSource = RowStr 
Me.Lst_Search.ReQuery
End Sub

OR

Set Rst = Dbs.OpenRecordset(Sql_Search)
    

Lst_Search.RowSourceType = "Value List"
If (Rst.RecordCount <> 0) Then
          Do While Not Rst.EOF
                 ' show Fields in 1 row 
                RowStr = Rst.Fields(0).Value & ";" &  Rst.Fields(1).Value & ";" & Rst.Fields(2).Value & ";"  & Rst.Fields(3).Value
                Lst_Search.AddItem RowStr
                Rst.MoveNext
           
           Loop
         rst.close
End If
Me.Lst_Search.ReQuery
End Sub

[LUTINO]

Thank you Mr. APR PiLLAI & all team..
My problem solved.