如何在查询数据旁边添加序列号 [英] How to add sequential numbers next to data from query

问题描述

我有一个名为 tblFriends 的表:

I have a table named tblFriends:

tblFriends 是从查询 qryFriends 生成的.tblFriends 中的数据和记录数每天都在变化，但不会超过 30.

tblFriends is generated from query qryFriends. The data and number of records within tblFriends changes everyday but is never more than 30.

我想在每个名称旁边生成序列号，但这似乎非常困难.

I would like to generate sequential numbers next to each of the Names, but this seems to be extremely difficult.

我尝试过循环插入查询，如下所示:

I have tried looping insert queries as shown below:

strSQLaddSEQ = "ALTER TABLE tblFriends ADD SEQ Number;"
DoCmd.RunSQL strSQLaddSEQ

For SEQNum = 1 To 30

   strSqlSEQNum = "INSERT INTO Friends (SEQ) Values(" & SEQNum & ");"
   DoCmd.RunSQL strSqlSEQNum

Next SEQNum

这总是导致我的 SEQ 编号显示在数据下方，即使它是一个新创建的字段.

This always results in my SEQ numbers showing up below the data even though it's a newly created field.

如何简单地在我的数据旁边添加 1-30 之间的序列号?或者至少如何为我拥有的数据添加序列号?

How do I simply add sequence numbers from 1-30 next to my data? Or at the very least how do I add a sequence number for the data I have?

(也许?)有没有办法循环遍历每一行(逐行)复制到一个新表，然后在那里创建一个 SEQ 编号并重复 30 次?即使我需要逐行逐行做这个字段，我也不在乎.

(Maybe?)Is there a way to maybe loop through each row(row by row) copying to a new table then creating a SEQ number there and repeating 30 times? I don't care even if I need to do this field by field row by row.

例如，如果我需要去就可以:

For example, I'm ok if I need to simply go:

1 - Bikes
2 - Food
3 - Money
4 - Shoes
5 - Computers 
6 - Clothes
7 - Soda 

但是我如何像这样逐行进行并保持我从一开始就拥有的序列顺序?有循环过程吗?

But how do I go row by row like that and maintain sequence order I had from the start? Is there a looping process?

初始记录顺序很重要，需要维护.

The initial record sequence is important and needs to be maintained.

推荐答案

你可以有一个外部函数，你可以将表单作为参数传递.

You can have an external function which you pass the form as an argument.

实现起来非常简单.研究内嵌评论:

It's very simple to implement. Study the in-line comments:

' Creates and returns a sequential record number for records displayed
' in a form, even if no primary or unique key is present.
' For a new record, Null is returned until the record is saved.
'
' Implementation, typical:
'
'   Create a TextBox to display the record number.
'   Set the ControlSource of this to:
'
'       =RecordNumber([Form])
'
'   The returned number will equal the Current Record displayed in the
'   form's record navigator (bottom-left).
'   Optionally, specify another first number than 1, say, 0:
'
'       =RecordNumber([Form],0)
'
'   NB: For localised versions of Access, when entering the expression, type
'
'       =RecordNumber([LocalisedNameOfObjectForm])
'
'   for example:
'
'       =RecordNumber([Formular])
'
'   and press Enter. The expression will update to:
'
'       =RecordNumber([Form])
'
'   If the form can delete records, insert this code line in the
'   AfterDelConfirm event:
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           Me!RecordNumber.Requery
'       End Sub
'
'   If the form can add records, insert this code line in the
'   AfterInsert event:
'
'       Private Sub Form_AfterInsert()
'           Me!RecordNumber.Requery
'       End Sub
'
' Implementation, stand-alone:
'
'   Dim Number As Variant
'
'   Number = RecordNumber(Forms(IndexOfFormInFormsCollection))
'   ' or
'   Number = RecordNumber(Forms("NameOfSomeOpenForm"))
'
'
' 2018-09-14. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RecordNumber( _
    ByRef Form As Access.Form, _
    Optional ByVal FirstNumber As Long = 1) _
    As Variant

    ' Error code for "There is no current record."
    Const NoCurrentRecord   As Long = 3021
    
    Dim Records             As DAO.Recordset
    
    Dim Number              As Variant
    Dim Prompt              As String
    Dim Buttons             As VbMsgBoxStyle
    Dim Title               As String

    On Error GoTo Err_RecordNumber
    If Form Is Nothing Then
        ' No form object is passed.
        Number = Null
    ElseIf Form.Dirty = True Then
        ' No record number until the record is saved.
        Number = Null
    ElseIf Form.NewRecord = True Then
        ' No record number on a new record.
        Number = Null
    Else
        Set Records = Form.RecordsetClone
        Records.Bookmark = Form.Bookmark
        Number = FirstNumber + Records.AbsolutePosition
        Set Records = Nothing
    End If
    
Exit_RecordNumber:
    RecordNumber = Number
    Exit Function
    
Err_RecordNumber:
    Select Case Err.Number
        Case NoCurrentRecord
            ' Form is at new record, thus no Bookmark exists.
            ' Ignore and continue.
        Case Else
            ' Unexpected error.
            Prompt = "Error " & Err.Number & ": " & Err.Description
            Buttons = vbCritical + vbOKOnly
            Title = Form.Name
            MsgBox Prompt, Buttons, Title
    End Select
    
    ' Return Null for any error.
    Number = Null
    Resume Exit_RecordNumber

End Function

这是我的项目 VBA.RowNumbers 的一部分，您会在其中找到很多其他枚举行的方法，每种方法都有一些优点和缺点.

It is part of my project VBA.RowNumbers where you will find a lot of other methods to enumerate rows, each with some advanteges and disadvantages.

这篇关于如何在查询数据旁边添加序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！