MS-Access 连接表在两个互斥列表（2 个列表框）之间插入/删除

发布于 2024-09-11 21:16:26 字数 615 浏览 10 评论 0原文

有了这种设计，我想创建一个功能，用于从连接表添加和删除记录。

该案例来自编辑员工并选择他们可以参加哪些考试时。左侧列表框中是他们没有资格参加的考试（无论如何），而右侧列表框中是他们有资格参加的考试。

该表如下所示：

TABLE EmpExam 
(
  EmpID,
  ExamID
)

EmpID 始终已知，但 ExamID 将从左侧列表框中选择。右侧列表框中的记录可能必须同时具有 EmpID 和 ExamID 才能被删除。

一旦进入各自的框中，就不需要立即删除/插入（它们可以等到表单关闭）。

在 Access 中是否有一个好的/标准的方法来完成此任务？

原文

With this kind of design, I would like to create a functionality with which to add and delete records from the junction table.

The case is from when editing an employee and selecting what exams they can take. In the left list box would be the exams that they aren't eligible for (yet, anyway) and the exams that they are eligible for on the right list box.

The table is something like this:

TABLE EmpExam 
(
  EmpID,
  ExamID
)

The EmpID would be known at all times, but the ExamID would be selected from the left list box. Records in the right list box would probably have to have both the EmpID and the ExamID in order to be deleted.

Instant deletions/insertions aren't necessary once they're into their respective boxes are not necessary (they can wait until the form is closed).

Is there a good/standard way to accomplish this in Access?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笨死的猪 2024-09-18 21:16:26

当您必须使用代码将项目添加到表中然后使用代码删除它们时，为什么要使用列表框？

对于您想要做的事情，子表单是通常的解决方案。此外，您可以使用子表单的记录集克隆。请注意，您可能应该在该设置中有一个日期时间字段。另外，如果ExamID唯一，并且启用了级联删除，则从主表删除也会从子表删除。

回复收藏 0 原文

蒗幽 2024-09-18 21:16:26

我最终使用了两个列表框，其中 1 个添加按钮和 1 个删除按钮触发执行原始 SQL 的 VBA On Click 方法。

该方法看起来像这样：

If IsNull(cboInEligible.Column(1))
  Exit Sub
End If

CurrentDB.Execute ("INSERT INTO tblEmpExam (ExamID, EmpID) " & _
                   "VALUES (" & ExamID & ", " & lstInEligible.Column(1) & ")")

lstInEligible.Requery
lstEligible.Requery

删除查询的完成方式类似。

这两个列表框是互斥的。

SELECT EmpID, EmpName
FROM Employee
WHERE EmpID NOT IN (SELECT EmpID FROM tblEmpExam WHERE ExamID = [txtExamID]);

txtExamID 是表单上的隐藏（但对设计者来说很明显）控件，因为除了通过控件或通过绝对命名之外，我无法引用表单的 ExamID。

另一个列表框使用 EmpID IN 而不是 EmpID NOT IN 以使其具有独占性。

I ended up using two listboxes with 1 add button and 1 remove button that triggers their VBA On Click methods that execute raw SQL.

The method looks something like this:

If IsNull(cboInEligible.Column(1))
  Exit Sub
End If

CurrentDB.Execute ("INSERT INTO tblEmpExam (ExamID, EmpID) " & _
                   "VALUES (" & ExamID & ", " & lstInEligible.Column(1) & ")")

lstInEligible.Requery
lstEligible.Requery

The delete query is similarly done.

The two listboxes are mutually exclusive.

SELECT EmpID, EmpName
FROM Employee
WHERE EmpID NOT IN (SELECT EmpID FROM tblEmpExam WHERE ExamID = [txtExamID]);

txtExamID is a hidden (but obvious to the designer) control on the form since I can't refer to the form's ExamID except through a control or through absolute naming.

The other list box has EmpID IN instead of EmpID NOT IN to make it exclusive.

回复收藏 0 原文

~没有更多了~