Update Multiple Tables with a Single Form RRS feed

  • Question

  • Hello, I am very new to Access and have been playing around with it to meet my needs. I am trying to update multiple tables with a single form. What is the best way I could go about doing this?

    For background, I want to use Access to collect data regarding hospitalized patients and the drugs they received. All patients have one medical record number (MRN). Every time a patient comes to the hospital, they keep their MRN and receive an unique encounter number (FIN). All drugs ordered should have a unique order number, tied to that FIN. So my (simplified) tables are below:

    ID - AutoNumber
    MRN - required, indexed, no dupes

    ID - AutoNumber
    MRN - required, indexed, dupes allowed
    FIN - required, indexed, no dupes

    orderID - AutoNumber
    FIN - required, indexed, dupes allowed

    tblPatient.MRN [one-to-many] tblEncounter.MRN
    tblEncounter.FIN [one-to-many] tblMedication.FIN

    The current error with the form created with Form Wizard is that once I get through the values for the patient table, entering values for the Encounter or Medication table throws the error of "join key not in record set". I assume this is because the new row with MRN created in Patient is not being pushed to a new row in Encounter. However, I have tried many ways to address this along with Join type without success.
    • Edited by ufdlim Thursday, September 12, 2013 9:58 PM
    Thursday, September 12, 2013 9:32 PM


  • Check you Access books / Access Help on "SubForm". 

    In your case, you may even need nested SubForms where the MainForm is based on the Entity [Patient], the first-level SubForm is based on the Entity [Encounter] and the second-level SubForm (nested inside the first-level SubForm) is based on the Entity [Medication].


    Van Dinh

    Friday, September 13, 2013 2:56 AM