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 Scott,

I finally managed to try your solution to my problem, and it works really well. As a newby to Access some solutions are byond my abilities at present, but this hit the spot. And you memory worked fine. The only thing missin was the .Edit, which I worked out.

Thanks for your help.

David