Update another table when field is changed

Question

I have a form contains "DateProduced" field. The table bound to it is called "Report".

I try to add after update event to this field and want this event to update "DateProduced" field in Quantity table if ID matches for both.

Me![Text0] displays the ID from Report field

Me![Text4] displays the DateProduced from Report field.

The event code is as below.

Private Sub Text4_AfterUpdate()
Dim strSQL As String

strSQL = "UPDATE Quantity " _
& "SET [DateProduced] = (#" & Me![Text4] & "#) " _
& "WHERE ID = (" & Me![Text0] & ")"

DoCmd.RunSQL strSQL
End Sub

But i can not succeed.

This seems pretty similar to http://stackoverflow.com/questions/28747227/update-linked-fields-in-access. Also, you have not included any error messages or your version of MS Access. — Fionnuala, Feb 27 '15 at 09:20
I'm using Access 2003 and that gives me no error messages and do not update the table — Ömer Buluş, Feb 27 '15 at 09:21
In that case, step though, print strSQL to the immediate window, and test it in query design. I suspect the problem is here : `& "WHERE ID = (" & Me![Text0] & ")"`, for one, why the brackets? — Fionnuala, Feb 27 '15 at 09:24
Also, you are very likely to run into locale problems on date `(#" & Me![Text4] & "#) "`. Try `SET [DateProduced] = #" & Format(Me![Text4],"yyyy/mm/dd") & "# "` — Fionnuala, Feb 27 '15 at 09:26
it says "run-time error '3075' syntax error in date in query expression" — Ömer Buluş, Feb 27 '15 at 09:38
What sql did you use? Did you test it in the query design window as I suggested above? Do you know the data types of the fields? Also see http://stackoverflow.com/a/14064314/2548 error 3075 is misreported in your omment — Fionnuala, Feb 27 '15 at 09:42
I am using Access 2003. yes i tested it. Type of fields matches. — Ömer Buluş, Feb 27 '15 at 10:20

score 0 · Answer 1 · answered Feb 27 '15 at 14:22

That date format will fail for values where dd/mm can be interchanged for a valid date. It should read:

Private Sub Text4_Exit(Cancel As Integer)

    Dim strSQL As String

    strSQL = "UPDATE Quantity " _
    & "SET [DateProduced] = #" & Format(Me!Text4.Value, "yyyy\/mm\/dd") & "# " _
    & "WHERE [ID] = " & Me![Text0].Value & ";"

    DoCmd.RunSQL strSQL

End Sub

A note: You should change the names of Text0 etc. to meaningful names.

score -1 · Accepted Answer · answered Feb 27 '15 at 10:43

I made it work with the following code. Thx

Private Sub Text4_Exit(Cancel As Integer)
Dim strSQL As String
strSQL = "UPDATE Quantity " _
& "SET [DateProduced] = #" & Format(Me.Text4.Value, "dd-mm-yyyy") & "#" _
& "WHERE [ID] = (" & Me![Text0] & ");"

DoCmd.RunSQL strSQL

End Sub

Update another table when field is changed

2 Answers2