Results 1 to 13 of 13
  1. #1
    Gerdien is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    5

    Update Query not working

    Hello,
    I'm working on a simple database for inventory.


    I have a form 'PurchaseOrder' with a subform' ' PurchaseOrderDetails'. In the subform you can chose multiple products in the same purchaseorder.
    When I want to update the quantity from the subform with an UpdateQuery in to a table called "product' under ' Quantity in'.It only updates one row and not all the rows . Someone know whats going wrong?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Need more information. How is the user selecting multiple products? Is the subform a continuous form with multiple records or a single? How are you doing the update? Post the SQL.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Can you post a jpg of your form and subform? It isn't clear from your description how you would select multiple records in the subform to update a common field in all of them.
    Also need to see the SQL of the update query.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm working on a simple database for inventory.
    I have a form 'PurchaseOrder' with a subform' ' PurchaseOrderDetails'
    Seems you would have a Supplier and/or Customer and Product tables also.

    You may get some idea from this free data model from Barry Williams.

    As others have said, more info would be helpful.


    Good luck

  5. #5
    Gerdien is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    Hi
    The SQL from the update query is this:

    UPDATE Products INNER JOIN PurchaseOrderDetails ON Products.ProductCode = PurchaseOrderDetails.ProductCode SET Products.Received = [Received]+[Forms]![New Purchaseorder]![Purchaseorder Subform]![Quantity]
    WHERE (((Products.ProductCode)=[Forms]![New Purchaseorder]![Purchaseorder Subform]![ProductCode]));

    Click image for larger version. 

Name:	Purchaseorder.jpg 
Views:	14 
Size:	52.5 KB 
ID:	29194 Click image for larger version. 

Name:	Purchaseordersubform.jpg 
Views:	14 
Size:	49.7 KB 
ID:	29195

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show us your tables and relationships as a jpg.
    Also, as mentioned earlier, you haven't discussed the source/supplier nor recipient/Customer in your set up.
    A clear, simple description of your business processes would be helpful to you and to readers.

  7. #7
    Gerdien is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    Sorry, here is a picture of the relationship diagram. I update it by using a macro and a button.
    As you can see in the jpg. there is a table for supplier, employees, products, purchase order and purchaseorderdetails to get the information from for the purchaseorder.

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	13 
Size:	87.8 KB 
ID:	29199

  8. #8
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    When you say "It only updates 1 row and not all three Rows" What do you mean by this? What three rows are you talking about. Are you using three as an example of how many different purchase details a sample would purchase?

  9. #9
    Gerdien is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    Yes, I tried 3 examples. So for example in one purchase order, I order 3 different products. I like to update the quantities of the three different products in the table 'Products' in Received from the belonging product.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Remove the join to purchase order details table

  11. #11
    Gerdien is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    when I remove the join, it needs a parameter for quantity and product code

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The join is between two tables, the values are coming from the form - two totally separate objects. This is your problem. Remove the join and fix the values coming from the forms, Access is not recognizing those values. The form isn't open, or one of the names are incorrect.

    To test the form values, create a query and copy and paste those two form fields into it as select fields so that when you run the query it displays what they contain.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Regarding quantity and price, you may get some ideas from this older post

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

Similar Threads

  1. UPDATE query not working
    By BrockWade in forum Queries
    Replies: 5
    Last Post: 02-10-2014, 04:25 PM
  2. Replies: 17
    Last Post: 12-23-2013, 11:22 AM
  3. Update Query Not Working Properly !
    By hamxa7 in forum Queries
    Replies: 3
    Last Post: 09-20-2012, 04:58 PM
  4. Update Inner Joing Query not working??
    By snowmman99 in forum Access
    Replies: 2
    Last Post: 09-08-2010, 12:15 PM
  5. IIf Update Query working OK but a better way?
    By dreamweaver547 in forum Queries
    Replies: 7
    Last Post: 04-19-2010, 09:47 AM

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