Move records up or down on continuous form 1

[netrusher]

I have a form (continuous) that is a page on another form. This form is used to type in parts and descriptions.
What I want to be able to do is move part # (records) up or down on the form. Does anyone have any code
or advice on how to accomplish this?

 

[MajP]

Are you wanting to create a manual sort order or do you to bring a specific record to the top?

 

[netrusher]

Well I would like to just move any record up or down one
record at a time.

 

[MajP]

Once moved do you need to save that order so next time the form opens in that order?

 

[netrusher]

Yes

 

[MajP]

Ok here is a rough idea. It works but there is probably a cleaner way.

1) in your table add a sort order field. I have a table of animals and my field is ("intAnimalSort")
2) When you open the form you populate the field with the records absolute position (0 to number of records)
3)Then as you move it up swap the sort order with the previous one.
4) currently I have the cmd button in the record, in the future I would clean this up so that you pick the record and just keep pushing the up arrow until it get where you want.
5)I have not done down yet

Private Sub cmdDown_Click()

End Sub

Private Sub cmdUp_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
  sngOldSort = Me.intAnimalSort
  MsgBox rs.AbsolutePosition
  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
  End If
  Me.Requery
End Sub

Private Sub Form_Load()
 Call setSortOrder
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

 

[MajP]

This looks a lot better.

1)same idea but I put a listbox on in the forms header using the same row sources as the form.
2)I put my up down buttons outside the listbox
3)I can select a record in the list box and move it up and down simulataneously moving it up and down in the form.
4)This would probably look nicer using a subform so that you can put the list box to the side of the continous subform.
[/code]
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)
'MsgBox sngOldSort
'MsgBox rs.AbsolutePosition
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
End If
Me.Requery
Me.lstSort.Requery
End Sub
[/code]

 

[netrusher]

Thanks MajP

I appreciate the help and will give this a try!!!