Hello!
Problem
A key violation error when appending the result of a query into a subform.
Overview
The purpose of the database is to create and manage market lists for a multi-unit restaurante group. Split into two parts:
1. Product Definition (where all products available for purchase are inserted into the database, from which a Master List is built).
2. Market List (for each restaurant, products are selected from the Master List, from which the Market List is built)
Tables / Relationships
Screenshot below:
Product Definition (upper set of 4 related tables in the screenshot above)
1. For each product (i.e. White Sugar), there may exist many specifications (i.e. Brand 1, Brand 2, etc.).
2. For each specification (i.e. Brand 1, Brand 2, etc.), there may exist many suppliers and their purchase conditions (i.e. Supplier 1 > 1kg Sacks > 10 Euros / kg, Supplier 2 > 5kg Sacks > 48 Euros / sack, etc.).
3. For each purchase conditions (i.e. Supplier 1 > 1kg Sacks > 10 Euros / kg, Supplier 2 > 5kg Sacks > 48 Euros / sack, etc.), there may exist conversion factors to convert the purchase unit to usage units (i.e. 1kg Sacks > 1kg > factor 1, 5kg Sacks > 1kg > factor 0.2, etc.).
Market Definition (lower set of 2 related tables in the screenshot above)
1. For each restaurant, there may exist a selection of products from the Master List, that will make up the restaurants Market List.
Forms
Screenshot below (very rough draft):
1. Main form: tblMarketList (pkMarketListID) > For selecting the restaurant.
2. Sub form (left side): tblMarketSelection (fkMarketListID) > For the list of all products selected that the restaurant can purchase.
3. The sub form on the right is based on a query that lists all products available in the market, that can be selected for each restaurant. In the screenshot the list is filtered for Sugar, and a specific brand, that generated 3 results.
a. Clicking on the button on the right-subform would copy that record to the left subform.
b. Clicking on the button on the left-subform, would delete the record. For now the buttons are not configured.
Problem
While the record is selected on the right-subform (as it is in the screenshot), I run the query without the append, the query correctly shows the record i want to copy over to the other subform.
When I add the append instructions to the query, I get the key violation error. The append query in design view:
And the append query in SQL view:
Code:
INSERT INTO tblMarketSelection ( strProduct, strSpecification, strSupplier, strSupplierCode, strPurchaseUnit, curPurchasePrice, dtmPriceDate, strFinalUnit, dblFactor )
SELECT qryMasterList.strProduct, qryMasterList.strSpecification, qryMasterList.strSupplier, qryMasterList.strSupplierCode, qryMasterList.strPurchaseUnit, qryMasterList.curPurchasePrice, qryMasterList.dtmPriceDate, qryMasterList.strFinalUnit, qryMasterList.dblFactor
FROM qryMasterList
WHERE (((qryMasterList.pkProductID)=Forms!frmMarketList!frmMasterList.Form!pkProductID) And ((qryMasterList.pkSpecificationID)=Forms!frmMarketList!frmMasterList.Form!pkSpecificationID) And ((qryMasterList.pkSupplierID)=Forms!frmMarketList!frmMasterList.Form!pkSupplierID));
I am not certain, but I think the problem might have to do with the fact that I am trying to append to a subform (subtable?) and its fkMarketListID field, since its the only field not addressed in the Append Query.
I would appreciate anyones help to help me resolve this.
Thank you in advance!