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.
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.
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
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).