INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

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

Jobs

List Boxes

How can I format my ListBox? by SiJP
Posted: 27 Jul 04

The simple answer is, you cannot.  The standard Listbox that comes with MS Access (97) does not have the properties to be able to format the color of your list items text, based on criteria you specify, or allow for images.

And, if you want the user to dynamically sort the column of their choice, you're having to resort to creating command buttons and lining them up accordingly.  Not very tidy.

So, what do you do about it?

 - Create a List View for your form.

eh? A what?

Ok, go into the design mode of a form.. click on Insert from the toolbar, then select "ActiveX Control..." from the sub menu (you're getting good at this already ;D).  In the selection box that is presented, scroll down to the entry that reads "Microsoft ListView Control, Version 6.0" and click on this, then click on OK.

Your ActiveXControl should appear on your form.

1) Setting The Properties

DOUBLE CLICK on this control, and a properties list will appear.  This is specific to the ListView control.  I personnaly set the following 'General' Settings, and leave the rest alone:

MousePointer: 0 - ccDefault
View: 3 - lvwReport
Arrange: 0 - lvwNone
LabelEdit: 1 - lvwManual
BorderStyle: 1 - ccFixedSingle
Appearance: 1 - cc3D
OLEDragMode: 0 - ccOLEDragManual
OLEDropMode: 0 - ccOLEDropNone

HideColumnHeaders: 0
HideSelection: 0
LabelWrap: 1
MultiSelect: 0
Enabled: 1
AllowColumnReorder: 1
Checkboxes: 0
FlatScrollBar: 0
FullRowSelect: 1
Gridlines: 1
HotTracking: 0
HoverSelection: 0


You can obviously set these using code, but it is beyond the scope of this FAQ to do so (yeah yeah, that's my get out of jail free card cus I haven't done it yet :D)

1) Name your control

Name your Control with care, and something a bit more exciting that "JimsListView".  Preferably, prefix it with 'lvx' and give it a meaningful name, like 'lvxEmployees'.

Set up is complete.. lets code!

2) Loading Data into the ListView

Create the following Function:

CODE

Private Function fLoadList()
On Error GoTo err_handle
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Variables
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim lvxObj As ListView
    Dim lstItem As ListItem
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim iColWidth As Integer
    Dim i As Integer
    Dim strSQL As String

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Set the SQL statement for our recordsource
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'By using the SQL 'AS' keyword, we can give our columns custom names...

        strSQL = "SELECT " & _
                 "EmployeeID as [ID], " & _
                 "EmployeeName as [Name], " & _
                 "NTLogin as [NT Login], " & _
                 "EmployeeTitle as [Title], " & _
                 "Active as [Active] " & _
                 "FROM tblEmployees " & _
                 "ORDER BY EmployeeID ASC;"
                
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'  Set up List View object, and invoke a recordset based on the SQL
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Set lvxObj = lvxEmployees.Object
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'  Clear any items in the current list.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        lvxObj.ListItems.Clear
    
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Clear existing, then add new column headers
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'code loops through the open recordset's field names (the custom
'ones with set using the AS keyword, if you remember) and sets them
'as our ListViews columnheaders.
'We also set the column widths to be all the same, which is calculated
'by taking the width of the ListView itself, divided by the number of
'columns.  I minus 20 of the end of each one, to ensure all fields are
'visible completely (not hanging over the edge of the listview).

        
            With lvxObj.ColumnHeaders
            .Clear
                For i = 0 To rs.Fields.Count
                    For Each fld In rs.Fields
                        If i = 0 Then
                         iColWidth = 0 ' This hides the first ID column from the user, but retains it's value as the identifying property
                        Else
                         iColWidth = (lvxEmployees.Width / (rs.Fields.Count - 1)) - 20
                        End If
                     .Add , , fld.Name, iColWidth
                     i = i + 1
                    Next fld
                Next i
            End With
    
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Check values present in recordset
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If rs.BOF Then
        'No data has been returned .. no need to add the items to the
    ' list view.
    Else
        'Records present.. setting up list of items
        
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Add in list items, with colour based on criteria of if the
' employee is active or not.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        rs.MoveFirst
        
        While Not rs.EOF
            For i = 0 To rs.Fields.Count
                If i = 0 Then
                    Set lstItem = lvxObj.ListItems.Add(, , Nz(Trim(rs(i)), "")) ' Set the value of the first column of the row
            
            'Set the Colour based on criteria
            If rs("Active") = 0 Then
             lstItem.ForeColor = vbBlack ' Black if not active
            Else
             lstItem.ForeColor = vbRed ' Red if active
            End If
                ElseIf i < rs.Fields.Count Then
                    lstItem.SubItems(i) = Nz(Trim(rs(i)), "") ' set the subsequent columns, known as subitems.
                
             'Repeat Colour setting based on criteria, for the subitems
            If rs("Active") = 0 Then
             lstItem.ForeColor = vbBlack ' Black if not active
            Else
             lstItem.ForeColor = vbRed ' Red if active
            End If
                Else
                End If
            Next i
            rs.MoveNext
        Wend
    End If


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Close off & Cleanup
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    rs.Close

err_handle:
    Select Case err.Number
        Case 0
         'ignore, not an Error
        Case Else
     'Handle error Appropriately.
    End Select
End Function

3) Handling a double click on the ListView

Use the following code:

CODE

Private Sub lvxEmployees_DblClick()
On Error GoTo err_handle

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Variables
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim lvxObj As ListView
    Dim lstItem As ListItem

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Set objects
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Set lvxObj = lvxEmployees.Object
    Set lstItem = lvxObj.SelectedItem
    

'lstItem now contains the content of EmployeeID

msgbox Dlookup("EmployeeName", "tblEmployees", "EmployeeID = " & lstitem)


err_handle:
    Select Case err.Number
        Case 0
         'ignore, not an Error
        Case Else
     'Handle error Appropriately.
    End Select
End Sub

4) Handling column sorting

CODE

Private Function fListColumnSort(ColumnHeader)
On Error GoTo err_handle

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Variables etc
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim i As Integer
    Dim lvxObj As ListView
    Set lvxObj = lvxEmployees.Object
    
    i = ColumnHeader.Index - 1

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Turn on sorting for that column header
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        lvxObj.Sorted = True
        lvxObj.SortKey = i

        If lvxObj.SortOrder = lvxAscending Then
         lvxObj.SortOrder = lvxDescending
        ElseIf lvxObj.SortOrder = lvxDescending Then
         lvxObj.SortOrder = lvxAscending
        End If

        lvxObj.Refresh

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Error Handling
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
err_handle:
    Select Case err.Number
        Case 0
         'ignore, not an Error
        Case Else
     'Handle error Appropriately.
    End Select
End Function


Summary

Although this all seems rather a lot of code for creating a list, it really is minimalised (well, from my early attempts anyway) and effective.

This should give you a heads up for creating your own list views.  Try adding in images for effect.. try playing with the properties of the list view.  And keep asking for support where needed; everyone is here to help!


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

My Archive

Resources

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