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,

Thanks for the information. I'll try it after the holidays.

regards,

David

Answer 2

Hi Tom,

Thanks for the information. It looks a bt beyond my capabilities, but I'll check out the link after the holidays.

Regards,

David

Answer 3

> how to have a blank form when it opens.

That should have been your question in this forum. This is very much possible with a bound form.

If you ONLY want to add new records, you can set the DataEntry property of the form to True, and of course AllowAdditions should also be true.

If you want to include existing records, again AllowAdditions=True, and in Form_Open you write a one-liner:

RunCommand acCmdRecordsGoToNew

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

The problem with using the Data Entry property as other's mentioned is that it only allows for entering new records. But you seem to want to edit existing records.

So there are a number of ways to present the user with a blank form initially. I'll mention a few of them

1. Use a search form. Initially open and unbound form with a control to enter or search for the record. In the After Update event of that control, use code like:

If IsNull(Me.controlname) Then

 DoCmd.OpenForm "formame", , , , acForAdd

Else

 DoCmd.OpenForm "formname", , , "PKfield = " & Me.controlname

End If

This code will open the new form to either a new record or the selected record

2. Hide the controls. Create a bound form with a "search combo" in the header. Place a box control over the bound controls with a back color to match the form background. In the After Update event of the Search combo add similar code as above to hide the box so the controls appear. If there is no existing record selected you can go to a new record.
3. Hide/unhide the bound controls. Again using the after Update event of a search combo,, go through and unhide each control after the existing record has been recovered or a new record is set.

Answer 5

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?

Hi David,

In the most simple way it you could use this oneliner:

Function As_text(cur_text As String) As String

*As\_text = "'" & Replace(cur\_text, "'", "''") & "'"* 

End Function

In due time you can refine this function, a.o. to handle Null-values.

If you study this function carefully, you see it replaces the same thing that you had to type in time after time, with the risk of mistypes.

This kind of techniques makes programming more powerful and efficient, and leads to more robust programs.

Imb.