多记录复制交易...... [英] Multiple Record Copy Transaction...

问题描述

如果有可能，我需要一些关于如何执行以下操作的指示/帮助：


在我的访问数据库中，我有以下内容：


表：产品，分装，产品包

表，产品

每个产品有1 ID


桌子，子组件

每个产品由许多子组件组成，每个子组件都有自己的

单独的ID，每个通过ProductID链接


表，产品包

每个产品由许多产品包项目组成，每个项目都有自己的

单独的ID，每个通过ProductID链接


__________________________________________________ __

MainForm来源：产品（表）

子表格来源：子装配（表格），通过ProductID链接

链接表格来源：产品包装（表格），链接到MainForm通过

ProductID ）


第1阶段：

有一个创建弹出窗口的按钮：

创建新产品或复制现有产品？

这个位没问题。创建新选项很好


复制现有稍微困难：


首先会有一个小的弹出窗体列出所有现有的

产品。

然后用户将选择他们需要复制的产品。然后将

存储要复制的现有产品的ProductID。他们将被要求

给这个新产品一个名字，它将取代

复制记录中的一个。


1 /我需要从ID匹配

所选产品ID的产品中复制记录，并将其替换为下一个新ID，如何？


2 /我需要复制Sub-Assembly中的所有记录，其中ProductID

与初始选定产品的匹配，然后将这些记录的

产品ID替换为新的ProductID，同时还为每条记录创建了

的新记录ID，如何？

显然，一旦我成功完成上述第2阶段，下面的第3阶段

完全相同，仅用于不同的表:-)


3 /我需要从Product-Pack复制所有记录，其中ProductID

与初始选定产品的产品匹配，然后用新产品ID替换所有这些记录的

产品ID，同时

创建新产品每个记录的dID，怎么样？


如果你知道怎么做，我很确定这很容易....我不知道怎么回事

困惑。

我将非常感谢您提供的任何帮助。


谢谢

David Gordon

Hi,

I need some pointers/help on how to do the following if it possible:

In my access db, I have the following:

Tables: Products, Sub-Assembly, Product-Pack
Table, Products
Each Product has 1 ID

Table, Sub-Assembly
Each Product is made up of many Sub-Assemblies, each with their own
seperate ID, and each linked via ProductID

Table, Product-Pack
Each Product is made up of many Product-Pack items, each with their own
seperate ID, and each linked via ProductID

__________________________________________________ __
MainForm Source: Products (Table)
Sub-Form Source: Sub-Assembly (Table), linked via ProductID
Linked-Form Source: Product-Pack (Table), linked to MainForm via
ProductID)

Stage 1:
Have a button which creates a pop-up asking:
"Create New or Copy Existing Product?"
This bit is fine. Create New option is fine

Copy Existing is slightly harder:

First there will be a small pop-up form which lists all the existing
products.
The user will then select the product they need to copy. This then will
store the ProductID of the existing Product to copy. They will be asked
to give this New Product a Name, which will replace the one in the
copied record.

1/ I need to copy the record from Products where the ID matches the
selected ProductID...and replace it with the next new ID, How ?

2/ I need to copy ALL the records from Sub-Assembly where the ProductID
matches that of the initial selected product and then replace the
ProductID of those records with the New ProductID, whilst also creating
new recordIDs for each record, How ?
Obviously, once I have managed to do the above stage 2, stage 3 below
is exactly the same, just for a different table :-)

3/ I need to copy ALL the records from Product-Pack where the ProductID
matches that of the initial selected product and then replace the
ProductID of ALL those records with the New ProductID, whilst also
creating new recordIDs for each record,How ?

I''m sure this is easy if you know how....I do not and get very
confused.
I will seriously appreciate any help you can offer.

Thanks
David Gordon

推荐答案

您需要熟悉编写VBA代码才能实现此目的。


您可以放置​​一个命令按钮您的产品表格复制此

记录。该按钮将通过在

表单的RecordsetClone上使用AddNew来创建新产品。这使您可以获得新的ProductID（我假设它是
是一个自动编号字段。）然后，您可以在附加查询

语句中使用该值来创建相关记录。相关表格。


此示例显示如何复制发票，以及

子表单中的订单项：


Private Sub cmdDupe_Click（）

Dim strSql As String

Dim db As DAO.Database

Dim lngInvID As Long


设置db = DBEngine（0）（0）


如果Me.Dirty那么

Me.Dirty = False

结束如果

如果Me.NewRecord那么

MsgBox"选择要复制的记录。"

Else


''复制主记录

使用Me.RecordsetClone

.AddNew

！InvoiceDate =日期

！ClientID = Me.ClientID

''等其他领域。

。更新

.Bookmark = .LastModified

lngInvID =！InvoiceID


''复制相关记录。

如果Me.fInvoiceDetail.Form.RecordsetClone.RecordCount> 0然后

strSql =" INSERT INTO tInvoiceDetail（InvoiceID，Item，

Amount）" &安培; _

" SELECT" &安培; lngInvID& "作为NewInvoiceID，

tInvoiceDetail.Item，" &安培; _

" tInvoiceDetail.Amount FROM tInvoiceDetail" &安培; _

" WHERE（tInvoiceDetail.InvoiceID ="& Me.InvoiceID&

"）;"

db。执行strSql，dbFailOnError

否则

MsgBox"主记录重复，但没有相关的

记录。"

结束如果


''显示副本。

Me.Bookmark = .LastModified

结束

结束如果


设置db = Nothing

结束Sub

注意：如果你不熟悉SQL字符串更正你的表格，模拟

查询，更改为追加（追加查询菜单），切换到SQL视图

（查看菜单），并复制你的内容看到那里。


-

Allen Browne - 微软MVP。西澳大利亚州珀斯。

访问用户提示 - http：// allenbrowne.com/tips.html

回复群组，而不是mvps dot org的allenbrowne。


< da **** *****@scene-double.co.uk>在消息中写道

news：11 ********************** @ g47g2000cwa.googlegr oups.com ...

You will need to be comfortable with writing VBA code to achieve this.

You could place a command button on your Product form to "Duplicate this
record". The button will create the new product by using AddNew on the
form''s RecordsetClone. This lets you get the new ProductID (which I assume
is an AutoNumber field.) You can then use that value in an Append query
statement to create the related records in the related tables.

This example shows how to duplicate an Invoice, and the line items from the
subform:

Private Sub cmdDupe_Click()
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

''Duplicate the main record
With Me.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
''etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

''Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail.Item, " & _
"tInvoiceDetail.Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
");"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

''Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub
Note: If you are stuck getting the SQL string correct for your tables, mock
up a query, change to Append (Append on Query menu), switch to SQL View
(View menu), and copy what you see there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@scene-double.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

如果可能的话，我需要一些关于如何执行以下操作的指示/帮助：

在我的访问数据库中，我有以下内容：

表：产品，子装配，产品包装

表，产品
每个产品有1个ID

表，子装配
每个产品由许多子组件组成，每个子组件都有自己的单独ID，每个子组件通过ProductID链接

表，产品包
每个产品由许多产品组成-Pack项目，每个都有自己的单独ID，每个都通过ProductID链接

__________________________________________________ __
MainForm来源：产品（表）
子表格来源：子装配（表格），通过ProductID链接
链接表格来源：产品包装（表格），通过
产品ID链接到MainForm

第1阶段：有一个创建pop-u的按钮请问：
创建新产品或复制现有产品？
这一点很好。创建新选项很好

复制现有稍微困难：首先会有一个小弹出窗口列出所有现有产品。给这个新产品一个名称，它将取代
复制记录中的名称。

1 /我需要从产品中复制记录ID匹配
选定的ProductID ...并将其替换为下一个新ID，如何？

2 /我需要从Sub-Assembly中复制所有记录，其中ProductID
匹配初始选定产品的那些，然后用新产品ID替换那些记录的
ProductID，同时为每条记录创建
新记录ID，如何？

显然，一旦我成功完成了上述第2阶段，下面的第3阶段就完全相同了，只是针对不同的表:-)

3 /我需要复制产品中的所有记录-Pack，其中ProductID
与初始选定产品的匹配，然后用新产品ID替换所有这些记录的产品ID，同时还为每条记录创建新的记录ID，如何？ br />
我很确定这很容易如果你知道怎么做......我不会感到非常困惑。
我会非常感谢你提供的任何帮助。

谢谢

David Gordon

I need some pointers/help on how to do the following if it possible:

In my access db, I have the following:

Tables: Products, Sub-Assembly, Product-Pack
Table, Products
Each Product has 1 ID

Table, Sub-Assembly
Each Product is made up of many Sub-Assemblies, each with their own
seperate ID, and each linked via ProductID

Table, Product-Pack
Each Product is made up of many Product-Pack items, each with their own
seperate ID, and each linked via ProductID

__________________________________________________ __
MainForm Source: Products (Table)
Sub-Form Source: Sub-Assembly (Table), linked via ProductID
Linked-Form Source: Product-Pack (Table), linked to MainForm via
ProductID)

Stage 1:
Have a button which creates a pop-up asking:
"Create New or Copy Existing Product?"
This bit is fine. Create New option is fine

Copy Existing is slightly harder:

First there will be a small pop-up form which lists all the existing
products.
The user will then select the product they need to copy. This then will
store the ProductID of the existing Product to copy. They will be asked
to give this New Product a Name, which will replace the one in the
copied record.

1/ I need to copy the record from Products where the ID matches the
selected ProductID...and replace it with the next new ID, How ?

2/ I need to copy ALL the records from Sub-Assembly where the ProductID
matches that of the initial selected product and then replace the
ProductID of those records with the New ProductID, whilst also creating
new recordIDs for each record, How ?
Obviously, once I have managed to do the above stage 2, stage 3 below
is exactly the same, just for a different table :-)

3/ I need to copy ALL the records from Product-Pack where the ProductID
matches that of the initial selected product and then replace the
ProductID of ALL those records with the New ProductID, whilst also
creating new recordIDs for each record,How ?

I''m sure this is easy if you know how....I do not and get very
confused.
I will seriously appreciate any help you can offer.

Thanks
David Gordon

你好Allen，


非常感谢你的快速回复： - ）


你可能不得不忍受这个。

到目前为止，我已经有了我的原创产品MainForm。

On这个表格我现在有一个''复制''按钮，复制所选的

产品，然后让我输入新产品的新名称。我现在可以轻松捕获新的ProductId。显然现在这个新记录上的子表格是空的

。


我创建了一个测试表调用Sub-Assembly_Copy，只是为了玩。 br />
我创建了一个追加查询来选择Sub-Assembly中的所有记录

ProductID = MainForm ProductID，当运行时拉起来说2条记录

从Sub-Assembly然后将它们很好地转储到Sub-Assembly_Copy中。


问题。当附加所需的记录时，它们带有

相同的唯一记录ID？假设选择产品A的产品有10个子组件，每个子组件在子组件表中都有自己的ID。

我需要一个每条记录的新ID。它们应该都是新的

记录有新ID？然后我需要用新产品ID替换每个

那10个新记录中的ProductID ....然后我的任务是

完成...


你能提供的任何进一步帮助都会让我非常开心:-)


再次感谢

David Gordon

Allen Browne写道：

Hi Allen,

Thanks so much for your prompt reply :-)

You may have to bear with me on this one.
So far, i''ve got my original Products MainForm.
On this form I now have a ''Duplicate'' button, which copies the selected
product and then lets me enter a new name for the new product. I can
now easily capture the new ProductId. Obviously the sub-form is empty
on this new record for now.

I created a test table call Sub-Assembly_Copy, just to play with.
I created an append query to select all records from Sub-Assembly where
ProductID = MainForm ProductID, which when run pulls up say 2 records
from Sub-Assembly and then dumps them nicely into Sub-Assembly_Copy.

The Problem. When the required records are appended, they carry the
same unique record ids ?. Lets say there are 10 Sub-Assemblies for the
selected Product A, each one has it''s own id in the Sub-Assembly table.
I will require a new ID for each record. They should each be new
records with new ids? I then need to replace the ProductID in each of
those 10 new records with the new productID....and then my task is
done...

Any further help you can offer would make me very happy :-)

Thanks again
David Gordon

Allen Browne wrote:

你需要习惯于编写VBA代码来实现这一点。

你可以放置一个命令按钮您的产品表格为复制此
记录。该按钮将使用
表单的RecordsetClone上的AddNew创建新产品。这使您可以获得新的ProductID（我假设
是一个自动编号字段。）然后，您可以在附加查询
语句中使用该值来在相关表中创建相关记录。

这个例子展示了如何复制Invoice，以及
子表单中的行项目：

私有子cmdDupe_Click（）
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

设置db = DBEngine（0）（0）

如果Me.Dirty那么
我.Dirty = False
结束如果
如果Me.NewRecord那么
MsgBox选择要复制的记录。
其他

''复制主要记录
随着Me.RecordsetClone
.AddNew
！InvoiceDate = Date
！ClientID = Me.ClientID
''等其他领域。
。更新
.Bookmark = .LastModified
lngInvID =！InvoiceID

''复制相关记录。
如果Me.fInvoiceDetail.Form.RecordsetClone.RecordCount> 0然后
strSql =" INSERT INTO tInvoiceDetail（InvoiceID，Item，
Amount）" &安培; _
" SELECT" &安培; lngInvID& "作为NewInvoiceID，
tInvoiceDetail.Item， &安培; _
" tInvoiceDetail.Amount FROM tInvoiceDetail" &安培; _
  WHERE（tInvoiceDetail.InvoiceID ="& Me.InvoiceID&
"）;"
db.Execute strSql，dbFailOnError
其他 MsgBox主要记录重复，但没有相关的
记录。
结束如果

''显示副本。
Me.Bookmark =。 LastModified
结束
结束如果

设置db = Nothing
结束子

注意：如果你不能正确获取SQL字符串对于你的表，模拟
查询，更改为追加（追加查询菜单），切换到SQL视图
（查看菜单），并复制你在那里看到的内容。
-
Allen Browne - 微软MVP。西澳大利亚州珀斯。
访问用户提示 - http://allenbrowne.com/ tips.html
回复群组，而不是mvps dot org的allenbrowne。

< da ********* @ scene-double.co。 UK>在消息中写道
新闻：11 ********************** @ g47g2000cwa.googlegr oups.com ...

You will need to be comfortable with writing VBA code to achieve this.

You could place a command button on your Product form to "Duplicate this
record". The button will create the new product by using AddNew on the
form''s RecordsetClone. This lets you get the new ProductID (which I assume
is an AutoNumber field.) You can then use that value in an Append query
statement to create the related records in the related tables.

This example shows how to duplicate an Invoice, and the line items from the
subform:

Private Sub cmdDupe_Click()
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

''Duplicate the main record
With Me.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
''etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

''Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail.Item, " & _
"tInvoiceDetail.Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
");"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

''Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub
Note: If you are stuck getting the SQL string correct for your tables, mock
up a query, change to Append (Append on Query menu), switch to SQL View
(View menu), and copy what you see there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@scene-double.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

如果可能的话，我需要一些关于如何执行以下操作的指示/帮助：

在我的访问数据库中，我有以下内容：

表：产品，子装配，产品包装

表，产品
每个产品有1个ID

表，子装配
每个产品由以下部分组成许多子组件，每个都有自己的单独ID，每个子组件都通过ProductID链接

表，产品包
每个产品都由许多产品包组成，每个都有自己的单独ID，每个都通过ProductID链接

__________________________________________________ __
MainForm来源：产品（表）
子表格来源：分组合（表），通过ProductID链接
链接表格来源：产品包（表格），通过
产品ID链接到MainForm

第1阶段：
有一个按钮这会产生一个弹出式问题ing：
创建新产品或复制现有产品？
这一点没问题。创建新选项很好

复制现有稍微困难：首先会有一个小弹出窗口列出所有现有产品。给这个新产品一个名称，它将取代
复制记录中的名称。

1 /我需要从产品中复制记录ID匹配
选定的ProductID ...并将其替换为下一个新ID，如何？

2 /我需要从Sub-Assembly中复制所有记录，其中ProductID
匹配初始选定产品的那些，然后用新产品ID替换那些记录的
ProductID，同时为每条记录创建
新记录ID，如何？

显然，一旦我成功完成了上述第2阶段，下面的第3阶段就完全相同了，只是针对不同的表:-)

3 /我需要复制产品中的所有记录-Pack，其中ProductID
与初始选定产品的匹配，然后用新产品ID替换所有这些记录的产品ID，同时还为每条记录创建新的记录ID，如何？ br />
我很确定这很容易如果你知道怎么做......我不会感到非常困惑。
我会非常感谢你提供的任何帮助。

谢谢

David Gordon

I need some pointers/help on how to do the following if it possible:

In my access db, I have the following:

Tables: Products, Sub-Assembly, Product-Pack
Table, Products
Each Product has 1 ID

Table, Sub-Assembly
Each Product is made up of many Sub-Assemblies, each with their own
seperate ID, and each linked via ProductID

Table, Product-Pack
Each Product is made up of many Product-Pack items, each with their own
seperate ID, and each linked via ProductID

__________________________________________________ __
MainForm Source: Products (Table)
Sub-Form Source: Sub-Assembly (Table), linked via ProductID
Linked-Form Source: Product-Pack (Table), linked to MainForm via
ProductID)

Stage 1:
Have a button which creates a pop-up asking:
"Create New or Copy Existing Product?"
This bit is fine. Create New option is fine

Copy Existing is slightly harder:

First there will be a small pop-up form which lists all the existing
products.
The user will then select the product they need to copy. This then will
store the ProductID of the existing Product to copy. They will be asked
to give this New Product a Name, which will replace the one in the
copied record.

1/ I need to copy the record from Products where the ID matches the
selected ProductID...and replace it with the next new ID, How ?

2/ I need to copy ALL the records from Sub-Assembly where the ProductID
matches that of the initial selected product and then replace the
ProductID of those records with the New ProductID, whilst also creating
new recordIDs for each record, How ?
Obviously, once I have managed to do the above stage 2, stage 3 below
is exactly the same, just for a different table :-)

3/ I need to copy ALL the records from Product-Pack where the ProductID
matches that of the initial selected product and then replace the
ProductID of ALL those records with the New ProductID, whilst also
creating new recordIDs for each record,How ?

I''m sure this is easy if you know how....I do not and get very
confused.
I will seriously appreciate any help you can offer.

Thanks
David Gordon

David，我建议您复制当前记录的形式

以'RecordsetClone'的形式进入新记录。如果您希望新产品

名称为空白，则不要在

代码的AddNew块中指定任何内容。如果您希望它是产品43的副本，然后你可以在代码中指定

。


之后，代码获取新记录的主键值，并且

将其保存在变量lngInvID中。 （对于这个

变量，使用你想要的任何名称。）然后INSERT字符串包含该值，这样当你b
重复相关的子记录时，它们与

的新数字刚刚在操作的第一阶段创建。你可以看到

数字连接到strSql的位置。当你执行该字符串时，所有

10子组件都被复制（由WHERE子句确定，

匹配主窗体的主键），以及所以在相关表格中创建了10条记录。


希望您能够将该代码调整到您的特定表格和字段名称。


-

Allen Browne - 微软MVP。西澳大利亚州珀斯。

访问用户提示 - http：// allenbrowne.com/tips.html

回复群组，而不是mvps dot org的allenbrowne。


< da **** *****@scene-double.co.uk>在消息中写道

news：11 ********************* @ g49g2000cwa.googlegro ups.com ...

David, what I suggested was that you copy the current record in the form
into a new record in the form''s RecordsetClone. If you want the new product
name to be blank, then don''t assign it anything in the AddNew block in the
code. If you want it to be "Copy of product 43" then you can assign it that
in the code.

After that, the code gets the primary key value for the new record, and
saves it in the variable lngInvID. (Use any name you want for this
variable.) Then the INSERT string incorporates that value, so that when you
duplicate the related child records, they are related to the new number that
just got created in the first stage of the operation. You can see where that
number gets concatenated into the strSql. When you execute that string, all
10 subassemblies are copied (that''s determined by the WHERE clause, which
matches the primary key of the main form), and so 10 records are created in
the related table.

Hope you can adapt that code to your specific table and field names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@scene-double.co.uk> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

艾伦，

非常感谢你的快速回复:-)

你可能不得不忍受这个。
到目前为止，我有我的原创产品MainForm。
在这个表格上，我现在有一个''复制''按钮，复制所选的产品，然后让我输入新产品的新名称。我现在可以轻松捕获新的ProductId。显然现在这个新记录上的子表格是空的。

我创建了一个测试表调用Sub-Assembly_Copy，只是为了玩。
我创建了一个追加查询选择子装配中的所有记录，其中
ProductID = MainForm ProductID，当运行时从子装配中说出2条记录，然后将它们很好地转储到Sub-Assembly_Copy中。
我需要为每个记录提供一个新的ID。他们应该每个都是新的记录与新的ID？然后我需要使用新的productID替换那10个新记录中的每一个的ProductID ....然后我的任务就完成了......

任何进一步的帮助你可以提供让我很开心:-)
再次感谢

David Gordon


Allen Browne写道：

Hi Allen,

Thanks so much for your prompt reply :-)

You may have to bear with me on this one.
So far, i''ve got my original Products MainForm.
On this form I now have a ''Duplicate'' button, which copies the selected
product and then lets me enter a new name for the new product. I can
now easily capture the new ProductId. Obviously the sub-form is empty
on this new record for now.

I created a test table call Sub-Assembly_Copy, just to play with.
I created an append query to select all records from Sub-Assembly where
ProductID = MainForm ProductID, which when run pulls up say 2 records
from Sub-Assembly and then dumps them nicely into Sub-Assembly_Copy.

The Problem. When the required records are appended, they carry the
same unique record ids ?. Lets say there are 10 Sub-Assemblies for the
selected Product A, each one has it''s own id in the Sub-Assembly table.
I will require a new ID for each record. They should each be new
records with new ids? I then need to replace the ProductID in each of
those 10 new records with the new productID....and then my task is
done...

Any further help you can offer would make me very happy :-)

Thanks again
David Gordon

Allen Browne wrote:

您需要熟悉编写VBA代码才能实现此目的。

您可以在产品表单上放置一个命令按钮以复制此
记录。该按钮将使用
表单的RecordsetClone上的AddNew创建新产品。这使您可以获得新的ProductID（我假设
是一个自动编号字段。）然后，您可以在附加查询
语句中使用该值来在相关表中创建相关记录。

这个例子展示了如何复制Invoice，以及来自
子表单的行项目：

Private Sub cmdDupe_Click（）
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

设置db = DBEngine（0）（0）

如果我.Dirty然后
Me.Dirty = False
结束如果
如果Me.NewRecord那么
MsgBox选择要复制的记录。
其他
''复制主要记录
使用Me.RecordsetClone
.AddNew
！InvoiceDate = Date
！ClientID = Me.ClientID
''等对于其他领域。
。更新
.Bookmark = .LastModif ied
lngInvID =！InvoiceID

''复制相关记录。
如果Me.fInvoiceDetail.Form.RecordsetClone.RecordCount> 0然后
strSql =" INSERT INTO tInvoiceDetail（InvoiceID，Item，
Amount）" &安培; _
" SELECT" &安培; lngInvID& "作为NewInvoiceID，
tInvoiceDetail.Item， &安培; _
" tInvoiceDetail.Amount FROM tInvoiceDetail" &安培; _
  WHERE（tInvoiceDetail.InvoiceID ="& Me.InvoiceID&
"）;"
db.Execute strSql，dbFailOnError
其他 MsgBox主要记录重复，但没有相关的
记录。
结束如果

''显示副本。
Me.Bookmark =。 LastModified
结束
结束如果

设置db = Nothing
结束子

注意：如果你不能正确获取SQL字符串对于你的表，
模拟
查询，更改为追加（追加查询菜单），切换到SQL视图
（查看菜单），并复制你在那里看到的内容。

-
Allen Browne - 微软MVP。西澳大利亚州珀斯。
访问用户提示 - http://allenbrowne.com/ tips.html
回复群组，而不是mvps dot org的allenbrowne。

< da ********* @ scene-double.co。 UK>在消息中写道
新闻：11 ********************** @ g47g2000cwa.googlegr oups.com ...

You will need to be comfortable with writing VBA code to achieve this.

You could place a command button on your Product form to "Duplicate this
record". The button will create the new product by using AddNew on the
form''s RecordsetClone. This lets you get the new ProductID (which I
assume
is an AutoNumber field.) You can then use that value in an Append query
statement to create the related records in the related tables.

This example shows how to duplicate an Invoice, and the line items from
the
subform:

Private Sub cmdDupe_Click()
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

''Duplicate the main record
With Me.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
''etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

''Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail.Item, " & _
"tInvoiceDetail.Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
");"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

''Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub
Note: If you are stuck getting the SQL string correct for your tables,
mock
up a query, change to Append (Append on Query menu), switch to SQL View
(View menu), and copy what you see there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<da*********@scene-double.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

>
>如果可能的话，我需要一些关于如何做以下事项的指示/帮助：
>
>在我的访问数据库中，我有以下内容：
>
>表：产品，子装配，产品包装
>
>
>表，产品
>每个产品都有1个ID
>
>表，子装配
>每个产品都由许多子组件组成，每个子组件都有自己的
>单独的ID，每个通过ProductID链接
>
>表，产品包
>每个产品都由许多产品包装产品组成，每个产品都有自己的产品包装。单独的ID，每个通过ProductID链接
>
> __________________________________________________ __
> MainForm来源：产品（表）
>子表格来源：子装配（表格），通过ProductID链接
>链接表格来源：产品包（表格），通过
>链接到MainForm产品ID）
>
>第1阶段：
>有一个按钮，可以创建一个弹出窗口，询问：
> 创建新产品或复制现有产品？
>这一点很好。创建新选项很好
>
>复制现有稍微困难：
>
>首先会有一个小弹出窗体列出所有现有的
>产品。
>然后，用户将选择他们需要复制的产品。这将是
>存储要复制的现有产品的ProductID。他们会被问到
>给这个新产品一个名称，它将取代
>中的名称。复制记录。
>
> 1 /我需要从ID匹配的产品中复制记录
>选择了ProductID ...并将其替换为下一个新ID，如何？
>
> 2 /我需要从Sub-Assembly复制所有记录，其中ProductID
>匹配初始选定产品的那些，然后替换
>具有新产品ID的那些记录的ProductID，同时也创建了
>每条记录的新记录ID，如何？
>
>
>显然，一旦我设法完成上述第2阶段，下面的第3阶段
>是完全一样的，只是为了一个不同的表:-)
>
> 3 /我需要从产品包中复制所有记录，其中ProductID
>匹配初始选定产品的那些，然后替换
>具有新产品ID的所有那些记录的ProductID，同时也是
>为每条记录创建新的记录ID，如何？
>
>如果你知道怎么做，我很确定这很容易......我不知道怎么做
>困惑。
>我将非常感谢您提供的任何帮助。
>
>谢谢
>
>
> David Gordon

>
> I need some pointers/help on how to do the following if it possible:
>
> In my access db, I have the following:
>
> Tables: Products, Sub-Assembly, Product-Pack
>
>
> Table, Products
> Each Product has 1 ID
>
> Table, Sub-Assembly
> Each Product is made up of many Sub-Assemblies, each with their own
> seperate ID, and each linked via ProductID
>
> Table, Product-Pack
> Each Product is made up of many Product-Pack items, each with their own
> seperate ID, and each linked via ProductID
>
> __________________________________________________ __
> MainForm Source: Products (Table)
> Sub-Form Source: Sub-Assembly (Table), linked via ProductID
> Linked-Form Source: Product-Pack (Table), linked to MainForm via
> ProductID)
>
> Stage 1:
> Have a button which creates a pop-up asking:
> "Create New or Copy Existing Product?"
> This bit is fine. Create New option is fine
>
> Copy Existing is slightly harder:
>
> First there will be a small pop-up form which lists all the existing
> products.
> The user will then select the product they need to copy. This then will
> store the ProductID of the existing Product to copy. They will be asked
> to give this New Product a Name, which will replace the one in the
> copied record.
>
> 1/ I need to copy the record from Products where the ID matches the
> selected ProductID...and replace it with the next new ID, How ?
>
> 2/ I need to copy ALL the records from Sub-Assembly where the ProductID
> matches that of the initial selected product and then replace the
> ProductID of those records with the New ProductID, whilst also creating
> new recordIDs for each record, How ?
>
>
> Obviously, once I have managed to do the above stage 2, stage 3 below
> is exactly the same, just for a different table :-)
>
> 3/ I need to copy ALL the records from Product-Pack where the ProductID
> matches that of the initial selected product and then replace the
> ProductID of ALL those records with the New ProductID, whilst also
> creating new recordIDs for each record,How ?
>
> I''m sure this is easy if you know how....I do not and get very
> confused.
> I will seriously appreciate any help you can offer.
>
> Thanks
>
>
> David Gordon

这篇关于多记录复制交易......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！