Updating tables with empty values

[Guest]

I'm updating a table with information from a form's controls. The UPDATe
sql does not work when any of the text boxes are empty when the sql is
executed.
But sometimes I need to update the fields to empty.
What do I need to do in order to resolve the error.? Any help is very much
appreciated.

Here's the code:

Private Sub btnUpdate_Click()
Dim d As Database
Dim strSQL As String
Dim dtDateLastSaved As Date
Set d = CurrentDb

dtDateLastSaved = Now()

strSQL = "UPDATE NCLog"
strSQL = strSQL & " SET ChassisSN = '" & Me![txtChassisSN]
strSQL = strSQL & "', CustomerPO = '" & Me![txtCustomerPO]

' ..... more controls to fields.....
strSQL = strSQL & "', NCReasonCode =" & Me!cboNCReason.Column(1)
strSQL = strSQL & ", NCReasonDesc = '" & Me!cboNCReason.Column(0)
If Me!txtOther.Enabled = True Then
strSQL = strSQL & "', NCReasonOther = '" & Me!txtOther
End If
strSQL = strSQL & "' WHERE CARNum = " & Me!txtCARnum.Value & ""
d.Execute strSQL
MsgBox "Changes to CAR Number " & Me!txtCARnum & " have been saved."
d.Close

Thanks. Samantha

 

[Marshall Barton]

I'm updating a table with information from a form's controls. The UPDATe
sql does not work when any of the text boxes are empty when the sql is
executed.
But sometimes I need to update the fields to empty.
What do I need to do in order to resolve the error.? Any help is very much
appreciated.

Here's the code:

Private Sub btnUpdate_Click()
Dim d As Database
Dim strSQL As String
Dim dtDateLastSaved As Date
Set d = CurrentDb

dtDateLastSaved = Now()

strSQL = "UPDATE NCLog"
strSQL = strSQL & " SET ChassisSN = '" & Me![txtChassisSN]
strSQL = strSQL & "', CustomerPO = '" & Me![txtCustomerPO]

' ..... more controls to fields.....
strSQL = strSQL & "', NCReasonCode =" & Me!cboNCReason.Column(1)
strSQL = strSQL & ", NCReasonDesc = '" & Me!cboNCReason.Column(0)
If Me!txtOther.Enabled = True Then
strSQL = strSQL & "', NCReasonOther = '" & Me!txtOther
End If
strSQL = strSQL & "' WHERE CARNum = " & Me!txtCARnum.Value & ""
d.Execute strSQL
MsgBox "Changes to CAR Number " & Me!txtCARnum & " have been saved."
d.Close

You can use this kind of logic for numeric type fields:

strSQL = strSQL & "', NCReasonCode =" & _
IIf(IsNull(Me!cboNCReason), "Null", _
Me!cboNCReason.Column(1))

and for Text type fields:

strSQL = strSQL & ", NCReasonDesc = " & _
IIf(IsNull(Me!cboNCReason), "Null",
"'" & Me!cboNCReason.Column(0) & "'")