Move Record on Continuous Form

Question

Hi, I'm trying with no luck to move records on my continuous form. Here is my code. It does not work but also gives no errors?

Continuous Form

tblEmployees

EmployeeID PK

Position Number Integer

Private Sub btnUp_Click() 'Move Employee Up In Rotation.

If Me!Position <= 1 Then 

MsgBox "This employee cannot be moved up"

Exit Sub 

End If

Dim NewPos As Integer

NewPos = Me!Position - 1

Me!Position = NewPos

DoCmd.GoToRecord , , acPrevious

Me!Position = Me!Position + 1

Me.Requery

Me.Position.SetFocus

DoCmd.FindRecord NewPos

End Sub

Answer 1

Thanks, I will try and work thru it and adapt if possible. I will post back later.

I was unable to use your approach but many thanks for the help. I managed to just use the OnChangeEvent and other code.

Answer 2

Visit my library VBA.RowNumbers and go to paragraph 3. Priority Numbers.

It is about my function RowPriority which serves exactly this purpose. Usage is listed at top:

' Set the priority order of a record relative to the other records of a form. 

' 

' The table/query bound to the form must have an updatable numeric field for 

' storing the priority of the record. Default value of this should be Null. 

' 

' Requires: 

'   A numeric, primary key, typical an AutoNumber field. 

' 

' Usage: 

'   To be called from the AfterUpdate event of the Priority textbox: 

' 

'       Private Sub Priority_AfterUpdate() 

'           RowPriority Me.Priority 

'       End Sub 

' 

'   and after inserting or deleting records: 

' 

'       Private Sub Form_AfterDelConfirm(Status As Integer) 

'           RowPriority Me.Priority 

'       End Sub 

' 

'       Private Sub Form_AfterInsert() 

'           RowPriority Me.Priority 

'       End Sub 

' 

'   Optionally, if the control holding the primary key is not named Id: 

' 

'       Private Sub Priority_AfterUpdate() 

'           RowPriority Me.Priority, NameOfPrimaryKeyControl 

'       End Sub 

' 

'       Private Sub Form_AfterDelConfirm(Status As Integer) 

'           RowPriority Me.Priority, NameOfPrimaryKeyControl 

'       End Sub 

' 

'       Private Sub Form_AfterInsert() 

'           RowPriority Me.Priority, NameOfPrimaryKeyControl 

'       End Sub 

' 

' 2022-03-12. Gustav Brock, Cactus Data ApS, CPH. 

' 

Public Sub RowPriority( _ 

    ByRef TextBox As Access.TextBox, _ 

    Optional ByVal IdControlName As String = "Id") 

    ' Error codes. 

    ' This action is not supported in transactions. 

    Const NotSupported      As Long = 3246 

    Dim Form                As Access.Form 

    Dim Records             As DAO.Recordset 

    Dim RecordId            As Long 

    Dim NewPriority         As Long 

    Dim PriorityFix         As Long 

    Dim FieldName           As String 

    Dim IdFieldName         As String 

    Dim Prompt              As String 

    Dim Buttons             As VbMsgBoxStyle 

    Dim Title               As String 

    On Error GoTo Err_RowPriority 

    Set Form = TextBox.Parent 

    If Form.NewRecord Then 

        ' Will happen if the last record of the form is deleted. 

        Exit Sub 

    Else 

        ' Save record. 

        Form.Dirty = False 

    End If 

    ' Priority control can have any Name. 

    FieldName = TextBox.ControlSource 

    ' Id (primary key) control can have any name. 

    IdFieldName = Form.Controls(IdControlName).ControlSource 

    ' Prepare form. 

    DoCmd.Hourglass True 

    Form.Repaint 

    Form.Painting = False 

    ' Current Id and priority. 

    RecordId = Form.Controls(IdControlName).Value 

    PriorityFix = Nz(TextBox.Value, 0) 

    If PriorityFix <= 0 Then 

        PriorityFix = 1 

        TextBox.Value = PriorityFix 

        Form.Dirty = False 

    End If 

    ' Disable a filter. 

    ' If a filter is applied, only the filtered records 

    ' will be reordered, and duplicates might be created. 

    Form.FilterOn = False 

    ' Rebuild priority list. 

    Set Records = Form.RecordsetClone 

    Records.MoveFirst 

    While Not Records.EOF 

        If Records.Fields(IdFieldName).Value <> RecordId Then 

            NewPriority = NewPriority + 1 

            If NewPriority = PriorityFix Then 

                ' Move this record to next lower priority. 

                NewPriority = NewPriority + 1 

            End If 

            If Nz(Records.Fields(FieldName).Value, 0) = NewPriority Then 

                ' Priority hasn't changed for this record. 

            Else 

                ' Assign new priority. 

                Records.Edit 

                    Records.Fields(FieldName).Value = NewPriority 

                Records.Update 

            End If 

        End If 

        Records.MoveNext 

    Wend 

    ' Set default value for a new record. 

    TextBox.DefaultValue = NewPriority + 1 

    ' Reorder form and relocate record position. 

    ' Will fail if more than one record is pasted in. 

    Form.Requery 

    Set Records = Form.RecordsetClone 

    Records.FindFirst "[" & IdFieldName & "] = " & RecordId & "" 

    Form.Bookmark = Records.Bookmark 

PreExit_RowPriority: 

    ' Enable a filter. 

    Form.FilterOn = True 

    ' Present form. 

    Form.Painting = True 

    DoCmd.Hourglass False 

    Set Records = Nothing 

    Set Form = Nothing 

Exit_RowPriority: 

    Exit Sub 

Err_RowPriority: 

    Select Case Err.Number 

        Case NotSupported 

            ' Will happen if more than one record is pasted in. 

            Resume PreExit_RowPriority 

        Case Else 

            ' Unexpected error. 

            Prompt = "Error " & Err.Number & ": " & Err.Description 

            Buttons = vbCritical + vbOKOnly 

            Title = Form.Name 

            MsgBox Prompt, Buttons, Title 

            ' Restore form. 

            Form.Painting = True 

            DoCmd.Hourglass False 

            Resume Exit_RowPriority 

    End Select 

End Sub 

In the demo for download, you'll find the form Products which demonstrates the usage.

Set the form (and your form) to sort on the priority field.

Answer 3

Position is that Order Field. Are you saying;

Me!Position = Me!Position + 1 and NewPos = Me!Position - 1 or am I misunderstanding ?

Answer 4

Are you trying to reorder the rows on the Continuous form?

If so, I would add an order field to the table. then sort the form on that field. If you want to move a record up then update thge order numbers of the record above by 2 and subtract the current record by 1. then reorder.