Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

ListView & Access

ListView & Microsoft Access - Part 1 - (Load Data) by ZmrAbdulla
Posted: 11 Aug 05 (Edited 12 Aug 05)

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.


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
    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.

    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
    'close recordset
    DoCmd.Echo True
    Exit Sub
    If Err = 3021 Then    ' no current record
        Resume Next
        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


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


lstItem.SubItems(1) = rs!TitleOfCourtesy
lstItem.SubItems(2) = rs!LastName


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.    


'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#")
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

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close