Add two fields and store in another field

[gconeuk]

Hi,

I have a form (frmFeeIncome) based on a table (tblFeeIncome)

On the form I have three fields which are FeesMonth, FeesYear, FeesUK (currency), FeesElsewhere (currency) and FeesTotal (currency). Each new record is entered like this

January 2015 25000 25000

What I need is for the total in the FeesUK field and FeesElsewhere field to be added up and stored in FeesTotal when FeesTotal has GotFocus

The FeesTotal has to be stored (I know it shouldn't be but it has to!)

Any advice on this? I have tried the following in the GotFocus Event procedure but it does not work.

=[FeesUK] + [FeesElsewhere]

Many thanks in advance.

 

[MarkK]

You need a query like . . .

SELECT fMonth, fYear, fUK, fOther, fUK + fOther As fTotal
FROM YourTable

So in this case the total is not stored, it is calculated by the query. There should not be an fTotal field in the table, it only exists in the query as a calculation.

 

[spikepl]

I know I should not stick my fingers into the wall socket but I realy really want to. So please please help me


There is a number of reasons why data that can be derived from exisiting data should not be stored. Unfortunately, "it has to" is not one of them. So spill it!

You'll likely also be better off of you explain the whole story, because your use of events also seems rather unconventional.

 

[gconeuk]

Hi there,

Feel a bit shameful by the phrase 'because it has too' so sorry, please accept my apologies.

The FeeIncomeTotal (as all the others fields in the database) will be used in a mail merge ms word document to populate a questionnaire/proposal form so I need to store the value somewhere so word can find it.

As an overview, I'm building a database of our business clients (insurance) with their individual data. I have several tables (twenty plus) each of which will be a form or subform which one of our employees inputs data into. I have rationalised these as much as possible, so like above example i havent just used Year1, FeesUk1 etc. Main form is frmClients and the others are subforms all relationship linked one to many by one field ClientRef (which is the common link on all tables). This isn't the Pk, each has its own auto number field for the Pk. Anyway I'm now worried that I should have created a query from all the tables and based all the forms on the query? And then there could be calculated fields on the query? Is there a limit on the number of fields you can use in a query?

Sorry for the extra questions!! But thanks in advance.