Syntax error with UPDATE statement in MS-ACCESS VBA

Question

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

Patrick Honorez · Accepted Answer · 2019-10-16 12:20:23Z

1

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.

answered Oct 16, 2019 at 12:20

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tombo Over a year ago

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

Patrick Honorez Over a year ago

@Tombo Can't you edit your question and include your code in it ?

Patrick Honorez · Accepted Answer · 2019-10-21 15:05:57Z

0

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

edited Oct 21, 2019 at 15:05

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

answered Oct 17, 2019 at 13:27

Tombo

1

Collectives™ on Stack Overflow

Syntax error with UPDATE statement in MS-ACCESS VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related