从连接的一侧删除记录 [英] Deleting records from just one side of a join

问题描述

大家好，


我已经和他摔跤了几天。我将向我实际处理的那个提供一个简单但相同的场景。


假设我有两个表： tblEmployees（ERN，last_name， first_name）和 tblArchivedData（ERN，earn_date，earn_amount）。归档数据表可能（并且实际上将拥有）每个员工的许多记录（许多记录的ERN相同）。 employees表只包含每个员工的一条记录。


接下来，假设我在数据表视图中有一个绑定的子表单，我的对象是显示ERN，last_name，first_name， earn_date和earn_amount。我想显示归档表中的所有记录。


如果我将数据表记录源设置为归档表，我会得到ERN，earn_date和earn_amount就好了;但我也希望在数据表中显示员工的姓名......即使它出现多次。当我通过构建涉及tblEmployees和tblArchivedData之间的连接的简单查询来执行此操作，并将数据表记录源设置为该查询时，它可以很好地工作。


问题是，如果我从数据表中删除一条记录，意图从tblArchivedData中删除该记录......它也会从tblEmployees中删除该员工的记录。我不想要这个; tblEmployees本质上是一个查找表，我不想删除任何东西。


我通过使用DLookup填充名称来避免这个问题数据表。但它的性能令人望而却步，而且你也失去了其他简单的功能，比如能够根据姓氏对数据表进行排序。


有什么想法吗？谢谢。


Pat

解决方案

只要从子表单中删除一条记录就可以了，你就是那种坚持删除记录源中的任何内容我很害怕。


也就是说，你可以通过处理 BeforeDelConfirm 事件和编码，使得要删除的记录通过特定的SQL完成，然后取消事件。听起来像一个计划？

我完全按照你的说法复制你的条件，并没有遇到你所描述的问题。以下是详细信息：

1. tblEmployees和tblArchivedData以下列方式涉及1到多个关系：
   

   展开 | 选择 | Wrap | 行号

出于某种原因，我一直忘记之前......。事件。它确实可行。我会在今天下午尝试一下，让你知道它是如何运作的。感谢。

Hi everyone,

I''ve been wrestling with this for a couple of days. I''m going to present a simple but equivalent scenario to the one I''m actually dealing with.

Suppose I have two tables: tblEmployees(ERN, last_name, first_name) and tblArchivedData(ERN, earned_date, earned_amount). The archived data table may have (and in practice will have) many dozens of records per employee (same ERN for many records). The employees table will contain just one record for each employee.

Next, suppose I have a bound sub-form in datasheet view, where my object is to display ERN, last_name, first_name, earned_date and earned_amount. I want to display all those records from the archive table.

If I set the datasheet recordsource to be the archive table, I get the ERN, earned_date and earned_amount just fine; but I also want to display the employee''s name in the datasheet...even if it''s appearing multiple times. When I do this by building a simple query involving a join between tblEmployees and tblArchivedData, and set the datasheet recordsource to that query, it works nicely.

The problem is that if I then cut a record from the datasheet, with the intention of deleting that record from tblArchivedData...it also cuts that employee''s record from tblEmployees. I don''t want this; tblEmployees is essentially functioning as a look-up table here, and I don''t want anything deleted from it.

I sort of circumvented the problem by using DLookup to populate the names in the datasheet. But it''s performance prohibitive, and you also lose other simple functionality like the ability to sort the datasheet based on last name.

Any ideas? Thanks.

Pat

解决方案

As far as simply deleting a record from the subform goes, you''re sort of stuck with deleting whatever''s in the record source I''m afraid.

That said, you may be able to get around it by handling the BeforeDelConfirm event and coding it such that the records otherwise to be deleted are done via specific SQL, and then Cancel the event. Sound like a plan?

I duplicated your conditions exactly as you have stated, and have not encountered the problem which you have described. Here are the details:

1. tblEmployees and tblArchivedData are involved in a 1 to MANY Relationship in the following manner:
   

   Expand|Select|Wrap|Line Numbers

For some reason I keep forgetting about the "Before..." events. It does sound workable. I''ll try it this afternoon and let you know how it works out. Thanks.

这篇关于从连接的一侧删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！