CODEWITHSUNDEEP
ms-accessms-access-2010
Coloumb
Coloumb

Reputation: 35

Microsoft Access 2010: Update a field in another table on button click

Basics about the database

I am working on a (relatively) simple database that stores inventory data. I am using Microsoft Access 2010 in order to do this. I have six tables with the following relationships: Relationships of Database

I have created forms which combine the Transaction table with Ordered, Received, Allocated, or Dispensed. Each form requests an amount which will then be used to update On Hand, On Order, or Allocated (from the Material table) respectively.

The Problem

For example, my form to update Transaction and Order should be able to take in the Amount ordered, save all the data from the fields to the Transaction and Order tables as well as add the amount from Amount to On Order in the Materials table.

I have been working on this database for the past two days. I have searched several times for possible ways to perform a similar function, but have come up with nothing. All the tutorials I have found which seem remotely close to what I need to accomplish are for versions of Access which are much older than 2010. Unfortunately I have had little experience with the actual coding within Access, so I am stuck clicking around within the buttons on its menus.

What I have tried

Currently, the program is set to run the following Update query: Screenshot of update query

This query works if I have one Material stored in the database but adds all the Amount values from Ordered to On Order every time it is ran, which is unfortunately not what I need it to do. I only need each Amount value added to On Order once.

Upvotes: 2

Views: 8779

Answers (1)

ron tornambe
ron tornambe

Reputation: 10780

You need to relate the Ordered and Material tables by adding a foreign key field to the Material table, ex. OrderedFK (Long Integer). This new field must be updated whenever a row is inserted into the Ordered table (assuming the "No" Field is AutoNumber). This is typically performed by using a Form (Ordered) and Sub-Form (Material) and setting the sub-form' Link Master (No) and Link Child fields (OrderedFK).

You can then join the Ordered and Material tables on the Update Query to achieve the desired result.

Upvotes: 1

Related Questions