Update query not updating records

[toer121]

Hi everyone

I'm kinda new at this but I can't find a reason for why this update query isn't updating records. I've got two unlinked tables, one lists all pupils and pupil identifiers (pupilsonroll1 table), and the other's basically the same but it's from another database and it doesn't have all the information that the first table has (I've copied the table and called it T_TestFinal). I created an update query which should update the UPN field from the T_TestFinal table using the data from the pupilsonroll table. When I run the query I get the normal box pop up stating how many records will be updated and if I want to continue, I press yes but it didn't update at all (no error messages or anything just didn't update the table) I copied the code into vba but it does the same thing when I run it from there as well. I'm at a loss at what I need to do to resolve this problem

(Oh before I forget I'm running XP SP2 and Access 2003)


Private Sub UpdateTest()



DoCmd.RunSQL _
"UPDATE T_TestFinal INNER JOIN PupilOnRoll1 ON (PupilOnRoll1.DoB = T_TestFinal.DoB) AND (T_TestFinal.Surname = PupilOnRoll1.Surname)" & _
"SET PupilOnRoll1.UPN = [T_TestFinal].[UPN]" & _
"WHERE (((T_TestFinal.UPN) Is Null));"

End Sub

Cheers Thomas

[orange]

Thomas,

You are trying to SET PupilOnRoll1.UPN to NULL--- Is that what you intended?
And you need a space between
,Surname and SET and
.UPN and Where.

With Updates involving inner joins try reversing the order of the tables. Put the table you are updating first in the Update statement.

DoCmd.RunSQL _
"UPDATE PupilOnRoll1 INNER JOIN T_TestFinal ON (PupilOnRoll1.DoB = T_TestFinal.DoB) AND (T_TestFinal.Surname = PupilOnRoll1.Surname) " & _
"SET PupilOnRoll1.UPN = [T_TestFinal].[UPN] " & _
"WHERE (((T_TestFinal.UPN) Is Null));"