C
christy
I have 3 tables:
T1: CustID, Cust
T2: ProdID, Prod
T1T2: CustID, ProdID, Qty
On the main form, I have a combo dropdown for selecting cust.
On the subform, I display a full set of products - left outer join. If the
user enters a Qty, I would like to insert a record to T1T2 junction table.
The left outer join looks like:
select T2.ProdID as T2ID, T2.Prod, T1T2.ProdID as JuncID, T1T2.Qty from T2
Left outer join T1T2 ON T2.ProdID = T1T2.ProdID
On the subform, I have a ID filed tied to T2ID. If the record already exists
in T1T2 junction table, an update to qty works fine. However, if the record
doesn't exist, an insert causes a write conflict error.
I think the problem is that the FK of ProdID (JuncID) is null at that point
- result of left outer join. How do I solve this problem without
pre-populating T1T2 with every possible combination of CustID, ProdID.
T1: CustID, Cust
T2: ProdID, Prod
T1T2: CustID, ProdID, Qty
On the main form, I have a combo dropdown for selecting cust.
On the subform, I display a full set of products - left outer join. If the
user enters a Qty, I would like to insert a record to T1T2 junction table.
The left outer join looks like:
select T2.ProdID as T2ID, T2.Prod, T1T2.ProdID as JuncID, T1T2.Qty from T2
Left outer join T1T2 ON T2.ProdID = T1T2.ProdID
On the subform, I have a ID filed tied to T2ID. If the record already exists
in T1T2 junction table, an update to qty works fine. However, if the record
doesn't exist, an insert causes a write conflict error.
I think the problem is that the FK of ProdID (JuncID) is null at that point
- result of left outer join. How do I solve this problem without
pre-populating T1T2 with every possible combination of CustID, ProdID.