Results 1 to 10 of 10
  1. #1
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11

    Key violation error appending result of query into a subform

    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:

    Click image for larger version. 

Name:	01.JPG 
Views:	16 
Size:	46.5 KB 
ID:	40169

    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):
    Click image for larger version. 

Name:	02.JPG 
Views:	16 
Size:	64.7 KB 
ID:	40170
    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.

    Click image for larger version. 

Name:	03.JPG 
Views:	16 
Size:	45.4 KB 
ID:	40171

    When I add the append instructions to the query, I get the key violation error. The append query in design view:

    Click image for larger version. 

Name:	04.JPG 
Views:	16 
Size:	85.5 KB 
ID:	40172

    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!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    First of all you append records to a table not a form/subform

    A key violation error occurs when running the query would result in duplication of values in the primary key field which isn't allowed
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    Quote Originally Posted by isladogs View Post
    First of all you append records to a table not a form/subform

    A key violation error occurs when running the query would result in duplication of values in the primary key field which isn't allowed
    Thank you for the reply.
    Yes of course, to the table underlying the subform.
    And yes I understand the definition of the key violation, but not sure where, what you describe, it is happening in my databse. Are you referring to the primary key in the table I am trying to append to? This table is currently empty. So there are no primary key values in the table to begin with, and it is only one record I am trying to append, so I dont see where the duplication is occuring, and why it would occur in this case.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It appears you are appending to all fields in tblMarketSelection except the pk and fk fields.
    Is it an auto number pk field? If not a value is required.
    As the table is linked to tblMarketList with referential integrity, the fk field is also required and must be in the other table.
    However it looks like you aren't populating that fk field so the append will fail.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    Quote Originally Posted by isladogs View Post
    It appears you are appending to all fields in tblMarketSelection except the pk and fk fields.
    Is it an auto number pk field? If not a value is required.
    As the table is linked to tblMarketList with referential integrity, the fk field is also required and must be in the other table.
    However it looks like you aren't populating that fk field so the append will fail.
    Thanks again!
    Yes, exactly. It is the tblMarketSelection table I am appending to, which has both a primary key (pkMarketSelectionID) - and a foreign key (fkMarketListID) - which are both not appended to.
    1. pkMarketSelectionID is an autonumber (long integer > increments > indexed with no duplicates).
    2. fkMarketListID is a number (long integer > indexed with duplicates allowed).
    I was assuming these fields would 'fill in' on their own, as they would if I was inserting data manually instead of using an append. So how would I get the sub-tables fkMarketListID to autopopulate so it matches the pkMarketListID of the main-table?


  6. #6
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    I figured it out!
    In the append query, I created a new calculated column:
    fkMarketListID: [Forms]![frmMarketList]![pkMarketListID]
    and appended to the fkMarketListID field in tblMarketSelection.
    Not sure if this is the best way, but it seems to work.
    Thank you once again for the input!


  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Congratulations. That's exactly what I was going to tell you...but for info there's no calculation involved. Its just taking the value from your form control.
    More importantly do you now understand why the earlier update failed?

    One other thing however. Why are you duplicating data in more than one table? That leads to data redundancy and potential errors.
    In good database design, each item of data is only stored once
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    Thank you!

    Yes I believe I understand why it failed. When performing an append query to a sub-table, that has a field that is a foreign key that links it to the main-table, I cannot assume that the foreign key will autopopulate on its own, because its not an autonumber. I have to address populating that field, as well, when running the append query. Its this, right?

    True, it does appear I am duplicating data in more than one table. But the database has two objectives:
    1. Compile a type of repository of all products available to purchase in the market, the suppliers that supply them, packaging size, and price.
    2. And then for each business unit, we can view the master list from point 1, filtering it down by product, brand, specification and packaging size, ordered by price, and select the products from that list that is most convenient for each business unit.

    If this was only 1 hotel or restaurant, there would be no need for this 'duplicating', but for a group of, lets say, 10 hotels, and 5 restaurants, spread around the country, each purchasing on average about 500+ products, where not all suppliers deliver all over the country, each hotel / restaurante a different concept and style, etc. etc. it was the easiest way I can think of to build practical market lists for each business unit.

    What would add value to this list, though, is being able to have na indication on the master list, that the product is already assigned to a business unit, but that will be the next challenge.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Your first paragraph looks correct though sub table isn't a phrase I would use.

    However you should not be duplicating data.
    Instead you should use queries that 'pull' the data required from one or more linked tables using filter criteria as required.
    No matter how many hotels and restaurants you have, that approach will work.
    What you are doing will lead to database bloat, inconsistencies between tables etc.
    Recommend you read up about normalisation of data.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Odeh Naber is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Cascais, Portugal
    Posts
    11
    Thank you for the feedback and suggestion!
    'Related-table' instead of 'sub-table'? My bad! I am an amateur programmer, but I will work on my Access terminology!

    Yes, I am always concerned about data bloat in my databases. Over the years I managed to improve this. And I have already considered that any update in data (price, packaging, etc.) in the master list / repository of this database, would be reflected in the market lists for each business unit. Master list data would be editable by the user, and market list data would not be editable to the user > in order to ensure no inconsistencies exist between the tables. But
    I will read up on 'normalisation' of data and see how I can design my database without duplication.

    Thank you once again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. displaying query result as a subform
    By uzair_rosli in forum Access
    Replies: 4
    Last Post: 02-26-2013, 04:29 AM
  2. Replies: 2
    Last Post: 12-01-2012, 07:10 AM
  3. Replies: 3
    Last Post: 10-31-2012, 12:14 PM
  4. Replies: 1
    Last Post: 10-29-2012, 02:02 PM
  5. Key violation when appending records
    By slaterino in forum Programming
    Replies: 10
    Last Post: 08-26-2010, 08:25 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums