复制没有主键构造错误的记录

I have found the way to copy the record that I would like, but now I am having a violation of the Primary Key constraint. Here is what I am trying to do:

我找到了复制我想要的记录的方法,但现在我违反了主键约束。这是我想要做的:

We make a product that comes out of our maching into 2000 lbs bags and it is giving a number, e.g. 26273.

我们生产的产品从我们的机器加工成2000磅的袋子,它给出了一个数字,例如26273。

We store and sell it in those bags, but we also can sell it in smaller 50 lbs and 25 lbs bags.

我们将这些袋子存放和出售,但我们也可以用较小的50磅和25磅的袋子出售。

When we convert the bag from 2000 lbs to 25 lbs the product takes up 80 bags.

当我们将袋子从2000磅转换为25磅时,产品需要80袋。

Only 40 bags can be put onto a pallet, making the product number 26273 take up two pallets.

只有40个袋子可以放在托盘上,使产品编号26273占用两个托盘。

The problem we have is when we store the pallet we scan the barcode of the product and then scan the barcode of the warehouse location, ONE pallet per location, and only ONE location per pallet. If we have two pallets with the same number than we cannot store them in the warehouse.

我们遇到的问题是当我们存储托盘时,我们扫描产品的条形码,然后扫描仓库位置的条形码,每个位置一个托盘,每个托盘只有一个位置。如果我们有两个托盘的数量相同,我们就无法将它们存放在仓库中。

To solve this problem my bosses what the first pallet to be number 26273B1 and the second pallet to be 26273B2 so that the pallets still contain the original number but is slighlty different in order to store them.

为了解决这个问题,我的老板将第一个托盘编号为26273B1,将第二个托盘编号为26273B2,这样托盘仍然包含原始编号,但为了存储它们,它们之间略有不同。

When the product receives a number it also goes through several tests and that data is part of the record so both of the records still nedd to contain those test results.

当产品收到一个数字时,它也经过几次测试,并且该数据是记录的一部分,因此两个记录仍然需要包含这些测试结果。

When I try to copy the record and place the B2 onto the number I get a Primary Key Constraint ODBC Failure. I know why I am getting the error, and I don't what to dissable the constraint to allow duplicate records, but I still need to have the ability to create this new record when we convert to 25lbs bags.

当我尝试复制记录并将B2放到数字上时,我得到主键约束ODBC失败。我知道为什么我会收到错误,而且我不知道如何禁止约束以允许重复记录,但我仍然需要能够在转换为25磅包时创建这条新记录。

So my question: Is there any way to copy a record, slighty change the Primary Key while copying it, and still be able to save it without the Primary Key Constraint error occuring?

所以我的问题是:有没有办法复制一条记录,轻微更改主键,同时复制它,仍然可以保存它而不会发生主键约束错误?

NOTE: the database is in SQL with the interface front-end is in Access 2007.

注意:数据库在SQL中,接口前端在Access 2007中。

3 个解决方案

#1

Why not store the original 2000 bag with the PK "26273-00-0000". That "00-0000" suffix indicates the original Bag.

为什么不用PK“26273-00-0000”存放原始的2000袋。 “00-0000”后缀表示原始Bag。

For each subdivision into a smaller bag, "one-up" or increment the sequence that is suffixed at the end. You could use "00" for palletts, and "0000" for the bag-sequence number.

对于每个细分为一个较小的包,“一次性”或增加结尾后缀的序列。您可以使用“00”表示托盘,“0000”表示行李序列号。

Hence "26273-B1-0001" - thru "26273-B1-0040" indicates the Product id that went into Pallett - one - the first 40 bags.

因此,“26273-B1-0001” - 通过“26273-B1-0040”表示进入Pallett的产品ID - 一个 - 前40个袋子。

#2

I am not sure how you are wanting to copy a record i.e. in a form or programatically.

我不确定你是如何想要复制一个记录,即以表格或编程方式。

Note you can have a Primary Key based on more than 1 column so provided the 2 columns together are unique then you will not violate the constraint.

请注意,您可以拥有一个基于多于一列的主键,因此如果两列一起是唯一的,那么您将不会违反约束。

#3

Simply build what is called an append query. You can pull all of the old previous values and substitute a value for any of the columns to be something of your choice.

只需构建所谓的追加查询。您可以提取所有旧的先前值,并将任何列的值替换为您选择的值。

So, the code + sql would look like:

所以,代码+ sql看起来像:

Dim strSql     As String
Dim oldPK      As String
Dim newPK      As String

oldPK = "26273B1"
newPK = "26273B2"

strSql = "insert into tblPallets (PalletNumber, Description, StockNumber)" & _
         "select '" & newPK & "', Description, StockNumber" & _
         "from tblPallets where PallentNumber = '" & oldPK & "'"
CurrentDb.Execute strSql, dbFailOnError

#1