UPDATE Statement error

[RedHeadedMonster via AccessMonster.com]

I have the following code / SQL and Im getting an "Update Statement" error.
Anyone see what Im doing wrong?

If IsNull(Me.MPANumber) Then
Me.MPA = "N"
sqlUpdateParts = "Update PartsNew " & _
"Set PartsNew.MPA = 'N' " & _
"Where PartsNew.Partnumber = ' " & Me.PARTNUMBER & " '"
Else
Me.MPA = "Y"
sqlUpdateParts = "Update PartsNew " & _
"Set PartsNew.MPA = 'Y' " & _
"Where PartsNew.Partnumber = ' " & Me.PARTNUMBER & " '"

End If


Thanx
RHM

 

[Douglas J. Steele]

Are both MPA and Partnumber text fields? Do you really have those spaces in
"Where PartsNew.Partnumber = ' " & Me.PARTNUMBER & " '"? How are you running
sqlUpdateParts? What's the exact error message you're getting?

 

[RedHeadedMonster via AccessMonster.com]

Yes both are text fields.
Yes on spaces.

Am running sqlUpdateParts, like this:

DoCmd.RunSQL sqlUpdateParts

Error message:
Run time error 3144
Syntax error UPDATE statement

RHM

Are both MPA and Partnumber text fields? Do you really have those spaces in
"Where PartsNew.Partnumber = ' " & Me.PARTNUMBER & " '"? How are you running
sqlUpdateParts? What's the exact error message you're getting?

I have the following code / SQL and Im getting an "Update Statement" error.
Anyone see what Im doing wrong?

[quoted text clipped - 14 lines]

Thanx
RHM

 

[John W. Vinson]

I have the following code / SQL and Im getting an "Update Statement" error.
Anyone see what Im doing wrong?

If IsNull(Me.MPANumber) Then
Me.MPA = "N"
sqlUpdateParts = "Update PartsNew " & _
"Set PartsNew.MPA = 'N' " & _
"Where PartsNew.Partnumber = ' " & Me.PARTNUMBER & " '"
Else
Me.MPA = "Y"
sqlUpdateParts = "Update PartsNew " & _
"Set PartsNew.MPA = 'Y' " & _
"Where PartsNew.Partnumber = ' " & Me.PARTNUMBER & " '"

End If

OK... you're defining a text string sqlUpdateParts but (at least in what
you've posted) you don't actually do anything with it. At what point do you
execute sqlUpdateParts? At what point do you get the error?

Note that you can simplify your code considerably: a single statement such as

On Error GoTo Proc_Error
sqlUpdateParts = "UPDATE PartsNew " & _
"SET PartsNew.MPA = " & IIF(IsNull(Me.MPANumber, "N", "Y") & _
"WHERE PartsNew.PartNumber = '" & Me.PARTNUMBER & "'"
CurrentDb.Execute sqlUpdateParts, dbFailOnError

Proc_Exit: Exit Sub
Proc_Error:
<do appropriate error handling>
Resume Proc_Exit

 

[Beetle]

Remove the spaces.

Also, you don't really need to write two separate queries.

Finally, you might prefer running the query using the CurrentDb.Execute
method, which bypasses the standard system warnings (which can be
redundant and annoying after a while).

Example;

If IsNull(Me.MPANumber) Then
Me.MPA = "N"
Else
Me.MPA = "Y"
End If

sqlUpdateParts = "Update PartsNew " & _
"Set PartsNew.MPA =""" & Me.MPA & _
""" Where PartsNew.Partnumber =""" & Me.PARTNUMBER & """"

CurrentDb.Execute sqlUpdateParts, dbFailOnError

--
_________

Sean Bailey

Yes both are text fields.
Yes on spaces.

Am running sqlUpdateParts, like this:

DoCmd.RunSQL sqlUpdateParts

Error message:
Run time error 3144
Syntax error UPDATE statement

RHM

Are both MPA and Partnumber text fields? Do you really have those spaces in
"Where PartsNew.Partnumber = ' " & Me.PARTNUMBER & " '"? How are you running
sqlUpdateParts? What's the exact error message you're getting?

I have the following code / SQL and Im getting an "Update Statement" error.
Anyone see what Im doing wrong?

[quoted text clipped - 14 lines]

Thanx
RHM