vba SQL producing field not updateable

[thescottsman]

Hi All

I hope you can help me again

I have an SQL statement in vba.

UpdateKPISQL = "UPDATE tbl_ActuatorMonth " & _
"SET ['" & Me.Text129.Value & "']= '" & Me.Text125.Value & "' " & _
"WHERE [ActuatorYear]='" & Me.Text110.Value & "' And [ActuatorMonth]='" & Me.Text101.Value & "' "

txt129 is producing the correct value for the field that I want to update, txt 125 is also producing the correct value to make that field.

Can anyone see the error - run time error 3113
Cannot update "xxx"; field not updateable

txt129 is a number field - that my be a factor

 

[Minty]

You don't need extra ' around all your field names or the number field values.

If you called your controls more meaningful names it would make it easier to differentiate. txtNumField or txtCustID

UpdateKPISQL = "UPDATE tbl_ActuatorMonth " & _
"SET [" & Me.Text129.Value & "]= " & Me.Text125.Value & " " & _
"WHERE [ActuatorYear]='" & Me.Text110.Value & "' And [ActuatorMonth]='" & Me.Text101.Value & "' "

This assumes year and month are text fields

 

[JHB]

Use Debug Print and paste the output into a new query, then I think you'll find the problem.

 Debug.Print UpdateKPISQL

 

[spikepl]

SET ['" & Me.Text129.Value & "']=

and

txt129 is a number field -

The above indicates with high probability that you have enumerated fields for calling different actuators and that violates normalization.