Moving Listbox Items Up/Down with a twist

[JoseO]

Hello!

Hopefully this will make sense - I'll do my best to explain:

I have a form which displays song lyrics in the SongLyrics memo control. Within this form there is a listbox named SelectedSongs. Below the latter listbox, I have a checkbox whereby when I check it, it places the title of the song lyrics I am viewing into the SelectedSongs checkbox.

Here is the code to the checkbox which works just fine:

If Me.Dirty Then Me.Dirty = False
  Set dbs = CurrentDb
  If PrintSong Then 'PrintSong is the name of the checkbox control.
    DoCmd.SetWarnings False
    Set rst = dbs.OpenRecordset("SelectedSongsT", dbOpenDynaset)
    If Not rst.EOF Then rst.MoveLast
    DoCmd.RunSQL "INSERT INTO SelectedSongsT ([PrintOrder], [SongTitle], [SongID]) VALUES (" & rst.RecordCount + 1 & "," & Chr(34) & Me.SongTitle & Chr(34) & "," & Me.SongID & ");"
  Else
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE FROM SelectedSongsT WHERE [SongTitle]=" & Chr(34) & Me.SongTitle & Chr(34) & " AND [SongId]=" & Me.SongID
    Set rst = dbs.OpenRecordset("SelectedSongsT", dbOpenDynaset)
    If Not rst.EOF Then
      Do
        rst.Edit
        rst![PrintOrder] = rst.AbsolutePosition + 1
        rst.Update
        rst.MoveNext
      Loop Until rst.EOF
    End If
  End If
  DoCmd.SetWarnings True
  Me.SelectedSongs.Requery

As you can see, it utilizes a table (SelectedSongT) to store the song titles which feeds the SelectedSongs listbox and when I uncheck the checkbox control it removes the song title(s).

Here's what I would like to do: Have command buttons that allow me to move the songs in the listbox up and down BUT this will also require that the songs also be re-arranged in the table which feeds the listbox - which is where I am having trouble.

I have tried several VBA approaches (now deleted) only to be met with an error from Access indicating - I'm paraphrasing, "This method requires the Row Source Type to be a 'Value List'"

Any guidance/help would be very much appreciated - thank you much!

José

 

[arnelgp]

ok put two command button for your up and down (cmdMoveUp and cmdMoveDown on this example).
you only need to update your PrintOrder field in your table:

Private Sub cmdMoveUp_Click()
Call moveUpDown(-1)
End Sub

Private Sub cmdMoveDown_Click()
Call moveUpDown(1)
End Sub

Private Sub moveUpDown(value As Integer)
Dim newpos As Long
Dim oldpos As Long
Dim ok As Boolean
Dim index As Long
If Me.SelectedSongs.ListIndex = -1 Then
    'do nothing nothing is selected
    
Else
    If value = -1 Then 'move up
        If Me.SelectedSongs.ListIndex <> 0 Then
            index = Me.SelectedSongs.ListIndex - 1
            oldpos = Me.SelectedSongs.column(0, Me.SelectedSongs.ListIndex)
            newpos = Me.SelectedSongs.column(0, index)
            ok = True
        End If
    Else 'move down
        If Me.SelectedSongs.ListIndex <> Me.SelectedSongs.ListCount - 1 Then
            index = Me.SelectedSongs.ListIndex + 1
            oldpos = Me.SelectedSongs.column(0, Me.SelectedSongs.ListIndex)
            newpos = Me.SelectedSongs.column(0, index)
            ok = True
        End If
    End If
    If ok Then
            DBEngine(0)(0).Execute "update SelectedSongsT set PrintOrder=9999 " & _
            "where PrintOrder = " & newpos & ";"
            DBEngine(0)(0).Execute "update SelectedSongsT set PrintOrder= " & newpos & " " & _
            "where PrintOrder = " & oldpos & ";"
            DBEngine(0)(0).Execute "update SelectedSongsT set PrintOrder= " & oldpos & " " & _
            "where PrintOrder = 9999" & ";"
        Me.SelectedSongs.Requery
        Me.SelectedSongs = Me.SelectedSongs.ItemData(index)
    End If
End If
End Sub

 

[JoseO]

Thank you! Thank you! Thank you! arnelgp

You have no idea how much this blesses me. I appreciate your insight and help. Works like a charm!!

Regards,
Jose