How to construct an SQL string

Question

Hi,

I an trying to construct a string to update a table with amended information. The table (tblSuppliers) holds Name, address , currency and Status (as below)

I have a form (frmSupplierAmend) which pulls in the relevant data. When I click the Save button, I want the data from the form to overwrite the data in the table.

All the form names and field names are correct, but I cant get the string right.

This is the code below

Private Sub btnSaveExit_Click()

Dim db As DAO.Database 

Dim strSQL As String 

Dim strSuppCode As String 

Dim strSupp\_Name As String 

Dim strAdd1 As String 

Dim strAdd2 As String 

Dim strAdd3 As String 

Dim strAdd4 As String 

Dim strPostCode As String 

Dim strCountry As String 

Dim strCurrency As String 

' Get the data from the form 

strSuppCode = Me.tbxSuppCode 

strSupp\_Name = Me.tbxSuppName.Value 

strAdd1 = Me.tbxAdd1.Value 

strAdd2 = Me.tbxAdd2.Value 

strAdd3 = Me.tbxAdd3.Value 

strAdd4 = Me.tbxAdd4.Value 

strPostCode = Me.tbxPostCode.Value 

strCountry = Me.tbxCountry.Value 

strCurrency = Me.cbCurrency.Value 

' SQL to update the record 

'This is where I can't get it right. I have put it across several lines so ity is easier to read

strSQL = "UPDATE tblSuppliers SET Supp\_Name = '" & strSupp\_Name & "', " 

strSQL = strSQL & "Address1 = '" & strAdd1 & ", " 

strSQL = strSQL & "Address2 = '" & strAdd2 & ", " 

strSQL = strSQL & "Address3 = '" & strAdd3 & ", " 

strSQL = strSQL & "Address4 = '" & strAdd4 & ", " 

strSQL = strSQL & "Post\_Code = '" & tbxPostCode & ", " 

strSQL = strSQL & "Country = '" & tbxCountry & ", " 

srtSQL = strSQL & "Currency = '" & cbCurrency & ", " 

strSQL = strSQL & "WHERE Supp\_Code = '" & strSuppCode & "" 

' Initialize the database 

Set db = CurrentDb 

' Execute the SQL command 

db.Execute strSQL, dbFailOnError 

' Close the database 

Set db = Nothing 

MsgBox "Record updated successfully.", vbInformation 

DoCmd.SetWarnings False 

DoCmd.Close acForm, "frmItemsAmend" 

DoCmd.OpenForm "frmItemsMenu" 

End Sub

If anyone can help, it would be greatly appreciated

Answer 1

You missed a few sing;e quotes after the text values:

    strSQL = "UPDATE tblSuppliers SET Supp_Name = '" & strSupp_Name & "', "
    strSQL = strSQL & "Address1 = '" & strAdd1 & "', "
    strSQL = strSQL & "Address2 = '" & strAdd2 & "', "
    strSQL = strSQL & "Address3 = '" & strAdd3 & "', "
    strSQL = strSQL & "Address4 = '" & strAdd4 & "', "
    strSQL = strSQL & "Post_Code = '" & tbxPostCode & "', "
    strSQL = strSQL & "Country = '" & tbxCountry & "', "
    srtSQL = strSQL & "Currency = '" & cbCurrency & "', "
    strSQL = strSQL & "WHERE Supp_Code = '" & strSuppCode & "'"

Answer 2

You are missing closing quote delimiters in a number of cases, e.g.

    strSQL = strSQL & "Address1 = '" & strAdd1 & ", "

You can ether use the single quotes character as the delimiter, i.e.

    strSQL = strSQL & "Address1 = '" & strAdd1 & "', "

Or you can represent each literal quotes character as a contiguous pair of quotes characters:

    strSQL = strSQL & "Address1 = """ & strAdd1 & """, "

This would be my preference as it allows for apostrophes in string expressions, which can be the case with personal names for instance, as is the case with my own name in its original non-anglicized form, Cináed O'Siridean.

Answer 3

strSQL = "UPDATE tblSuppliers SET Supp_Name = '" & strSupp_Name & "', "

strSQL = strSQL & "Address1 = '" & strAdd1 & ", "

strSQL = strSQL & "Address2 = '" & strAdd2 & ", "

strSQL = strSQL & "Address3 = '" & strAdd3 & ", "

strSQL = strSQL & "Address4 = '" & strAdd4 & ", "

strSQL = strSQL & "Post_Code = '" & tbxPostCode & ", "

strSQL = strSQL & "Country = '" & tbxCountry & ", "

srtSQL = strSQL & "Currency = '" & cbCurrency & ", "

strSQL = strSQL & "WHERE Supp_Code = '" & strSuppCode & ""

Hi David,

This quoting is terrible, and an eternal problem.

I solved this problem eternally by using a simple function As_text, placed in a general module. This functions surrounds a text value with single quotes, and to accomodate the warning of Ken, doubles single quotes in the value.

The original strSQL then becomes:

strSQL = "UPDATE tblSuppliers SET Supp_Name = " & As_text(strSupp_Name) _

        & ", Address1 = " & As\_text(strAdd1) \_

        & ", Address2 = " & As\_text(strAdd2) \_

        & ", Address3 = " & As\_text(strAdd3) \_

        & ", Address4 = " & As\_text(strAdd4) \_ 

etc.

Problem definitive solved!

Imb.

Answer 4

Are you using an unbound form? If so, just curious why not use a bound form?

Answer 5

First, I agree with DBGuy. There are good reasons to use an unbound form. However, your code doesn't indicate you are using any of those reasons. What are you trying to accomplish in using an unbound form.

Second, I would not use an UPDATE SQL Statement in this case. I would be ore inclined to use a Recordset.

The code would look something like this (Note this is aircode off the top of my head and may not be syntactically correct but should give you the Idea)

In the After Update event of the control where the PK value is entered use

Dim db As Database, rs As Recordset

Dim strSQL As String

Set db = CurrentDB()

strSQL = "SELECT * FROM tblSuppliers WHERE Supp_Code = '" & Me.Supp_code & "';"

Set rs = db.OpenRecordset(strSQL)

With rs

 Me.controlname = .Fields("fieldname")

 *repeat for each control and field on the form*

End With

Then in the On click event of your Save button.

Dim db As Database, rs As Recordset

Dim strSQL As String

Set db = CurrentDB()

strSQL = "SELECT * FROM tblSuppliers WHERE Supp_Code = '" & Me.Supp_code & "';"

Set rs = db.OpenRecordset(strSQL)

With rs

 .Fields("fieldname") = Me.controlname

*repeat for each control and field on the form*

.Update

End With