Hi All,
I am trying to allow a user to edit existing data via a form, then save any changes using a query.
As there could well be some empty fields (strings) I check to see if they are empty then build the sql string using only the fields that are not empty.
When executing the query I get an error 3144, a syntax error in Update Query.
Please could someone help on this one.
Thanks in advance.
Code below:
Private Sub BuildSQL()
strSQL = " UPDATE tblCompany SET "
If Trim([txtCompanyName] & "") = "" Then
strSQL = strSQL
Else
strSQL = strSQL & " tblCompany.CompanyName = " & "'" & Me.txtCompanyName & "',"
End If
If Trim([txtTele] & "") = "" Then
strSQL = strSQL
Else
strSQL = strSQL & " tblCompany.Telephone = " & Me.txtTele & ","
End If
If Trim([txtFax] & "") = "" Then
strSQL = strSQL
Else
strSQL = strSQL & " tblCompany.fax = " & Me.txtFax & ","
End If
If Trim([txtEMail] & "") = "" Then
strSQL = strSQL
Else
strSQL = strSQL & " tblCompany.email = " & Me.txtCompanyName
End If
strSQL = strSQL & " WHERE (((tblCompany.CompanyRef)= " & Me.cboCompany & ";"
MsgBox strSQL
DoCmd.RunSQL (strSQL)
End Sub
I am trying to allow a user to edit existing data via a form, then save any changes using a query.
As there could well be some empty fields (strings) I check to see if they are empty then build the sql string using only the fields that are not empty.
When executing the query I get an error 3144, a syntax error in Update Query.
Please could someone help on this one.
Thanks in advance.
Code below:
Private Sub BuildSQL()
strSQL = " UPDATE tblCompany SET "
If Trim([txtCompanyName] & "") = "" Then
strSQL = strSQL
Else
strSQL = strSQL & " tblCompany.CompanyName = " & "'" & Me.txtCompanyName & "',"
End If
If Trim([txtTele] & "") = "" Then
strSQL = strSQL
Else
strSQL = strSQL & " tblCompany.Telephone = " & Me.txtTele & ","
End If
If Trim([txtFax] & "") = "" Then
strSQL = strSQL
Else
strSQL = strSQL & " tblCompany.fax = " & Me.txtFax & ","
End If
If Trim([txtEMail] & "") = "" Then
strSQL = strSQL
Else
strSQL = strSQL & " tblCompany.email = " & Me.txtCompanyName
End If
strSQL = strSQL & " WHERE (((tblCompany.CompanyRef)= " & Me.cboCompany & ";"
MsgBox strSQL
DoCmd.RunSQL (strSQL)
End Sub