Automatically asign data

[Del_Piero_3]

Hi,

I am trying to create a form which involves 3 tables. The 2nd table (CaseRef_Case_Control) is the junction because I needed many-to-many relationship between “CaseReference” and “Cases” tables.

All I want to do is automatically fill in the fields in the “CaseRef_Case_Control” table when I add data to “CaseReference” and “Cases” table. Check the relationship diagram attached to make more sense. Can someone please help me with this?

Thanks.

 

[Tim L]

Del_Piero_3,

I'm not entirely sure why you would want to have a slide associated with different cases , should not all slides be associated to a single case, as is suggested by the inclusion of the CaseID field in the Cases table?

Currently I think if you continue how you have suggested that you run the risk of having CaseRefences (people) without cases (slides) (possibly acceptable I suppose) and cases (slides) without associated CaseReferences (people) (something that I would have thought would not be acceptable).

In the meantime, if you have a form based on CaseRef_Case_Control and then have two subforms, one for each of CaseReference and Cases then the fields should link together automatically; CaseRef_Case_Control will be providing the control numbers to the subforms and the tables in the subforms will automatically assign the value for CaseRefID to records in CaseReference and SlideNo to records in Cases. How you generate the numbers for CaseRefID and SlideNo in CaseRef_Case_Control is up to you but I'd recommend an autonumber field for both. You'd then set the field type fo CaseReference and SlideNo to number, longinteger to tie in with the autonumber field.

I'm not sure if this is the ideal solution though, a bit more info on why the many-to-many relationship might help, as would knowing how records are created (i.e: forms and subforms used).



HTH

Tim