We help IT Professionals succeed at work.

automatic update field value in one table based on other table field value automatically

nivasnet
nivasnet asked
on
Dear all,
if user open frm_attend form (attend table) and change field status value to 'close' same thing can updtate automatically in another table (tbl_faults table) for related reference id field status.to 'close'. is it posiible in background i mean automatically>
both tables i am using reference id field, and Status field but user only one place he is updating... so i want to update same thing in backgroud automatically.so that i can use my tables for achieving other tasks without much code.
Comment
Watch Question

Top Expert 2016

Commented:
you need to write an update query to do that, in VBA.
you can use the Forms after update event or other event to execute the the vba codes.

if possible, why not create a form based on tbl_faults and make it a subform of the form frm_attend

Author

Commented:
no sir i already created forms...
please give code for vba...for afterupdate...
is it possible instead of form update i want use that particular field afterupdate.
my field names are
cbo_statusattend  this is combo box user will change here.
same thing i want to update in the field cbo_statusfaulat
form name frm_attend
Top Expert 2016
Commented:
private sub cbo_statusattend_afterupdate()

if me.cbo_statusattend="Close" then
  currentdb.execute "update tbl_faults set status='Close' where [reference id]=" & me.[reference id]

end if

end sub


'change me.[reference id] with the actual name of control in the form

Author

Commented:
it is not updating....

here is the code...
Private Sub cbo_ActionStatus_AfterUpdate()
If Me.ActionStatus = "CLOSE" Then
CurrentDb.Execute "update tbl_Requests set status='Close' where [refid]=" & Me.[RefID]
End If
End Sub
Top Expert 2016

Commented:
what is the rowsource of combo ActionStatus?

Author

Commented:
this is the rowsource....
SELECT [tbl_StatusCodes].[StatusID] FROM [tbl_StatusCodes]
Top Expert 2016

Commented:
what are the values from this table/field [tbl_StatusCodes].[StatusID]

Author

Commented:
"OPEN"  and "CLOSE"

Top Expert 2016

Commented:
can you upload your db

Author

Commented:
sir,
Please check jpg file for fields location
screen1.JPG
status.mdb
Top Expert 2016

Commented:
in the form frm_Actions, you don't have a field/control named RefID, it is RequestID
the status is CLOSED not CLOSE. pls, read carefully..
status.mdb

Author

Commented:
pease check my other question