[AccessD] MAny-To-May

> How does one work around this problem?
Rocky,

This works in MS Access 2003:

- create join query of relation table and Jobs
- use this query as the recordsource of subform
- use BOTH relation table's and job table's JobId field in this subform;
- make this subform a datasheet form (not necessary - just to better see 
test results);
- open main form in normal view;
- enter new job information in subform's row and save it
- watch how new autonumber Ids are assigned to relation table's row and to 
new job row.

You can now hide autonumber IDs.

Shamil

----- Original Message ----- 
From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Sunday, March 19, 2006 6:25 PM
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