Selecting a Row in a ListView 2

[beckwiga]

Here's another one I can't figure out...

I have a ListView on my form. I want to be able to select a row in the ListView, and upon doing so, I want the rest of my form to goto that record id. For example, my Listview consists of Name, City, State. I have other fields on my form, also Name, City, State, Phone Number, Email, etc, but I want jump to that record (all my fields on the form) when that row is selected. I'm sure I have toi capture the row ID somehow and add 1 or subtract 1 to match my ID in the underlying Address table (my Address form). I'm not sure how to get started though. Been google'ing for a while. Not much on this yet.

 

[ZmrAbdulla]

On which application you are working? Access? Excel? VB6?
What version of ListView? ListView6 has ability to fullrow select. Others need to be done programtically.

________________________________________
Zameer Abdulla
Visit Me
There is only one perfect child in this world. Every mother has it.

 

[beckwiga]

Hello Zmr. I am on an Access form and am using VBA with the MS ListView 6.0. I believe the control is mscomctl.ocx. I have full row select enabled. How would I programatically code: upon full row select of an ID(row) in the List View, do something else. I'm having difficulty finding examples or documentation on this. Thanks.

 

[ZmrAbdulla]

Here is a code that opens a form with details of employee. EmployeID AlphaNumeric If EmployeeID is Numeric then use the second style. You can change the code to display details on the same form

Public Sub ListView1_DblClick()
    On Error GoTo Err_ListView1_DblClick
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmEmployees"
    [b]stLinkCriteria = "[EmployeeID]=" & "'" & Me.ListView1.SelectedItem.Text & "'"[/b]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
Exit_ListView1_DblClick:
    Exit Sub
Err_ListView1_DblClick:
    MsgBox Err.Description
    Resume Exit_ListView1_DblClick
End Sub

   stLinkCriteria = "[EmployeeID]=" &  Me.ListView1.SelectedItem.Text

hope this helps

________________________________________
Zameer Abdulla
Visit Me
There is only one perfect child in this world. Every mother has it.

 

[ZmrAbdulla]

BTW, There are 8 forums dedicated to MS Access in TEK-TIPS.
Please post your next post to most appropriate forum.

Microsoft: Access Forms Forum
Microsoft: Access Modules (VBA Coding) Forum
Microsoft: Access Queries and JET SQL Forum
Microsoft: Access Reports Forum
Microsoft: Access Tables and Relationships Forum
Microsoft: Access Other topics Forum
Microsoft: Access Project (ADP) Forum
Regards

________________________________________
Zameer Abdulla
Visit Me
There is only one perfect child in this world. Every mother has it.

 

[beckwiga]

Hello again Zameer. Let me be more specific. Here is what I have so far:

Private Sub ListView0_Click()

Dim i As Integer
Dim Text1

With ListView0
For i = 1 To .ListItems.Count
If .ListItems(i).Selected Then

DoCmd.GoToRecord , , acGoTo, (i)
Text1 = ListView0.ListItems(i)
'MsgBox (Text1) This returns "Smith, Adam"

End If
Next i
End With

End Sub

This works fine, but does not givem me what I need. This works fine as long as my ListView columns are not sorted(by clicking the column heading). If a column is sorted, when you click on an ListItem it takes the ListView number(i) and goes to that record. If I click on row 3 after a sort on column1, the rest of my form goes to record 3, but record 3 is no longer my 3rd row in the ListView. Does this make sense? Here is my sort code:

Private Sub ListView0_ColumnClick(ByVal ColumnHeader As Object)
' When a ColumnHeader object is clicked,
' the ListView control is sorted by the subitems of that column.
' Set the SortKey to the Index of the ColumnHeader - 1

Me.ListView0.SortKey = ColumnHeader.Index - 1
' Set Sorted to True to sort the list.
If ListView0.SortOrder = lvwAscending Then
ListView0.SortOrder = lvwDescending
Else
ListView0.SortOrder = lvwAscending
End If
Me.ListView0.Sorted = True
End Sub

 

[ZmrAbdulla]

Private Sub ListView0_Click()
    Dim i As Integer
    Dim Text1
    With ListView0
        For i = 1 To .ListItems.Count
            If .ListItems(i).Selected Then
                Me.Text1.Value = ListView0.ListItems(i)
                Me.Text2.Value = ListView0.ListItems(i).ListSubItems(1)
                Me.Text3.Value = ListView0.ListItems(i).ListSubItems(2)
            End If
        Next i
    End With
End Sub

The column sort is only Alpha sort as it is provided by Microsoft. When you click on header it sorts like text sorting even it is number or date. Result will be like below.
1
10
13
2
20
21
3
30

In VB6 I have seen some workarounds that work well their only. I have no succes on Access with them. Only workaround in Access I could suggest is to load listview with different SQL ORDER BY the column clicked. (Though this is a slow method , if you have too many records)
Here is a full page of code that I have tested and working well
DECLARE SECTION

Option Compare Database
Option Explicit
Dim strSql As String
[b]'YOU NEED TO HAVE DAO XXX REFRENCE[/b]

CODE TO FILL LISTVIEW

Private Sub FillListViewTown()
    On Error GoTo ErrorHandler
    Dim rs As DAO.Recordset    
    Dim db As Database
    Dim lstItem As listItem
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)
    DoCmd.Echo False
    'Clear header and Listview
    Me.ListView1.ColumnHeaders.Clear
    Me.ListView1.ListItems.Clear
    'Set up column headers
    With Me.ListView1.ColumnHeaders
        .Add , , " ", 1000, lvwColumnLeft
        .Add , , "Town", 3000, lvwColumnCenter
        .Add , , "Chk", 1000, lvwColumnCenter
    End With
    rs.MoveFirst
    Do Until rs.EOF
        ' Add items and subitems to list control.
        Set lstItem = Me.ListView1.ListItems.Add(1)
        lstItem.Text = 0 & rs!TownID
        lstItem.SubItems(1) = rs!Town
        lstItem.SubItems(2) = rs!YesNO
        rs.MoveNext
    Loop
    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

ON LOAD FILL IT WITH NORMAL SORTED

Private Sub Form_Load()
strSql = "SELECT  * FROM tblTown ORDER BY TownID"
    Call FillListViewTown
End Sub

SHOW SELECTED RECORD IN THE TEXTBOXES

Private Sub ListView1_Click()
    Dim i As Integer
    Dim Text1
    With ListView1
        For i = 1 To .ListItems.Count
            If .ListItems(i).Selected Then
                Me.Text1.Value = ListView1.ListItems(i)
                Me.Text2.Value = ListView1.ListItems(i).ListSubItems(1)
                Me.Text3.Value = ListView1.ListItems(i).ListSubItems(2)
            End If
        Next i
    End With
End Sub

SORT IT BY SELECTED COLUMN

Public Sub ListView1_ColumnClick(ByVal ColumnHeader As Object)
    Select Case ColumnHeader.Index
    Case 1
        If strSql = "SELECT  * FROM tblTown ORDER BY TownID" Then
            strSql = "SELECT  * FROM tblTown ORDER BY TownID DESC"
        Else
            strSql = "SELECT  * FROM tblTown ORDER BY TownID"
        End If
        Call FillListViewTown
    Case 2
        If strSql = "SELECT  * FROM tblTown ORDER BY Town" Then
            strSql = "SELECT  * FROM tblTown ORDER BY Town DESC"
        Else
            strSql = "SELECT  * FROM tblTown ORDER BY Town"
        End If
        Call FillListViewTown
    Case 3
        If strSql = "SELECT  * FROM tblTown ORDER BY YesNo" Then
            strSql = "SELECT  * FROM tblTown ORDER BY YesNo DESC"
        Else
            strSql = "SELECT  * FROM tblTown ORDER BY YesNo"
        End If
        Call FillListViewTown
    End Select
End Sub

Hope this helps... or please come back if you need more help

Regards

________________________________________
Zameer Abdulla
Visit Me
There is only one perfect child in this world. Every mother has it.

 

[beckwiga]

Zameer -- Thanks for writing again. I'm still working on implementing your suggestions here. Only problem I have on the ListView Fill is in the subitems.

Do Until rs.EOF
' Add items and subitems to list control.
Set lstItem = Me.ListView0.ListItems.Add(1)
lstItem.Text = 0 & rs!ID
lstItem.SubItems(1) = rs!FamilyName
' lstItem.SubItems(2) = rs!Group
lstItem.SubItems(3) = rs!City
lstItem.SubItems(4) = rs!State
rs.MoveNext
Loop
rs.Close

The line that is commented out... I will occassionally have a Null in the Group or City or State. Right now, I am getting Invalid use of Null (Error 94) when I try and fill a null. How do I get around this? Should I fill a "" (space) in the table?

 

[PHV]

Why not using the Nz function ?
lstItem.SubItems(2) = Nz(rs!Group, "")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckwiga]

Thanks, PHV. I'll try that. I think this works too:

lstItem.SubItems(2) = IIf(IsNull(rs!Group), " ", rs!Group)

 

[beckwiga]

Zameer --

Private Sub ListView1_Click()
Dim i As Integer
With ListView1
For i = 1 To .ListItems.Count
If .ListItems(i).Selected Then
Me.FullName.Value = ListView1.ListItems(i)
End If
Next i
End With
End Sub

I have to find another way to do this, unless I do not understand. This code works, but instead of setting the values of the ListView to the text field(which works), I am wanting to go the record itself. I will not be able to just set all subitems in the listview to text fields, as I have several other fields on my form (such as phone number, email address, ect) which I want to allow the user to edit on the form. I need to be able go to the selected record somehow by clicking the listview item.

By the way, I have implemented your code to fill the ListView and Sort by Column heading and I like this a lot better than what I had. This is all still new to me, so thanks for the assistance. I am learning a lot though

Here is my FillListView:

Private Sub FillListView0()
On Error GoTo ErrorHandler
Dim rs As DAO.Recordset
Dim db As Database
Dim lstItem As ListItem
Set db = CurrentDb()
Set rs = db.OpenRecordset(StrSQL)
DoCmd.Echo False
'Clear header and Listview
Me.ListView0.ColumnHeaders.Clear
Me.ListView0.ListItems.Clear
'Set up column headers
With Me.ListView0.ColumnHeaders
' .Add , , "ID", 500, lvwColumnLeft
.Add , , "Family Name", 2000, lvwColumnLeft
.Add , , "Group", 900, lvwColumnLeft
.Add , , "City", 1400, lvwColumnLeft
.Add , , "State", 700, lvwColumnLeft
End With
rs.MoveFirst
Do Until rs.EOF
' Add items and subitems to list control.
Set lstItem = Me.ListView0.ListItems.Add(1)
' lstItem.Text = 0 & rs!ID
lstItem.Text = IIf(IsNull(rs!FamilyName), "", rs!FamilyName)
' lstItem.SubItems(1) = rs!FamilyName
lstItem.SubItems(1) = IIf(IsNull(rs!Group), "", rs!Group)
lstItem.SubItems(2) = IIf(IsNull(rs!City), "", rs!City)
lstItem.SubItems(3) = IIf(IsNull(rs!State), "", rs!State)
rs.MoveNext
Loop
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

 

[beckwiga]

Just tried something else, but didn't work either.

With ListView0
For i = 1 To .ListItems.Count
If .ListItems(i).Selected Then

Text1 = ListView0.ListItems(i).ListSubItems(4)
DoCmd.GoToRecord , , acGoTo, Text1
'Text1 = ListView0.ListItems(i)
'MsgBox (Text1) 'This returns the record ID number (autonumber)

End If
Next i
End With

I added SubItem(4) which is the Record ID(which is an autonumber). I'm getting the ID successfully, but I just realized that the ID does not necessarily mean that is the record number. When records are deleted from the table, autonumber does not account for this, and keeps counting. I could have a record with an ID of 17, but only 12 records in my rs. Hence, the above will error when I try and go to record 17. Grrrr..... Is there a way I can automatically tell Access to reorder my IDs if records get deleted and leave gaps in my IDs? For example, I have IDs 1,2,3,4,5,6,7,11,12,15,20,21. This is only 12 records.

 

[PHV]

You may try something like this:
strCrit = ""
With ListView0
For i = 1 To .ListItems.Count
If .ListItems(i).Selected Then
strCrit = "," & ListView0.ListItems(i).ListSubItems(4) & strCrit
End If
Next i
End With
If strCrit <> "" Then
Me.Filter = "[ID] In (" & Mid(strCrit, 2) & ")"
Me.FilterOn = True
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZmrAbdulla]

Too busy schedule today. Try this let me know. also don't forget to use IIF,NZ functions as suggested by PHV to replace if you have null values in the records

Private Sub ListView1_Click()
    Dim rstA As DAO.Recordset
    Dim strSQLA As String
    Dim db As Database
    Dim lstItem As listItem
    Set db = CurrentDb()
    strSQLA = "SELECT  * FROM tblTown where [TownID]=" & Me.ListView1.SelectedItem.Text
    Set rstA = db.OpenRecordset(strSQLA)
    With rstA
        Me.Text1.Value = !TownID
        Me.Text2.Value = !Town
        Me.Text3.Value = !YesNO
        'ADD MORE FIELDS HERE
    End With
End Sub

________________________________________
Zameer Abdulla
Visit Me
There is only one perfect child in this world. Every mother has it.

 

[beckwiga]

Yaahoooo! Yeah, that works great PHV. I changed it a little but it's doing what I want now. Thank you and Zameer again for your help.

 

[ZmrAbdulla]

I think PHV's suggestion is for a bound form and mine of course for an unbound form.

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together