重新查询导致重复的表条目 [英] Requery causing duplicate table entry

问题描述

我正在为医院部门创建一个计划应用程序，指出他们何时超过了特定班次所需的最低人员。我有一个跟踪计数的表和另一个包含基于特定班次和角色的最小值的表。例如，我知道凌晨2点需要的工作人员的确切人数，我有一名工作人员从晚上11点到早上7点工作，所以我需要表明这个人将在凌晨2点完成所需的一个插槽。如本例所示，我还需要将工作人员计入不同的一天，因此我必须创建多天的计数（如计划日期之前一天或计划日期之后的一天）。我需要提供这些信息多天。当我创建初始日的信息时，我开始得到非描述性的Microsoft Access遇到问题并需要关闭。对于给您带来的不便，我们深表歉意。我在代码中嵌入了一个requery命令，它在第一天就正常运行。当我尝试在后续日期输入信息时，我收到一个错误，即requery命令在数据库中创建了一个重复的条目。由于此代码找到角色和班次的当前条目，然后在计数中添加一个，我不明白它是如何创建重复值的。这是模块的vb代码：

展开 | 选择 | Wrap | 行号

解决方案

虽然我花一点时间来消化你的代码请执行以下步骤：


[Z {编辑}您使用的是什么版本的Access？从第11行开始，您似乎使用的旧版本之一与2003年的单行dim（muli varible）类型相同。气馁，2007/2010年将导致你的问题。您应该返回并更改任何类似的内容：
Dim FinalLoop，LoopCount，TempAMG As Integer

to
Dim FinalLoop As Integer，LoopCount As Integer，TempAMG As Integer

这样当你升级时就不会遇到类型转换问题。{/ edit}]



请转到MS Access中的vba编辑器

然后转到功能区/工具栏：>工具：>选项

编辑器选项卡：

取消选中自动语法检查所有这一切都是添加一个恼人的弹出窗口，说...你傻了......点击确定......当你错误的代码时，线路已经变红了。


选中标记要求变量声明 （这将在每个新代码模块的顶部放置选项explicate ）

选中所有剩余选项。


我喜欢自动缩进4;但是，作为个人喜好，我不会少于3或超过5。

其余的东西可以保留为默认值。


现在通过所有表单模块，标准模块和类模块，确保以下两行是每个模块中的前两行

展开 < span class =codeDivider> | 选择 | Wrap | Line数字

这只是粗略的一瞥。


1）在95,151,163,231,244和你正在创造新的记录。这是你最有可能造成重复的原因。

2）你的数据库设计看起来不规范。


3）你可能正在做这个所有代码都需要复杂得多。如果表中包含所有必需的数据，那么您只需要一个查询来检查时间范围是否被覆盖。

请列出相关表，如下所示：


表名

[fieldname]（类型）（键/索引）（关系）


示例：

tbl_A

[A_pk]（自动编号）（PrimaryKey）

[A_fk_B]（长）（forngn key 1 to many with tbl_b）（必需，否nulls）

[A_field1]（text（25））


tbl_B

[B_pk]（自动编号）（PrimaryKey）（ B_1：m_with tbl_a）

[B_fk_C]（长）（forngn key 1 to many with tbl_C）（必填，无空）

[B_field1]（text（25） ）必需，没有空，索引没有重复

[B_field2]（长）


示例数据会有所帮助......只需几条记录


如果隐私需要

姓名可以改为John Doe。和Jane Doe等...

电话号码[phone]等...

与SSN相同


我不需要每张桌子，只需要提供表格并互相喂食的桌子。


我不需要每一张唱片，只需一张只要它们反映整体数据，就可以有三个例子。


-

为什么我要问的是你的硬编码似乎很多您的角色和其他信息，然后尝试用手工打击这些硬编码插槽来获取您的计数。这是您的问题的根源。


根据您的表的设置方式，您可以使用交叉表查询执行此操作，并且可能不需要任何VBA或至少将是能够将代码切割成更小，更易于理解的集合。

-


我意识到，一旦你花了这么多精力去做这个项目，反馈意见就有点令人生畏了...... 。

这可能是更好的方式。请不要把这个作为对你能力的个人评论 - 但是作为一个帮助之手的另一个同伴。


我强烈建议你继续阅读通过以下两个链接...我怀疑你会发现它们有用：
访问教程
数据库规范化和表结构。

I''m creating a scheduling application for a hospital department that indicates when they''ve exceeded the minimum required staff for a particular shift. I have a table keeping track of the count and another table that contains the miniumum values based on specific shifts and roles. For example, I know the exact number of staff needed at 2 AM, and I have a staff member working from 11PM through 7AM so I need to indicate that this person will fulfill one of the required slots at 2AM. I also need to account for staff carrying over into a different day, as indicated in this example, so I have to create counts for multiple days (as in day before scheduled date or day after scheduled date). I need to provide this information for multiple days. When I created the information for the initial day, I started getting the non-descript "Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience." I embedded a requery command in the code and it all functions correctly for the first day. When I try and enter information on subsequent days, I get an error that the requery command is creating a duplicate entry in the database. Since this code finds current entries for a role and a shift then adds one to the count, I don''t understand how it could be creating duplicate values. Here''s the vb code for the module:

Expand|Select|Wrap|Line Numbers

解决方案

While I take a second to digest your code please do the following steps:

[Z{Edit} What version of Access are you using? From line 11 it appears that you are using one of the older versions as in 2003 the single line "dim (muli varible) as type" was discouraged and in 2007/2010 will cause you issues. You should go back and change anything looking like:
Dim FinalLoop, LoopCount, TempAMG As Integer
to
Dim FinalLoop As Integer, LoopCount As Integer, TempAMG As Integer
This way when you upgrade you wont run into the typecasting issue.{/edit}]


Please go to your vba editor in MS Access
Then go to the Ribbon/toolbar:> Tools :> Options
Editor tab:
Uncheck "Auto Syntax Check" all this does is add an annoying pop-up that says... you goofed... click ok... the line will already turn red when you mis-form the code.

Check Mark "Require Variable Declaration" (this will place the option explicate at the top of every new code module)
checkmark all of the remaining options.

I like an auto indent of 4; however, I wouldn''t go less than 3 or more than 5 just as a personal preference.
The remaining stuff can be left as default.

Now go thru all of your form modules, standard modules, and class modules and make sure that the following two lines are the VERY first two lines in every one of these modules

Expand|Select|Wrap|Line Numbers

This is only from a cursory glance.

1) On line 95, 151, 163, 231, 244 and you are creating new records. That''s your most likely cause of duplicates.

2) Your database design looks unnormalized.

3) You are probably making this much more complicated than it needs to be with all that code. If you have all the necessary data in the tables, all you need is a single query to check whether or not a time frame is covered.

Please list the relavent tables as follows:

Tablename
[fieldname] (type) (key/index) (relationships)

Example:
tbl_A
[A_pk] (autonumber)(PrimaryKey)
[A_fk_B] (long)(foriegn key 1 to Many with tbl_b)(required, no nulls)
[A_field1] (text(25))

tbl_B
[B_pk] (autonumber)(PrimaryKey)(B_1:m_with tbl_a)
[B_fk_C] (long)(foriegn key 1 to Many with tbl_C)(required, no nulls)
[B_field1] (text(25)) Required, no nulls, indexed no duplicates
[B_field2] (long)

Example data would help... just a few records

If needed for privacy
Names can be changed to "John Doe" and "Jane Doe" etc...
Phone numbers to [phone], [phone] etc...
Same thing with SSN

I don''t need every table, just the ones that feed the form and feed each other.

I don''t need every record, just an example or three so long as they reflect the overall data.

-
Why I''m asking is that it appears that you have hard coded a lot of your roles and other information and then are trying hand-bash your human resources against these hardcoded slots to get your counts. This is the source of your issues.

Depending on how your tables are setup, you may be able to do this with a crosstab query and may not need any VBA or at least will be able to cut your code to a smaller and more comprehensible set.
-

I realize that once you''ve put so much effort into doing the project it''s somewhat daunting to have feedback that says...
"there''s maybe a better way." Please don''t take this as personal comment on your abilities - but as one peer to another in the spirit of a helping hand.

I would highly recommend that you go ahead and read thru the following two links... I have an suspicion that you''ll find them useful:
A Tutorial for Access
Database Normalization and Table Structures.

这篇关于重新查询导致重复的表条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！