时间戳

首页 > 解决方案 > 访问具有多对多关系的两个列表框

ms-access - 访问具有多对多关系的两个列表框

问题描述

我有一个 Access2017 数据库。在一个表单上，我有“PLCS Group”多选列表框和“IPS Elements”第二个多选列表框。

-----FIRST LIST-----
ID
GRAPES
APPLES
CHERRIES
LETTUCE
--------------------

-----SECOND LIST----
FK
COLD
DARK
LEAVE IN SUN
DIED
---------------------

So if someone selected GRAPES and APPLES from first list. And selected COLD,DIED from second list

This is what the second table should have it it.
GRAPES,COLD
GRAPES,DIED
APPLES,COLD
APPLES,DIED

我尝试嵌套第二个列表框的记录，但没有更新表。我将代码更改为建议的内容，但现在我收到错误 3020“更新或取消更新，没有添加新或编辑”

我在其中有 AddNew 语句我不确定是什么使它无法识别它们。如果我注释掉第二个循环，则保存第一个列表框项目。这是我的更新代码：

Dim db            As DAO.Database
Dim rs            As DAO.Recordset
Dim rs2            As DAO.Recordset
Dim ctl           As Control
Dim ctl2           As Control
Dim varItem       As Variant
Dim varItem2        As Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("ProposalTracker3", dbOpenDynaset, dbAppendOnly)
Set rs2 = db.OpenRecordset("IPSElementsDetails", dbOpenDynaset, dbAppendOnly)


Set ctl  = Me.PLCSGroupListBox
Set ctl2 = Me.IPSList

For Each varItem In ctl.ItemsSelected
        rs.AddNew
            rs!GACP = ctl.ItemData(varItem)
        rs.Update
    For Each varItem2 In ctl2.ItemsSelected
        rs2.AddNew
            rs2!ProposalID = Me.txtID
            rs2!Element = ctl2.ItemData(varItem2)
        rs.Update
    Next varItem2
rs2.Close
Next varItem
rs.Close
db.Close

标签： ms-access

解决方案

似乎您想要两个列表框的选定项目的所有可能组合。您将条目与两个嵌套循环结合起来

Set db = CurrentDb()
Set rs = db.OpenRecordset("DestinationTable", dbOpenDynaset, dbAppendOnly)

Set lb1 = Me.PLCSGroupListBox
Set lb2 = Me.IPSList
For Each item1 In lb1.ItemsSelected
    For Each item2 In lb2.ItemsSelected
        rs.AddNew
        rs!Somefield = lb1.ItemData(item1) & ',' & lb2.ItemData(item2)
        rs!ProposalID = Me.ID
        rs.Update
    Next item2
Next item1
rs.Close
db.Close

或者，也许您想将两个列表框中的值添加到两个单独的字段中

rs.AddNew
rs!Somefield = lb1.ItemData(item1)
rs!Anotherfield = lb2.ItemData(item2)
...

推荐阅读