Update Query not returning results

[JamieF]

Sorry if I've missed something obvious, but I'm trying to do a really basic update query in Access 2007, and I can't get it to work.

UPDATE DISC INNER JOIN DISC2 ON DISC.DiscID = DISC2.DiscID SET DISC.Filename = [DISC2].[Filename];

The Filename field in DISC is null throughout. Some of the values (62 records) in DISC2 are populated. Other than this, both tables are identical.

I'm trying to get the DISC2 values into DISC. The results grid shows just one record which is blank.

I've tried this 4 or 5 different ways. Thanks for any suggestions.

[FishVal]

Hi, JamieF.

Try to run simple SELECT query on the same table join and check whether the records are being combined as you are expecting.

Regards,
Fish

[JamieF]

Hi Fish

Thanks for the reply. Yes they are. Obviously a Select query needs to be a little different, but the following combines the tables as expected:

SELECT DISC.[Disc Num], DISC2.FileName
FROM DISC INNER JOIN DISC2 ON DISC.DiscID = DISC2.DiscID;

[FishVal]

Hi Fish

Thanks for the reply. Yes they are. Obviously a Select query needs to be a little different, but the following combines the tables as expected:

SELECT DISC.[Disc Num], DISC2.FileName
FROM DISC INNER JOIN DISC2 ON DISC.DiscID = DISC2.DiscID;

Hi, JamieF.

That means UPDATE query is supposed to run on a desired records.
The only reason for the fault I could figure so far is possible key/index violations.
Access warns user about such kind of problems unless these warnings were accidentally disabled. Run in VBA immediate window

DoCmd.SetWarnings True
 

and then try to run the query. Check for possible Access warnings.

Regards,
Fish

[JamieF]

Hi Fish

Okay, I think this is just a simple misunderstanding on my part. When I do View (datasheet), none of the replacement values are shown (just the old blank values). When I Run the query, it does seem to be working.

Thanks for your help.

Jamie