Results 1 to 2 of 2
  1. #1
    toer121 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011

    Update query not updating records

    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL

    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));"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using an update query, but updating +2 weeks...
    By AudiA4_20T in forum Queries
    Replies: 2
    Last Post: 08-02-2011, 07:12 AM
  2. HELP for updating selected records
    By explorer19 in forum Programming
    Replies: 7
    Last Post: 06-01-2011, 01:29 PM
  3. Update query for numbering the records
    By kumar.dkr in forum Queries
    Replies: 4
    Last Post: 11-24-2010, 06:06 AM
  4. Update query for ID #s to link records
    By fspswen in forum Queries
    Replies: 0
    Last Post: 11-20-2009, 01:52 PM
  5. Return records not updated by update query
    By ars80 in forum Queries
    Replies: 2
    Last Post: 05-01-2006, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums