Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    38

    Unanswered: Can't Update Tables

    I have a table (tblProjects) with a number of records with ProjectID as its PK. I have another table (tbllCMS) from a different database which i imported in my test DB.

    I would like to select a few records from tblCMS and update mapped fields in tblProjects. To restrict this update to only selected records, I identified records in tblCMS by populating field consID with the same number as is the projectid in the tblProjects. This way in my query when I add these two tables, I can link these togather to get right number of records. So my SQL appears like this.

    UPDATE tblCMS INNER JOIN tblProjects ON tblCMS.ConsID = tblProjects.ProjectID SET tblProjects.PSTartDate = [tblCMS]![PSTartDate], tblProjects.ProjectNo = [tblCMS]![ProjNo], tblProjects.PM = [tblCMS]![PM], tblProjects.RefrigEngr = [tblCMS]![RefrigEngr], tblProjects.ElectEngr = [tblCMS]![ElectEngr], tblProjects.SiteStart1 = [tblCMS]![SiteStart];

    There are three fields I would like to update.

    I have come across the issue that when I update, I get the message that x number of records are being updated but in reality nothing gets updated. To investigate this further, I changed the update query to select query and noticed that the tblCMS fields I am trying to use to update do not show any data. But as soon as I remove tblProjects from the grid, I can see the data in these fields.

    I have never come across this issue before and would like some help. Copy of DB is attched.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You got me on this one. I see no problem in the database, therefore the problem must be in your head!

    I changed the update query to select query and noticed that the tblCMS fields I am trying to use to update do not show any data.
    Only fields from the project table are actually included in the query, so just changing to a select query won't even be looking in the right table.

    I just blanked out your grid, added a few fields from each table, changed it to a select query and looked. Data was present in all fields. I changed to an update query and run, it worked fine. There's no problem.

    Mind you, I can't type tblCMS to save my life, it always comes out tblcmd
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2006
    Posts
    38
    Thanks mate. There is no issue in creating a select query. I merely used it to see if I could see tables from both tables which I can't. Would you be able to upload the example where you populated tblProjects fields with relevant fields from tblCMS. For the life of me, I cannot update more than one field (projectno).

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Done.

    Hope this helps you find the answer you are looking for!
    Attached Files Attached Files
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •