主键值更改后，自动编号的列重新启动值是否从1开始? [英] Have autonumbered column restart value from 1 after primary key value changes?

问题描述

我有2个表，分别称为PO和PO_LI. PO中有一列称为PO#的列，它是主键，并且与PO_LI中的PO#列具有一对多关系(PO中的1).在PO_LI中，其他列为Line#，Description和LineAmount.

I have a 2 tables, call them PO and PO_LI. There is a column in PO called PO# which is a primary key, and is in a 1 to many relationship with the PO# column in PO_LI (the 1 being in PO). In PO_LI, the other columns are Line#, Description and LineAmount.

如何为每个新的PO#将数字重置为1?
仍然可以使用自动编号功能吗?
可以在ms-access gui中完成此操作，还是需要VBA代码?

How can I reset the number back to 1 for every new PO #?
Can this be done while still using autonumber?
Can this be done in the ms-access gui or is VBA code required?

推荐答案

您不能手动编辑自动编号"字段或更改其起始值.最好的选择是使用VBA来获取最大行号并递增它，如果使用的是新的PO，则从1开始.您可以将此VBA放在PO字段的AfterUpdate事件中.

You cannot manually edit an AutoNumber field or change its starting value. Your best bet is to maybe use VBA to get the max line number and increment it, and if you're using a new PO then you start at 1. You would put this VBA in the AfterUpdate event of the PO field.

您可以执行以下操作:

Dim db as Database
Dim rec as Recordset
Dim rec2 as Recordset
Dim MyVal as Integer

Set db = CurrentDB
Set rec = db.OpenRecordset("Select LineNum from MyTable where PO = '" & Me.txtPO & "' group by PO")

'If there are no records returned, start at 1.  Otherwise, increment.
If rec.EOF = true then
  MyVal = 1
else
  Set rec = db.OpenRecordset("Select Max(LineNum) from MyTable where PO = '" & Me.txtPO & "'")
  MyVal = rec(0) + 1
endif

MyVal现在是您要写入LineNum的数字.当然，您需要更改变量，以使其成为表单和表中的实际值，但是您应该明白这一点.

MyVal is now the number you will write to LineNum. Of course, you'll need to change the variables and such to be what's actually on your forms and in your tables, but you should get the idea.

这篇关于主键值更改后，自动编号的列重新启动值是否从1开始?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！