在多级结构上使用VBA循环的计算表字段（Access 2007） [英] Calculated table field looping with VBA on multi-level structure (Access 2007)
问题描述
大家好，
我是VBA的新手，我正在尝试将一些代码包含在现有的数据库中。我有一个带有数字值的简单字段（ Stato semplice ）的表格。列出的记录定义了具有字段 Lev。的结构 - 从1到n的级别。另一个字段（ STD ）给出关于记录类型的信息（std或空）。我希望1）定义行是否没有较低的级别 - 因此附加的字段P / N Semplice用s填充。通过第一个循环; 2）第二个循环用简单算法计算附加场（ Stato Composto ） - Stato Composto。等于Stato Semplice对于s行和等于0,4 *Stato Semplice+0,5（较低细节的总和/细节数）+0,1（STD行的总和/ STD行的数量）其他的
我有一个Excel宏（附加）在Excel工作表中完成整个工作，但我想转换为一些可以由Access宏驱动的代码。
感谢任何help
Hi all,
I''m new of VBA and I''m trying to include some code to an existing DB. I,ve a table with a simple field (Stato semplice) with a numeric value. Records listed define a structure with the field Lev. - levels from 1 to "n". Another field (STD) gives the information about the type of record ("std" or empty). I want 1)to define if a row has no lower levels - so an additional field P/N Semplice is filled with "s" by a first Loop; 2)a second loop calculate an additional field (Stato Composto) with a simple algorithm - "Stato Composto" is equal to "Stato Semplice" for "s" rows and equal to 0,4*"Stato Semplice"+0,5(sum of lower details/number of details)+0,1(sum of the STD rows/number of STD rows) for the others
I have an Excel macro (attached) that do the complete job in the Excel worksheet, but I want to convert to some code that can be driven by an Access macro.
Thanks for any help
推荐答案
I为了帮助理解，我试图重写这一点，但它仍然没有做到很有意义。当然不是我能做的任何事情。看看它是否对你有意义并告诉我哪里错了。
I''ve tried to rewrite this a bit to help understanding, but it still doesn''t make a lot of sense. Certainly not anything I can work to. See if it makes sense to you and tell me where it''s wrong.
Marmo：
大家好，
我是VBA的新手，我正在尝试为现有数据库添加一些代码。我有一个带有数字值的简单字段（ Stato semplice ）的表。列出的记录定义了具有字段 Lev。的结构 - 从1到n的级别。另一个字段（ STD ）提供有关记录类型的信息（std或空）。
我想要：
Marmo:
Hi all,
I''m new to VBA and I''m trying to add some code to an existing DB. I have a table with a simple field (Stato semplice) with a numeric value. Records listed define a structure with the field Lev. - levels from 1 to "n". Another field (STD) gives the information about the type of record ("std" or empty).
I want :
- 要定义行是否没有较低级别 - 所以附加字段P / N Semplice用s填充。通过第一个循环。
- 第二个循环用一个简单的算法计算一个额外的字段（ Stato Composto ）：
[Stato Composto]等于[Stato Semplice] fors行和等于0.4 * [Stato Semplice] + 0.5 *（细节的总和/细节数的总和）+ 0.1 *（STD行的总和/ STD行的数量）。
我有一个Excel宏（附加），可以在Excel工作表中完成整个工作，但我想转换为一些可以由Access宏驱动的代码。
感谢您的帮助
表的布局如下：
TableName = [ ??? ]
I have an Excel macro (attached) that do the complete job in the Excel worksheet, but I want to convert to some code that can be driven by an Access macro.
Thanks for any help
The layout of the table is as follows :
TableName = [???]
你好NeoPa，
the zi p文件包含带有宏（xlsm）的excel文件，该文件在工作表中执行操作。使用excel行和列没有问题。问题是将此方法迁移到DB内的VBA Access模块。表格的布局是您显示的。我有一个项目的分级结构（Lev.1是比Lev.2等更高的水平），每个都有3个基本信息（Level，Stato Semplice和STD）：第一个循环应该是定义是否项目是一个较低级别（如果它是一个细节 - 当然重置该字段）;第二循环根据P / N Semplice的值分配值。和STD。
我希望不要添加熵......（这有点复杂）。我报告Excel宏并且可能更清楚
Hi NeoPa,
the zip file contains the excel file with macro (xlsm) that perform the action in a worksheet. It works with no problem using the excel rows and columns. The problem is to migrate this approach to a VBA Access module inside a DB. The layout of the table is the one that You show. I have a jerarchical structure of items (Lev. 1 is the higher level, than Lev. 2 etc.)each having 3 basic information ("Level", "Stato Semplice" and "STD"): a first loop should define wether or not the item ia a lower level (if it''s a detail - of course resetting this field); a second loop assign a value depending on the values of "P/N Semplice" and "STD".
I hope not to have added entropy ... (it''s a little complex). I report the Excel macro and maybe is more clear
代码可见Marmo肯定更清晰，但不是这样很容易理解发生了什么。通常我会坚持你用文字解释它，但我认为你做得很好，所以我必须看看我能用代码做什么。如果你愿意，可以为我做点什么。请发布您拥有的整个Excel文件，因为我没有数据可供使用。如果有很多行，那么你可以删除一些，如果有必要，但我需要看看数据在Excel代码中出现的顺序（Access的布局很好，但Excel的列不使用名称）。如果你可以用XLS格式而不是XLSX保存它那么那会更好，但我想我可能会用2003阅读XLSX（我可以用于DOCX，所以我希望我也能用于XLSX）。
另一点：
It is certainly clearer with the code visible Marmo, but not so easy to understand what is going on. Normally I''d insist you explain it in words, but I think you''re doing a good job getting as far as you have with that so I''ll have to see what I can do with the code. Do something for me though if you would. Please post the whole of the Excel file that you have as I don''t have the data to work with. If there are very many lines then you can delete some if necessary, but I need to see which order the data appears in for Excel code (The layout is fine for Access, but Excel doesn''t use names for its columns). If you can save it in XLS format instead of XLSX then that would be better, but I think I can probably read XLSX with 2003 (I can for DOCX so I hope I can for XLSX too).
Another point :
这篇关于在多级结构上使用VBA循环的计算表字段（Access 2007）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
