Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Move a record up or down.

Posted on 2004-10-26
5
Medium Priority
?
313 Views
Last Modified: 2011-10-03
I need to move a record up or down in a form. I am trying to adapt this code below that is for a subform. The field for the sort that I want reordered is "Sequence" the table is "FolderLabels" and the query is "MoveRecord". Having problems with sytax removing refernces to subforms. The record selector has to move up or down with the record also.

Original post containing code: http://www.experts-exchange.com/Databases/MS_Access/Q_20645846.html?query=Move+Records+up+and+down+in+Access++&clearTAFilter=true

'Save the current sequence value
intSavePos = Me.sfrmPositions.Form.Sequence

'is it the first ? ==> no action
If intSavePos = 1 Then Exit Sub

'Switch positions. Put a zero as a placeholder in the current sequence position, move the selected record up one position
'and move that record down one

DoCmd.SetWarnings False

strSQL = "UPDATE positions SET positions.Sequence = 0 WHERE KOLID=" & Me.sfrmPositions.Form.KOLID & " AND sequence=" & intSavePos & ";"
DoCmd.RunSQL strSQL

strSQL = "UPDATE positions SET positions.Sequence = " & intSavePos & " WHERE KOLID=" & Me.sfrmPositions.Form.KOLID & " AND sequence=" & intSavePos - 1 & ";"
DoCmd.RunSQL strSQL

strSQL = "UPDATE positions SET positions.Sequence = " & intSavePos - 1 & " WHERE KOLID=" & Me.sfrmPositions.Form.KOLID & " AND sequence=0;"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

'Refresh  form to put everything in order or sequence again
Me.Refresh

'Set cursor on new location
Me.sfrmPositions.Form.SelTop = (intSavePos - 1)
0
Comment
Question by:tplowe56
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 12418977
Problem is, the above code is not very good, neither for a subform nor for a main form.
Here is an alternative, assuming only a very few things:

- There is a field called [Sequence], containing unique numbers
- In this example these are bytes (0 to 255 only), replace Byte with Long if needed :)
- The form is always sorted by Sequence
- No record has the Sequence number 0 (zero)
- There is a button somewhere called cmdUp :)

--------------------------------------

Private Sub cmdUp_Click()

    Const bytFree As Byte = 0   ' Must not exist in Sequence
    Dim bytSaved As Byte        ' Save current Seq. number
    Dim bytSwap As Byte         ' Previous Seq. number

    ' Some checks...
    If Me.Sequence = 1 Then Exit Sub
    If Me.CurrentRecord = 1 Then Exit Sub

    With Me.RecordsetClone
        ' synchronize records and save current seq. number
        .Bookmark = Me.Bookmark
        bytSaved = !Sequence
       
        ' set to free sequence number
        .Edit
        !Sequence = bytFree
        .Update
       
        ' Move to previous and store Seq. number
        .MovePrevious
        bytSwap = !Sequence
        ' replace with "current" Seq. number
        .Edit
        !Sequence = bytSaved
        .Update

        ' Return to current and assign Seq. number of previous record
        .MoveNext
        .Edit
        !Sequence = bytSwap
        .Update
       
    End With
   
    ' Requery the form and return to the moved record...
    Me.Requery
    Me.RecordsetClone.FindFirst "Sequence = " & bytSwap
    Me.Bookmark = Me.RecordsetClone.Bookmark
       
End Sub
0
 

Author Comment

by:tplowe56
ID: 12423182
Thank you very much your code works great. I had searched the net extensively and not found any code to accomplish this task, that worked.

Now I am trying to code the "Move Down" button, but inverting "MoveNext" and "MovePrevious" is not giving me the correct result. Can you suugsest the changes to accomplish this? Thanks.
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 12425905
Believe me, I saw this one coming :) Just wanted you to have a go at it...


Private Sub cmdDown_Click()

    Const bytFree As Byte = 0   ' Must not exist in Sequence
    Dim bytSaved As Byte        ' Save current Seq. number
    Dim bytSwap As Byte         ' Next Seq. number

    ' Some checks...
    If Me.NewRecord Then Exit Sub

    With Me.RecordsetClone
        ' synchronize records and save current seq. number
        .Bookmark = Me.Bookmark
        bytSaved = !Sequence
       
        ' is there a next record?
        .MoveNext
        If .EOF Then Exit Sub
        ' there is...
        .MovePrevious
       
        ' set to free sequence number
        .Edit
        !Sequence = bytFree
        .Update
       
        ' Move to next and store Seq. number
        .MoveNext
        bytSwap = !Sequence
        ' replace with "current" Seq. number
        .Edit
        !Sequence = bytSaved
        .Update

        ' Return to current and assign Seq. number of previous record
        .MovePrevious
        .Edit
        !Sequence = bytSwap
        .Update
       
    End With
   
    ' Requery the form and return to the moved record...
    Me.Requery
    Me.RecordsetClone.FindFirst "Sequence = " & bytSwap
    Me.Bookmark = Me.RecordsetClone.Bookmark
       
End Sub

Incidentally, the line:

    If Me.NewRecord Then Exit Sub

should also be added to the "some checks" section of cmdUp

Good luck
0
 
LVL 58

Expert Comment

by:harfang
ID: 12425999
While I'm at it...

After With Me.RecordsetClone, you could have an additional check:

    With Me.RecordsetClone

        ' still more error catching :)
        .FindFirst "Sequence = " & bytFree
        If Not .NoMatch Then
            MsbBox "Error: the sequence number " & bytFree & " should not be used" & vbCrLf _
                & "Please correct this problem"
            Exit Sub
        EndIf

       ' synchronize records and save current seq. number
       .....


Cheers!
0
 

Author Comment

by:tplowe56
ID: 12426143
Thanks again, the code works perfect. I had never used Access until 3 weeks ago, and I don't normally write code, so I'm in way over my head. I've learned a lot but this Moveup/Movedown thing was hard to find any code snippets that worked.

Thanks!!!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question