需要帮助SQL语句将子表单数据复制到新表 [英] Need help with SQL statement for copying subform data to a new table

问题描述

我有一个食谱主表单，其中包含一个子表单，其中的数据源自名为TRecipeID的表，其中包含以下字段。

recipeID

RecipeVersion

productID

ProductVersion

WtPct（重量百分比数量）


主窗体有以下文本，组合框，和一个名为DateCodeID（字符串）的计算变量。

txt_recipeID

txtRecipeVer

txtBatch（食谱批量）

CboUOM（食谱数量单位）


只需单击一个命令按钮，我就会尝试将子表单中的当前数据添加到名为T_Inv_Transaction的表中。作为库存更新。 T_Inv_Transaction是指T_Inv_Transaction。 table有以下字段。

recipeID

RecipeVersion

Job_ID

UOM

数量

产品

产品


想法是更新库存表，其配方ID和版本号等于相应的数据在当前主窗体的文本框中（[txt_recipeID]和[txtRecipeVer]）。


我修改了其他人的类似帖子的代码，并提出了以下代码。但是，它仅在单击命令按钮时返回子窗体的第一行。我需要一些帮助来指出出了什么问题。


非常感谢任何帮助。

I have a recipe main form containing a sub form with data sourced from a table called “TRecipeID” with following fields.
recipeID
RecipeVersion
productID
ProductVersion
WtPct (weight percent as quantity)

The main form has following text, combo boxes, and a calculated variable called DateCodeID (a string).
txt_recipeID
txtRecipeVer
txtBatch (recipe batch quantity)
CboUOM (recipe quantity unit of measure)

With a click of a command button, I am trying to add current data in the subform to the table called "T_Inv_Transaction" as inventory update. The "T_Inv_Transaction" table has the following fields.
recipeID
RecipeVersion
Job_ID
UOM
QTY
ProdID
ProdV

The idea is to update the inventory table with recipe ID and version number equal to the corresponding data in text boxes in current main form ([txt_recipeID] and [txtRecipeVer]).

I adapted codes of similar posts from others and came up with following codes. However, it only return first row of the subform when the command button is clicked. I need some helps to point out what went wrong.

Any help is greatly appreciated.

展开 | 选择 | Wrap | 行号

推荐答案

将变量设置为's记录集的形式
将dim zFormsRS设置为DAO.Recordset
设置zFormsRS =我！zctrl_sfc_child.Form.Recordset


现在你可以查看是否有任何记录
IF zFormsRS .recordcount那么...


如果= 1或更高（你不需要一个完整的计数，只要看看是否至少有一个你可以设置启动SQL并执行 DB.Execute SQL，dbFailOnError 或通过循环 zFormsRS 值。


不确定如何直接将Form的记录集直接合并到表中 - 我通常只需要主键值并使用相关的值表根据需要。 ：）

Set a variable to the form''s recordset
Dim zFormsRS as DAO.Recordset
Set zFormsRS = Me!zctrl_sfc_child.Form.Recordset

Now you can check to see if there are any records
IF zFormsRS.recordcount THEN ...

and if =1 or greater (you don''t need a full count, just see if there is at least one) you can either set up the SQL and do a DB.Execute SQL, dbFailOnError or using the add-method to another recordset by looping thru the zFormsRS values.

Not sure how to directly merge a Form''s recordset directly to a table - I''ve usually only needed the primary key values and use those in the related tables as needed. :)

zmbd，


您能否解释并提供更多详情？我的vba级别无法理解你的方法。


谢谢，

Joe

zmbd,

Could you please explain and provide more details? My level of vba is not capable of understanding your method.

Thanks,
Joe

1 ）建议检查您的数据库是否已正确规范化和设计：数据库规范化和表结构

看起来确实如此;然而，这只是审查整体设计的一个很好的起点:)。

2） Joe Y，我想你可以这样做： - ）

让我们布置逻辑

+需要一个变量来保存SQL字符串

+需要一个变量来指向当前数据库

+需要一个变量来指向子表单记录集（ post＃2 ）

+设置子窗体记录集的变量

+检查子表单中存在任何记录

+如果至少存在一个（也发布＃2

设置当前数据库的变量（你的第19行）代码）

++（如果不使用INSERT / EXECUTE方法）

++移动到第一条记录（< a rel =nofollowhref =https://msdn.microsoft.com/en-us/library/bb243789(v=office.12).aspx\"target =_ blank> rs.movefirst - syntax ）在子表单记录集中

++ Do_Until循环（语法）逐步通过子表单的记录（rs.movenext）
每个记录的
构建你的SQL字符串（基本上是你的OP中的第15行;但是，参考表格'的记录来源字段见同上： 3 b ）

使用执行方法（第21行然而，你的代码有点混乱）直到你到达记录集的末尾（ rs.eof ）

+++使用插入/执行方法，您可以检查受影响的记录等...以查看插入是否成功

+将子表单的记录集变量设置为空

+将当前数据库的变量设置为空

3）我在编写实际代码时遇到了一些问题：
a）在OP中，您首先要引用主要形式。那么你指的是一个子形式。最后你发布的代码使用 ME。[****] 构造 - 你会验证 ME。[*** *] 来自子表单中的代码而不是主表单，并且您将命令按钮放在子表单而不是主表单上。
b ）帖子中的第8行到第11行是指表单上的控件。这些控件是否未绑定？或绑定到表单的记录源。使用记录集方法，我们将需要表单记录源的字段的实际名称。
c）如果我编写代码，那么您不会从经验中受益，也不会你真的明白代码是做什么的（即Line19然后Line21 通常，你需要一个或另一个不是两个:)）;但是，如果您尝试编写代码并回发，我们可以帮助您完善脚本，您将更好地了解正在完成的工作。


- 我住的地方有大风暴现在所以我可能在接下来的几天里没有力量:-（我们昨晚已经经历了一场大风暴 - 树木看起来像是通过绞肉机。悲伤。

1) It''s advisable to check that your database is properly normalized and designed: Database Normalization and Table Structures
It does appear to be; however, it''s just a good starting point to review the overall design :) .

2) Joe Y, I think you can do this :-)
Let''s lay out the logic
+ Need a variable to hold the SQL String
+ Need a variable to point to the current database
+ Need a variable to point to the subform recordset (post#2)
+ Set the variable for the subform recordset
+ Check for the existence of any records in the subform
+ if there is at least one then (Also post#2)
set the variable for the current database (line19 of your code)
++ (if not using the INSERT/EXECUTE method)
++ move to the first record(rs.movefirst - syntax) in the subform recordset
++ Do_Until loop (syntax) to step thru the subform''s records (rs.movenext)
at each record build your SQL string (basicly line 15 in your OP; however, referring to the form''s record source fields see ibid: 3b )
use the execute method (line 21 however, your code is a bit jumbled) until you hit the end of the recordset (rs.eof)
+++ using the insert/execute method you can check for effected records etc... to see if the insert was successful
+ set the variable for the subform''s recordset to nothing
+ set the variable for the current database to nothing

3) There a few issues I have with writing the actual code at this point:
a) In OP you first refer to the "main-form" then you refer to a "sub-form" and finally the code you''ve posted uses the ME.[****] construct - would you verify that ME.[****] is from code in the sub-form and not the main-form and that you are placing the command button on the sub-form and not the main form.
b) Lines 8 thru 11 in your post refer to the controls on the form. Are these controls "unbound" or bound to the record-source for the form. Using the recordset method, we will need the actual names of the fields of the record-source of the form.
c) if I write the code then you do not benefit from the experience nor will you really understand what it is that the code is doing (i.e. Line19 and then Line21 usually, you need one or the other not both :) ); however, if you attempt to write the code and post back we can help you to perfect the script and you will better understand what is being done.

- there are huge storms where I live right now so I may be without power for the next few days :-( we''ve already been thru one major storm last night - the trees look like they''ve been thru a meat-grinder. sad.

这篇关于需要帮助SQL语句将子表单数据复制到新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！