Movenext/moveprevious

[kimtp]

I use a list view to pick out a record which fill data on a separate form. Then if I want to scroll through the database from the present record, I have two arrows to move forward or backwards.

The database is indexed on fldTitle. fldTitle is not unique to one record for two artists can be working together i.e. Getz & Mulligan if I want to show all works for a particular artist.

When I get to the first title of duplication, the move next button stops after showing both artists of the same title. When I move previous and get to the first title of duplication, the button skips the second record and moves to the next title.

Here is my code for both:

Private Sub MoveNext()
    sSql = "SELECT * FROM Artists ORDER BY fldTitle"
    If rs.State = adStateOpen Then rs.Close
    rs.Open sSql, cn, adOpenKeyset, adLockOptimistic, adCmdText
    Do Until rs.Fields("fldTitle").Value = frmSSTab.txtTitle.Text
        rs.MoveNext
    Loop
    rs.MoveNext
    If rs.EOF Then
        rs.MoveFirst
        If rs.BOF Then
            Exit Sub
        End If
    End If
End Sub 

Private Sub MovePrevious()
    sSql = "SELECT * FROM Artists ORDER BY fldTitle"
    If rs.State = adStateOpen Then rs.Close
    rs.Open sSql, cn, adOpenKeyset, adLockOptimistic, adCmdText
    Do Until rs.Fields("fldTitle").Value = frmSSTab.txtTitle.Text
        rs.MoveNext
    Loop
    rs.MovePrevious
    If rs.BOF Then
        rs.MoveLast
        If rs.EOF Then
            Exit Sub
        End If
    End If
End Sub

Any help would be great. Thanx.

 

[pkailas]

Let me see if I understand what you are doing. You first populate a ListView with all the records on a form.

Then when you select one of these out of the ListView, you then zoom to a different form where you populate the textboxes from the database based upon the record selected out of the listview?

If that assumption is correct, why not just have the Next and Back buttons move the selection on the Listview itself, which in turn would populate the form with the data you want?

 

[kimtp]

Thanx for the response. I do all of my editing in the text boxes, save the record and then want to move next or previous. How to move the selection in the listview?

Kim

 

[pkailas]

You'd have to get the index number over to the textbox form, or make a global variable. I usually make a hidden textbox, myself and set it's value on the ListView click event. Then you can send that value back.. Kinda like.

'to move to next record
Set frmListViewForm.ListView1.SelectedItem = frmListViewForm.ListView1.ListItems(X + 1)

'to go back
Set frmListViewForm.ListView1.SelectedItem = frmListViewForm.ListView1.ListItems(X - 1)

 

[kimtp]

Seems that if the index number is used which is the same as the record ID in my table, then the records would be ORDERED BY ID and not fldTitle. Is this correct?

 

[BiggerBrother]

The recordset should be ordered by fldTitle. The index only refers to the position within the recordset, and has no relation to the database field values.

BB

 

[kimtp]

Thanx for the response. I load the lvw using the record ID:

    Dim mItem As ListItem
    Dim sKey As String
    Dim sName As String
    lngID = nArtists.ID
    sName = nArtists.Lastname & ", " & nArtists.Firstname
    sTitle = nArtists.Title
    sLabel = nArtists.Label
    sCategory = nArtists.Category
    sKey = Format(lngID, "000")
    Set mItem = frmSSTab.lvwArtists.ListItems.Add(, "K" & sKey)
    mItem.Text = sKey
    mItem.SubItems(1) = sName
    mItem.SubItems(2) = sTitle
    mItem.SubItems(3) = sLabel
    mItem.SubItems(4) = sCategory
End Sub

where nArtists is where I pick up the data from Access through the class module cArtists. Is there another way around using the record ID?

Thanx.

 

[BiggerBrother]

Have you got the sort property of the listview set to false?

BB

 

[kimtp]

Yes, it is.

Kim