Results 1 to 9 of 9
  1. #1
    FabioW is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2018
    Posts
    4

    Updating two fields in two different tables from one fold in a form

    Hi guys. I am sure it is easy and someone allerwdy asked for it, but I just can't Google solution for it.


    I have a table and a form to register what is happening witch chemicals - TblChemRegister. And there are informational like: Date, Employee (lookup from another table), Chemical (lookup from TblChemType) quantity that was used (ChemUsage), and a comment what was it used for.
    The goal is that when usage is registered (John took 5l of paint), then field ChemQuantity in table TblChemType should be updated (paint - 5).
    I would like to do it by some simple VBA code. The one that I tried to use does not work.
    ChemType!ChemQuantity = ChemType!ChemQuantity + ChemRegister!ChemUsageCan you guys help me please.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Aggregate data, such as inventory balances, should not be saved - they should be calculated when needed. Enter transaction records (product received, product used) and calculate the balance. Review http://allenbrowne.com/AppInventory.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    FabioW is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2018
    Posts
    4
    And if I would like to do it my way?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Serious risk of data getting screwed up.

    Would need to know a lot more about your db structure and form design. If you want to provide, follow instructions at bottom of my post.

    You reference table names TblChemType and ChemType - which is it?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    FabioW is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2018
    Posts
    4
    I don't need it to be very accurate. Some chemicals are in very big containers and you don't really know how much did you use. That's why I have to go once a month and check the stock. At the moment I have two tables.
    TblChemType which contains fields like:
    Id
    ChemType -which is just the name of Chemical
    ChemQuantity

    At the moment there is no form for this table, but there will be and it I will use Ito create new chemicals and changing stock levels.

    TblChemRegister which contains fields like:
    Id
    ChemType -lookup from TblChemType
    ChemUsage

    The form for this table have the same names and, fields have the same names as columns in the table. Additional there are two buttons "in" and "out". "Out" changes ChemUsage value to negative.

    The only thing I am missing is to update ChemQuantity in TblChemType after filling up the TblChemRegister form.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Again, if you are creating a transaction record in TblChemRegister, storing ChemQuantity is not needed and at risk of distorting data.


    However, if you must, consider something like:

    Your two IN and OUT buttons should be part of an Option Group then code references the Option Group control (name it optSign). Should be saving the ID from TblChemType as foreign key into TblChemRegister

    If Me.NewRecord Then
    CurrentDb.Execute "UPDATE TblChemType SET ChemQuantity = ChemQuantity + " & Me!ChemUsage * IIf(optSign=1,1,-1) & " WHERE ID=" & Me.ChemType
    End If

    Now the real trick is figuring out which event to put this code into. Perhaps the form AfterUpdate.


    A better name than ChemUsage may be ChemInOut.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    FabioW is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2018
    Posts
    4
    I Have fixed it, I simply added the field ChemQuantity from TblChemType to the TblChemRegister form and the simple:
    Me.ChemQuantity=Me.ChemQuantity + Me.ChemUsage
    worked just fine


    Cheers

  8. #8
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035

    inventory balances

    Quote Originally Posted by June7 View Post
    Aggregate data, such as inventory balances, should not be saved - they should be calculated when needed. Enter transaction records (product received, product used) and calculate the balance. Review http://allenbrowne.com/AppInventory.html
    Hi, I worked with a lot of warehouse systems in Oracle/SQL even way back AS400 and each professional system I worked with (SAP, NAVISION or other ERP systems) DOES use inventory tables together with transaction tables and in the most cases they even have inventory history tables. Otherwise the system gets to slow and you run the risk that altering or deleting a transaction record screws up your current inventory. So an inventory table stating the current situation speeds up the system and makes it a lot more stable.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, I should have qualified statement with 'usually', and the Allen Browne link also describes when it is justified to save, for the same reason you provided. Just do it very carefully.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Updating 2 tables from a single form
    By jpm in forum Forms
    Replies: 1
    Last Post: 06-17-2016, 03:50 AM
  2. Replies: 1
    Last Post: 10-19-2014, 03:51 PM
  3. Updating Multiple Tables Via One Form
    By JoshuaRogers in forum Forms
    Replies: 2
    Last Post: 03-14-2012, 11:17 AM
  4. Updating 4 Tables From 1 Form
    By kevsim in forum Forms
    Replies: 2
    Last Post: 11-15-2010, 03:08 AM
  5. Auto-updating fields in linked tables?
    By Leelers in forum Database Design
    Replies: 27
    Last Post: 01-08-2010, 06:23 PM

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