Junction Table problem

[Cordeiro82]

Hello Everybody,

I am creating a many to many relationship and created a junction table but cannot get it to populate using a form/subform method.

My tables are as follows:
PK - primary Key; AN - AutoNumber; N - Number
TblPO - PoID(PK, AN), PurchDoc, CreatedBy, VendorNumber, CreatedOn
TblInvoice - InvoiceID(PK, AN), PurchDoc, Item, Quantity, CurrAmt
TblLink_PO_Invoice - PoID(N), InvoiceID(N); Both the PoID and InvoiceID is the PK in the Link table.

I have enforced referential Integrity but have not selected Cascade Update and Cascade Delete.

I created a form based on TblPO and then a subform based on the junction table. In the subform I hid the PK PoID and changed the InvoiceID to a combo box referencing [Invoice].[InvoiceID] as the data source.

I then imbedded the subform into the main form but with no results. Obviously I am doing something wrong but cannot figure out what it is. Any help would be greatly appreciated.

Thanks,

Kevin

 

[lupins46]

Have you set the LinkMaster and LinkChild fields in the subform control on your main form? You would have been asked to nominate these when you added your subform to your main form.

 

[Cordeiro82]

Hey Lupins46,

I did set the LinkMaster, LinkChild fields but with no results. Is there another way to populate the junction table?


Thanks,

Kevin

 

[pbrodsky]

I think in your combo box, invoice.invoiceid should be the row source, the data source should be invoiceid from the link table.

 

[pbrodsky]

Sorry - not a complete answer. Row Source Type should be Table/query, and Row Source should be a query that returns a list of invoiceid.

 

[MajP]

What you are doing sounds right. That is how I do a many to many form. You say you get no results, what does that mean? I assume no records appear and the subform is not editable.
However, I never link PK to PK and I never have a field be a primary key in two tables. I only link PK to FK. So I never make the FK's in the join table also PK's. I would do a composite index on them if the combination must be unique, but I would just use an arbitrary autonumber in the join table as the PK. I may be wrong about this, but that is the way I have always done it. You may be able to link PK to PK, but you may try removing the key from POID an InvoiceID from the join tables.