Creating a Balance # from multiple fields on a form

[jjz0512]

I have a problem that I will try to explain. I have a Access UI link to an Oracle database. In one of the tables, CME, I have 10 CME Expense element and a Balance of CME stored in the table. That populated with data, if available, that was stored for an ID. When I add 7CME information, 7CME Expense Desc, 7CME Expense & 7CME Date Paid, whenever I, move to another field, click the Refresh Button or change to another tab form, I would like to update Balance of CME with the amount entered and/or the sum of 1-10CME Expense fields. Balance of CME can come from the table, CME, when I first populate the form, and I need to updated it when ever an 1-10CME expense is inserted or updated. But don't know how to do it. The control source for Balance of CME is the element CME.BALANCE_OF_CME. I included a copy of the form.





Thanks for your help, it's much appreciated.

[pbaldy]

Option 1 would be to fix the design, which is not normalized:

Fundamentals of Relational Database Design -- r937.com

Presuming that won't be done, I'd create a form level function that added the 10 amount fields and put the result in the total textbox, and call that function from the after update event of the 10 amount textboxes.

[jjz0512]

pbaldy, no, the database won't be changed, it's a inherited UI and Database. I not sure what you mean by a form level function, as I'm relatively new to this.

[jjz0512]

I got it to work. I entered code for each CME_Expense, after update, of;

Forms("DEMOGRAPHICS").[CME].Form.[BALANCE_OF_CME] = Nz(Forms("DEMOGRAPHICS").[CME].Form.[1CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[2CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[3CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[4CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[5CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[6CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[7CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[8CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[9CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[9CME_expense], 0) + Nz(Forms("DEMOGRAPHICS").[CME].Form.[10CME_expense], 0)

But had to create 10 subs, is there an easier way?

[pbaldy]

Yes, the function I mentioned, called from each event. See if this helps:

http://www.baldyweb.com/Function.htm