[AccessD] MAny-To-May

John:

Unfortunately, I had it that way but the the users actually look at it 
the other way - they want to select a piece of equipment and then see 
all the jobs for that piece.  So I have to change the job form. 

Rocky


John Colby wrote:
> 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
>
>   

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