Access 2003, sql error 3144. Please help

[dr_destructo]

I'm in need of help. I have very little coding/access experience. I'm trying to create a way in inputing orders for a catering company. On my form I have many text & combo boxes. Essentially, I have a form with text boxes for quantity ordered, cost of the item, & total cost(which is quantity x cost). The combo box pulls information from a table with the menu items & cost. Cost of each item is pulled from the table of menu items.
The problem I'm running into, is when I click a button to update the tables I get a syntax 3144 on this piece of code: doCmd.RunSQL sql, False

any help is much appreciated.

Here is the other code from this:


Private Sub Form_AfterUpdate()
' updates totals in table after record change
UpdateTotals
End Sub

Private Sub menu1_Change()

Me.cost1 = Me.menu1.Column(2)
Me.cost2 = Me.menu_2.Column(2)
Me.cost3 = Me.menu3.Column(2)
Me.cost4 = Me.menu4.Column(2)
Me.cost5 = Me.menu5.Column(2)
Me.cost6 = Me.menu6.Column(2)
Me.cost7 = Me.menu7.Column(2)
Me.cost8 = Me.menu8.Column(2)
Me.cost9 = Me.menu9.Column(2)
Me.cost10 = Me.menu10.Column(2)
Me.cost11 = Me.menu11.Column(2)
Me.cost12 = Me.menu12.Column(2)
Me.cost13 = Me.menu13.Column(2)
Me.cost14 = Me.menu14.Column(2)
Me.cost15 = Me.menu15.Column(2)
Me.cost16 = Me.menu16.Column(2)
Me.cost17 = Me.menu17.Column(2)
Me.cost18 = Me.menu18.Column(2)
Me.cost19 = Me.menu19.Column(2)
Me.cost20 = Me.menu20.Column(2)
Me.cost21 = Me.menu21.Column(2)
Me.cost22 = Me.menu22.Column(2)
Me.cost23 = Me.menu23.Column(2)
Me.cost24 = Me.menu24.Column(2)
Me.cost25 = Me.menu25.Column(2)
Me.cost26 = Me.menu26.Column(2)
End Sub

Private Sub form_GotFocus()
'This refreshes the form to add the new record to the list
'when the entry form is closed and focus returns
Me.Refresh
End Sub

Private Sub Add_Item_Click()
On Error GoTo Err_Add_Item_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "menu"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Add_Item_Click:
Exit Sub

Err_Add_Item_Click:
MsgBox Err.Description
Resume Exit_Add_Item_Click

End Sub

Private Sub UpdateTotals()
Dim sql As String
sql = "UPDATE [master contract] SET total1 = " & Me.total1 & ", cost1 = " & Me.cost1 & ", "
sql = sql & "total2 = " & Me.total2 & ", cost2 = " & Me.cost2 & ", "
sql = sql & "total3 = " & Me.total3 & ", cost3 = " & Me.cost3 & ", "
sql = sql & "total4 = " & Me.total4 & ", cost4 = " & Me.cost4 & ", "
sql = sql & "total5 = " & Me.total5 & ", cost5 = " & Me.cost5 & ", "
sql = sql & "total6 = " & Me.total6 & ", cost6 = " & Me.cost6 & ", "
sql = sql & "total7 = " & Me.total7 & ", cost7 = " & Me.cost7 & ", "
sql = sql & "total8 = " & Me.total8 & ", cost8 = " & Me.cost8 & ", "
sql = sql & "total9 = " & Me.total9 & ", cost9 = " & Me.cost9 & ", "
sql = sql & "total10 = " & Me.total10 & ", cost10 = " & Me.cost10 & ", "
sql = sql & "total11 = " & Me.total11 & ", cost11 = " & Me.cost11 & ", "
sql = sql & "total12 = " & Me.total12 & ", cost12 = " & Me.cost12 & ", "
sql = sql & "total13 = " & Me.total13 & ", cost13 = " & Me.cost13 & ", "
sql = sql & "total14 = " & Me.total14 & ", cost14 = " & Me.cost14 & ", "
sql = sql & "total15 = " & Me.total15 & ", cost15 = " & Me.cost15 & ", "
sql = sql & "total16 = " & Me.total16 & ", cost16 = " & Me.cost16 & ", "
sql = sql & "total17 = " & Me.total17 & ", cost17 = " & Me.cost17 & ", "
sql = sql & "total18 = " & Me.total18 & ", cost18 = " & Me.cost18 & ", "
sql = sql & "total19 = " & Me.total19 & ", cost19 = " & Me.cost19 & ", "
sql = sql & "total20 = " & Me.total20 & ", cost20 = " & Me.cost20 & ", "
sql = sql & "total21 = " & Me.total21 & ", cost21 = " & Me.cost21 & ", "
sql = sql & "total22 = " & Me.total22 & ", cost22 = " & Me.cost22 & ", "
sql = sql & "total23 = " & Me.total23 & ", cost23 = " & Me.cost23 & ", "
sql = sql & "total24 = " & Me.total24 & ", cost24 = " & Me.cost24 & ", "
sql = sql & "total25 = " & Me.total25 & ", cost25 = " & Me.cost25 & ", "
sql = sql & "total26 = " & Me.total26 & ", cost26 = " & Me.cost26 & ", "
sql = sql & "total27 = " & Me.total27 & ", cost27 = " & Me.cost27 & ", "
sql = sql & "total28 = " & Me.total28 & ", cost28 = " & Me.cost28 & ", "
sql = sql & "total29 = " & Me.total29 & ", cost29 = " & Me.cost29 & ", "
sql = sql & "total30 = " & Me.total30 & ", cost30 = " & Me.cost30 & ", "
sql = sql & "subtotal = " & Me.subtotal & ", grandtotal = " & Me.grandtotal

sql = sql & " WHERE ID = " & Me.ID

DoCmd.RunSQL sql, False


End Sub

Private Sub btnReport_Click()
Me.Requery
UpdateTotals
DoCmd.OpenReport "master_contract", acViewPreview, , "ID = " & Me.ID
End Sub

Private Sub total1_Changes()
MsgBox "test"
End Sub

 

[pbaldy]

Will every one of the fields have a value? Add this right before the DoCmd line:

Debug.Print sql

and examine the finished SQL in the Immediate window. If you don't see the problem, post the SQL here.

 

[dr_destructo]

The likelyhood that all fields having values is slim to none. I added the code you posted, and am still getting the same error.

 

[pbaldy]

As I said, post the SQL from the Immediate window. That said, if not every field will have a value, that method will not work. You'll either need to switch to a recordset method or do something like this:

If Not IsNull(Me.cost2) Then
  sql = sql & ", cost2 = " & Me.cost2
End If

You may want to read up on normalization; the field names and the fact they will often be empty point to a normalization issue.

http://www.r937.com/Relational.html

 

[dr_destructo]

So I added Debug.Print.sql and nothing happened. Nothing popped up. But same 3144 error occurred. For that last snippet of code, where would that go?

 

[pbaldy]

Nothing will pop up, and I wouldn't expect it to fix anything; it's a debugging tool. The finished SQL should be in the Immediate window of the VB editor after that runs.

That type of code would replace what you've got now. You're building SQL that updates every field, whether it contains a value or not. That would only include fields that have a value. It occurs to me you could use the Nz() function with your existing code:

sql = "UPDATE [master contract] SET total1 = " & Nz(Me.total1, 0) & ", cost1 = " & Nz(Me.cost1, 0) & ", "

 

[dr_destructo]

Thanks so much for you help thus far, but i'm still getting the same 3144 error. Here's what I've done:
Private Sub UpdateTotals()
Dim sql As String
'sql = "UPDATE [master contract] SET total1 = " & Me.total1 & ", cost1 = " & Me.cost1 & ", "
sql = "UPDATE [master contract] SET total1 = " & Nz(Me.total1, 0) & ", cost1 = " & Nz(Me.cost1, 0) & ", "
'sql = sql & "total2 = " & Me.total2 & ", cost2 = " & Me.cost2 & ", "
'sql = sql & "total3 = " & Me.total3 & ", cost3 = " & Me.cost3 & ", "
'sql = sql & "total4 = " & Me.total4 & ", cost4 = " & Me.cost4 & ", "
'sql = sql & "total5 = " & Me.total5 & ", cost5 = " & Me.cost5 & ", "
'sql = sql & "total6 = " & Me.total6 & ", cost6 = " & Me.cost6 & ", "
'sql = sql & "total7 = " & Me.total7 & ", cost7 = " & Me.cost7 & ", "
'sql = sql & "total8 = " & Me.total8 & ", cost8 = " & Me.cost8 & ", "
'sql = sql & "total9 = " & Me.total9 & ", cost9 = " & Me.cost9 & ", "
'sql = sql & "total10 = " & Me.total10 & ", cost10 = " & Me.cost10 & ", "
'sql = sql & "total11 = " & Me.total11 & ", cost11 = " & Me.cost11 & ", "
'sql = sql & "total12 = " & Me.total12 & ", cost12 = " & Me.cost12 & ", "
'sql = sql & "total13 = " & Me.total13 & ", cost13 = " & Me.cost13 & ", "
'sql = sql & "total14 = " & Me.total14 & ", cost14 = " & Me.cost14 & ", "
'sql = sql & "total15 = " & Me.total15 & ", cost15 = " & Me.cost15 & ", "
'sql = sql & "total16 = " & Me.total16 & ", cost16 = " & Me.cost16 & ", "
'sql = sql & "total17 = " & Me.total17 & ", cost17 = " & Me.cost17 & ", "
'sql = sql & "total18 = " & Me.total18 & ", cost18 = " & Me.cost18 & ", "
'sql = sql & "total19 = " & Me.total19 & ", cost19 = " & Me.cost19 & ", "
'sql = sql & "total20 = " & Me.total20 & ", cost20 = " & Me.cost20 & ", "
'sql = sql & "total21 = " & Me.total21 & ", cost21 = " & Me.cost21 & ", "
'sql = sql & "total22 = " & Me.total22 & ", cost22 = " & Me.cost22 & ", "
'sql = sql & "total23 = " & Me.total23 & ", cost23 = " & Me.cost23 & ", "
'sql = sql & "total24 = " & Me.total24 & ", cost24 = " & Me.cost24 & ", "
'sql = sql & "total25 = " & Me.total25 & ", cost25 = " & Me.cost25 & ", "
'sql = sql & "total26 = " & Me.total26 & ", cost26 = " & Me.cost26 & ", "
'sql = sql & "total27 = " & Me.total27 & ", cost27 = " & Me.cost27 & ", "
'sql = sql & "total28 = " & Me.total28 & ", cost28 = " & Me.cost28 & ", "
'sql = sql & "total29 = " & Me.total29 & ", cost29 = " & Me.cost29 & ", "
'sql = sql & "total30 = " & Me.total30 & ", cost30 = " & Me.cost30 & ", "
'sql = sql & "subtotal = " & Me.subtotal & ", grandtotal = " & Me.grandtotal

sql = sql & " WHERE ID = " & Me.ID

Debug.Print sql
DoCmd.RunSQL sql, False

 

[pbaldy]

Sorry, I should have specified that every value coming off the form would have to be wrapped in an Nz() function. It will replace your potential empty fields with a zero so that the SQL won't bomb. You still haven't posted the SQL, but my guess is that something like this is happening because of empty form controls:

...Total1 = 123, Cost1 = 456, Total2 = , Cost2 = , ...

 

[dr_destructo]

ok, so it's attached now.

 

[pbaldy]

So what is the issue now? The report button runs without error for me, and opens the report.

 

[dr_destructo]

Ok, so when I add an item via the Add Item button, and then select it as a menu item, I get the same 3144 error. Thoughts?

**Edit**This is only happening on menu1 apparently

 

[pbaldy]

Well, you have code like this:

If Me.qty2 > 0 Then sql = sql & "total2 = " & Me.total2 & ", cost2 = " & Me.cost2 & ", "

for every other option, so it would seem like you also need it for the first one. As noted earlier, the repeating fields are a real normalization problem. They should almost certainly be in a related table, where each menu item was a record.

 

[dr_destructo]

So I'm going to start from scratch on this. I've realized my original design is creating way too many headaches. So I wanted to ask you if this is feasible. Create a form with the event details(location, time, contact etc). On this form is a button to bring up a new form for adding the menu items & quantity. Upon submission, this information is then displayed on the original form. This would happen on an item by item basis, as opposed to having the 26 combo boxes with listed at all times. What are your thoughts on this?

 

[pbaldy]

I agree. Your situation is much like a standard sales application. That relationship is normally done using two tables with a one-to-many relationship. One is the order header, with info about the customer, date, invoice number, etc. The other is for the item(s) purchased, with a record for each item and fields for quantity, price, etc. The two tables would be related by the invoice number or a similar field. In your case, the "header" is your master contract data, and the detail is the menu items. If I order 4 menu items, I have 4 records in that detail table.

The above relationship is usually presented with a form/subform, where the form is bound to the header table and the subform to the detail table. Master/child links keep them in sync with each other.

 

[dr_destructo]

So i've re-read some books, and done a lot of searches, and attached is my most recent design. The problem I'm having now, is that my subform isn't updating. My main form updates just fine, it's just that my subform doesn't update at all. I'm also running into an error, the first time I click on any field in the subform and try to type, i get an error reading: Can't assign a value to this object. Any help is much appreciated.

 

[pbaldy]

How are the two tables related? There should be a field in the order table for the ID field from the contract table. That would be a field that related the two tables together. In other words, you have a record in the order table for 15 Pepsi; what contract is that order for?

 

[dr_destructo]

They're linked via the 'ID' box. I hid the 'ID' box on the order subform.

 

[pbaldy]

The ID field in the orders table is not related to the ID field in the contract table. It's an autonumber in the orders table, which makes it a good primary key for the orders table but it can't be related to the contracts table (since the value can't repeat). You need another field in the order table to hold the contract ID.

 

[dr_destructo]

That makes perfect sense, but how would I go about doing this?

 

[MSAccessRookie]

Thanks so much for you help thus far, but i'm still getting the same 3144 error. Here's what I've done:
Private Sub UpdateTotals()
Dim sql As String

sql = "UPDATE [master contract] SET total1 = " & Nz(Me.total1, 0) & ", cost1 = " & Nz(Me.cost1, 0) & ", "

sql = sql & " WHERE ID = " & Me.ID

Debug.Print sql
DoCmd.RunSQL sql, False

No one has talked about the meaning of the error.

Error #3144 = Syntax error in UPDATE
​

Evaluation of this version of the code (when Total1 = 25, Cost1 = $35.00 and ID = 2345) shows the following:

sql = "UPDATE [master contract] SET total1 = 25, cost1 = 35.00, WHERE ID = 2345"

It looks like there may be an extra comma here. I know that this is a sample version, but removing the comma might make it work. If it does, there there might be a similar error in the complete formula.

Another thing for you to consider is that you may be exceeding the character limit for an SQL Query.