Hope I explain this well.
You must first put the activex listview 6.0 controller on your form.
On the load event of your form. Put the following code.
dim lv as object
dim col as MsComctLib.ColumnHeader
(dim col1, col2, etc for each column you want in the listbox with the MsComctlib.Columheader extension)
Me.Listview0.fullrowselect= true
' (Listview0 is the name of the control on your form. (will give it full row select to act like a listbox)
set lv = Me.Listview0
'''(Listview0 is name of listview control on your form)
lv.view = 3
''' (report view for the listview controller. Makes it act like a listbox.)
lv.listitems.clear
lv.columnheaders.clear
''(clears both listitems and columnheaders each time program runs, else you will have duplicate date in listbox. )
set col = lv.columnheaders.add(,,"NAME of YOUR COLUMN",listview0.width / 8)
' (DO THIS FOR ALL OF YOUR COLUMNS THAT YOU WANT, THE NAME YOU PUT WILL BE THE NAME DISPLAYED IN THE COLUMN HEADER BOX. THE LISTVIEW0.WIDTH IS THE WIDTH OF THE COLUMN....)
**** I suggest placing the following code in the vba modules section and call the sub everytime you want to requery the listbox. You need to loop a recordset and return the values to the listbox.
''(Call the sub from the modules section)
listloop
end sub
***********************************************************
Sub listloop()
dim lv as object
dim itm as MsComctlib.listitem
dim rs as adodb.recordset
dim cn as adodb.connection
set rs = new adodb.recordset
set cn = currentproject.connection
set lv = forms!NameofForm!Listview0
''''(Name of Form is the form in which the listview controller is on, the control name is the listview controller)
rs.open "SELECT * FROM NAME TABLE",cn,adopenstatic
lv.listitemsclear
lv.columnheaders.clear
''' again clear the listitems and columnheaders, necessary when requerying the listbox.
do until rs.eof
set itm = lv.listitems.add(,,rs!tablename)
itm.subitems(1) = (rs!nexttablename)
itm.subitems(2) = (rs!nexttable)
''' the first statement is for the first column in the listbox, then the subitems are for each additional column, keep adding subitems in increments to incorporate all of your columns.. Make sure you have the columnheaders in the first subroutine declared, else they won't populate. The rs!tablename is the name of the tables you want to put into the listview controller. ..)
*** to conditionally format the items in the listview controller, use the following with an IF statement or whatever you want, they can be static or change as you see fit.
itm.forecolor = vbred
'' (only good for first column in listview controller, will change the color for all other columns use the below statement.)
itm.listsubitems(1).forecolor = vbred
itm.listsubitems(2).forecolor = vbwhatever
''' so on and so on. This has the ability to change only one row at a time and it can be conditionally formatted. Hope this works well, give me a yell if you need anything else.
Regards.
