Have a Master Record where Account Information is stored and a Detail Record where debits and Credits are entered. They are joined together and by clicking on the detail you can see the total that is owed by the Account. It's a small version of an accounts receivable program. What I need is to be able to see in real time, the total that is owed by adding the sum of the Accounts Table Amount total.
I discovered the code below in the forums and I am trying to do the same thing...although I am not having success.
Tables are as follows:
Database is the Master Record
Total is the fieldname of the field that I want to store the Total Owed
membernum is the keyfield on the Database table
Accounts is the Detail Record
Amount is the fieldname where the money is entered...the sum of which is displayed at the bottom of the list page on the Accounts
MemberNum is the keyfield on the Accounts table
My code entered on the Events tab, placed on the Accounts EDIT and ADD as an AFTER RECORD ADDED
str = "select sum(Amount)from Accounts where Amount="& values("MemberNum")
Set rstmp = server.CreateObject("ADODB.Recordset")
rstmp.open str,dbConnection
'update master table
dbConnection.Execute "update Database set Total="&rstmp(0)&" where MasterKey="&values("membernum")
rstmp.close
set rstmp=nothing
It simply doesn't work.
What am I missing?
Steve Divine