Hello, I was hoping one of you kind people might help. I have come undone trying to simplify my access database. I have a built a database to record and collate info about clinical trials that are run within my department. I have one large table [Trial Info] which contains all trial info and have created a second and third table [Milestones] & [Comments] to collect dates of various milestones and a notes that occur throughout the course of a study.
The milestones (dates) are recorded in the 2nd table and the comments are recorded in the 3rd table.
All data is entered by staff on forms. The first form captures basic info about the study, once this has been entered, the idea is to click a button to open the second form to document the trial milestones, this form also contains a subform for listing any free text comments required during the life of the study which are stored in the 3rd table.
I have setup the relationships between tables as best I can.
The main table with basic trial info is the parent table, linking the primary key to the foreign key in the second table, the foreign key of the third table is related to the primary key in the second table so these tables should be looking up the correct clinical trial as far as I can tell.
The form containing data from the main table has a button which I have set to open the Milestones & Comments form, and I have set this to open to the record that relates to the record in view in the primary form. The primary key is related to the foreign look-up key in the milestones table.
This opens the form fine, however this is not updating the foreign look-up key, this remains '0' which I assume is the cause of the following error when I try to save changes to the record
How do I trigger the foreign key to update to the primary key so that the first time a milestone page is opened for a new study it creates a matching record?
I read elsewhere that this error means that relationships have been entered the wrong way around, either I am barking up the wrong tree, am asking access to do something impossible or have taken the wrong approach.
I hope that makes some sort of sense? I would appreciate any suggestions as this is a major headache!
The milestones (dates) are recorded in the 2nd table and the comments are recorded in the 3rd table.
All data is entered by staff on forms. The first form captures basic info about the study, once this has been entered, the idea is to click a button to open the second form to document the trial milestones, this form also contains a subform for listing any free text comments required during the life of the study which are stored in the 3rd table.
I have setup the relationships between tables as best I can.
The main table with basic trial info is the parent table, linking the primary key to the foreign key in the second table, the foreign key of the third table is related to the primary key in the second table so these tables should be looking up the correct clinical trial as far as I can tell.
The form containing data from the main table has a button which I have set to open the Milestones & Comments form, and I have set this to open to the record that relates to the record in view in the primary form. The primary key is related to the foreign look-up key in the milestones table.
This opens the form fine, however this is not updating the foreign look-up key, this remains '0' which I assume is the cause of the following error when I try to save changes to the record
How do I trigger the foreign key to update to the primary key so that the first time a milestone page is opened for a new study it creates a matching record?
I read elsewhere that this error means that relationships have been entered the wrong way around, either I am barking up the wrong tree, am asking access to do something impossible or have taken the wrong approach.
I hope that makes some sort of sense? I would appreciate any suggestions as this is a major headache!
