SQL Update statement (1 Viewer)

[astarbyfar]

Has anyone any idea what is up with the following code. What I want it to do is update the record which is identified on the form by the text box called "linked". Im getting an error message saying:

Run time error 3075

Syntax errot (missing operator) in query expression 'PSM Code=1000'

which is the correct value stored in the text box named linked. The full code is:

Private Sub ExitForm_Click()

DoCmd.RunSQL "UPDATE Location settings SET location1 = " & [Forms]![View die locations]![location1] & " " & ", location2 = " & [Forms]![View die locations]![location2] & " WHERE PSM Code = " & [Forms]![View die locations]![linked] & ";"

DoCmd.RunMacro "View.welcome"

End Sub

Any help will greatly be appreciated.

Regards Mike

 

[Sergeant]

If all the fields are type = string:

DoCmd.RunSQL "UPDATE [Location settings] SET location1 = '" & [Forms]![View die locations]![location1] & "', location2 = '" & [Forms]![View die locations]![location2] & "' WHERE PSM Code = '" & [Forms]![View die locations]![linked] & "'"

If all fields are type = number:

DoCmd.RunSQL "UPDATE [Location settings] SET location1 = " & [Forms]![View die locations]![location1] & ", location2 = " & [Forms]![View die locations]![location2] & " WHERE PSM Code = " & [Forms]![View die locations]![linked]

 

[astarbyfar]

THanks for the reply Sergeant, that unfortunately doesnt work. What Iv edone now is split the code to make sure all small parts are working. So what Ive done now is:

Private Sub ExitForm_Click()

Dim sqlstring As String

sqlstring = "UPDATE Location SET location1= " & [Forms]![View die locations]![location1] & " WHERE [PSM Code] = '" & linked & "'"

DoCmd.RunSQL sqlstring

DoCmd.RunMacro "View.welcome"
End Sub

Now, when I click the button I get a prompt text box identical to the prompt for a query parameter which is titled 'tr' (this is the value in the text box called "location1". If I enter tr into this then the update is done, however i need it to do it automatically, without prompting the user to enter the value.

Any ideas?

 

[astarbyfar]

apoligies sergeant ive re-read your last post and made the changes with the single quotation marks. It is now working thanks very much

Mike

 

[igkuk7]

I think you've forgot to put quotes around location1. Since you say the value is 'tr' it must be a string and thus would need quotes around it.

Try this:

sqlstring = "UPDATE Location SET location1= """ & [Forms]![View die locations]![location1] & """ WHERE [PSM Code] = '" & linked & "'"

 

[astarbyfar]

Thanks for the reply igkuk7 the code I needed is:

Private Sub ExitForm_Click()

If Not IsNull(linked) Then

Dim sqlstring As String

sqlstring = "UPDATE Location SET location1= '" & [Forms]![View die locations]![location1] & "', location2= '" & [Forms]![View die locations]![location2] & "', location3= '" & [Forms]![View die locations]![location3] & "', location4= '" & [Forms]![View die locations]![location4] & "', location5= '" & [Forms]![View die locations]![location5] & "', location6= '" & [Forms]![View die locations]![location6] & "' WHERE [PSM Code] = '" & linked & "'"

DoCmd.RunSQL sqlstring
End If

DoCmd.RunMacro "View.welcome"
End Sub

here what it does is checks that the field "linked" is not empty and if it isnt then it does the necessary update command. The problem was I was forgetting the syntax of an update command requests that it is in quotation marks. Hence I needed a single quotation mark for the syntax and a double quotaion mark for the paramaters which are being passed from the form.

Thanks for all your input both sergeant and igkuk