Move records up or down on continuous form

[oxicottin]

Hello this is in reference to thread702-1459219

Anyways, I have my form and it moves my field up one like sugested in the post but my question is how can I get all the fields in the record to move with it? The record has a PK and I know I cant move that so can I move the fields along with the number? If so how....

Thanks,
Chad

Thanks,
SoggyCashew.....

 

[MajP]

I am a little confused. That code allows you to manually sort the record making it appear as if the record moves up and down in the form. It does not move a field value up or down among records. So please provide your code. So if implemented correctly I think that code does what you are asking. Please provide your implementation of the code.

 

[oxicottin]

Majp, if my table looks like this:

PK | JobStepNumber Field = number

1 | 1
2 | 2
3 | 3
4 | 4
5 | 5

And i use the code below it moves my JobStepNumber up in my table. I wanted to be able to move the other fields along with it. I made an example and uploaded it to MediaFire if you wanted to see what I was talking about.

Thanks,
SoggyCashew.....

 

[MichaelRed]

just curious ... why / what is the purpose of 'moving records' within a form?


MichaelRed

 

[oxicottin]

Michael, It is for doing visual work instructions which is steps you have to follow to do a job safely. When a job step is written their is several steps to follow and if in the future we needed to add a step lets say between step 4 and 5 I could add a new record and move it up to be step 4 without havering to do the whole VWI over.

Thanks,
SoggyCashew.....

 

[dhookom]

If you want a step between 4 and 5, create a step number of 4.5. Then requery the form. If you want to see all integer values then you will need to have an update query that identifies the rank based on the number and updates the field.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

That code was not complete. Here is more current code

Private Sub Form_Load()
  SetInitialSort "JobStepNumber"
End Sub

Private Sub cmdDown_Click()
  MoveSortDown "JobStepNumber"
End Sub

Private Sub cmdListUp_Click()
 moveSortUp "JobStepNumber"
End Sub

Public Sub SetInitialSort(SortField)
  'Your forms recordsource needs to sort the records by
  Dim rs As DAO.Recordset
  Set rs = Me.Recordset
  Do While Not rs.EOF
    rs.Edit
    rs.Fields(SortField) = rs.AbsolutePosition + 1
    rs.Update
    rs.MoveNext
  Loop
  rs.MoveFirst
End Sub

Public Sub moveSortUp(SortField)
  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 Not Me.NewRecord Then
       sngOldSort = Nz(Me.JobStepNumber, 0)
     End If
     'MsgBox "abs" & rs.AbsolutePosition & " old" & sngOldSort
  If Not rs.AbsolutePosition <= 0 And Not Me.NewRecord Then
    rs.Edit
    rs.Fields(SortField) = (rs.AbsolutePosition)
    rs.Update
    rs.MovePrevious
    rs.Edit
    rs.Fields(SortField) = sngOldSort
    rs.Update
  End If
  Me.Requery
End Sub

Public Sub MoveSortDown(SortField)
  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 Not Me.NewRecord Then
       sngOldSort = Nz(Me.JobStepNumber, 0)
     End If
     'MsgBox "abs" & rs.AbsolutePosition & " old" & sngOldSort
  If Not (rs.AbsolutePosition >= Me.Recordset.RecordCount - 1 Or rs.AbsolutePosition < 0) Then
    rs.Edit
    rs.Fields(SortField) = (rs.AbsolutePosition + 2)
    rs.Update
    rs.MoveNext
    rs.Edit
    rs.Fields(SortField) = sngOldSort
    rs.Update
  End If
  Me.Requery
End Sub

http://www.4shared.com/document/m6QiTIkK/MajP_Move_records_up_or_down_o.html

This will only work if the recordsource of the form is sorted on your sort field. See working demo.

 

[MajP]

Here is what I consider a much better user interface. I use a listbox to sort. Then you can quickly move up or down and it is more intuitive.

http://www.4shared.com/document/wrC5jTgl/MajPListSort.html

 

[oxicottin]

Majp, That is exactly what I was talking about..... Thank you so much!

Thanks,
SoggyCashew.....

 

[TheAceMan1]

How are ya oxicottin . . .

[blue]my question is how can I get all the fields in the record to move with it? [purple]The record has a PK and I know I cant move that[/purple] so can I move the fields along with the number? If so how....[/blue]

You have to keep the same PK just in case there's a depedant subform. You do however have a seperate field [blue]JobStepNumber[/blue] that you can sort by and use for tne transfer. In essence what you would be doing is swapping records (including [blue]JobStepNumber[/blue]). The steps are as follows (for the upkey ... with code in the forms [blue]OnKeyDown[/blue] event):
[ol][li]Put a question mark [purple]?[/purple] in the [blue]Tag[/blue] property of all the controls of interest.[/li]
[li]In the event mentioned above ... perform the following:
[ol a][li]Find the current [blue]JobStepNumber[/blue]and hold the previous [blue]JobStepNumber[/blue] or dis-allow movement if your on the 1st record.[/li]
[li]Swap records wih a do loop (including [blue]JobStepNumber[/blue]).[/li]
[li]Requery the form[/li]
[li]Find the record = the held [blue]JobStepNumber[/blue].[/li][/ol a][/li]
[li]For the down key just detect when your on the last line and perform the same in reverse.[/li][/ol]
To be sure ... this is all dependent on a query or SQL set as the [blue]RecordSource[/blue] of the form ... sorted by [blue]JobStepNumber[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Huh?

 

[oxicottin]

Thanks AceMan, I will try that on Monday....

Thanks,
SoggyCashew.....

 

[TheAceMan1]

oxicottin . . .

The post I gave is all wrong! All you have to do is swap [blue]JobStepNumber[/blue] of the current record with [blue]JobStepNumber[/blue] of the previous record and perform a requery and find record. Copy/paste the following to the forms [blue]On Key Down[/blue] event. Also ... bet sure to set the forms [blue]Key Preview[/blue] event to [blue]Yes[/blue]. The code triggers on the [blue]Up[/blue] arrow key:

[blue]   Dim rst As DAO.Recordset
   Dim idxCur As Long, idxPrev As Long
   
   
   If Shift = 0 And KeyCode = 38 Then
      Set rst = Me.RecordsetClone
      rst.FindFirst "[JobStepNumber]=" & Me!JobStepNumber
      
      If rst.AbsolutePosition > 0 Then
         idxCur = rst!JobStepNumber
         rst.MovePrevious
         idxPrev = rst!JobStepNumber
         
         rst.Edit
         rst!JobStepNumber = idxCur
         rst.Update
         rst.MoveNext
         
         rst.Edit
         rst!JobStepNumber = idxPrev
         rst.Update
         
         Me.Requery
         Me.Recordset.FindFirst "[JobStepNumber]=" & idxPrev + 1
         
         Set rst = Nothing
      End If
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

AceMan,
Does this work as written? Aren't you missing a line of code? I did not test. You make all your modification to the recordsetclone

Set rst = Me.RecordsetClone

But I do not see any code to set the forms recordset to the modified recordsetclone. I would think you would need something like
...
rst.Update
Set me.recordset = rst
Me.Requery
...
However, not sure if I see what you are trying to add. This seems like the same technique as the working solution already provided, just slightly modified.

 

[TheAceMan1]

MajP . . .

Yes ... code was tested. Works just fine.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Now I see what AceMan is doing. By keeping the focus on the changed record, it is much more intuitive. I modified the code to keep it generic so it can be used with any table that has a sort field. Also keeping the procedures seperate from the events it provides more flexibility for the interface.

form code

Private Sub Form_Load()
  SetInitialSort "JobStepNumber", Me
End Sub

Private Sub cmdDown_Click()
  MoveSortDown "JobStepNumber", Me
End Sub

Private Sub cmdListUp_Click()
 moveSortUp "JobStepNumber", Me
End Sub

Private Sub txtJobStep_KeyDown(KeyCode As Integer, Shift As Integer)
  'can do this for any field
  If Shift = 0 And KeyCode = 38 Then
     moveSortUp "JobStepNumber", Me
  ElseIf Shift = 0 And KeyCode = 40 Then
    MoveSortDown "JobStepNumber", Me
  End If
End Sub

form for a standard module

Public Sub SetInitialSort(SortField, frm)
  Dim rs As DAO.Recordset
  Set rs = frm.Recordset
  Do While Not rs.EOF
    rs.Edit
    rs.Fields(SortField) = rs.AbsolutePosition + 1
    rs.Update
    rs.MoveNext
  Loop
  rs.MoveFirst
End Sub

Public Sub moveSortUp(SortField, frm As Access.Form)
  Dim rsClone As DAO.Recordset
  Dim lngNewSort As Long
  Dim lngOldSort As Long
  Set rsClone = frm.RecordsetClone
  rsClone.AbsolutePosition = frm.Recordset.AbsolutePosition
  If Not (IsNull(rsClone.Fields(SortField)) Or rsClone.AbsolutePosition <= 0) Then
    lngOldSort = rsClone.Fields(SortField)
    lngNewSort = lngOldSort - 1
    rsClone.Edit
    rsClone.Fields(SortField) = lngNewSort
    rsClone.Update
    rsClone.MovePrevious
    rsClone.Edit
    rsClone.Fields(SortField) = lngOldSort
    rsClone.Update
  frm.Requery
  frm.Recordset.FindFirst SortField & " = " & lngNewSort
  End If
End Sub

Public Sub MoveSortDown(SortField, frm As Access.Form)
  Dim rsClone As DAO.Recordset
  Dim lngNewSort As Long
  Dim lngOldSort As Long
  Set rsClone = frm.RecordsetClone
  rsClone.AbsolutePosition = frm.Recordset.AbsolutePosition
  If Not (IsNull(rsClone.Fields(SortField)) Or rsClone.AbsolutePosition >= rsClone.RecordCount - 1) Then
    lngOldSort = rsClone.Fields(SortField)
    lngNewSort = lngOldSort + 1
    rsClone.Edit
    rsClone.Fields(SortField) = lngNewSort
    rsClone.Update
    rsClone.MoveNext
    rsClone.Edit
    rsClone.Fields(SortField) = lngOldSort
    rsClone.Update
  frm.Requery
  frm.Recordset.FindFirst SortField & " = " & lngNewSort
  End If
End Sub

 

[oxicottin]

Thanks again... I will have to try on wed! I'm off tomorrow and I don't have a backup of the full DB at home. I did however implement the arrow up/down and not the list box due to I just don't have the space needed for the list box and the other works just fine.

I have ran into another problem (different problem) and im not sure what happened? here is the thread on the subject.

http://www.tek-tips.com/viewthread.cfm?qid=1636459&page=1

Thanks again!

Thanks,
SoggyCashew.....