CODEWITHSUNDEEP
sqlms-access
Tombo
Tombo

Reputation: 1

Syntax error with UPDATE statement in MS-ACCESS VBA

I am new to MS-ACCESS and SQL. I am trying to update a field in a linked SQL table but I keep getting

3075 Syntax Error.

PartData is the linked table What am I missing?

Private Sub REVISE_Click()
On Error GoTo REVISE_Click_Err
'
'***** Define Variables *****
Dim strSQL1 As String 
Dim strSQL2 As String
'
'***** Set Default Values *****
strSQL1 = "Delete * From Inspector"
strSQL2 = "UPDATE PartData SET SpecialNote = " & SpecNotes & " WHERE PartNo= '" & PartNumberSearch & "'"
'
'***** Clear Inspector Values ***** 
CurrentDb.Execute strSQL1, dbFailOnError 
CurrentDb.Execute strSQL2, dbFailOnError
'
DoCmd.Quit acPrompt 
'
REVISE_Click_Exit:
   Exit Sub 
'
REVISE_Click_Err:
   MsgBox Error$ 
   Resume EXIT_Click_Exit
'
End Sub

Upvotes: 0

Views: 91

Answers (2)

Tombo
Tombo

Reputation: 1

Patrick, Sorry, still learning. Please see code below.

Private Sub REVISE_Click()
On Error GoTo REVISE_Click_Err
'
'***** Define Variables *****
Dim strSQL1 As String 
Dim strSQL2 As String
'
'***** Set Default Values *****
strSQL1 = "Delete * From Inspector"
strSQL2 = "UPDATE PartData SET SpecialNote = " & SpecNotes & " WHERE PartNo= '" & PartNumberSearch & "'"
'
'***** Clear Inspector Values ***** 
CurrentDb.Execute strSQL1, dbFailOnError 
CurrentDb.Execute strSQL2, dbFailOnError
'
DoCmd.Quit acPrompt 
'
REVISE_Click_Exit:
   Exit Sub 
'
REVISE_Click_Err:
   MsgBox Error$ 
   Resume EXIT_Click_Exit
'
End Sub

Upvotes: 0

iDevlop
iDevlop

Reputation: 25252

I guess you're building query from VBA. You then have a quote issue, since SpecNotes is obviously a string. Try this: 

strSQL2 = "UPDATE PartData SET SpecialNote = '" & SpecNotes & "' WHERE PartNo= '" & PartNumberSearch & "'"

I also think that you could improve your query by using parameters, but that could be another question, and you should then show a bit more of your code, so we can see the context. Note that, without parameters, strSQL2 could produce unexpected results if SpecNote contains quotes.

Upvotes: 1

Related Questions