I am using this code and once I run it I get runtime error 3144. I don't know where in the code needs to be changed or is not correct. Can you please help me to fix it?
CurrentDb.Execute "UPDATE WORKED_HOURS " & _
" set EMPLOYEE_ID=" & `enter code here`Me.cboEmployeeID & _
", SET FIRST_NAME='" & Me.FIRST_NAME & "'" & _
", SET LAST_NAME='" & Me.LAST_NAME & "'" & _
", Set WORK_ORDER_NO='" & Me.cboWorkOrderNo & "'" & _
", SET PROJECT_TITLE='" & Me.txtProjectTitle & "'" & _
", SET WORKED_DATE='" & Me.txtWorkedDate & "'" & _
", SET TIME_STARTED='" & Me.txtTimeStarted & "'" & _
", SET TIME_FINISHED='" & Me.txtTimeFinished & "'" & _
", SET WEEK_ENDING='" & Me.txtWeekEnding & "'" & _
" where EMPLOYEE_ID=" & Me.cboEmployeeID.Tag
You (also) need proper formatting of your date and time values.
A method is to apply this function. See in-line comments for usage:
' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
' SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
' SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
' SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
' SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
ByVal Value As Variant) _
As String
Const vbLongLong As Integer = 20
Const SqlNull As String = " Null"
Dim Sql As String
Dim LongLong As Integer
#If Win32 Then
LongLong = vbLongLong
#End If
#If Win64 Then
LongLong = VBA.vbLongLong
#End If
Select Case VarType(Value)
Case vbEmpty ' 0 Empty (uninitialized).
Sql = SqlNull
Case vbNull ' 1 Null (no valid data).
Sql = SqlNull
Case vbInteger ' 2 Integer.
Sql = Str(Value)
Case vbLong ' 3 Long integer.
Sql = Str(Value)
Case vbSingle ' 4 Single-precision floating-point number.
Sql = Str(Value)
Case vbDouble ' 5 Double-precision floating-point number.
Sql = Str(Value)
Case vbCurrency ' 6 Currency.
Sql = Str(Value)
Case vbDate ' 7 Date.
Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
Case vbString ' 8 String.
Sql = Replace(Trim(Value), "'", "''")
If Sql = "" Then
Sql = SqlNull
Else
Sql = " '" & Sql & "'"
End If
Case vbObject ' 9 Object.
Sql = SqlNull
Case vbError ' 10 Error.
Sql = SqlNull
Case vbBoolean ' 11 Boolean.
Sql = Str(Abs(Value))
Case vbVariant ' 12 Variant (used only with arrays of variants).
Sql = SqlNull
Case vbDataObject ' 13 A data access object.
Sql = SqlNull
Case vbDecimal ' 14 Decimal.
Sql = Str(Value)
Case vbByte ' 17 Byte.
Sql = Str(Value)
Case LongLong ' 20 LongLong integer (Valid on 64-bit platforms only).
Sql = Str(Value)
Case vbUserDefinedType ' 36 Variants that contain user-defined types.
Sql = SqlNull
Case vbArray ' 8192 Array.
Sql = SqlNull
Case Else ' Should not happen.
Sql = SqlNull
End Select
CSql = Sql & " "
End Function
Update statements in practically every SQL dialect does not allow multiple SET commands but only one with multiple expressions separated by commas. Consider doing so and enclosing properly according to data type with quotes (strings), pound signs (dates) or nothing (integer):
CurrentDb.Execute "UPDATE WORKED_HOURS " _
& " SET EMPLOYEE_ID=" & Me.cboEmployeeID _
& " , FIRST_NAME='" & Me.FIRST_NAME & "'" _
& " , LAST_NAME='" & Me.LAST_NAME & "'" _
& " , WORK_ORDER_NO='" & Me.cboWorkOrderNo & "'" _
& " , PROJECT_TITLE='" & Me.txtProjectTitle & "'" _
& " , WORKED_DATE=#" & Me.txtWorkedDate & "#" _
& " , TIME_STARTED=#" & Me.txtTimeStarted & "#" _
& " , TIME_FINISHED=#" & Me.txtTimeFinished & "#" _
& " , WEEK_ENDING=#" & Me.txtWeekEnding & "#" _
& " WHERE EMPLOYEE_ID=" & Me.cboEmployeeID.Tag
But for best practices, consider a paramterized querydef which even avoids needed quotes or pound signs. Jet/ACE SQL allows parameter declaration with the PARAMETERS clause.
Dim qdef As querydef
strSQL = "PARAMETERS [EMPLOYEE_ID] Integer, [FIRST_NAME] Text(255)," _
& " [LAST_NAME] Text(255), [WORK_ORDER_NO] Text(255)," _
& " [PROJECT_TITLE] Text(255), [WORKED_DATE] Datetime," _
& " [TIME_STARTED] Datetime, [TIME_FINISHED] Datetime," _
& " [WEEK_ENDING] Datetime;"
& " UPDATE WORKED_HOURS " _
& " SET EMPLOYEE_ID = [EMPLOYEE_ID]" _
& " , FIRST_NAME = [FIRST_NAME]" _
& " , LAST_NAME = [LAST_NAME] " _
& " , WORK_ORDER_NO = [WORK_ORDER_NO] " _
& " , PROJECT_TITLE = [PROJECT_TITLE]" _
& " , WORKED_DATE = [WORKED_DATE]" _
& " , TIME_STARTED = [TIME_STARTED]" _
& " , TIME_FINISHED = [TIME_FINISHED]" _
& " , WEEK_ENDING = [WEEK_ENDING]" _
& " WHERE EMPLOYEE_ID = [EMPLOYEE_ID]"
Set qdef = Currentdb.CreateQueryDef("", strSQL)
qdef!EMPLOYEE_ID = Me.cboEmployeeID
qdef!FIRST_NAME = Me.FIRST_NAME
qdef!LAST_NAME = Me.LAST_NAME
qdef!WORK_ORDER_NO = Me.cboWorkOrderNo
qdef!PROJECT_TITLE = Me.txtProjectTitle
qdef!WORKED_DATE = Me.txtWorkedDate
qdef!TIME_STARTED = Me.txtTimeStarted
qdef!TIME_FINISHED = Me.txtTimeFinished
qdef!WEEK_ENDING = Me.txtWeekEnding
qdef.Execute
Set qdef = Nothing
