SQL update from an empty value textbox by vba (1 Viewer)

I've textbox in a form which is an option for data entry. I encounter a problem of SQL update from an empty value textbox to the table with VBA.

Welcome any suggestions

What have you tried?
It's difficult to understand what your issue is without seeing what you have tried and what error you are getting.

sJobPosition = Nz(txtJobPosition.Value, "")



sAppt = "UPDATE tblTAppt SET ApptStart=#" & txtApptStart & "#, ApptEnd=#" & txtApptEnd & "#, Completed=" & chkCompleted & _
", AppointedBy=" & sApptedBy & ", RankCode=" & fraHSR & ", JobPosition=" & sJobPosition & ", Dept=" & sDept & _
", Company=" & sCompany & ", Honorarium=" & sHon & ", Allowance=" & sAll & ", FCF_No=" & sFCF_No & _
", Budget=" & iBudget & ", DaysNotAvailable=" & sDaysNotAvailable & ", Remark=" & sRemark & _
" WHERE tblTAppt.TRef =" & iID & " and iTApptID = " & txtTApptID



If txtJobPosition contains nothing, the above SQL result in "... JobPostion= ...". No value is assigned to JobPosition.

I have no idea of what should be assigned to the SQL if txtJobPosition has nothing.

thank you any advice.

Use whatever you want instead of the empty string e.g "Unknown" or "No data"

I've applied a function (CSql) to sDept = CSql(txtDept.Value) to handle empty value in the textbox and rebuilt the sql as follows


sAppt = "UPDATE tblTAppt SET ApptStart=#" & txtApptStart.Value & "#, ApptEnd=#" & txtApptEnd.Value & "#, JobPosition=" & sJobPosition & _
", Dept=" & sDept & ", Company=" & sCompany & ", Honorarium=" & sHon & ", Allowance=" & sAll & ", FCF_No=" & sFCF_No & _
", DaysNotAvailable=" & sDaysNotAvailable & ", Remark=" & sRemark & _
" WHERE (tblTAppt.TRef=" & iID & " and tblTApptID=" & txtTApptID.Value & ");"

The immediate windows displays the following sql
UPDATE tblTAppt SET ApptStart=#1/1/2017#, ApptEnd=#31/12/2018#, JobPosition= 'J & P' , Dept= 'S&M' , Company= 'times' , Honorarium= Null , Allowance= Null , FCF_No= Null , DaysNotAvailable= Null , Remark= Null WHERE (tblTAppt.TRef=1 and tblTApptID=102);


The runtime error is "3061 Too few parameters. Expected 1." and

It seems nothing wrong with the sql but the runtime error keeps prevaling





' 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



I would be grateful if there's any idea on why the error keep happening.

I haven't read all your post but I believe you have a missing . before ID in the WHERE line - marked in RED
You also don't need .Value as its the default

It should be

Thank you very much. I'm really careless mistake person.

Thank you again.

You're welcome. It's the simple mistakes that are often the hardest to find in our own work.