Update Query problem

[tigersden]

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

 

[Golom]

The most obvious problem is

 strSQL = strSQL & " WHERE (((tblCompany.CompanyRef)= " & Me.cboCompany & ";"

Unmatched parentheses.

You may also need to place single quotes around the strings coming from your text boxes (assuming that the corresponding database fields are of type TEXT.)

Try this

Private Sub BuildSQL()
    strSQL = " UPDATE tblCompany SET "
    If Len(Trim([txtCompanyName])) > 0 Then
        strSQL = strSQL & " CompanyName = '" & Me.txtCompanyName & "',"
    End If
    If Len(Trim([txtTele])) > 0 Then
        strSQL = strSQL & " Telephone = '" & Me.txtTele & "',"
    End If
    If Len(Trim([txtFax])) > 0 Then
        strSQL = strSQL & " fax = '" & Me.txtFax & "',"
    End If
    If Len(Trim([txtEMail])) > 0 Then
        strSQL = strSQL & " email = '" & Me.txtCompanyName & "' "
    End If

    strSQL = strSQL & " WHERE CompanyRef = '" & Me.cboCompany.Text & "';"

    MsgBox strSQL

    DoCmd.RunSQL (strSQL)
End Sub

 

[SkipVought]

Hi,

Have you checked the SQL that you are building?

Can you take that SQL and plug it into a query and run it?

I see one possible problem -- where are the tics (') around the phone, fax, co name?



Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[tigersden]

Hi Golom & Skip,
Thanks for your advice. I have now done away with checking for null becasue it could be that a value gets deleted.
All is working fine.
Thanks