访问:重置自动编号的查询 [英] Access: A query that resets the autonumbering

问题描述

我的数据库是为滑雪比赛制作的.这个想法是你可以填写人们滑雪的次数，数据库会根据先设置时间的人、你的性别和年龄自动计算你获得的奖牌类型.我制作了一个表格，可以让它注册并提供所有结果.

我现在遇到的唯一问题是在与某人签约时，必须按年​​龄订购.我这样做了，但现在自动编号都搞砸了.

我想要的是我可以输入所有竞争对手的名字，然后我想要一个查询，我可以选择分配所有起始号码.即使号码已经分配好，我也想重置它并在最近有人加入时再次分配它们.我不想删除自动编号字段并重新创建它，因为大多数时候我自己不会使用它，但其他人会使用它，所以我想简单地按下分配它的按钮.

提前致谢！

例如我的数据库和比赛的样子

解决方案

自动编号字段仅用于识别记录.不多不少.

您需要的是一个Priority(或Rank)字段.

在显示记录的表单中，为该字段运行如下代码:

私有子Priority_AfterUpdate()Dim rst As DAO.RecordsetDim lngId as longDim lngPriorityNew As LongDim lngPriorityFix As Long' 保存记录.Me.Dirty = 错误'准备表格.DoCmd.Hourglass True我.重绘Me.Painting = 错误' 当前 ID 和优先级.lngId = 我！Id.ValuelngPriorityFix = Nz(Me!Priority.Value, 0)如果 lngPriorityFix <= 0 那么lngPriorityFix = 1我！Priority.Value = lngPriorityFixMe.Dirty = 错误万一' 重建优先级列表.设置 rst = Me.RecordsetClone首先移动虽然 rst.EOF = False如果 rst!Id.Value <>lngId 然后lngPriorityNew = lngPriorityNew + 1如果 lngPriorityNew = lngPriorityFix 那么' 将此记录移至下一个较低的优先级.lngPriorityNew = lngPriorityNew + 1万一如果 Nz(rst!Priority.Value, 0) = lngPriorityNew 那么' 该记录的优先级没有改变.别的' 分配新的优先级.第一编辑rst!Priority.Value = lngPriorityNew第一次更新万一万一第一次移动温德' 重新排序表单并重新定位记录.我再问设置 rst = Me.RecordsetClonerst.FindFirst "Id = " &lngId &"Me.Bookmark = rst.Bookmark'现在的形式.Me.Painting = 真DoCmd.Hourglass 错误设置 rst = 无结束子

只需为任何记录分配一个等级，记录将根据需要重新编号.

My database is made for skiing competitions. The idea is that you can fill in the times people ski, and the databse automatically calculates what kind of medal you earned based on someone who set the time first, your gender and your age. I have made a form that makes it able to sign up and give all the results.

The only problem I'm having now is when signing someone up, it must be ordered on age. I did that, but now the autonumbering is all messed up.

What I want is that I can put all the names of the competitors in, and after that I want to have a query which I can choose that assigns all of the starting numbers. Even when the numbers are allready assigned I want to reset it and assign them again if someone joins in lately. I don't want to have to delete the auto numbering field and make it again because most of the time I won't be using it myself but other people will, so I want a simple push on the button that assigns it.

Thanks in advance!

Example how my database and a competition looks like

解决方案

An Autonumber field is only for identifying records. No more no less.

What you need is a Priority (or Rank) field.

In your form where you display the records, run code like this for that field:

Private Sub Priority_AfterUpdate()

    Dim rst             As DAO.Recordset
    Dim lngId           As Long
    Dim lngPriorityNew  As Long
    Dim lngPriorityFix  As Long

    ' Save record.
    Me.Dirty = False

    ' Prepare form.
    DoCmd.Hourglass True
    Me.Repaint
    Me.Painting = False

    ' Current Id and priority.
    lngId = Me!Id.Value
    lngPriorityFix = Nz(Me!Priority.Value, 0)
    If lngPriorityFix <= 0 Then
        lngPriorityFix = 1
        Me!Priority.Value = lngPriorityFix
        Me.Dirty = False
    End If

    ' Rebuild priority list.
    Set rst = Me.RecordsetClone
    rst.MoveFirst
    While rst.EOF = False
        If rst!Id.Value <> lngId Then
            lngPriorityNew = lngPriorityNew + 1
            If lngPriorityNew = lngPriorityFix Then
                ' Move this record to next lower priority.
                lngPriorityNew = lngPriorityNew + 1
            End If
            If Nz(rst!Priority.Value, 0) = lngPriorityNew Then
                ' Priority hasn't changed for this record.
            Else
                ' Assign new priority.
                rst.Edit
                    rst!Priority.Value = lngPriorityNew
                rst.Update
            End If
        End If
        rst.MoveNext
    Wend

    ' Reorder form and relocate record.
    Me.Requery
    Set rst = Me.RecordsetClone
    rst.FindFirst "Id = " & lngId & ""
    Me.Bookmark = rst.Bookmark

    ' Present form.
    Me.Painting = True
    DoCmd.Hourglass False

    Set rst = Nothing

End Sub

Just assign a rank to any record, and records will be renumbered as and if needed.

这篇关于访问:重置自动编号的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！