Running total

I have the need to update a field in another table . It is a calculation in one table that updates the running total in another table .
ex. Table Orders

fldqty * fldcost
after Record updated event

ex. Table Inventory

fldInv = fldInv - fldqty(from Orders Table on the fly for only the record updated)

1. Would it better to do in an event or code snippet
   Update : basically trying to update a field in inventory table using a calculation of two fields in Orders
   I tested with the following code in after record updated
   str = "select * from POS_Inventory where fldpos='" & values("fldavail") & "'"
   
   Set rsTemp = server.CreateObject("ADODB.Recordset")
   
   rsTemp.open str, dbConnection
   
   Session("fldinv") = rsTemp("fldinv")
   
   rsTemp.close : set rsTemp = nothing
   sql ="update POS_Inventory set fldinv = " & values("posinv")& " - " & values("fldinv")&" where fldavail = ' " & dict("fldpos") & "'"
   
   customQuery(sql)
   . Getting error
   
   below.
   Microsoft JET Database Engine error '80040e14'
   Syntax error (missing operator) in query expression 'fldpos=Key Chains'.
   /cdi/output3/Promotions_edit.asp, line 0
   Any Ideas fldpos is a text field.

1. Would it better to do in an event or code snippet
   Update : basically trying to update a field in inventory table using a calculation of two fields in Orders
   I tested with the following code in after record updated
   str = "select * from POS_Inventory where fldpos='" & values("fldavail") & "'"
   
   Set rsTemp = server.CreateObject("ADODB.Recordset")
   
   rsTemp.open str, dbConnection
   
   Session("fldinv") = rsTemp("fldinv")
   
   rsTemp.close : set rsTemp = nothing
   sql ="update POS_Inventory set fldinv = " & values("posinv")& " - " & values("fldinv")&" where fldavail = ' " & dict("fldpos") & "'"
   
   customQuery(sql)
   . Getting error
   
   below.
   Microsoft JET Database Engine error '80040e14'
   Syntax error (missing operator) in query expression 'fldpos=Key Chains'.
   /cdi/output3/Promotions_edit.asp, line 0
   Any Ideas fldpos is a text field.
   

And an even easier explanation! but not solved.
Table 1

buy1 Inv1 Text1 (Note : Inv1 in Table 1 is a Read Only field here dependent dropdown from Table 2))
Table 2

Inv1 Text2
When buy1(table1) is updated need ( table 1 to subtract buy1 from Inv1 and UPDATE Inv1 in Table 2)
Any Ideas Here? Need help with UPDATE statement using values("...") with a calculation (After Update Event) . Trying to stay away from Stored Procedure which can't be accomplished in an event inside ASPRunner.
Asprunner 7.0 Build 9948. Note : Updated read only field values with 6.2 in the past . Having problems with update statement and using vaules("fieldname") keep getting type mismatch error when fields are all numbers in the database (access) . Have tested with update statements setting just a value to (ex.. 1) which works but using the dal dictionary values("...") or dict("...") does not pull values for the record being updated.
As to the "where" statement I am looking for the record of the matching text2 in table2 that is also updated in table1 text1 which not a key field but can use the key field if it would be availalbe for that record being updated . Not sure the syntax on 7.0 for key field.
Need help with example . Thanks.

As a universal advice - print SQL query on the page instead of executing to see what might be wrong and what is causing type mismatch error.

Thanks Sergey for getting back to me on this but I set debug mode to true . It only gets past the original SQL query . When I use the update statement
This is after Record Updated.
sql ="update POS_Inventory set fldinv = fldinv - " & values("posinv")& " where pos = ' " & dict("fldavail") & "'"

customQuery(sql)
( fldinv is from the table to be updated as well as pos which is not updated but matches value in original table)
I get expected end of statement on server but before building syntax okay on check ASPrunner syntax check ; so I put a Calculated field in my updating table and tried to use that instead it is (read only) and used this query
sql ="update POS_Inventory set fldinv = " & values("totpos")& " Where pos=" & values("fldavail")

customQuery(sql)
( fldinv is from the table to be updated as well as pos which is not updated but matches value in original table)
Type Mismatch error

Note Where statement are text fields and if I take out where it still does not update calculated field values("totpos") which is a calculated field in editor custom field rs("posinv) - rs("fldqty")
all it gives on nonfriendly error messages.
Note : I haven't used update statements to often with SQL ; read your manual and also tried the following without a new query . Not sure if I need a coresponding query with the below either as the values should already be queried. The param ; I used old values because the read only field when updated should use the old value first to update the other table . Not to confuse but the below was tried after the above codes ; either of the 3 has not worked
Did not work either below.
"No corresponding query . As The values below are should already be queried. except for the fldinv feild on the other table to be updated" the sample below according to your manual should update but don't know how it would just update the current record without knowing the key field. ???
dal.Table("POS_Inventory").Param("fldinv")=oldvalues("posinv")
dal.Table("POS_Inventory").Value("fldinv")=values("posinv")
dal.Table("POS_Inventory").Update()
values.Remove("fldinv")

My suggestion is to print your custom query on the page rather than turning debug mode on.

Printed sql see below statements with printed sql :
sql ="update POS_Inventory set fldinv =' " & values("totpos")& " ' Where pos='" & values("fldavail") & "'"

response.write sql

response.end

customQuery(sql)
Returns :

update POS_Inventory set fldinv =' ' Where pos='tshirts'
Also tried this:
sql ="update POS_Inventory set fldinv = fldinv - " & values("posinv")& " where pos = ' " & dict("fldavail") & "'"
response.write sql

response.end

customQuery(sql)

Returns:
"update POS_Inventory set fldinv = fldinv - 7 where pos = ' tshirts'
The second one seems to work correctly but table is not updated. The first does not seem to pull value of totpos.
Any Ideas.Thanks

I guess this query gives you trouble:

If fldinv is a numeric field that would produce type mismatch error.

If fldinv is a numeric field that would produce type mismatch error.

I didn't realize the where statement required the same field type as field I am trying to update . I also just tried this.
sql ="update POS_Inventory set fldinv = fldinv - " & values("posinv")& " where ID = ' " & values("ID") & "'"
Not sure if this grabs current record which is all I need to do in the where statement.
Now get Data Type Mismatch in criteria statement . Both are numeric.
Returns this on print sql:
update POS_Inventory set fldinv = fldinv - 7 where ID = ' 204'

John,
your understanding is not 100% correct. You are trying to fix the query that was correct.
In short, when you are working with numeric fields (in update statement or in WHERE clause) do not use single quotes. When you are working with text or date fields do use single quotes.

Okay . Finally got it to work but had to change the statement altoghter. I'm sure there is an easier statement but the below works.
sql ="update POS_Inventory set fldinv = fldinv - " & values("posqty")& " Where pos IN ( Select pos From POS_Inventory where pos = '" & values("fldavail") & "') And Brand IN ( Select Brand from POS_Inventory where Brand = '" & values("fldbrand") & "') And Supplier IN ( Select Supplier from POS_Inventory where Supplier = '" & values("fldsupplier") & "')"

customQuery(sql)
sql Printed Results look good.

update POS_Inventory set fldinv = fldinv - 250 Where pos IN ( Select pos From POS_Inventory where pos = 'tshirts') And Brand IN ( Select Brand from POS_Inventory where Brand = 'Testbrand') And Supplier IN ( Select Supplier from POS_Inventory where Supplier = 'testsupplier')