Results 1 to 3 of 3

Data from one table to be updated against a specific record in another table

  1. #1
    HybridOzone is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    5

    Data from one table to be updated against a specific record in another table

    Hello everyone.......
    I am very much in the process of learning both Access and associated programming. (Albeit I learn both by doing and not in any courses or schooling)
    I have already built a large database and now need some assistance with the code for a particular requirement:

    Background:
    The database is used to monitor and control customer details.
    Part of the database is specifically for asset tracking and I have already used basic code to allow inputs to be subtracted or added to the specified record thus allowing a live tracking of the particular asset "Stillages".
    I have 2 tables:
    • table 1 "CustomerT" holds all information relevant to each customer including the current quantity of stillages "StillageQty"
    • table 2 "StillageLocationT" holds the location of our 4 main storage areas and the quantity of stillages at each location "Stillages"


    LocationID Location Stillages
    1 profine UK 2000
    2 Bentons 2000
    3 Foilit 245
    4 HL Plastics 200

    What I need now is to add code to my existing code that on updating any customer StillageQty within table CustomerT it also adds or subtracts from record 1 in table StillageLocationT


    The code I am using to amend the customer StillageQty is bound to a form and its buttons as follows (when a number is typed into "StillageQtyText" and the "Delivered" button is clicked):

    Private Sub DeliveredButton_Click()
    Me.StillageQty = Me.StillageQty + Me.StillageQtyText
    Me.StillageQtyText = ""
    Me.StillageQtyText.SetFocus
    End Sub

    I know there will be a really simply line to or two to add in the above to not only update the StillageQty but also of record 1 of table StillageLocationT.....I just cannot find this simple solution!!!

    I would appreciate any assistance at all.....thanks!
    Attached Thumbnails Attached Thumbnails Capture1.PNG  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,540
    you will want to run 2 update queries:
    1 updates tbl1.Stillage from the given amt
    1 updates tbl2.StillageQty from the given amt


  3. #3
    HybridOzone is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    5
    Hi ranman
    apologies for the extremely late reply.....was travelling!

    Thanks for the assist, I managed it a totally different way by simply splitting the stillage location table into one specific for the company and the other for the various outbases.
    Each table that needs this information is linked to the related primary key of the specific table (company or outbase)
    It works exactly how I wanted it to!

    Thanks

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

Similar Threads

  1. Replies: 1
    Last Post: 02-21-2019, 02:39 PM
  2. Replies: 9
    Last Post: 07-11-2017, 06:51 AM
  3. Replies: 2
    Last Post: 04-28-2014, 12:38 PM
  4. Replies: 6
    Last Post: 11-15-2012, 04:38 PM
  5. Replies: 1
    Last Post: 10-08-2012, 11:04 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
  •  
Tech Forums: Microsoft Office Forums