Help me clean this up: making a series of calculation dependent calculations update when changing...

[bytreeide]

Help me clean this up: making a series of calculation dependent calculations update when changing one of the controls.



I am trying to make a "table" that projects profit depending on number of units sold per month. The trouble that I am having is that if I want to change the number of units sold in Month 6 then I am not getting an automatic update from Month 7 through Month 12.

I have tried this two ways.
1. I have tried putting code in "Form_Current"
2. I have tried putting code in "AfterUpdate" for each month

The problem that I am getting is that in case 1, I have to hit the "Refresh All" button to get the rest of the controls to update; with case 2, I have to go through all of the controls for each subsequent month and reenter the number there.

Case 1 Code

Private Sub Form_Current()
Me.ProfPerM1 = Me.UnitsPerM1 * Me.ProfPerUnit
Me.ProfPerM2 = Me.UnitsPerM2 * Me.ProfPerUnit
Me.ProfPerM3 = Me.UnitsPerM3 * Me.ProfPerUnit
Me.ProfPerM4 = Me.UnitsPerM4 * Me.ProfPerUnit
Me.ProfPerM5 = Me.UnitsPerM5 * Me.ProfPerUnit
Me.ProfPerM6 = Me.UnitsPerM6 * Me.ProfPerUnit
Me.ProfPerM7 = Me.UnitsPerM7 * Me.ProfPerUnit
Me.ProfPerM8 = Me.UnitsPerM8 * Me.ProfPerUnit
Me.ProfPerM9 = Me.UnitsPerM9 * Me.ProfPerUnit
Me.ProfPerM10 = Me.UnitsPerM10 * Me.ProfPerUnit
Me.ProfPerM11 = Me.UnitsPerM11 * Me.ProfPerUnit
Me.ProfPerM12 = Me.UnitsPerM12 * Me.ProfPerUnit
Me.SumM1 = Me.ProfPerM1
Me.SumM2 = Me.SumM1 + Me.ProfPerM2
Me.SumM3 = Me.SumM2 + Me.ProfPerM3
Me.SumM4 = Me.SumM3 + Me.ProfPerM4
Me.SumM5 = Me.SumM4 + Me.ProfPerM5
Me.SumM6 = Me.SumM5 + Me.ProfPerM6
Me.SumM7 = Me.SumM6 + Me.ProfPerM7
Me.SumM8 = Me.SumM7 + Me.ProfPerM8
Me.SumM9 = Me.SumM8 + Me.ProfPerM9
Me.SumM10 = Me.SumM9 + Me.ProfPerM10
Me.SumM11 = Me.SumM10 + Me.ProfPerM11
Me.SumM12 = Me.SumM11 + Me.ProfPerM12
End Sub

Case 2 Code

Private Sub UnitsPerM1_AfterUpdate()
Me.ProfPerM1 = Me.UnitsPerM1 * Me.ProfPerUnit
Me.SumM1 = Me.ProfPerM1
End Sub

Private Sub UnitsPerM2_AfterUpdate()
Me.ProfPerM2 = Me.UnitsPerM2 * Me.ProfPerUnit
Me.SumM2 = Me.SumM1 + Me.ProfPerM2
End Sub

........ etc

Private Sub UnitsPerM12_AfterUpdate()
Me.ProfPerM12 = Me.UnitsPerM12 * Me.ProfPerUnit
Me.SumM12 = Me.SumM11 + Me.ProfPerM12
End Sub

The one solution that I have thought of that would yield the result that I desire would be to implement Case 1 in all of the "After_Update" events for months 1 through 12. This solution just feels sloppy to me. I was wondering if there is a cleaner way to do this?

[ranman256]

All these calculations should be in a query. THEN you get instant recalcs.

[bytreeide]

ranman,

I didn't mean to ignore you, I have been out of the office for a week.

So to clarify, because I am a novice, should I make a new query or add these calculations to the Record Source for the Form?

If i do a new query, then how do I get the calculated information in to the text box?

The second option seems to make more sense to me, your advice please.

bytreeide

[bytreeide]

To follow-up with my previous question: I have successfully created the query to perform calculations and I have used that query as a source in the Form Query Builder.

Now I can't enter values into the text boxes, I checked the properties and the boxes are not locked.

bytreeide

[June7]

Point ranman makes is that, normally, calculated values should not be saved into table. Do calcs when needed - in queries or textbox.

Why would you need to do data entry into calculated fields or textboxes? Since the ControlSource is an expression or bound to a calculated field, cannot enter data - the value is calculated.

[bytreeide]

I have a text box that is included in the calculation, not the outcome of the calculation, but one of the input instead. That particular text box needs to be able to receive user input.

If you look into my original post, then the text box that I have labeled "Me.UnitsPerM1" requires a user input.

So when I followed ranman's suggestion to do the calculations in a query, then I am unable to change the value in the text box labeled "Me.UnitsPerM1"

bytreeide

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[bytreeide]

I would rather not provide my db for analysis, thank you. Can you suggest something without me providing my project?

[June7]

No idea why the textbox is not editable. Ranman's suggestion is sound.

However, why is query referencing textbox in calculation? The query should reference other fields. Does textbox have same name as field it is bound to? I always give controls a name different from the field, like tbxQuantity.

[bytreeide]

However, why is query referencing textbox in calculation?

It was suggested that I use a query to perform my calculations, so I did. Part of the calculation requires user input, so that is why there is a text box involved. But really the QUERY pulls data from a TABLE (to my understanding) so there are no "text boxes" on the table, however the controls on the FORM, that takes data from the TABLE, are being populated with the calculation as well as the ones that are used as inputs for the calculation are all "text boxes" (to my understanding).

The query should reference other fields. Does textbox have same name as field it is bound to?

My query does reference other fields, from a table. Yes, the textbox and the field have the same name.

I always give controls a name different from the field, like tbxQuantity.

Do you mean that the control (textbox, combobox, etc) name that is on the FORM needs to be different from the field name on the TABLE?

[June7]

The user input to bound control should pass to record in table and the calc should work. Focus must be moved from the input control for the field update to commit then the calc will take place.

Why textbox doesn't allow input is unknown without being able to examine query and form design.

That is my practice for naming.

[bytreeide]

Okay. I have two different forms where I am doing this. One works and the other does not. I will try to figure out the differences between the two and that should solve my problem.

[bytreeide]

Cost Reducing Tool Help.zip

I was wrong in my last posting. The one FORM that I thought was working was only because I didn't have the QUERY working for it. Now I have both of my forms set up to run calculations in queries in stead of in the VBA code area. I still have the problem of being able to input values where I want to manipulate the calculation. I am frustrated and clueless, I hesitate showing off my db like a hoarder hesitates inviting guests over to their house.

The key focuses are "Form5EXP", "Form6CAP", "Q5_CummulativeDM", "Q5_DmPerMonth", and "Q6_CashFlow"

The rest of the db is probably unorthodoxily organized and assembled, so please ignore the rest of it for the moment. I think I removed all sensitive information, just emails and phone numbers.

[June7]

The forms have tables and queries in their RecordSource but these datasets are not JOINED. The queries need ID field then set links to the table in the RecordSource SQL statement. At its simplest, like:

SELECT T5EXP.*, Q5_DmPerMonth.*, Q5_CummulativeDM.*
FROM Q5_CummulativeDM RIGHT JOIN (Q5_DmPerMonth RIGHT JOIN T5EXP ON Q5_DmPerMonth.ID = T5EXP.ID) ON Q5_CummulativeDM.ID = T5EXP.ID;

Will have to change ControlSource property as needed.

However, these queries are causing the form RecordSource to be non editable, although I am not quite sure why. If all these calculated fields as well as the native fields were all in a single query, the RecordSource is then editable.

SELECT T5EXP.*, T5EXP.[T5CummDM1]+[T5DMperM2] AS T5CummDM2, T5EXP.[T5CummDM2]+[T5DMperM3] AS T5CummDM3, T5EXP.[T5CummDM3]+[T5DMperM4] AS T5CummDM4, T5EXP.[T5CummDM4]+[T5DMperM5] AS T5CummDM5, T5EXP.[T5CummDM5]+[T5DMperM6] AS T5CummDM6, T5EXP.[T5CummDM6]+[T5DMperM7] AS T5CummDM7, T5EXP.[T5CummDM7]+[T5DMperM8] AS T5CummDM8, T5EXP.[T5CummDM8]+[T5DMperM9] AS T5CummDM9, T5EXP.[T5CummDM9]+[T5DMperM10] AS T5CummDM10, T5EXP.[T5CummDM10]+[T5DMperM11] AS T5CummDM11, T5EXP.[T5CummDM11]+[T5DMperM12] AS T5CummDM12, [T5DmPerUnit]*[T5UnitsPerM2] AS T5DMperM2, [T5DmPerUnit]*[T5UnitsPerM3] AS T5DMperM3, [T5DmPerUnit]*[T5UnitsPerM4] AS T5DMperM4, [T5DmPerUnit]*[T5UnitsPerM5] AS T5DMperM5, [T5DmPerUnit]*[T5UnitsPerM6] AS T5DMperM6, [T5DmPerUnit]*[T5UnitsPerM7] AS T5DMperM7, [T5DmPerUnit]*[T5UnitsPerM8] AS T5DMperM8, [T5DmPerUnit]*[T5UnitsPerM9] AS T5DMperM9, [T5DmPerUnit]*[T5UnitsPerM10] AS T5DMperM10, [T5DmPerUnit]*[T5UnitsPerM11] AS T5DMperM11, [T5DmPerUnit]*[T5UnitsPerM12] AS T5DMperM12
FROM T5EXP;

Really should do this sort of data manipulation and output on reports, not forms.

[ssanfu]

I, also, had a look at your dB. There are several things that I would change.

Overall your naming scheme is pretty good. There are a few special characters in object names that I would change and a couple of reserved words. Only one field with a space (shouldn't use spaces in object names).
Your table structure, however, needs lots of work.

- You have some PK fields as text. (In my dBs, ALL PK fields are Autonumber (type Long).)
Read these about Autonumber use:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm


- You have PK fields linked (related) to another PK field. This results in a 1-to-1 relationship. 1-to-1 relationships are extremely rare.
- I do not use attachment fields. They tend to cause dB bloat.
- There are 6 PK fields named "ID". In addition to being a very poor name, "ID" is not very descriptive. And when used in queries, having two field named "ID" can get confusing.


- From statments you have made and the looks of your tables, it looks like you have tried to convert an Excel spreadsheet to an Access dB. Your tables are short and fat and have repeating fields. A normalized DB table structure is tall and narrow and without repeating fields (usually).

-------------------------------
OK, enough of that for now.

The key focuses are "Form5EXP", "Form6CAP", "Q5_CummulativeDM", "Q5_DmPerMonth", and "Q6_CashFlow"

In looking at "Form5EXP", "Q5_CummulativeDM" and "Q5_DmPerMonth", I saw that all of the calculated fields in "Q5_CummulativeDM" and "Q5_DmPerMonth" were from "T5EXP" so I combined the table and the calculated fields into one query. I named this query "Q_T5EXP" and set it as the record source for the form "Form5EXP".

The did the same thing for "Form6CAP" and "Q6_CashFlow".

Referring to "Form5EXP":
I changed the form record source to a saved query.
You have fields in the table named the same as calculated fields in the query. In the new query, I changed the names of the calculated fields to differentiate the field bound to the controls on the form. All of the calculations are displayed on the form but not saved to the table. That will take VBA code.

"Form6CAP" was treated similarly but without having to change the calculated field names......(I learned from "Form5EXP")


In the attached dB, I deleted unnecessary queries and forms to highlight my changes, leaving only "Form5EXP", "Q_T5EXP", "Form6CAP" and "Q_Cashflow6".

Sorry this is so long.....