CODEWITHSUNDEEP
ms-accessvba
Darius
Darius

Reputation: 489

Error VB DoCmd.RunSQL Update

I am trying to run an update query in VB in Access and I get a syntax error in the Update statement. Can anyone help please?

Sub Update_Project()
Dim ProjectID, ProjectName, ProjectManager, sql_text as string

ProjectID = Wkbk.Sheets(1).Range("C5").Value

ProjectName = Wkbk.Sheets(1).Range("D5").Value
ProjectManager = Wkbk.Sheets(1).Range("E5").Value

sql_text = "UPDATE Projects (set ProjName, ProjManager) = '" & ProjectName & "," & ProjectManager & "' where ProgrammeID = " & ProjectID
DoCmd.RunSQL(sql_text)
End Sub

I think he error may be in the concatenation of the fields to update, but I can't work it out.

Thanks!

Upvotes: 0

Views: 2297

Answers (1)

PaulFrancis
PaulFrancis

Reputation: 5819

You have the syntax of the UPDATE statement muddled up. Also you need to explicitly declare variables that will save you a lot of hassle. The following is what you need.

Sub Update_Project()
    Dim ProjectID As Long, ProjectName As String, ProjectManager As String, sql_text As String

    ProjectID = Wkbk.Sheets(1).Range("C5").Value

    ProjectName = Wkbk.Sheets(1).Range("D5").Value
    ProjectManager = Wkbk.Sheets(1).Range("E5").Value

    sql_text = "UPDATE Projects SET ProjName = '" & ProjectName & "', ProjManager = '" & ProjectManager & _
               "' WHERE ProgrammeID = " & ProjectID
    DoCmd.RunSQL sql_text
End Sub

Upvotes: 2

Related Questions