CODEWITHSUNDEEP
ms-accessms-access-2010
chrisleav
chrisleav

Reputation: 13

How to update field on one table based on field in another table in MS Access 2010

Good morning,

I'm designing a database in Access 2010 to track inventory. It specifies a bin location for each item that is tracked. Each product will always associated with one bin, and no bin will be associated with more than one product (but can be associated with 0 products). Products may move to a different bin at any given time, and I need to be able to search/filter/etc. by either bin location or product name.

Currently, I have a form that will display several different fields for one record (product) at a time. In it I've included a combo box whose control is a query that provides a list of all empty bins. This combo box will be used to select a bin location for a product, and updates the [CurrentInventory].[Bin] field. When [CurrentInventory].[Bin] is updated I would like two other tasks completed as well:

  1. Update [Bins].[AName] with the same value contained in [CurrentInventory].[AName] where the value of [Bins].[Bin] is the same as [CurrentInventory].[Bin].
  2. Clear the previous value of [Bins].[AName] in the record where [Bins].[Bin] equals the previous value of [CurrentInventory].[Bin]. This is so the previous bin will show as empty and become available for another product.

I'm not very experienced with Access yet, and have limited experience with both Macros and VB, so I appreciate any suggestions!

Upvotes: 1

Views: 16083

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

In Access 2010 (and newer) you can accomplish your objective by using an After Update event-driven data macro on the [CurrentInventory] table:

AfterUpdate.png

For more information on Data Macros, see

Create a data macro

Upvotes: 0

Related Questions