如何在SQL中使用MS Access作为前端重新排列ROW_NUMBER()项?

huangapple go评论74阅读模式
英文:

How to rearrange ROW_NUMBER() items in SQL using MS Access as a front end?

问题

我有这个表在Microsoft SQL Server

OrderDetailID (PK IDENTTIY) OrderID (FK tblOrders)
1 1
2 1
3 1
4 2
5 2

然后我使用ROW_NUMBER()函数为每个订单显示一个唯一的项目编号,像这样:

SELECT ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderDetailID) AS ItemNo, OrderDetailID, OrderID FROM tblOrders

输出结果是:

ItemNo OrderDetailID (PK IDENTTIY) OrderID (FK tblOrders)
1 1 1
2 2 1
3 3 1
1 4 2
2 5 2

我希望在MS Access中的用户能够重新排列ItemNo。例如,用户想要将OrderID 1中的ItemNo 2向上移动一个位置。这意味着这个项目将变成ItemNo 1,原来的ItemNo 1将变成ItemNo 2。

我应该如何实现这个目标?是在SQL端还是前端端更好?

英文:

I have this table in Microsoft SQL Server:

OrderDetailID (PK IDENTTIY) OrderID (FK tblOrders)
1 1
2 1
3 1
4 2
5 2

Then I use the ROW_NUMBER() function to display a unique item number for each order, like this:

SELECT ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderDetailID) AS ItemNo, OrderDetailID, OrderID FROM tblOrders

The output is:

ItemNo OrderDetailID (PK IDENTTIY) OrderID (FK tblOrders)
1 1 1
2 2 1
3 3 1
1 4 2
2 5 2

I want users in MS Access to be able to rearrange the ItemNo. For example, a user wants to move ItemNo 2 within OrderID 1 one place up. Meaning, this item will become ItemNo 1, and the original ItemNo 1 will become ItemNo 2.

How do I achieve this? Is it better to do on the SQL side or the front end side?

答案1

得分: 1

这可以通过在我的GitHub存储库中找到的函数_RowPriority_来完成:VBA.RowNumbers

请注意,其中包含一个在Access中包含工作演示的zip文件的演示文件夹。

' 设置记录相对于表单中其他记录的优先级顺序。
'
' 表单绑定的表/查询必须具有一个可更新的数字字段
' 用于存储记录的优先级。此字段的默认值应为 Null。
'
' 需要:
'   数字,主键,通常是一个AutoNumber字段。
'
' 用法:
'   从优先级文本框的AfterUpdate事件中调用:
'
'       Private Sub Priority_AfterUpdate()
'           RowPriority Me.Priority
'       End Sub
'
'   在插入或删除记录之后:
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           RowPriority Me.Priority
'       End Sub
'
'       Private Sub Form_AfterInsert()
'           RowPriority Me.Priority
'       End Sub
'
'   可选地,如果主键的控件不是命名为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")
    
    ' 错误代码。
    ' 此操作在事务中不受支持。
    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
        ' 如果表单的最后一条记录被删除,则会发生这种情况。
        Exit Sub
    Else
        ' 保存记录。
        Form.Dirty = False
    End If
    
    ' 优先级控件可以有任何名称。
    FieldName = TextBox.ControlSource
    ' Id(主键)控件可以有任何名称。
    IdFieldName = Form.Controls(IdControlName).ControlSource
    
    ' 准备表单。
    DoCmd.Hourglass True
    Form.Repaint
    Form.Painting = False
    
    ' 当前 Id 和优先级。
    RecordId = Form.Controls(IdControlName).Value
    PriorityFix = Nz(TextBox.Value, 0)
    If PriorityFix <= 0 Then
        PriorityFix = 1
        TextBox.Value = PriorityFix
        Form.Dirty = False
    End If
    
    ' 禁用过滤器。
    ' 如果应用了过滤器,那么只有经过过滤的记录
    ' 将被重新排序,并且可能会创建重复的记录。
    Form.FilterOn = False
    
    ' 重建优先级列表。
    Set Records = Form.RecordsetClone
    Records.MoveFirst
    While Not Records.EOF
        If Records.Fields(IdFieldName).Value <> RecordId Then
            NewPriority = NewPriority + 1
            If NewPriority = PriorityFix Then
                ' 将此记录移至下一个较低的优先级。
                NewPriority = NewPriority + 1
            End If
            If Nz(Records.Fields(FieldName).Value, 0) = NewPriority Then
                ' 此记录的优先级未更改。
            Else
                ' 分配新的优先级。
                Records.Edit
                    Records.Fields(FieldName).Value = NewPriority
                Records.Update
            End If
        End If
        Records.MoveNext
    Wend
    
    ' 为新记录设置默认值。
    TextBox.DefaultValue = NewPriority + 1
    
    ' 重新排序表单并重新定位记录位置。
    ' 如果粘贴了多条记录,则将失败。
    Form.Requery
    Set Records = Form.RecordsetClone
    Records.FindFirst "[" & IdFieldName & "] = " & RecordId & ""
    Form.Bookmark = Records.Bookmark
   
PreExit_RowPriority:
    ' 启用过滤器。
    Form.FilterOn = True
    ' 显示表单。
    Form.Painting = True
    DoCmd.Hourglass False
    
    Set Records = Nothing
    Set Form = Nothing
    
Exit_RowPriority:
    Exit Sub
    
Err_RowPriority:
    Select Case Err.Number
        Case NotSupported
            ' 如果粘贴了多条记录,则会发生这种情况。
            Resume PreExit_RowPriority
        Case Else
            ' 意外错误。
            Prompt = "错误 " & Err.Number & ": " & Err.Description
            Buttons = vbCritical + vbOKOnly
            Title = Form.Name
            MsgBox Prompt, Buttons, Title
            
            ' 恢复表单。
            Form.Painting = True
            DoCmd.Hourglass False
            Resume Exit_RowPriority
    End Select
    
End Sub

在我的_Experts Exchange_文章中有完整的文档:

Microsoft Access中的顺序行

英文:

This can be done with the function RowPriority found at my repository at GitHub: VBA.RowNumbers.

Notice the demo folder with a zip containing a working demo in Access.

&#39; Set the priority order of a record relative to the other records of a form.
&#39;
&#39; The table/query bound to the form must have an updatable numeric field for
&#39; storing the priority of the record. Default value of this should be Null.
&#39;
&#39; Requires:
&#39;   A numeric, primary key, typical an AutoNumber field.
&#39;
&#39; Usage:
&#39;   To be called from the AfterUpdate event of the Priority textbox:
&#39;
&#39;       Private Sub Priority_AfterUpdate()
&#39;           RowPriority Me.Priority
&#39;       End Sub
&#39;
&#39;   and after inserting or deleting records:
&#39;
&#39;       Private Sub Form_AfterDelConfirm(Status As Integer)
&#39;           RowPriority Me.Priority
&#39;       End Sub
&#39;
&#39;       Private Sub Form_AfterInsert()
&#39;           RowPriority Me.Priority
&#39;       End Sub
&#39;
&#39;   Optionally, if the control holding the primary key is not named Id:
&#39;
&#39;       Private Sub Priority_AfterUpdate()
&#39;           RowPriority Me.Priority, NameOfPrimaryKeyControl
&#39;       End Sub
&#39;
&#39;       Private Sub Form_AfterDelConfirm(Status As Integer)
&#39;           RowPriority Me.Priority, NameOfPrimaryKeyControl
&#39;       End Sub
&#39;
&#39;       Private Sub Form_AfterInsert()
&#39;           RowPriority Me.Priority, NameOfPrimaryKeyControl
&#39;       End Sub
&#39;
&#39; 2022-03-12. Gustav Brock, Cactus Data ApS, CPH.
&#39;
Public Sub RowPriority( _
    ByRef TextBox As Access.TextBox, _
    Optional ByVal IdControlName As String = &quot;Id&quot;)
    
    &#39; Error codes.
    &#39; 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
        &#39; Will happen if the last record of the form is deleted.
        Exit Sub
    Else
        &#39; Save record.
        Form.Dirty = False
    End If
    
    &#39; Priority control can have any Name.
    FieldName = TextBox.ControlSource
    &#39; Id (primary key) control can have any name.
    IdFieldName = Form.Controls(IdControlName).ControlSource
    
    &#39; Prepare form.
    DoCmd.Hourglass True
    Form.Repaint
    Form.Painting = False
    
    &#39; Current Id and priority.
    RecordId = Form.Controls(IdControlName).Value
    PriorityFix = Nz(TextBox.Value, 0)
    If PriorityFix &lt;= 0 Then
        PriorityFix = 1
        TextBox.Value = PriorityFix
        Form.Dirty = False
    End If
    
    &#39; Disable a filter.
    &#39; If a filter is applied, only the filtered records
    &#39; will be reordered, and duplicates might be created.
    Form.FilterOn = False
    
    &#39; Rebuild priority list.
    Set Records = Form.RecordsetClone
    Records.MoveFirst
    While Not Records.EOF
        If Records.Fields(IdFieldName).Value &lt;&gt; RecordId Then
            NewPriority = NewPriority + 1
            If NewPriority = PriorityFix Then
                &#39; Move this record to next lower priority.
                NewPriority = NewPriority + 1
            End If
            If Nz(Records.Fields(FieldName).Value, 0) = NewPriority Then
                &#39; Priority hasn&#39;t changed for this record.
            Else
                &#39; Assign new priority.
                Records.Edit
                    Records.Fields(FieldName).Value = NewPriority
                Records.Update
            End If
        End If
        Records.MoveNext
    Wend
    
    &#39; Set default value for a new record.
    TextBox.DefaultValue = NewPriority + 1
    
    &#39; Reorder form and relocate record position.
    &#39; Will fail if more than one record is pasted in.
    Form.Requery
    Set Records = Form.RecordsetClone
    Records.FindFirst &quot;[&quot; &amp; IdFieldName &amp; &quot;] = &quot; &amp; RecordId &amp; &quot;&quot;
    Form.Bookmark = Records.Bookmark
   
PreExit_RowPriority:
    &#39; Enable a filter.
    Form.FilterOn = True
    &#39; 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
            &#39; Will happen if more than one record is pasted in.
            Resume PreExit_RowPriority
        Case Else
            &#39; Unexpected error.
            Prompt = &quot;Error &quot; &amp; Err.Number &amp; &quot;: &quot; &amp; Err.Description
            Buttons = vbCritical + vbOKOnly
            Title = Form.Name
            MsgBox Prompt, Buttons, Title
            
            &#39; Restore form.
            Form.Painting = True
            DoCmd.Hourglass False
            Resume Exit_RowPriority
    End Select
    
End Sub

Full documentation in my article at Experts Exchange:

Sequential Rows in Microsoft Access

huangapple
  • 本文由 发表于 2023年6月13日 06:50:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/76460752.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定