英文:
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_文章中有完整的文档:
英文:
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.
' 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
Full documentation in my article at Experts Exchange:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论