Populate Multi Column listbox using Recordset

[johnmidcap]

Hello. I have no problem populating a listbox from a recordset with one column but am having a lot of difficulty trying to figure out how to do it with two columns. Any help is appreciated.

Thanks,

John

 

[MajP]

example

Private Sub Form_Load()
  Dim lst As Access.ListBox
  Dim rs As DAO.Recordset
  
  Set lst = Me.lstOne
  lst.ColumnCount = 2
  lst.ColumnWidths = "1 in; 1 in"
  
  Set rs = CurrentDb.OpenRecordset("select EmployeeID, LastName from Employees")
  Set lst.Recordset = rs
  
End Sub

 

[SeeThru]

If you need to build it as a semi permanent list, I have used a technique of generating a string

1;"one";2;"two"3;"Three" etc and then placing that as the source.

It's crude, but gets round a couple of updating issues had at the time.

You can buld the string from a record source by stepping thruogh it one row at a time, and adding to a string value

rst=(recordset)
do while rst.eof=false
  mystring=mystring& ";" rst!index & """ & rst!description & """
  rst.movenext
Loop

mycombo.rowsource=mystring

SeeThru
Synergy Connections Ltd - Uk Telemarketing and Telesales Services and
Synergy Mobile Solutions - UK Mobile phones, land lines and call packages

 

[TheAceMan1]

Howdy MajP . . .

I got errors on the following line.

[blue]Set lst.Recordset = rs[/blue]

Searching my library I found:

[blue]   Dim lst As ListBox
   [green][b]'
   '
   '[/b][/green]
   lst.RowSource = rst.Name[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

AceMan,
I have no idea what you are talking about. What is your error? Does it not populate? What is a libraray? Do you get an error code? What version of Access do you use?
Bottom line The code is correct. Prior to A2003 the list box did not have a recordset property. You could refer to the recordset name as long as the sql string was less than 255 characters. The name of a recorset based on a sql string is the sql string. Hope that helps.

 

[TheAceMan1]

[blue]Prior to A2003 the list box did not have a recordset property.[/blue]

That explains it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]