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 =
TT_Query.SumOfTransactionTotal
Where CustomerID IN (SELECT DISTINCTROW
Transaction_Table.CustomerID, Sum
(Transaction_Table.TransactionTotal) AS
SumOfTransactionTotal);

Thanks for your time!

Ben

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
clause.

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