Results 1 to 7 of 7
  1. #1
    DoctorFosterGloster is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jul 2016
    Location
    New Zealand
    Posts
    2

    Question Help! -You cannot add or change a record because a related record is required in table <name>

    Hello

    I am trying to add data to my sub-form 'Address', however whenever I complete this form and click on my main 'patient' info form it gives me this error. This is my second time i have had this problem. I remade this entire database hoping that this would fix it, however the problem remained.

    Some info



    I wish to have all my sub-forms in one tabbed-type form layout.
    It is a medical-centre/ doctors database.
    I am a novice so any help in layman's terms would be appreciated.

    Below is some images of my database:
    Note; that I have not created the other forms as this problem has set me back. However, my previous database I included all the forms and added data to them all, much to my dismay i got the same error. (my previous database was the exact same as this one.)

    Error image;
    Click image for larger version. 

Name:	Error 1.png 
Views:	30 
Size:	5.1 KB 
ID:	25282
    Relationship;
    Click image for larger version. 

Name:	Error 2.png 
Views:	29 
Size:	41.3 KB 
ID:	25281
    Patient Form within the tab;
    Click image for larger version. 

Name:	Error 3.png 
Views:	30 
Size:	29.6 KB 
ID:	25283
    Address Sub-form within the tab;
    Click image for larger version. 

Name:	Error 4.png 
Views:	30 
Size:	26.3 KB 
ID:	25284

    If you need any more info/ screenshots please ask.
    Thank you all in advance

    Edit: How can I add a spoiler Dropbox to hide my images for convenience? (Squarebrac)spoiler(Squarebrac) words/images/info (Squarebrac)/Spoiler(Squarebrac) doesn't seem to work.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think you need to step back and look at your table design/structure. It looks like you are missing 3 tables.

    Look at the "Patient" table and the "Doctor" table.
    Can 1 patient have many doctors? Can 1 doctor have many patients? If the answer is YES, then you need a junction table.

    Look at the "Patient" table and the "Medical Procedure" table. (shouldn't have spaces in object names)
    Can 1 patient have many Medical Procedures? Can 1 Medical Procedure be performed on many patients? If the answer is YES, then you need a junction table.

    Look at the "Patient" table and the "Medication" table.
    Can 1 patient take many medications? Can 1 medication be given many patients? If the answer is YES, then you need a junction table.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you are trying to add patient information (e.g. address) before you have added the patient record. You get the message because you have defined the relationship as one to many (the 1 and infinity signs).

    Sou you need to make the patients table the recordsource to your main table (not a subform), and all other tables will be in subforms on that main form

  4. #4
    ineuw's Avatar
    ineuw is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Location
    Canada
    Posts
    33
    The Medication_ID and the Medical_Procedure_ID have their own tables which is correct, so perhaps the two fields in the Patient table should be labeled "Last medication ID" and "Last Procedure ID", assuming that is what you wanted the two fields for. If not, then they are not needed. Always ask yourself if a field info is unique, or is it changing information. The two cannot reside in the same table.

  5. #5
    DoctorFosterGloster is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jul 2016
    Location
    New Zealand
    Posts
    2
    Quote Originally Posted by ssanfu View Post
    I think you need to step back and look at your table design/structure. It looks like you are missing 3 tables.

    Look at the "Patient" table and the "Doctor" table.
    Can 1 patient have many doctors? Can 1 doctor have many patients? If the answer is YES, then you need a junction table.

    Look at the "Patient" table and the "Medical Procedure" table. (shouldn't have spaces in object names)
    Can 1 patient have many Medical Procedures? Can 1 Medical Procedure be performed on many patients? If the answer is YES, then you need a junction table.

    Look at the "Patient" table and the "Medication" table.
    Can 1 patient take many medications? Can 1 medication be given many patients? If the answer is YES, then you need a junction table.
    Thank you for this. This seems to work so far. However, on the tables it says they are not connected. eg: for patient 'John Smith' I enter his doctor's info into the form. His doctor has ID1. This ID does not appear on the patient table - it remains empty. Thereby suggesting that they are not connected even with a junction table in between.


    The error still remains for the address form. I cannot enter info onto it without it reporting the same error. This is strange because I have worked on another database with the exact same relationship between "patient' and "address" without issue. The address form is a sub-form on the main patient form by the way. Any fix?

    Thank you Ajax and ineuw for your replies.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think it is possible that there may still be tables missing. I see fields in the Patient table eg DateBecamePatient, Date Last Visit.. that indicate that a Hospital Stay or patient Session or Visit is important to your system.
    I'd like to step back and say that getting your tables identified and getting your proposed relationships identified and tested, is key to getting a successful database. But all this starts with a clear description (modified/reviewed/adjusted/ etc over and over) to get a set of Business facts that represent your "business". You can build a small model, and create some test data, then attack the model with various scenarios. Everytime there is an "issue", you must reconcile it --was it data, was it a missing table, a bad relationship....--fix it and try again. Once you and colleagues are happy with the structure---you now have a blueprint (at least a working plan) from which you can build your database.

    It is much easier to fix these things before you have invested considerable time and effort loading data and making it pretty. We see lots of posts where people wish that had spent more time on analysis in order to get the design to meet the requirements. This doesn't mean you need everything identified and defined to the nth detail, but you should not be guessing what a table represents, or why a field is present---that is no major surprises. If there are features identified to be done as "future enhancements", you can map out where these fit generally and accommodate them in advance in your model. These features will change with time, but at least you have identified where they will fit--even it it's just a black box on the model.

    Good luck.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is a quick and dirty example. Is this close to what you have?

    (any control in yellow is my way of indicating a not visible control)
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 04-20-2015, 02:08 PM
  2. Replies: 15
    Last Post: 03-05-2015, 03:30 PM
  3. Replies: 1
    Last Post: 12-05-2014, 07:47 PM
  4. Replies: 8
    Last Post: 06-26-2014, 08:52 AM
  5. Replies: 40
    Last Post: 08-20-2013, 11:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums