• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

How update field in separate table when value in form changes

I have a form which has tblPartsMasters as its record source.  When the vale in one of the fields on the form changes I want that value written to a field in a separate table.  Basically this is what I'm trying to accomplish.  I need help figuring out what the after update event of the field on the form should be...

On afterupdate event of field “txtPatt_Loc” (control source is [Patt_Loc] in “tblPartsMasters”) on “frmJobCards” update
The field [Patt_Loc] in “tblOpenOrders”
Where
[Part_No] = “txtPartN” on “frmJobCards”

--Steve
0
SteveL13
Asked:
SteveL13
  • 5
  • 4
1 Solution
 
mbizupCommented:
In the After Update event:

DIM strSQL as String
strSQL =  "UPDATE tblOpenOrders SET [Patt_Loc] = " & chr(34) & Me.txtPatt_Loc & chr (34) & " WHERE [Part_No] = " & chr(34) Me.txtPartN & chr(34)
CurrentDB.Execute strSQL, dbFailOnError

Open in new window


I'm assuming that both Patt_Loc and Part_No are text.  If either is numeric, drop the surrounding chr(34)'s
0
 
SteveL13Author Commented:
Yes, both fields are text.  But as usual, I forgot to mention one thing.  I am sorry.  When the update happens it needs to update all records in the tblOpenOrders where the Part_No is the same AND a Yes/No field named "Complete" is false.

Can you help a little more?
0
 
mbizupCommented:
Just add it on to the WHERE clause:


DIM strSQL as String
strSQL =  "UPDATE tblOpenOrders SET [Patt_Loc] = " & chr(34) & Me.txtPatt_Loc & chr (34) & " WHERE [Part_No] = " & chr(34) Me.txtPartN & chr(34) & " AND Complete = 0 "
CurrentDB.Execute strSQL, dbFailOnError

Open in new window



Alternatively:

DIM strSQL as String
strSQL =  "UPDATE tblOpenOrders SET [Patt_Loc] = " & chr(34) & Me.txtPatt_Loc & chr (34) & " WHERE [Part_No] = " & chr(34) Me.txtPartN & chr(34) & " AND Complete = FALSE "
CurrentDB.Execute strSQL, dbFailOnError

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SteveL13Author Commented:
When I copy/paste this line...

strSQL =  "UPDATE tblOpenOrders SET [Patt_Loc] = " & chr(34) & Me.txtPatt_Loc & chr (34) & " WHERE [Part_No] = " & chr(34) Me.txtPartN & chr(34) & " AND Complete = FALSE "

it turns red.

??
0
 
SteveL13Author Commented:
A message pops up...
Compile Error: Expected: End of statement
0
 
mbizupCommented:
It should all be on one line...
0
 
mbizupCommented:
Ooops - It was missing an ampersand:


    strSQL = "UPDATE tblOpenOrders SET [Patt_Loc] = " & Chr(34) & Me.txtPatt_Loc & Chr(34) & " WHERE [Part_No] = " & Chr(34) & Me.txtPartN & Chr(34) & " AND Complete = FALSE "

Open in new window

0
 
SteveL13Author Commented:
Perfect.  Thanks!
0
 
mbizupCommented:
:)

Glad to help
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now