Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Unanswered: Subform Autonumber doesn’t update many-side table

    I’ve got a form (ProtocolPatients) containing a continous subform based on a 3 table many to many relationship query. With a comboBox in the main form, I select a protocol number and the subform populates with the patients for that protocol. The main form and subform are linked by Protocol_ID. Works great so far.

    The problem is when adding a new patient record in the subform, the new Autonumber doesn’t update the many side ProtocolPatients table with the new patient_ID. (Patient_ID) does show up in the one side Patients table. All other info is entered in the new record updates to correct tables.

    What am I missing? Thanks for the help.

  2. #2
    Join Date
    Sep 2011
    Posts
    2
    Quote Originally Posted by RxExec View Post
    I’ve got a form (ProtocolPatients) containing a continous subform based on a 3 table many to many relationship query. With a comboBox in the main form, I select a protocol number and the subform populates with the patients for that protocol. The main form and subform are linked by Protocol_ID. Works great so far.

    The problem is when adding a new patient record in the subform, the new Autonumber doesn’t update the many side ProtocolPatients table with the new patient_ID. (Patient_ID) does show up in the one side Patients table. All other info is entered in the new record updates to correct tables.

    What am I missing? Thanks for the help.
    I think you need to re-visit your tables/relationships. From your description, I would say your protocol table should have a primary key and your patient table should be linked via this key giving you a one to many relationship. What is the relationship of the 3rd table?

  3. #3
    Join Date
    Jul 2011
    Posts
    14
    The parent form is bound to the Protocols table and linked to the subform by protocol_ID...maybe I should be using an unbound form or maybe an event to INSERT the new Autonumber into the ProtocolPatients table.

    I "think" I have the relationships right. Ordinarily, I would use the Patient_ID from the ProtocolPatients table to create the detail/subform, but since I wanted to use the subform for data entry I used the Patient_ID from the Patients table to get the Autonumber.

    Adding a new record updates the individual fields from the subform to the correct table except the new Autonumber isn't added to the ProtocolPatients table. I understand that by design it's not supposed to.

    Really appreciate your help
    Attached Thumbnails Attached Thumbnails relation.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •