Unanswered: Saving calculated amounts, filtering records and setting criterion for form
I'm developing a financial projections system and
1 - I have a main form which has command buttons on it that open other forms, each of which have subforms. One form, Approvals, for instance, has 10 subforms in it. I use default value in the mainID (FK) field of each of the subforms to copy the ID from the main form. That works fine when you just open it, enter data and then close it but when you reopen the form to check the values, there is nothing to tell it to filter the forms based on the mainID. Any idea on how can I solve this issue?
2 - I need to save calculated fields to the table.
In the approvals form, I have ten subforms, each for one future year. For example, year 1 would be the next year, year 2 would be the year after, year 3 the year after and so on. The user enters a percentage growth for each year and a calculated field shows the increased amount. For year 1 the amount would be an increase on the 'Operations Level' which is a field in the main form. The year 2 amount would be an increase on the year 1 amount, based on the year 2 percentage growth. I need to be able to save this calculated amount to the Growth table (which is the table on which each subform is based).
To solve this issue I added another field to the form with the record source as the Amount field in the growth table. I then set this field to populate using an AfterUpdate event procedure in the calculated amount field. Doesn't work though, any other ideas?
3 - I have a form, disbursement, which has 8 subforms for eight different types of financing agreements. The idea is that once an financing amount is agreed, the money will be disbursed over a period of years. In each of these subforms the user will enter percentage figures in a series of ten text boxes (for ten years), all of which have to add up to 100%. I've created another text box in each subform which shows the total for that particular finance type. Now I have two issues here.
a - The first is that the user should not be allowed to exit the disbursement form unless the calculated fields for each of the 8 finance types show exactly 100.
b - I have set the default value for each of the 80 (10x8) user entered fields as 0 because the calculated field won't show a value if any of the text boxes are empty. If a user enters a figure in lets say year 7 and then changes his mind and says, let's just disburse these amounts over a 6 year period, he'll naturally just delete the amount in the field and then leave it blank. So how do I get the calculated field to treat a null field as 0?
subform that has 10 textboxes in which users enter percentage figures. At the end there's a text box that sums up all this and shows what the total percentage is. I need to find a way that prevents the user exiting the form unless this calculated field is exactly 100.
I'm on a very tight deadline and I would really appreciate any help on this. Thanks!