用户信息和用户输入的值填充表格的最简单方法是什么？ [英] Easiest way for user information and user entered values to populate table?
问题描述
我已开始为用户创建Access 2007数据库，以审核在单独的ERP系统中输入的部件号设置信息，但需要有关最佳设计的建议，以允许自动填充某些值和其他值的用户输入。需要记录审计的部分信息，审计员姓名，审计日期，通过或失败，添加''关注或评论。
审计范围
每月执行和季度
多个客户对某些价值具有独特标准
每月审计数百到数千个零件
每季度审计数万个零件
执行审计的多个用户
审计的两个值的标准可以每周更改
（或者在某些情况下更常见）
手动审核由于两个值的标准变化频率，但考虑了将来自动化审核的方法。
首先处理季度审计功能因为这将带来最大的好处。
数据库有一个表季度审计从查询填充的字段链接到ERP系统中的表，并具有提取正确的零件和零件信息的标准。它还具有以下字段来记录审计信息 - 执行者，日期，通过或失败，以及添加关注或评论。
我有一个登录表单，我正在设置一个全局变量gbl_User =用户名。登录数据库时设置的值。
我有一个表单frm_QuarterlyAudit使用子表单frm_QuarterlyAudit_sub受到Quaterly Audit的约束表并显示在数据表视图中。我的想法是，执行审计的用户可以使用数据表过滤器按客户端进行过滤，然后通过其他字段来识别通过审计的所有部分，在Addl Concern（s）或Comments中输入关注点或注释。字段，选择通过通过在分组控件中标记单选按钮（一个单选按钮用于通过，一个用于失败，只有一个可以随时标记），然后保存表单。
保存表单时，需要自动填充以下字段并将其保存到季度审计表中以获取已过滤的记录（仅显示可见记录而不对其他记录进行更改）：
1）Performed By - 来自全球的用户名
变量" gbl_user"，登录时设置的变量值
2）日期 - 表格保存时的系统日期
3）通过或失败 - 保存到Quaterly Audit的价值
基于所选的单选按钮
4）Addl Concern（s）or Comments - 用户输入的文字
这个设计是否有效？我可以保存到数据表视图中过滤后显示的记录的表字段吗？你有更好的方法建议吗？
我提前感谢您的帮助。
I have started creating an Access 2007 database for users to audit part number setup information entered in separate ERP system, but need advice on best design to allow for auto-populating some of the values and user-entry for other values. Need to record part info audited, auditor name, audit date, pass or fail, add''l concerns or comments.
Scope of Auditing
Performed Monthly and Quarterly
Multiple clients with unique criteria for some values
Hundreds to thousands of parts audited monthly
Tens of thousands of parts audited quarterly
Multiple useres performing audits
Criteria for two of the values audited can change
weekly (or in a some cases more often)
Manual auditing due to frequency of changes to criteria on the two values, but considering ways to automate the auditing in the future.
Working on Quarterly Auditing functionality first because that will provide most benefit.
Database has a table "Quarterly Audit" with fields populated from query that links to tables in ERP system and has criteria to pull the correct parts and part info. It also has following fields to record auditing info - "Performed By", "Date", "Pass or Fail", and "Addl Concern(s) or Comments".
I have a login form in place and am setting a global variable "gbl_User" = username. Value set during login to the database.
I have a form "frm_QuarterlyAudit" with a subform "frm_QuarterlyAudit_sub" bound to the "Quaterly Audit" table and displayed in datasheet view. My thought was that the user performing the audit could use the datasheet filters to filter by client and then by other fields to identify all parts that pass the audit, enter concerns or comments in the "Addl Concern(s) or Comments" field, select "Pass" by marking radio button in grouped controls (one radio button for Pass and one for Fail and only one can be marked at any time), and then save the form.
When the form is saved need the following fields auto-populated and saved to Quarterly Audit table for filtered records (just visible records no changes made to other records):
1) "Performed By" - username from global
variable "gbl_user", variable value set during log in
2) "Date" - system date when form saved
3) "Pass or Fail" - value saved to "Quaterly Audit"
based on radio button selected
4) "Addl Concern(s) or Comments" - text entered by user
Will this design even work? Can I save to the table fields of just the records showing after filter in datasheet view? Do you have suggestion(s) for a better approach?
My thanks in advance for your help.
推荐答案
似乎对我来说，你已经知道需要做什么，主要是如何做到这一点。但是我不确定你是否知道一些VBA功能会让这个变得简单。
首先，我没有使用表单过滤器，所以我不是100％确定Recordstclone是否注意过滤器。我没有过滤表单，而是在用户选择过滤器选项时更改表单的Recordsource。可能是我的方法对于大型数据库记录集/表来说速度较慢，但我从未发现它存在问题。让我们暂时假设Recordsetclone关注过滤器。
您可以在主窗体上放置一个更新按钮，其中包含一个包含此代码的单击事件：
Seems to me you''ve got a good idea about what needs done and mostly how to do it. But I''m not sure you''re aware of a few VBA features that will make this simple.
First, I don''t use form filter very much, so I''m not 100% sure about whether a Recordstclone pays attention to filters or not. Instead of filtering a form I change the the Recordsource for the form when the user selects filter options. It could be that my method is slower for large database recordsets/tables, but I never see a problem with it. Let''s assume for a moment that Recordsetclone pays attention to filters.
You could place an update button on your main form with a click event that would contain this code:
@Jim
我很确定RecordsetClone会镜像Recordset确切地说。
至于使用带表格的过滤器，大多数记录源都可以更新以包含过滤，但生活很远，使用过滤器和<时更简单b>过滤属性（均由 DoCmd.OpenForm（）的 WhereCondition 参数自动设置）。一个包装：
@Jim
I''m pretty sure a RecordsetClone would mirror the Recordset exactly.
As for using Filters with Forms, it''s true that most Record Sources can be updated to include filtering, but life is far, far simpler when using the Filter and Filter On properties (both set automatically by the WhereCondition parameter of DoCmd.OpenForm()). A wrapper of :
@Jim我对VBA几乎一无所知。我在数据库中使用的大部分代码我已经在其他帖子中找到，或者已经在帖子的答案中提供，就像你在这里所做的那样。对此我非常感激。
当我单击按钮运行代码时，我收到以下错误编译错误：用户定义的类型未定义突出显示第1行。
@NeoPa我在宏中设置为子交换机上按钮的click事件，使用OpenForm操作打开表单。我没有做任何初始过滤，用户将进行过滤。我可以在宏中实现您的建议，还是需要使用DoCmd.OpenForm（）从使用宏更改为使用VBA代码？
@Jim I know next to nothing about VBA. The majority of the code I am using in the database I have found in other posts or has been provided in answers to post as you have done here. For which I am very grateful.
When I click the button to run the code I get the following error "Compile error: User-defined type not defined" with line 1 highlighted.
@NeoPa I am opening the form using the OpenForm action in a macro that is set to the click event of a button on a sub switchboard. I am not doing any initial filtering, the user will be doing the filtering. Can I implement your suggestion in the macro or do I need to change from using the macro to using VBA code using the DoCmd.OpenForm()?
这篇关于用户信息和用户输入的值填充表格的最简单方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
