SQL Update Syntax Error

[Phred]

Access 2007, Moderate skill level, not compiled, not split, one user, running in Before Update event of ComboBox. Problems with syntax error in SQL update Query. I am only updating one table.

My combobox allows the end user to select a substitute meal item such as Baked Beans instead of Green Beans just before production.

I have a combo box on a form. The combo box ItemName comes from a lookup table and is bound to the table Tbl_ScheduledMealItems.ItemName.

The combo box brings up additional information prior to updating.

I set watches, stepped through, and all variables call up the right data.

I run the stSQL statement in the Immediate Window and the information from the SQL statement is all correct. (I'm learning)

The error message says I have a syntax error in the update statement.

I understand the difference in syntax between strings and integers, but is there a different syntax for Double, and Currency data types?

This all runs in the Before Update event of the combo box. I want the user to be able to undo it.



Can anyone see what's wrong?

All of the variables seem correct from the combo box.

Dim stItemName As String ' Column 0 Item Name Tbl_LPV Current
Dim intVendID As Integer 'Column 8 VedorID Tbl_LPVCurrent
Dim stVendName As String 'Column 2 Vendor Name Tbl_LPVCurrent

Dim intInventoryID As Integer 'column 9 IntentoryID Tbl_LPVCurrent
Dim stItemNumb As String 'column 1 Vendor Item Number Tbl_LPVCurrent
Dim stPUname As String 'column 3 Packing Unit Tbl_LPVCurrent

Dim dIuPerPU As Double 'column 4 IUperPU Tbl_LPVCurrent - DOUBLE
Dim stIUDescription As String 'column 10 IUDescription Tbl_LPVCurrent
Dim stIUmeasure As String 'column 5 IUMeasure Tbl_LPVCurrent

Dim dYield As Double 'column 6 Yield Tbl_LPVCurrent - DOUBLE
Dim curPUprice As Currency 'column 7 PricePerUnit Tbl_LPVCurrent -CURRENCY

Dim cPPU As Currency 'column 11 PricePerUnit Tbl_LPVCurrent - CURRENCY

Dim intPK As Integer 'Primary Key Tbl_ScheduledMealItems 'Row I want to update to.

Dim stSQL As String

On Error GoTo CmbPickAlternateItem_BeforeUpdate_Error

All of these statements call the correct information in the watch window.

stItemName = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(0) 'Item Name Tbl_LPV Current
stItemNumb = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(1) 'Vendor Item Number Tbl_LPVCurrent
stVendName = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(2) 'Vendor Name Tbl_LPVCurrent
stPUname = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(3) 'Packing Unit Tbl_LPVCurrent
dIuPerPU = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(4) 'IUperPU Tbl_LPVCurrent
stIUmeasure = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(5) 'IUMeasure Tbl_LPVCurrent
dYield = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(6) 'Yield Tbl_LPVCurrent
curPUprice = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(7) 'PricePerUnit Tbl_LPVCurrent
intVendID = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(8) ' VedorID Tbl_LPVCurrent
intInventoryID = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(9) ' IntentoryID Tbl_LPVCurrent
stIUDescription = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(10) ' IUDescription Tbl_LPVCurrent
cPPU = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!CmbPickAlternateItem. Column(11) ' PricePerUnit Tbl_LPVCurrent
intPK = Forms!Frm_Main!Frm_SiteCategorySelection!Sub_Frm_C ategory_Site_Date_Edit_Child!ScheduledMeal3ID 'Primary Key Tbl_ScheduledMealItems

Returns correct information when run in Immediate window.

stSQL = "UPDATE Tbl_ScheduledMealItems" _
& " SET ItemName = '" & stItemName & "'," _
& " VendorID = " & intVendID & "," _
& " VendorName = '" & stVendName & "'," _
& " InventoryID = " & intInventoryID & "," _
& " ItemNumber = '" & stItemNumb & "'," _
& " PUname = '" & stPUname & "'," _
& " IUperPU = " & dIuPerPU & "," _
& " IUDescription = '" & stIUDescription & "'," _
& " IUMeasure = '" & stIUmeasure & "'," _
& " YieldPerIU = " & dYield & "," _
& " PUPrice = " & curPUprice & "," _
& " PriceperUnit = " & cPPU & "," _
& " WHERE ScheduledMeal3ID = " & intPK & ";"

DoCmd.RunSQL stSQL

Get Syntax error in UPDATE Statement.

Debug.Print stSQL

Dim Msg, Style, Title, Response, MyString
Msg = "Do you want to keep these changes?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Commit Changes"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Me.Undo
End If
Me.Undo
Exit Sub
'Else

[pbaldy]

What did the Debug.Print produce (it may need to be above the line to run the SQL)?

[pbaldy]

Just noticed you have a comma before the WHERE clause, which will certainly produce an error.

[Phred]

Am I looking in the right place? Directly in front of WHERE statement I don't see a comma.

Do i move the Debug.print stSQL above the docmd.runsql stSQL?

[pbaldy]

It's at the end of the previous line.

See here for an example

http://www.baldyweb.com/ImmediateWindow.htm

[Phred]

That comma did it. Thanks. I've learned a lot from you. I appreciate the time you spend with my questions. It now updates the right information in the right table.

I have an ancellary problem

I moved my VBA SQL to After Update.

After the update I open the table to verify the update and all the fields have updated correctly.

Then I try to exit the form, or go to a different tab, but I get the error message that the record has been changed since I changed it. Do I want to save it or discard the changes?

How can I force a save so it doesn't ask me that and get a clean exit?

Thanks again.

[pbaldy]

Is it bound to the same table being updated by the code?

[Phred]

Yes, one field, the combo box, looks up from a lookup list and writes to the Tbl_ScheduledMealItems. The one field is bound to the form. I though I had to have one field bound. I'll give it a try.

[Phred]

The ComboBox "Item Name" has its Control in Properties Data set to the Tbl_ScheduledMealItems.ItemName field.

See Pic 1.

When I remove the control from Item Name (I assume that means it is then unbound) the the Item Name disappears. and you get

See Pic 2.

When the control is gone all data changes.

See Pic 3.

Thanks Phred

[pbaldy]

Well, you're updating via code the same table and field that the form is bound to, which is typically a problem. Why not just update the form fields?