MS ACCESS 2007 - PROBLEM WITH update STATEMENT in my form

[jker]

I have a form based on a SALES table. in the form there are 2 unbound text values (L1PRODUCTID and L2PRODUCTID, both values are different)



there is an CLICK event which INSERTs into the SALES table (the INSERT works fine) . At the same time, I want to UPDATE my PRODUCTS table where the [PRODUCT].[PRODUCT_ID] = L1PRODUCTID
and UPDATE my PRODUCTS table where the [PRODUCT].[PRODUCT_ID] = L2PRODUCTID.

Update [PRODUCT]
Set [PRODUCT].[PRODUCT_IN_STOCK] = ([PRODUCT].[PRODUCT_IN_STOCK] - L1TXTQTLSOLD)
WHERE [PRODUCT].[PRODUCT_ID] = L1ProductId

Update [PRODUCT]
Set [PRODUCT].[PRODUCT_IN_STOCK] = ([PRODUCT].[PRODUCT_IN_STOCK] - L2TXTQTLSOLD)
WHERE [PRODUCT].[PRODUCT_ID] = L2ProductId


PROBLEM: i get a compile error 'SUB OR FUNCTION NOT DEFINED POINTING TO MY update STATEMENT

i DONT UNDERSTAND why i get this error

[orange]

Please show all the code for the CLICK event. I see SQL but no method to actually execute it??? Need more info.

[davegri]

What's with the Update [PRODUCT] code line? Access thinks you are tryting to call a procedure named Update

[jker]

Here is my event code that inserts new entries into SALES table and updates the PRODUCT table

Private Sub btnSave_Click()
'INSERT ENTRY INTO SALES TABLE
'************************************************* ***************
' UPDATE SALES TABLE
'************************************************* ****************
Dim MYSQL2 As String
Dim FULLSQL As String

MYSQL = "INSERT INTO SALES"
MYSQL1 = " ([SALES_CUSTOMER_ID],[SALES_DATE_SOLD],[SALES_DATE_TIME]" _
& ",[SALES_PRODUCT_ID1],[SALES_PRODUCT_TYPE1],[SALES_PRODUCT_REFERENCE1],[SALES_PRODUCT_SIZE1],[SALES_PRODUCT_COLOR1],[SALES_ITEM_QTY1], [SALES_ITEM_COST1]" _
& ",[SALES_PRODUCT_ID2],[SALES_PRODUCT_TYPE2],[SALES_PRODUCT_REFERENCE2],[SALES_PRODUCT_SIZE2],[SALES_PRODUCT_COLOR2],[SALES_ITEM_QTY2], [SALES_ITEM_COST2]" _
& ",[SALES_PRODUCT_ID3],[SALES_PRODUCT_TYPE3],[SALES_PRODUCT_REFERENCE3],[SALES_PRODUCT_SIZE3],[SALES_PRODUCT_COLOR3],[SALES_ITEM_QTY3], [SALES_ITEM_COST3]" _
& ",[SALES_PRODUCT_ID4],[SALES_PRODUCT_TYPE4],[SALES_PRODUCT_REFERENCE4],[SALES_PRODUCT_SIZE4],[SALES_PRODUCT_COLOR4],[SALES_ITEM_QTY4], [SALES_ITEM_COST4]" _
& ",[SALES_PRODUCT_ID5],[SALES_PRODUCT_TYPE5],[SALES_PRODUCT_REFERENCE5],[SALES_PRODUCT_SIZE5],[SALES_PRODUCT_COLOR5],[SALES_ITEM_QTY5], [SALES_ITEM_COST5]" _
& ",[SALES_PRODUCT_ID6],[SALES_PRODUCT_TYPE6],[SALES_PRODUCT_REFERENCE6],[SALES_PRODUCT_SIZE6],[SALES_PRODUCT_COLOR6],[SALES_ITEM_QTY6], [SALES_ITEM_COST6]" _
& ",[SALES_PRODUCT_ID7],[SALES_PRODUCT_TYPE7],[SALES_PRODUCT_REFERENCE7],[SALES_PRODUCT_SIZE7],[SALES_PRODUCT_COLOR7],[SALES_ITEM_QTY7], [SALES_ITEM_COST7]" _
& ",[SALES_PRODUCT_ID8],[SALES_PRODUCT_TYPE8],[SALES_PRODUCT_REFERENCE8],[SALES_PRODUCT_SIZE8],[SALES_PRODUCT_COLOR8],[SALES_ITEM_QTY8], [SALES_ITEM_COST8]" _
& ",[SALES_PRODUCT_ID9],[SALES_PRODUCT_TYPE9],[SALES_PRODUCT_REFERENCE9],[SALES_PRODUCT_SIZE9],[SALES_PRODUCT_COLOR9],[SALES_ITEM_QTY9], [SALES_ITEM_COST9]" _
& ",[SALES_PRODUCT_ID10],[SALES_PRODUCT_TYPE10],[SALES_PRODUCT_REFERENCE10],[SALES_PRODUCT_SIZE10],[SALES_PRODUCT_COLOR10],[SALES_ITEM_QTY10], [SALES_ITEM_COST10]" _
& ",[SALES_PRODUCT_ID11],[SALES_PRODUCT_TYPE11],[SALES_PRODUCT_REFERENCE11],[SALES_PRODUCT_SIZE11],[SALES_PRODUCT_COLOR11],[SALES_ITEM_QTY11], [SALES_ITEM_COST11]" _
& ",[SALES_PRODUCT_ID12],[SALES_PRODUCT_TYPE12],[SALES_PRODUCT_REFERENCE12],[SALES_PRODUCT_SIZE12],[SALES_PRODUCT_COLOR12],[SALES_ITEM_QTY12], [SALES_ITEM_COST12]" _
& ",[SALES_PRODUCT_ID13],[SALES_PRODUCT_TYPE13],[SALES_PRODUCT_REFERENCE13],[SALES_PRODUCT_SIZE13],[SALES_PRODUCT_COLOR13],[SALES_ITEM_QTY13], [SALES_ITEM_COST13]" _
& ",[SALES_PRODUCT_ID14],[SALES_PRODUCT_TYPE14],[SALES_PRODUCT_REFERENCE14],[SALES_PRODUCT_SIZE14],[SALES_PRODUCT_COLOR14],[SALES_ITEM_QTY14], [SALES_ITEM_COST14]" _
& ",[SALES_PRODUCT_ID15],[SALES_PRODUCT_TYPE15],[SALES_PRODUCT_REFERENCE15],[SALES_PRODUCT_SIZE15],[SALES_PRODUCT_COLOR15],[SALES_ITEM_QTY15], [SALES_ITEM_COST15]" _
& ",[SALES_TOTAL_B4TAX],[SALES_TAX],[SALES_INVOICE_TOTAL],[SALES_TYPE])"

'TXTdATEsOLD
MYSQL2 = " VALUES (TEXT202,TEXT453,TEXT455" _
& ", L1ProductId,L1PRODUCTTYPE,L1PRODUCTREFERENCE,L1PRO DUCTSIZE, L1PRODUCTCOLOR,L1TXTQTYSOLD,L1COST" _
& ", L2ProductId,L2PRODUCTTYPE,L2PRODUCTREFERENCE,L2PRO DUCTSIZE, L2PRODUCTCOLOR,L2TXTQTYSOLD,L2COST" _
& ", L3ProductId,L3PRODUCTTYPE,L3PRODUCTREFERENCE,L3PRO DUCTSIZE, L3PRODUCTCOLOR,L3TXTQTYSOLD,L3COST" _
& ", L4ProductId,L4PRODUCTTYPE,L4PRODUCTREFERENCE,L4PRO DUCTSIZE, L4PRODUCTCOLOR,L4TXTQTYSOLD,L4COST" _
& ", L5ProductId,L5PRODUCTTYPE,L5PRODUCTREFERENCE,L5PRO DUCTSIZE, L5PRODUCTCOLOR,L5TXTQTYSOLD,L5COST" _
& ", L6ProductId,L6PRODUCTTYPE,L6PRODUCTREFERENCE,L6PRO DUCTSIZE, L6PRODUCTCOLOR,L6TXTQTYSOLD,L6COST" _
& ", L7ProductId,L7PRODUCTTYPE,L7PRODUCTREFERENCE,L7PRO DUCTSIZE, L7PRODUCTCOLOR,L7TXTQTYSOLD,L7COST" _
& ", L8ProductId,L8PRODUCTTYPE,L8PRODUCTREFERENCE,L8PRO DUCTSIZE, L8PRODUCTCOLOR,L8TXTQTYSOLD,L8COST" _
& ", L9ProductId,L9PRODUCTTYPE,L9PRODUCTREFERENCE,L9PRO DUCTSIZE, L9PRODUCTCOLOR,L9TXTQTYSOLD,L9COST" _
& ", L10ProductId,L10PRODUCTTYPE,L10PRODUCTREFERENCE,L1 0PRODUCTSIZE, L10PRODUCTCOLOR,L10TXTQTYSOLD,L10COST" _
& ", L11ProductId,L11PRODUCTTYPE,L11PRODUCTREFERENCE,L1 1PRODUCTSIZE, L11PRODUCTCOLOR,L11TXTQTYSOLD,L11COST" _
& ", L12ProductId,L12PRODUCTTYPE,L12PRODUCTREFERENCE,L1 2PRODUCTSIZE, L12PRODUCTCOLOR,L12TXTQTYSOLD,L12COST" _
& ", L13ProductId,L13PRODUCTTYPE,L13PRODUCTREFERENCE,L1 3PRODUCTSIZE, L13PRODUCTCOLOR,L13TXTQTYSOLD,L13COST" _
& ", L14ProductId,L14PRODUCTTYPE,L14PRODUCTREFERENCE,L1 4PRODUCTSIZE, L14PRODUCTCOLOR,L14TXTQTYSOLD,L14COST" _
& ", L15ProductId,L15PRODUCTTYPE,L15PRODUCTREFERENCE,L1 5PRODUCTSIZE, L15PRODUCTCOLOR,L15TXTQTYSOLD,L15COST" _
& ",INVB4TAX,SALES_TAX,SALES_INVOICE_TOTAL,CBXSALETY PE)"



FULLSQL = MYSQL & MYSQL1 & MYSQL2

DoCmd.RunSQL FULLSQL


'************************************************* ***************
' UPDATE TABLE
'************************************************* ****************
Me.Refresh

'If L1TxtQtySold > 0 Then
Update [PRODUCT]
Set [PRODUCT].[PRODUCT_IN_STOCK] = ([PRODUCT].[PRODUCT_IN_STOCK] - L1TXTQTLSOLD)
WHERE [PRODUCT].[PRODUCT_ID] = L1ProductId
Me.Refresh
'End If

End Sub

[jker]

Update [PRODUCT] is supposed to be my SQL code to update columns in that table.


here is the code as it is. It includes a SQL INSERT into SALES table and an UPDATE into products table
Private Sub btnSave_Click()
'INSERT ENTRY INTO SALES TABLE
'************************************************* ***************
' UPDATE SALES TABLE
'************************************************* ****************
Dim MYSQL2 As String
Dim FULLSQL As String

MYSQL = "INSERT INTO SALES"
MYSQL1 = " ([SALES_CUSTOMER_ID],[SALES_DATE_SOLD],[SALES_DATE_TIME]" _
& ",[SALES_PRODUCT_ID1],[SALES_PRODUCT_TYPE1],[SALES_PRODUCT_REFERENCE1],[SALES_PRODUCT_SIZE1],[SALES_PRODUCT_COLOR1],[SALES_ITEM_QTY1], [SALES_ITEM_COST1]" _
& ",[SALES_PRODUCT_ID2],[SALES_PRODUCT_TYPE2],[SALES_PRODUCT_REFERENCE2],[SALES_PRODUCT_SIZE2],[SALES_PRODUCT_COLOR2],[SALES_ITEM_QTY2], [SALES_ITEM_COST2]" _
& ",[SALES_PRODUCT_ID3],[SALES_PRODUCT_TYPE3],[SALES_PRODUCT_REFERENCE3],[SALES_PRODUCT_SIZE3],[SALES_PRODUCT_COLOR3],[SALES_ITEM_QTY3], [SALES_ITEM_COST3]" _
& ",[SALES_PRODUCT_ID4],[SALES_PRODUCT_TYPE4],[SALES_PRODUCT_REFERENCE4],[SALES_PRODUCT_SIZE4],[SALES_PRODUCT_COLOR4],[SALES_ITEM_QTY4], [SALES_ITEM_COST4]" _
& ",[SALES_PRODUCT_ID5],[SALES_PRODUCT_TYPE5],[SALES_PRODUCT_REFERENCE5],[SALES_PRODUCT_SIZE5],[SALES_PRODUCT_COLOR5],[SALES_ITEM_QTY5], [SALES_ITEM_COST5]" _
& ",[SALES_PRODUCT_ID6],[SALES_PRODUCT_TYPE6],[SALES_PRODUCT_REFERENCE6],[SALES_PRODUCT_SIZE6],[SALES_PRODUCT_COLOR6],[SALES_ITEM_QTY6], [SALES_ITEM_COST6]" _
& ",[SALES_PRODUCT_ID7],[SALES_PRODUCT_TYPE7],[SALES_PRODUCT_REFERENCE7],[SALES_PRODUCT_SIZE7],[SALES_PRODUCT_COLOR7],[SALES_ITEM_QTY7], [SALES_ITEM_COST7]" _
& ",[SALES_PRODUCT_ID8],[SALES_PRODUCT_TYPE8],[SALES_PRODUCT_REFERENCE8],[SALES_PRODUCT_SIZE8],[SALES_PRODUCT_COLOR8],[SALES_ITEM_QTY8], [SALES_ITEM_COST8]" _
& ",[SALES_PRODUCT_ID9],[SALES_PRODUCT_TYPE9],[SALES_PRODUCT_REFERENCE9],[SALES_PRODUCT_SIZE9],[SALES_PRODUCT_COLOR9],[SALES_ITEM_QTY9], [SALES_ITEM_COST9]" _
& ",[SALES_PRODUCT_ID10],[SALES_PRODUCT_TYPE10],[SALES_PRODUCT_REFERENCE10],[SALES_PRODUCT_SIZE10],[SALES_PRODUCT_COLOR10],[SALES_ITEM_QTY10], [SALES_ITEM_COST10]" _
& ",[SALES_PRODUCT_ID11],[SALES_PRODUCT_TYPE11],[SALES_PRODUCT_REFERENCE11],[SALES_PRODUCT_SIZE11],[SALES_PRODUCT_COLOR11],[SALES_ITEM_QTY11], [SALES_ITEM_COST11]" _
& ",[SALES_PRODUCT_ID12],[SALES_PRODUCT_TYPE12],[SALES_PRODUCT_REFERENCE12],[SALES_PRODUCT_SIZE12],[SALES_PRODUCT_COLOR12],[SALES_ITEM_QTY12], [SALES_ITEM_COST12]" _
& ",[SALES_PRODUCT_ID13],[SALES_PRODUCT_TYPE13],[SALES_PRODUCT_REFERENCE13],[SALES_PRODUCT_SIZE13],[SALES_PRODUCT_COLOR13],[SALES_ITEM_QTY13], [SALES_ITEM_COST13]" _
& ",[SALES_PRODUCT_ID14],[SALES_PRODUCT_TYPE14],[SALES_PRODUCT_REFERENCE14],[SALES_PRODUCT_SIZE14],[SALES_PRODUCT_COLOR14],[SALES_ITEM_QTY14], [SALES_ITEM_COST14]" _
& ",[SALES_PRODUCT_ID15],[SALES_PRODUCT_TYPE15],[SALES_PRODUCT_REFERENCE15],[SALES_PRODUCT_SIZE15],[SALES_PRODUCT_COLOR15],[SALES_ITEM_QTY15], [SALES_ITEM_COST15]" _
& ",[SALES_TOTAL_B4TAX],[SALES_TAX],[SALES_INVOICE_TOTAL],[SALES_TYPE])"

'TXTdATEsOLD
MYSQL2 = " VALUES (TEXT202,TEXT453,TEXT455" _
& ", L1ProductId,L1PRODUCTTYPE,L1PRODUCTREFERENCE,L1PRO DUCTSIZE, L1PRODUCTCOLOR,L1TXTQTYSOLD,L1COST" _
& ", L2ProductId,L2PRODUCTTYPE,L2PRODUCTREFERENCE,L2PRO DUCTSIZE, L2PRODUCTCOLOR,L2TXTQTYSOLD,L2COST" _
& ", L3ProductId,L3PRODUCTTYPE,L3PRODUCTREFERENCE,L3PRO DUCTSIZE, L3PRODUCTCOLOR,L3TXTQTYSOLD,L3COST" _
& ", L4ProductId,L4PRODUCTTYPE,L4PRODUCTREFERENCE,L4PRO DUCTSIZE, L4PRODUCTCOLOR,L4TXTQTYSOLD,L4COST" _
& ", L5ProductId,L5PRODUCTTYPE,L5PRODUCTREFERENCE,L5PRO DUCTSIZE, L5PRODUCTCOLOR,L5TXTQTYSOLD,L5COST" _
& ", L6ProductId,L6PRODUCTTYPE,L6PRODUCTREFERENCE,L6PRO DUCTSIZE, L6PRODUCTCOLOR,L6TXTQTYSOLD,L6COST" _
& ", L7ProductId,L7PRODUCTTYPE,L7PRODUCTREFERENCE,L7PRO DUCTSIZE, L7PRODUCTCOLOR,L7TXTQTYSOLD,L7COST" _
& ", L8ProductId,L8PRODUCTTYPE,L8PRODUCTREFERENCE,L8PRO DUCTSIZE, L8PRODUCTCOLOR,L8TXTQTYSOLD,L8COST" _
& ", L9ProductId,L9PRODUCTTYPE,L9PRODUCTREFERENCE,L9PRO DUCTSIZE, L9PRODUCTCOLOR,L9TXTQTYSOLD,L9COST" _
& ", L10ProductId,L10PRODUCTTYPE,L10PRODUCTREFERENCE,L1 0PRODUCTSIZE, L10PRODUCTCOLOR,L10TXTQTYSOLD,L10COST" _
& ", L11ProductId,L11PRODUCTTYPE,L11PRODUCTREFERENCE,L1 1PRODUCTSIZE, L11PRODUCTCOLOR,L11TXTQTYSOLD,L11COST" _
& ", L12ProductId,L12PRODUCTTYPE,L12PRODUCTREFERENCE,L1 2PRODUCTSIZE, L12PRODUCTCOLOR,L12TXTQTYSOLD,L12COST" _
& ", L13ProductId,L13PRODUCTTYPE,L13PRODUCTREFERENCE,L1 3PRODUCTSIZE, L13PRODUCTCOLOR,L13TXTQTYSOLD,L13COST" _
& ", L14ProductId,L14PRODUCTTYPE,L14PRODUCTREFERENCE,L1 4PRODUCTSIZE, L14PRODUCTCOLOR,L14TXTQTYSOLD,L14COST" _
& ", L15ProductId,L15PRODUCTTYPE,L15PRODUCTREFERENCE,L1 5PRODUCTSIZE, L15PRODUCTCOLOR,L15TXTQTYSOLD,L15COST" _
& ",INVB4TAX,SALES_TAX,SALES_INVOICE_TOTAL,CBXSALETY PE)"



FULLSQL = MYSQL & MYSQL1 & MYSQL2

DoCmd.RunSQL FULLSQL


'************************************************* ***************
' UPDATE TABLE
'************************************************* ****************
Me.Refresh

'If L1TxtQtySold > 0 Then
Update [PRODUCT]
Set [PRODUCT].[PRODUCT_IN_STOCK] = ([PRODUCT].[PRODUCT_IN_STOCK] - L1TXTQTLSOLD)
WHERE [PRODUCT].[PRODUCT_ID] = L1ProductId
Me.Refresh
'End If

End Sub

[xps35]

Looking at the fields in the query, I think your database is very poorly designed. There is little point in continuing on this path. You will then continue to encounter problems. First design a good (normalized) database. Then we'll talk further.

[mike60smart]

Hi
As Peter says your tables are not structured correctly.
Can you upload a zipped copy of the database with no confidential data?

[jker]

Looking at the fields in the query, I think your database is very poorly designed. There is little point in continuing on this path. You will then continue to encounter problems. First design a good (normalized) database. Then we'll talk further.

AS i STATED,,I AM NEW TO ACCESS..... this is my first design... So,,, you might be a gold medal expert, but at least give me a break

[xps35]

I would like to help you. Therefore, I warn you that you are on the wrong path. However, I don't want to waste time on a bad solution. If you are willing to thoroughly adjust your design, I would be happy to help you with that.