Using--After Update-- to update field in table. 
Author Message
 Using--After Update-- to update field in table.


I am trying to update the AccountBalance field, in my
Customer_Balance_Table, every time a transaction is
entered into the Transaction_Table.

I am trying to code this into the After Update field of
the form for entering transactions into the
Transaction_Table.  The code I have used that updates the
field is:

UPDATE Customer_Balance_Table
SET Customer_Balance_Table.AccountBalance = 256;

However this puts 256 in the field and not the sum total
that I need.

I have a query [TT_Query] that sums the totals per
customer and I have tried to get that sum total to enter
instead of 256 but I am having trouble with my SQL
statement to do that.

I have tried to the following code with no luck:

UPDATE Customer_Balance_Table
SET Customer_Balance_Table.AccountBalance =
Transaction_Table.CustomerID, Sum
(Transaction_Table.TransactionTotal) AS

Thanks for your time!


Sun, 23 Oct 2005 05:09:31 GMT  
 Using--After Update-- to update field in table.

> UPDATE Customer_Balance_Table
> SET Customer_Balance_Table.AccountBalance
>        = TT_Query.SumOfTransactionTotal
> WHERE CustomerID IN
>       Transaction_Table.CustomerID,
>       Sum(Transaction_Table.TransactionTotal) AS
>           SumOfTransactionTotal
> );

There is a number of problems here:

1) TT_Query.SumOfTransactionTotal refers to a table that does not appear
anywhere else. I think you can use a FROM clause to do updates that need to
happen on joins

2) When you use IN SELECT... you must only return one value: this subSELECT
returns two columns and an unknown number of rows, and there is no FROM

You might be better served simply by calculating the working balance
(perhaps with a DSUM function) and putting it directly into the UPDATE
command like this:

  ' get the working balance
  dblNewBalance = DSum("TransactionTotal","TTQuery", _
      "CustomerID=""" & Me!txtCustID &   """")

  ' Now create the SQL
  strSQL = "UPDATE CustomerBalance " & vbCrLf
  strSQL = strSQL & "SET AccountBalance = " & _
       Format$(dblNewBalance,"0.00") & ";"

  ' This is really helpful for debugging!
  MsgBox strSQL

  ' Okay, now send it: bracket this with On Error etc.
  db.Execute strSQL, dbFailOnError

On the other hand, the fact that this is difficult shows what you lead
yourself into when you ditch relational theory. The AccountBalance is not
dependent on the CustomerID, so it should not be in this table. It's
actually dependent on a number of records in another table, and that is
where it should be coming from.

Your very best plan might be simply to use a query to get the _actual_
account balance when it's needed. But then again, I'm not a banker.

Best wishes

Tim F

Mon, 24 Oct 2005 06:15:12 GMT  
 [ 2 post ] 

 Relevant Pages 

1. 2nd SQL Server update after update text field corrupts row - using RDO

2. How to update datetime field with NULL using ADO update

3. Using Update to update an Access field

4. Update a field in each record of a table using a recordset

5. how to update a Numeric field in a table using VB6 ADO

6. Updating data to a memo field using SQL UPDATE

7. update table field from xref table

8. updating fields in table from field in another table

9. Use report fields to update table fields

10. Newbie Update field in DB using AutoNumber field

11. Newbie Update field in DB using AutoNumber field

12. Using a defined Variable to Update a Table using DoCmd.RunSQL


Powered by phpBB® Forum Software