Control like the 'Tab Order' dialogue box?

[batteam]

I have an application that, among other things, lists courses for a given curriculum. The user wants to be able to move the order of how the courses are displayed for a chosen curriculum on a report and/or a list box.

My questions is, does MS Access 2007 have an Active X control similar to the 'Tab Order' dialogue box that is available when you want to change the tab order of controls on a form? It would be nice to be able to list the courses somewhere and click on a course and 'drag' it to the new order spot as can be done on a form's Tab Order box. Thanks for any help you can provide.

 

[MajP]

There are many ways to roll your own. Here are two ways to dynamically order a listbox, or query.

http://www.4shared.com/document/BAoBzuYb/SortList_V1_MajP.html

 

[batteam]

Thanks for the info MajP but my company's filter won't let me access the site listed in your response. Any other way you can help?

 

[dhookom]

I would expect that you would have a value stored in a table that would determine the order. Allow your users to set the value which would then be used to sort the records in your listbox and/or report.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

if you load a listbox as a valuelist then you can easily sort it. Add a listbox "lstSort" and up button "cmdUp", and a down button "cmdDown"

Private Sub Form_Load()
  Dim lst As Access.ListBox
  Dim str As String
  Dim rs As DAO.Recordset
  Set lst = Me.lstSort
  Set rs = CurrentDb.OpenRecordset("Products")
  Do While Not rs.EOF
    str = str & rs!productid & ";" & rs!productname & ";"
    rs.MoveNext
  Loop
  lst.RowSource = str
  lst.SetFocus
  lst.Selected(0) = True
End Sub
Private Sub cmdDown_Click()
  Call moveDown(Me.lstSort)
End Sub

Private Sub cmdup_Click()
  Call moveUp(Me.lstSort)
End Sub
Public Sub moveUp(lst As Access.ListBox)
  Dim ind As Long
  Dim val As String
  Dim col As Integer
  ind = lst.ListIndex
  If Not ind <= 0 Then
     For col = 0 To lst.ColumnCount - 1
       val = val & lst.Column(col, ind) & ";"
     Next col
     Call delItem(ind, lst)
     Call addItem(ind - 1, val, lst)
  End If
End Sub
Public Sub moveDown(lst As Access.ListBox)
  Dim ind As Long
  Dim val As String
  Dim col As Integer
  ind = lst.ListIndex
  If Not (ind = lst.ListCount - 1 Or ind < 0) Then
     For col = 0 To lst.ColumnCount - 1
      val = val & lst.Column(col, ind) & ";"
     Next col
     Call delItem(ind, lst)
     Call addItem(ind + 1, val, lst)
  End If
End Sub
Public Sub delItem(ind As Long, lst As Access.ListBox)
  lst.RemoveItem ind
End Sub
Public Sub addItem(ind As Long, val As String, lst As Access.ListBox)
  lst.addItem val, ind
  lst.Selected(ind) = True
End Sub

To dynamically order a table, do what Duane said and put in a sort order field.

This code allows you to order that field based on values in a listbox. Same visual interface but entirely different approach

Private Sub cmdListUp_Click()
  Dim rsClone As DAO.Recordset
  Dim rs As DAO.Recordset
  Dim sngNewSort As Integer
  Dim sngOldSort As Integer
  Set rsClone = Me.RecordsetClone
  Set rs = Me.Recordset
  If IsNull(Me.lstSort) Then Exit Sub
  sngOldSort = Me.lstSort.Column(1, lstSort.ListIndex)
  rs.FindFirst "intAnimalSort = " & sngOldSort
  If Not rs.AbsolutePosition <= 0 Then
    rs.Edit
    rs.Fields("intAnimalSort") = (rs.AbsolutePosition - 1)
    rs.Update
    rs.MovePrevious
    rs.Edit
    rs.Fields("intAnimalSort") = sngOldSort
    rs.Update
    Me.Requery
    Me.lstSort.Requery
  End If
End Sub

Private Sub cmdLstDown_Click()
  Dim rsClone As DAO.Recordset
  Dim rs As DAO.Recordset
  Dim sngNewSort As Integer
  Dim sngOldSort As Integer
  Set rsClone = Me.RecordsetClone
  Set rs = Me.Recordset
  If IsNull(Me.lstSort) Then Exit Sub
  sngOldSort = Me.lstSort.Column(1, lstSort.ListIndex)
  rs.FindFirst "intAnimalSort = " & sngOldSort
  If Not rs.AbsolutePosition >= rs.RecordCount - 1 Then
    rs.Edit
    rs.Fields("intAnimalSort") = (rs.AbsolutePosition + 1)
    rs.Update
    rs.MoveNext
    rs.Edit
    rs.Fields("intAnimalSort") = sngOldSort
    rs.Update
    Me.Requery
    Me.lstSort.Requery
  End If
End Sub
Private Sub Form_Load()
 Call setSortOrder
 Me.lstSort.SetFocus
 Me.lstSort.Selected(0) = True
End Sub
Public Sub setSortOrder()
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  Do While Not rs.EOF
      rs.Edit
      rs.Fields("intAnimalSort") = rs.AbsolutePosition
      rs.Update
    rs.MoveNext
  Loop
End Sub

 

[batteam]

Thanks lots for the tips. I have to digest this but I do now have a good idea on what has to be done.

Thanks again.

 

[MajP]

This may be also helpful. The easiest way to move things in a listbox is to create a value list. Then you can add and delete items. This code will take a listbox based on a query and turn it into a value list. This can save a lot of code writing.

Public Sub convertToValueList(theListBox As Access.ListBox)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim fldField As DAO.Field
  Dim strLstValue As String
  Dim intColCount As Integer
  Dim intColCounter As Integer
  Dim intRowCounter As Integer
  If theListBox.RowSourceType = "Table/Query" Then
    intColCount = theListBox.ColumnCount
    strSql = theListBox.RowSource
    theListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    theListBox.RowSourceType = "Value List"
    Do While Not rs.EOF
       For intColCounter = 0 To intColCount - 1
          strLstValue = strLstValue & """" & CStr(Nz(rs.Fields(intColCounter), " ")) & """;"
       Next intColCounter
       intRowCounter = intRowCounter + 1
       rs.MoveNext
       strLstValue = Left(strLstValue, Len(strLstValue) - 1)
       theListBox.AddItem (strLstValue)
       strLstValue = ""
    Loop
 End If
End Sub