Question Updating Table with Forms

[fleurette]

Hi,
I have created a form and subform using form wizard where users could enter the data of a new order.

In the form, there are some expressions where it does a computation of the fees that the company earned for each order. This is a percentage of the gross income.

The subform expression formula updates the fee amount and net income automatically when the gross income is entered and fee percentage entered.

Is there a way to update the fee amount and net income which the expressions derived into a data field in a certain table?

I been trying but can't seem to figure it out.

I actually need these information to be in the table too, as data as they are required by other users.

Or is there a better way to do it?
I am mainly using wizards and don't know how to use any SQL or VBA.

Has anyone done the same thing?
Able to share?

Many thanks!

 

[Pat Hartman]

Generally we don't store calculated values. They are just calculated in queries so they are "fresh" each time and you don't have to worry about changes to the underlying operands. It sounds like you are already storing two of the three fields involved in the calculation and that should be sufficient.

 

[billmeye]

It's better not to store calculated fields but to do the calculation in an unbound control at the time it is needed, for example, in your report or for reference on your form. For every control in a report or on a form you can set the Control Source to a calculation:

=IIF(Nz(GrossIncome],0)>0,FormatPercent(Nz([Expense],0)/[GrossIncome],2),0)

If you make changes to Expense or GrossIncome and want to see the calculated control change, you can use the AfterUpdate event procedure and requery this calculation.

 

[fleurette]

Generally we don't store calculated values. They are just calculated in queries so they are "fresh" each time and you don't have to worry about changes to the underlying operands. It sounds like you are already storing two of the three fields involved in the calculation and that should be sufficient.

Hi Pat
Thanks for your reply.
The calculated fields are current in my form. They appear in the subform field when gross income and brokerage percentage are entered.

But they do not update the tables with the calculated results which I need.

Is Access 2007 not able to do that?

Previously I thought I could add a calculated field in the table itself - have the data stored there and yet reflect in the subform for users when they use it. I tried it but that does not seem possible.

I also tried doing a query for the calculated fields, but have no idea either on how I could update the table with the queried results.


Any other tips?
Maybe I am structuring my database wrongly?
Thanks.

 

[fleurette]

It's better not to store calculated fields but to do the calculation in an unbound control at the time it is needed, for example, in your report or for reference on your form. For every control in a report or on a form you can set the Control Source to a calculation:

=IIF(Nz(GrossIncome],0)>0,FormatPercent(Nz([Expense],0)/[GrossIncome],2),0)

If you make changes to Expense or GrossIncome and want to see the calculated control change, you can use the AfterUpdate event procedure and requery this calculation.

Hi billmeye
thank you very much.

I did try using an unbound control and to type in an expression, but a #Name? error came out and I was unable to solve this the whole day.
All the field names look correct but it still does not reflect the calculated results.

As for your suggested formula, do I cut and paste this in the control source itself under the Property tab?

Thank you.

 

[John Big Booty]

Given that you are working with a form/subform set you will need to check this link to get the correct syntax for referring to subform from the main form or visa versa.

 

[billmeye]

If you would like to attach your DB I can take a look.

 

[fleurette]

If you would like to attach your DB I can take a look.

Hi billmeye
Thanks

I really wanted some of the features into the database (which I thought was simple to do using wizards since I know nothing about coding).
- automated invoice number
- able to print a particular invoice for an instalment when it is due instead of a whole string of invoices.
- ageing report (haven't explored yet but I have to do this)
- updating calculated amounts back into the tables as other users need this as raw data.

but even things like these which i thought was simple (compared to the other complicated database and coding I seen in this forum) is proving hard. I spend days trying out on the sample database but things don't work at all.

I have attached the sample database.

Pls let me know if you need any clarifications.

 

[Pat Hartman]

But they do not update the tables with the calculated results which I need.

That's why doing the calculation in a query is more convenient. You can create a single query that does the calculation and use it in many places. Yes, it is possible to store the result but storing calculated values should be avoided unless there is a really good reason. For this simple calculation, it is no burdon to calculate it in a query.

When a control with a calculated values shows #Name, it is generally because the control name duplicates the name of a column from the RecordSource. Usually this happens when you change a control that was added by a wizard or dragged from the field list because in those two cases, Access names the control with the same name as the column it is bound to. So if your control is bound to MyField and the control name is MyField and you change the ControlSource to =MyField * MyOtherField, Access will recognize a conflict since a control name can only = a column name when the column is bound to that control.

 

[fleurette]

That's why doing the calculation in a query is more convenient. You can create a single query that does the calculation and use it in many places. Yes, it is possible to store the result but storing calculated values should be avoided unless there is a really good reason. For this simple calculation, it is no burdon to calculate it in a query.

When a control with a calculated values shows #Name, it is generally because the control name duplicates the name of a column from the RecordSource. Usually this happens when you change a control that was added by a wizard or dragged from the field list because in those two cases, Access names the control with the same name as the column it is bound to. So if your control is bound to MyField and the control name is MyField and you change the ControlSource to =MyField * MyOtherField, Access will recognize a conflict since a control name can only = a column name when the column is bound to that control.

Thanks Pat for the tip.
I will try to see if I can draw my calculated values from the query into the form field.

Currently the calculated values are in the form field itself and I suspect maybe this might slow down as there are more data?

Or does it not make a difference at all?

UPDATE:
I tried removing the =MyField ... it didn't work.
I added the query into the form record source, it worked and draws out the calculated values. But i seem to run into another problem. i now have multiple records when actually there should be only a few records relating to a particular order at each time.

I went back to recheck the query and the results when it is run but they look fine.

In my main and subform, I added the order table and instalment table. The instalment database refers to the detailed payments relating to each other.

Am I suppose to add the query somewhere too, to draw out the calculated values?

 

[fleurette]

If you would like to attach your DB I can take a look.

Hi Billmeye
any comments on my sample database?
I attached it a while back.

 

[billmeye]

Before I go dancing around, could you specifically tell me which form and what calculations you would like performed. Thanks.

 

[RainLover]

If you really want to save the result, bearing in mind it could cause problems down the track if someone changes the values that are been used in the calculation.

You save the result in exactly the same way you save all the other values in the form. So you need a field in the Table and the control on the form is bound to that field.

It will save when you leave that record.

BTW someone advised using the After Update Event. It is better that you use the before update event for all data actions.