I have an error in an afterupdate query on a cbo to populate tblProjects.Buy_CP to the value on Me.cboBuy_CP. I think my problem is that Buy_CP is text and the single quote double quote is not correct in the below.
CurrentDb.Execute "UPDATE tblProjects SET Buy_CP='" & Me.cboBuy_CP.Column(1) & "' WHERE ID=" & Me.ID
It's always a good idea to create an extra SQL variable, but when you think the error comes from your UPDATE, then you need to look at your data. Cause your way of SQL string concatenation is vulnerable for SQL injection, thus it may fail due to single quotes in your data. Also as your ID seems to be numeric, enforcing this by using CLng() can help you to spot errors in data. And you need error handling with On [Local] Error GoTo LocalError (see attached file).
Dim Sql As StringSql = "UPDATE tblProjects " & _ "SET Buy_CP = '" & Replace(Me.cboBuy_CP.Column(1), "'", "''") & "' WHERE ID = " & CLng(Me.ID) & ";"Debug.Print SqlCurrentDb.Execute Sql
Private Sub YourMethod() Const MSG_UPDATE_ERROR As String = "Error during update." Const SQL_UPDATE As String = "UPDATE tblProjects SET Buy_CP = {0} WHERE ID = {1};" If Not SqlExecuteFmt(SQL_UPDATE, SqlQuoteNull(Me.cboBuy_CP.Column(1)), CLng(Me.ID)) Then MsgBox MSG_UPDATE_ERROR, vbWarning End IfEnd Function
Open in new window
As ready to use-solution:a) using a function for escaping quotes or NULL:
Open in new window
b) with some error handlingOpen in new window