Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ListView & Microsoft Access - Part 1 - (Load Data)

ListView & Access

ListView & Microsoft Access - Part 1 - (Load Data)

by  ZmrAbdulla  Posted    (Edited  )
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]

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top