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

Hi Ibm,

Your solution sound great, but I'm unsure as to how I ccode your As_text function. I know its a bit cheeky, but would you reply with the code please?

Thanks in advance.

Regards,

David

Answer 2

Hi HansV,

Thanks for the code, however it would appear that there is a problem with it. When I run the code Iget a Runtime Error 3144. Syntax Error in UPDATE statement. I'm sure it's very close, but Access will now proceed.

Regards,

David

Answer 3

Ideally you would do this with zero code: bind the form to the table by setting its RecordSource property. Highly recommended.

If you have a good reason for an unbound form (and I don't see it here), then I would recommend first downloading the Northwind 2 Developer Edition template, and copying its modStrings to your app. Then use the StringFormatSQL function to do the concatenation for you, applying the correct escape characters (single quotes or hashmarks) automatically. Kim Young and I discuss the Northwind string functions here, starting at 35:30.

Answer 4

Hi,

I'm using an unbound form as I don't know how to have a blank form when it opens.

Thanks

David

Answer 5

I'm using an unbound form as I don't know how to have a blank form when it opens.

With a bound form, setting its DataEntry property to True (Yes) will cause the form to only allow the insertion of new rows, so it will open blank.