[AccessD] MAny-To-May

Make them create the job first.  Make the job the main form and the
equipment a subform child to jobs.  Then set the equipment subform locked
while on the new record of the job form.


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin -
Beach Access Software
Sent: Sunday, March 19, 2006 10:25 AM
To: Access Developers discussion and problem solving
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

--
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com