[AccessD] MAny-To-May

Rocky,

    My sample db named MedicalDiagnosis might be of interest to you. It is available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.
 
    The db demonstrates a user friendly, integrated interface for data entry, facilitating smooth population of junction table bridging the many to many relationship between diseases & symptoms.
 
Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: Rocky Smolin - Beach Access Software 
  To: Access Developers discussion and problem solving 
  Sent: Sunday, March 19, 2006 20:55
  Subject: [AccessD] MAny-To-May


  Dear List:

  This is a preventive maintenance application.

  There is a table of Jobs and a table of Equipment. Since one job can do many equipments and one equipment can be called out in many jobs there is a table that sits between them - tblJobsEquipment which has the PK from tblJobs and the PK from tblEquipment as FKs.

  The equipment table is relatively static.  The job table changes all the 
  time.

  On the form where jobs are created and maintained, the user selects the equipment from a combo box.  The jobs for that equipment are then 
  displayed in a sub-form.

  The problem comes when trying to create a new job.  Since there's no PK yet for the job I get an error "cannot create record; join key (to the 
  JobEquipment table) is not in the recordset.

  Indeed if I put a debug statement in the BeforeInsert event of the 
  sub-form the PK of the Jobs table is still null. 

  How does one work around this problem?

  MTIA

  Rocky