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
'Set Reference to Microsoft DAO 3.xx Library.

    'set variables
    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
        'Set ListView style
        .View = lvwReport
        'This is not supported by ListView 5
        .GridLines = True
        .FullRowSelect = True
        'Clear Header and ListItems
        .ListItems.Clear
        .ColumnHeaders.Clear
    End With
    'Set up column headers
    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
    ' Add items and subitems to list control.

    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
       'Next row
        rs.MoveNext
    Loop
    'close recordset
    rs.Close
    DoCmd.Echo True
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then    ' no current record
        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

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

'formatting date
lstItem.SubItems(4) = Format(rs!HireDate, "Medium Date")
lstItem.SubItems(4) = Format(rs!HireDate, "dd-mmm-YYYY")

'formatting currency
lstItem.SubItems(4) = Format(rs!SumOfFreight, "##,##0.00#")
lstItem.SubItems(2) = Format(rs!UnitPrice, "$#,##,0.00#")

'Percentage
lstItem.SubItems(4) = Format(rs!Discount, "#,##,0%")

Hope this helps you...
Download Sample (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
Sample Function to Fill a ListView Controla(Access 97)
Sample Function to Fill a ListView Control(Access 2000 and higher)

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

Microsoft: Access Forms FAQ

ListView & Access

CODE

CODE

CODE

CODE

CODE

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!