how to create a trigger like action

[Greg25]

how is everyone ,

I am working with access and linked to a SQL server
I created a form that can change the status of the jobs that are active here,
I would like to have the date on another table change when we change the status,

I also have a trigger on the sql server that is fired when an insert or update is done to bring back the current date, but the software i am using will not let the trigger update the field in the other table

I am hopping that i could do somthing in ACCESS because we are using it to create reports and to change the status of the jobs here's a list of the tables and fields

thanks for any help,
Greg
Table where i am getting most of the info
Order_entry The Field the trigger works is Last_cost_center_code that gives me the date back in field status_date
If i were able to pull status_date into VT(thats the software i am using ) all would be great but i cant,

the field that VT created is on order_entry_1 and field is VT_status_date

is their a way when status_date on order-entry ,(whitch the trigger is ) to update VT_status_date on order_entry_1 to the same date

 

[Aivars]

Hi!

In such case you can use recordset or Update SQL for updating of your data.

'First way:
dim strSQL as string
dim rst as recordset

strSQL = "Select LastUpdateDate From Table1 Where CommonField = " & me!CommonField & ";"

set rst=currentdb.openrecordset(strSQL)
if not rst.eof then
rst.edit
rst!LastUpdateDate = now()
rst.update
end if
rst.close
set rst=nothing

'Second way:
dim strSQL as string

strSQL = "UPDATE Table1 SET Table1.LastUpdateDate = Now() Where CommonField = " & me!CommonField & ";"

docmd.setwarnings false
docmd.runsql strSQL
docmd.setwarnings true

Aivars