[AccessD] MAny-To-May

Shamil:

Beautiful!  I had the join query already set up as the record source of 
the subform.  All I needed to do was to drop the FK of the relation 
table into the grid, add it as an invisible field on the subform (not 
sure if that was really necessary) and viola! it worked.  But it 
probably would have taken me a month to figure it out.

Thank you.  I think I'll take the rest of the day off now.


With best regards,

Rocky


Shamil Salakhetdinov wrote:
>> 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 
>>     
>
>   

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