Embedded subforms and data-entry on M:M relationships

[Markpud]

I have a question around using forms to display and add/edit data which is
organised in a complex relationship..

The database is for a call centre, and controls which type of caller has
access to specific account activities, and messages that will be displayed
for the allowed/denied access.

The table Activities has fields Activity_ID (PK), Activity_Name.

The table Caller_Role has fields Caller_ID (PK), Caller_Name.

There is a many-to-many relationship between these 2 tables, as many
different callers can access many different activities.

So I have created a link-table called Caller_Role_Access, with
Caller_Role_Access_ID (PK), Caller_Role (FK to Caller_Role.Caller_Role_ID),
Activity (FK to Activities.Activity_ID), Access_Allowed (Y/N),
Message_Displayed (Y/N).

Then the messages are stored in Caller_Role_Messages, which has Message_ID
(PK), Message_Type, Message_Text.

As there can be many messages displayed for each combination of
Caller/Activity, I have created another link-table called
Caller_Role_Message_Display, containing Message_Display ID (PK),
Caller_Role_Access (FK to Caller_Role Access.Caller_Role_Access ID) and
Message (FK to Caller_Role_Messages.Message_ID).

This structure seems to work, and allows me to pull information via queries.

However when trying to design the forms to view/add/amend the data, I am
hitting problems..

I first created a form from the Caller_Role table, then added a subform for
the Caller_Role_Access table. On this subform I deleted the ID that the user
should not see, Caller_Role_Access_ID, and removed the Caller_Role field
control, as this is duplicated from the main form. Then I changed the
Activity field control to a combo box that draws its data from the Activities
table to display Activity_Name rather than the ID.

This all works OK up to here!

Then I need to display the messages for the Caller_Role and Activity
selected, so I added another subform inside the existing subform, linked to
the Caller_Role_Message_Display table. I removed all the field controls from
this subform, as this is a link-table and contains only IDs. I added another
subform inside this subform to bring up the Caller_Role_Messages table. Then
I converted the Message_ID field control to a combo box linked to Message_ID,
as these Message_IDs are understood by the business.

When viewing the form it looks at first as though it works OK, but in fact
it only shows one Message for each Caller_Role_Access combination, even
though many are possible.

On adding a new Caller_Role, I can then select an Activity in the first
subform, but I can’t then select a Message in the final subform, as I get an
error stating “You tried to assign the Null value to a variable that is not a
Variant data typeâ€. Then it does let me select something, but if I tryto move
to a new record I get the message “You cannot add or change a record because
a related record is required in table ‘Caller_Role_Access’.

I appreciate this is a lot to follow, but does anyone have any ideas how I
can get this to work so I can add a Caller, then chose an Activity, then
chose a Message, and have the form populate all the appropriate link-tables??

Any help or advice gratefully appreciated!

 

[Markpud]

Well I think I've solved my own problem by rebuilding the form from the
beginnning, so I'll share it just in case anyone is unlucky enough to be
dealing with this type of thing!

The problem was in the Caller_Role_Message_Display subform. By removing all
the field controls on that subform and then trying to manipulate the
Caller_Role_Message subform inside it, I had lost the connection. By keeping
the field control Caller_role_Message_Display.Message, and changing this to a
combo linked to Caller_Role_Message.Message_ID then it works as I intended.

The lesson is to take a bit more time to think about each subform and what
it contains, so that the links between the tables can be reporduced in the
forms!