如果Emp存在数据。已提交相同类型的ID在两个日期之间离开 [英] if Data exist for an Emp. ID who already submitted same type leave between two dates

问题描述

我在ms访问时有一个名为[tabLeave]的表，其中包含以下字段： -

1. eeID = Number

2. Emp_LeaveType = Text

3. Leave_From =日期[dd-mmm-yy]

4. Leave_Upto =日期[dd-mmm-yy]

5. LeaveEntryType = Text


我还有一个名为[frmLeave]的未绑定表单，其中包含以下字段，我在此表中提交数据。


1. eID = Number

2. L_Type =文字

3. Start_Date =日期[dd-mmm-yy]

4. End_Date =日期[dd-mmm-yy ]

5. EntryType =文字


所以我的问题是，在填写eID和L_Type之后，使用Start_Date和End_Date将是什么vba代码（更新后）使用相同的EntryType，如果tabLeave中已经存在与所有5个字段匹配的休假期，则可以出现一个消息框以防止具有相同休假期的重复数据。


提前感谢。

附加图片

	（17.6 KB，169 views）
	（18.9 KB，154 views）

解决方案

您实际上必须执行两个测试，一个用于Start_Date，另一个用于End_Date。我可能只会做两个DCount（）函数。所以像这样（航空代码）：

展开 | 选择 | Wrap | 行号

我会创建一个测试重叠的函数并将其放入表单的BeforeUpdate事件，如果有重叠，则取消保存。


我认为你可以通过几个DCounts来实现该功能。我看起来像下面这样。这是空气代码，您必须拥有它：

展开 | 选择 | Wrap | 行号

感谢Seth和jforbes的回复


你们两个都没有在你的公式中添加LeaveEntryType。可能是我忘了它提。

LeaveEntryType也是表和表格中的一个文本框，它描述了关于请假入境是每月，PUC，调整等。


所以我尝试使用以上字段修改的Seth代码，但它没有用。如果我尝试了Seth的公式没有LeaveEntryType那么它对我来说很好。所以请加上上面的字段来解决我的问题..


这是我的代码。

展开 | 选择 | Wrap | 行号

I have a table named [tabLeave] in ms access with following fields:-
1. eeID = Number
2. Emp_LeaveType = Text
3. Leave_From = Date [dd-mmm-yy]
4. Leave_Upto = Date [dd-mmm-yy]
5. LeaveEntryType = Text

I also have unbound form named [frmLeave] with following fields where from I submit data in this table.

1. eID = Number
2. L_Type = Text
3. Start_Date = Date [dd-mmm-yy]
4. End_Date = Date [dd-mmm-yy]
5. EntryType = Text

So my question is that what would be vba code (after update) with Start_Date and End_Date after filling up eID and L_Type with same EntryType so that A message box could appear to prevent duplicate data with same leave period if leave period already exist in tabLeave with all 5 fields match.

Thanks in advance.

Attached Images

	(17.6 KB, 169 views)
	(18.9 KB, 154 views)

解决方案

You will actually have to perform two tests, one for Start_Date and one for End_Date. I would probably just do two DCount() functions. So something like this (air code):

Expand|Select|Wrap|Line Numbers

I would create a Function that tests for the overlap and put it in the BeforeUpdate Event of the Form, having it Cancel the Save if there is an overlap.

I think you can make the function out of a few DCounts. I would looks something like the following. This is air code, you''ll have to take ownership of it:

Expand|Select|Wrap|Line Numbers

Thanks to Seth and jforbes for reply

you both not added LeaveEntryType in your formula..may be i Forgot it to mention.
LeaveEntryType is also a text box in table and form which describes about Leave Entry is monthly, PUC, Adjustment etc.

So I tried following code of Seth with above field amendment but It not worked. If I tried formula of "Seth" without LeaveEntryType then it works fine for me. So kindly solve my issue with adding above field..

Here''s my code.

Expand|Select|Wrap|Line Numbers

这篇关于如果Emp存在数据。已提交相同类型的ID在两个日期之间离开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！