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

 

[J_Goddard via AccessMonster.com]

Hi -

I don't see anything that looks wrong, but if HoldWeekNo or HoldCMYear are
blank (Null), you will get a syntax error. After the strQuery = ....
statement, put Debug.print strQuery, which will put print your SQL to the
immediate pane, which you view with ^G. The SQL error should be apparent.

strQuery = "Update ....
debug.print strQuery

John

 

[J_Goddard via AccessMonster.com]

Oops - see my post in the Modules / VBA board.

You are missing a "&" at the end of line 2:

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

John

 

[J_Goddard via AccessMonster.com]

And that of course should read lines 2 and 3 :

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

It's late - time to go home!

John

 

[Opal]

Thank you!!!