UPDATE with WHERE conditions reference to fields in other table

[Joe Y]

In my access database there is a transaction table named “T_Inv_Transaction” that has records of ingredient quantity generated when a Job_ID is created.

The fields in this table related to my question includes followings. There is no primary key assigned to this table.

Job_ID
ProdID
ProdV
BatchQTY
UOM

Each time when user hits a production button, a Job_ID is created by program. BatchQTY and UOM associated to specific Job_ID, ProdID, and ProdV are inserted into this table for tracking purpose.

The next step I need to develop is to update inventory in table “T_Inventory”. Fields in this table include followings with two primary keys: Prod_ID and Prod_V. This inventory table serves as storage place for ingredient quantity and get updated every time when a batch of recipe is produced.

Prod_ID
Prod_V
Qty
UOM

I am stuck with the UPDATE query statement. Is there any way the UPDATE with WHERE condition statement can reference to fields in other table? In this case, update quantity (Qty) in inventory table (with certain math) where Prod_ID and Prod_V equal to fields ProdID and ProdV in transaction table and with specific group of Job_ID?

[Frinavale]

You should be able to accomplish this by joining on the two tables.

Here is a rough example:

UPDATE T_Inventory
SET ....
FROM T_Inventory JOIN T_Inv_Transaction ON T_Inv_Transaction.Prod_ID = T_Inventory.ProdID 
WHERE ...put your condition here
 

[Joe Y]

Thanks Frinavale. I will try this approach. I am not a VBA expert so it will take a while to tell results.

[NeoPa]

That shouldn't be a problem Joe, nor is Frinavale. This is SQL code not VBA. She's great at SQL. Not so much at VBA ;-)

[NeoPa]

Actually, it's interesting that SQL is a generic standard, but not all implementations of SQL are the same (or indeed fully standard).

In this case there actually is a problem with using Frinny's SQL in Access as Access uses the Jet SQL engine which has a slightly different syntax when dealing with linked tables within an UPDATE query.

As a general rule you should find the full syntax by following the instructions in Finding Jet SQL Help. In this particular case the linked tables must be defined within the UPDATE clause itself in order to get Access (Jet) to follow the instructions. The Help on this particular feature is poor (It isn't properly covered except for individual tables :-( ) but I was able to find the syntax from some old queries I'd done in the past.

So, Frinny's suggestion, for when using Jet SQL, would be :

UPDATE T_Inventory 
       INNER JOIN
       T_Inv_Transaction
    ON T_Inv_Transaction.Prod_ID = T_Inventory.ProdID
SET ....
WHERE ...put your condition here

Another restriction within Jet SQL is that updates will only work if the underlying recordset is updatable. That is more restrictive in Jet/Access than in most other flavours of SQL. See Reasons for a Query to be Non-Updatable for more on that.

[Joe Y]

It works! However, I had to put the query in a string and execute the query with DoCmd.RunSQL in order to make it work.

Somehow, when I put the update statement directly in the VBA, Access keep popping up error message asking for “expect end statement”.
Below is the codes that worked for this case.

Thanks to both Frinavale and NeoPa for concept and VBA helps.

Dim InvSQL As String
Dim strJID As String
 
strJID = Me.txtJob_ID
 
InvSQL = "UPDATE [T_Inventory] INNER JOIN [T_Inv_Transaction] " & _
"ON ([T_Inventory].[Prod_V] = [T_Inv_Transaction].[ProdV]) AND ([T_Inventory].[Prod_ID] = [T_Inv_Transaction].[ProdID]) " & _
"SET [T_Inventory].[Qty] = NZ([T_Inventory].[Qty]) - [T_Inv_Transaction].[BatchQTY], “ & _ 
“[T_Inventory].[UOM] = [T_Inv_Transaction].[UOM] " & _
"WHERE [T_Inv_Transaction].[Job_ID] =" & " '" & strJID & "' "
 
DoCmd.RunSQL InvSQL

[Frinavale]

Glad it worked out for you!


And this is a reminder of why I tend to shy away from Access questions...the SQL I know and work with daily doesn't apply!

-Frinny

[NeoPa]

You know you're always welcome to post here Frinny. Someone will generally pick up if there are syntax variations for Jet/ACE.

Joe Y:
It works! However, I had to put the query in a string and execute the query with DoCmd.RunSQL in order to make it work.

Exactly. This is SQL. The VBA interpreter won't handle SQL code as it's SQL. It handles VBA code only.

The normal way of executing SQL from within your VBA code is to prepare a string with the SQL code in it and pass it to either the DoCmd.RunSQL() or {DatabaseObject}.Execute() methods. The former prompts the user depending on your settings. The latter doesn't. A {DatabaseObject} can be retrieved for the current database by running the function CurrentDb().

[Joe Y]

NeoPa,

You are right. I am evaluating if the prompt from Access of "## record updated, Yes/No" is a good or bad thing to user.

Thanks,
Joe

[Joe Y]

Frinny,

You guided me to the right direction of using Join. The prompt response and helps is why I like to ask question in this forum.

Much appreciated!

[NeoPa]

Joe Y:
You are right. I am evaluating if the prompt from Access of "## record updated, Yes/No" is a good or bad thing to user.

Yes. I'm generally right one way or another Joe (j/k).

When using the {DatabaseObject}.Execute() method you have access to that information via the {DatabaseObject}.RecordsAffected property. That then becomes available to you as a value within your VBA code. Display or not display - You have that choice. It won't naturally give the user an opportunity to proceed or turn back though. You could do that with a Transaction of course, but that would be a whole 'nother question.