One form, two tables UPDATE help?

[Opal]

I am running Access 2003. A user enters data into
one field on a continuous form bound to one table. I want to be
able to UPDATE this same data into a similar field in
another table based on an equal week number, year
and Part number for the data. Is this possible?

I have been trying something like:

Sub UpdateCM1()

Dim strQuery As String
Dim HoldWeekNo as Integer
Dim HoldYear as Integer
Dim HoldPartNo as String

HoldWeekNo = Forms!frmMnthCMNew!WeekNo
HoldYear = Forms!frmMnthCMNew!Yr
HoldPartNo = Forms!frmMnthCMNew!PartNo

strQuery = "UPDATE CMMonth " & _
"SET Countermeasure = " ??? & _
" WHERE PartNo = " & HoldPartNo & _
" AND WeekNo = " & HoldWeekNo & _
" AND Year = " & HoldYear &

CurrentDb.Execute strQuery, dbFailOnError


End Sub

I am getting stuck because the form is a
continous form based on a query showing the top 3
part numbers for that week.

I thought of writing the query for the top 3 to a temp
table and then writing everything to a perm table but that
seemed like a lot of work. Can anyone point me in the
right direction?

 

[Opal]

I have also been trying this SQL:

UPDATE CMMonth1 INNER JOIN CMGLMonth1
ON (CMMonth1.CMYear = CMGLMonth1.CMYear)
AND (CMMonth1.WeekNo = CMGLMonth1.WeekNo)
AND (CMMonth1.PartNo = CMGLMonth1.PartNo)
SET CMMonth1.PartNo = [CMGLMonth1].[PartNo],
CMMonth1.Countermeasure = [CMGLMonth1].[Countermeasure],
CMMonth1.WeekNo = [CMGLMonth1].[WeekNo],
CMMonth1.CMYear = [CMGLMonth1].[CMYear];

but it doesn't update all the Countermeasures.....
What am I missing?

 

[Chegu Tom]

I guess the key is the ??? in you code. What is the name of the control in
your current form where you are typing this information that you want copied
into CMMonth.countermeasure?

Let assume the field is called txtCM and it is a text field.
put your code int he afterupdate event of that control box

SET Countermeasure = '" & me.txtCM & "'" _

(note single quote inside of the double quotes to put quotes around you text

If txtCM is a numeric field then it would be

SET Countermeasure = " & me.txtCM & _ (without the single quotes)

 

[Opal]

Thank... still having some issues...

strQuery = "UPDATE CMMonth " & _
"SET Countermeasure = '" & txtCM & "'" _
" WHERE PartNo = '" & HoldPartNo & "'" _
" AND WeekNo = " & HoldWeekNo & _
" AND CMYear = " & HoldCMYear & _
" AND CarryOver = -1"

I am getting syntax errors and expected end of statement errors...
Not sure where I messed up....

 

[Opal]

Thank you!!!