If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Embedded subforms and data-entry on M:M relationships
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! |
#2
|
|||
|
|||
Embedded subforms and data-entry on M:M relationships
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! "Markpud" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|