I have a db with a set of related tables as outlined below (* indicates PK):
LU_Anaes_Proc - contains a list of anaesthetic procedures (I use the prefix LU for lookup tables when naming them)
LU_Anaes_Comp - contains a list of potential complications
LU_Comp_Action - contains a list of remedies to the potential complications
LU_Proc_Comp_Action - records of relevant procedure/complication/actions
Each of the ID fields in the first 3 tables are linked to the corresponding ID field in the 4th table in a one-to-many relationship.
When a user is completing a data input form for a given procedure e.g. epidural analgesia, he/she can choose from a list of complications (checkboxes). When a relevant checkbox is checked a hidden combobox appears listing the potential remedies for that complication(the datasource for the combobox is a pre-written query that filters the table LU_Proc_Comp_Action using both the procedure and the complication as filtered fields). Each remedy list contains a "Other action taken" option. Selecting this opens an inputbox to allow the useer to type in this 'other' action taken. Saving from this inputbox generates a temporary record in a table which records the procedure, complication and the other action taken as entered by the user. This setup works perfectly.
The aim is that an administrator can review the records in this table and decide if he/she wishes to permanently add the 'other' remedy to the existing LU_Comp_Action table. I can easily add a new record to the table LU_Comp_Action using VBA SQL but all I can add is the remedy. I am wondering if I need to add a new record to the table LU_Proc_Comp_Action but how do I add the values for the three ID fields? Essentially I am asking how to add a new record to related tables.
Hoping someone can help!