UPDATE help

[Indigo]

Help, I am running Access 2003 and have been struggling with this issue for 2 days and cannot get it resolved. I have a DB that tracks Scrapped Parts along with the Countermeasures entered by area supervisors on a weekly basis to work towards eliminating the scrap. I need to be able to update one field in Table1 from Table2 based on user selection to update.
I created the following query to show what I want to update:

SELECT DISTINCT CMGLMonth1.PartNo, CMGLMonth1.Countermeasure, CMGLMonth1.WeekNo, CMGLMonth1.CMYear, CMGLMonth1.CarryOver
FROM CMGLMonth1 INNER JOIN CMMonth1 ON CMGLMonth1.PartNo = CMMonth1.PartNo
WHERE (((CMGLMonth1.WeekNo)=[Forms]![frmMnthCMNew]![WeekNo]) AND ((CMGLMonth1.CMYear)=[Forms]![frmMnthCMNew]![Yr]) AND ((CMGLMonth1.CarryOver)=-1));

The "CarryOver" field is a check box on the form (user request) to indicate what "Countermeasures" the user wants to carry over to Table1 (CMMonth1). If I turn the above into an update query I get an error message "Operation must use an updateable query"

So I tried to use the following Sub:

Dim strQuery As String
Dim HoldWeekNo As Integer
Dim HoldCMYear As Integer
Dim HoldPartNo As String
Dim Countermeasure As String
 
    HoldWeekNo = Forms!frmMnthCMNew!WeekNo
    HoldCMYear = Forms!frmMnthCMNew!Yr
    HoldPartNo = Forms!frmMnthCMNew!subfrmCurrCMGLMonth.Form!PartNo
        strQuery = "UPDATE CMMonth " & _
                        "SET Countermeasure = '" & Countermeasure & "'" & _
                        " WHERE PartNo = '" & HoldPartNo & "'" & _
                        " AND WeekNo =" & HoldWeekNo & _
                        " AND CMYear =" & HoldCMYear
                        
  CurrentDb.Execute strQuery, dbFailOnError

But it doesn't work either..... Can anyone help me out? I am really really stumped....

 

[namliam]

Distinct will make multiple unique records into one, thus access loses "track" of what record to change. This is perfectly logical.

For the second, where is your Countermeasure beeing filled?

 

[Indigo]

Sorry, I should provide some more background. Each subform is a continuous form based on a query that only shows the Top 3 instances (based on cost) for scrapped parts. Each table has a field called "Countermeasure" that contains the information I want to update from one table to the other.

 

[namliam]

Why would you want to have the same information stored in two tables? That goes against "good' database design

 

[Indigo]

Yeah, I know, but technically, it is not the same information. The CMMonth table contains the Top 3 for the whole shop, so there is only 3 entries per week. The CMGLMonth contains the Top 3 per GL (Supervisor)
for the month, so since there are 3 Groups there are 9 entries per week. Not all Groups will have the same top 3 as the CMMonth and we want to move only specific Countermeasure information over, so the Supervisors do not have to enter things twice. Clearer?