I was wondering if someone to help with the design of this form as I'm having trouble conceptualizing it. It's simply intended to store a database of relational words.
As the title suggests, it's a M2M involving only table:
-
This is what I would like to happen, as an example (using frm_elements):
-
Of course, this doesn't work as when you navigate to lava, there are no records because there is no juntion table record where elements1 = lava. I would need to have the splitform list based on elements2's field, however that's clearly the same problem.
I can't wrap my head around this as I want to
I hope that makes sense and i'm just missing something obvious. I don't want to have to enter the connections twice - going to volcano, adding lava - then going to lava and adding volcano.
Kind regards
I'd say you need a UNION query as record source of the subform, to cover both directions.
E.g.
SELECT e.ID, e.element
FROM tbl_elements e INNER JOIN tblJ_elements j ON e.ID = j.J_elements1
WHERE j.J_elements2 = Forms!frm_elements!element_ID
UNION ALL
SELECT e.ID, e.element
FROM tbl_elements e INNER JOIN tblJ_elements j ON e.ID = j.J_elements2
WHERE j.J_elements1 = Forms!frm_elements!element_ID
ORDER BY element
frm_elements!element_ID is the element you have selected on the left side.
UNION queries are always read-only, so your subform will be read-only. You need to handle additions and deletions on the right side with VBA.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments