This is the first part of FAQ ListView & Microsoft Access.
As you know ListView is an Activex Control from Microsoft
Microsoft ListView Control 6.0 (SP6) = MSCOMCTL.OCX
Microsoft ListView Control 5.0 (SP2) = MSCOMCTL32.OCX
Unlike Access's native control "ListBoxes", ListViews support Text align, Conditional Formatting, AlphaSorting, Column Total etc..
The best part I like with ListView is you can make the interface better than using a datasheet on a form. If there are a few records then datasheet leaves a blank gray area that looks ugly. ListView 6 supports GridLines too. So the data looks like written on ruled ledger.
To insert a ListView on a form when you are at the design view of the form
Insert |> Activex Controls |> Microsoft ListView Control(X)
Here is a routine that loads the "Employees" table from the famous NorhtWind DB.
Code:
Public Sub FillEmployees()
On Error GoTo ErrorHandler
[color green]'Set Reference to Microsoft DAO 3.xx Library.[/color]
[color green] 'set variables[/color]
Dim rs As DAO.Recordset
Dim db As Database
Dim lstItem As ListItem
Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT * FROM Employees"
Set rs = db.OpenRecordset(strSQL)
With Me.ListView1
[color green]'Set ListView style[/color]
.View = lvwReport
[color green] 'This is not supported by ListView 5[/color]
.GridLines = True
.FullRowSelect = True
[color green]'Clear Header and ListItems[/color]
.ListItems.Clear
.ColumnHeaders.Clear
End With
[color green] 'Set up column headers[/color]
With Me.ListView1.ColumnHeaders
.Add , , "Emp ID", 1000, lvwColumnLeft
.Add , , "Salutation", 700, lvwColumnLeft
.Add , , "Last Name", 2000, lvwColumnLeft
.Add , , "First Name", 2000, lvwColumnLeft
.Add , , "Hire Date", 1500, lvwColumnRight
End With
[color green] ' Add items and subitems to list control.[/color]
rs.MoveFirst
Do Until rs.EOF
Set lstItem = Me.ListView1.ListItems.Add()
lstItem.Text = rs!EmployeeID
lstItem.SubItems(1) = rs!TitleOfCourtesy
lstItem.SubItems(2) = rs!LastName
lstItem.SubItems(3) = rs!FirstName
lstItem.SubItems(4) = rs!HireDate
[color green]'Next row[/color]
rs.MoveNext
Loop
[color green] 'close recordset[/color]
rs.Close
DoCmd.Echo True
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 3021 Then [color green] ' no current record[/color]
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub
Then call it OnLoad Event of the of the form or Click of a commnad button
Code:
Private Sub Form_Load()
Call FillEmployees
End Sub
You need take care of Null Values. Null values will bring a runtime error.
Replace it with
Nz,IIF,Trim functions
Code:
lstItem.SubItems(1) = rs!TitleOfCourtesy
lstItem.SubItems(2) = rs!LastName
to
Code:
lstItem.SubItems(1) = Nz(rs!TitleOfCourtesy, "N/A")
lstItem.SubItems(2) = Nz(Trim(rs!LastName))
Also if you need to format the data at your choice, you can do the formatting too.
Code:
[color green]'formatting date[/color]
lstItem.SubItems(4) = Format(rs!HireDate, "Medium Date")
lstItem.SubItems(4) = Format(rs!HireDate, "dd-mmm-YYYY")
[color green]'formatting currency[/color]
lstItem.SubItems(4) = Format(rs!SumOfFreight, "##,##0.00#")
lstItem.SubItems(2) = Format(rs!UnitPrice, "$#,##,0.00#")
[color green]'Percentage[/color]
lstItem.SubItems(4) = Format(rs!Discount, "#,##,0%")
Hope this helps you...
[link http://www.geocities.com/zameerabd/dl/LVLoadData.zip]Download Sample[/link] (112 kb zip) [Access 2000 file format (396 kb)]
There are issues on usage of Activex controls on an access form. Most of them are related to "Activex Not Registered" error. A few threads from the Tek-Tips forums are below for example.
Object Doesn't Support this Property or Method thread705-1071549
Listview checkbox property is missing? thread222-840619
Access Listboxes - Too Basic thread1121-838377
data in listview not shown thread705-655602
Here are some intersting links to Microsoft
[link http://support.microsoft.com/kb/q155178/]Sample Function to Fill a ListView Controla(Access 97)[/link]
[link http://support.microsoft.com/default.aspx?scid=kb;en-us;210006]Sample Function to Fill a ListView Control(Access 2000 and higher)[/link]
