auto distribute figures into form

[AOGFADUGBA]

Hi Again,
am trying to distribute an amount into several field in a form at once. is there a way to go about it.
This is the senerio. i have a form bound to a table with 55 fields all currency/number fields and i want to create a unbound text box to input the lumpsum amount into and it will distribute it based on percentage into the 55 fields on the form. is it possible?
thanks

 

[AOGFADUGBA]

thanks for you great input. But am still a bit confused, do you mean i should create a table called percentages with 2 fields called department and percentage or two tables called percentages and department. i know i need to join two tables for the inner join query. but am still pretty confused. can u please clarify your last post.
thanks so much for your time.

 

[sheetspread]

Good point, you already have a percentage field, so name the table something else ("allocation", "allotment", etc). You don't have to create another department table, just join the existing one with the new table in a new query (you're creating just a new table and a new query).

 

[Micron]

You can do this as you've indicated in your first post and here's how (but I think there is a better way, which I will mention at the end).
- Regardless, I think your deptartment table should have a primary key that you can relate to tblPercent (a table you create with ID and PCT fields & do not use the word percent). This might help in future (e.g. to manage department percentages). Easiest way would be to create an autonumber field for [Department].[ID]. If Department is already a PK, remove that key and create an index on that field instead (No Dupes). Make PCT field a Double data type with Fixed format). Paste Append your new autonumber list from Department into the ID field of tblPercent, then enter each dept percent in the PCT field. Add a Total row in tblPercent (see Totals button on Home tab of the ribbon) to ensure you do not exceed 100 percent.
- you'd need one textbox for the amount (e.g. txtAmt), and one for each department allocation amount. In each unbound allocation textbox, enter =[txtAmt]*DLookUp("PCT","tblPercent","DEPT=1") as the controlsource property, but you would have to change the 1 to the correct number for each department. There's the rub - 55 is too many controls to manage this way, and a textbox cannot be set to a query field. You have to use a function of some sort. If the amount textbox gets updated and loses focus, the allocation calculation should automatically update. However, depending on what actions a user can take, it might be possible for the user to commit these values before they update. So worse yet, you would have to ensure each allocation textbox can recalculate. Also, any changes to the number of departments or their values (1, 2, 3, etc.) would make for a lot of work.

I think this is better:
As mentioned, create and set up the tables. Add a subform to your form and have this form display as a datasheet that cannot be edited by the user. On the main form, have your unbound textbox for the amount and a button to run a click event. The query for this subform datasheet would have a field to calculate the allocation (the percent times the textbox amount as done above) so that it gives you an allocation for each department. You would have 55 rows in the datasheet if you have 55 departments. Your button code simply exits if there is no value in the amount textbox, otherwise, it requeries the subform datasheet to show the new values.

This way, you can easily add as many fields to the datasheet from any other tables by building the query as needed. If you add or delete departments, the datasheet rows will adjust accordingly. You can easily negate any percent by setting it to zero, effectively obsoleting any department. If the subform seems daunting, you would probably do OK using a form wizard as long as you have your main form, subform and query built first.

BTW, this took so long to compose that I might seem out of sequence with the responses.

 

[AOGFADUGBA]

Thanks micron for your input. But am having a challenge on where to place the [txtAmt]*DLookUp("PCT","tblPercent","DEPT=1")

when i used the Expr1: [forms]![AIEForm].[txtAmt]*[PCT] in the query it worked fine but only opened the query on click and created another column in the query data sheet for the result.
The AIEForm is bound to the AIE Distribution query with four fields idno, code, subhead and balance, where subhead is like the departments. the amount is supposed to be distributed into the balance field.

This two part of you last response is not very clear.
1. - you'd need one textbox for the amount (e.g. txtAmt), and one for each department allocation amount. In each unbound allocation textbox, enter =[txtAmt]*DLookUp("PCT","tblPercent","DEPT=1") as the controlsource property, but you would have to change the 1 to the correct number for each department.

2.The subform u mentioned what will be the source for the subform

As mentioned, create and set up the tables. Add a subform to your form and have this form display as a datasheet that cannot be edited by the user. On the main form, have your unbound textbox for the amount and a button to run a click event. The query for this subform datasheet would have a field to calculate the allocation (the percent times the textbox amount as done above) so that it gives you an allocation for each department. You would have 55 rows in the datasheet if you have 55 departments. Your button code simply exits if there is no value in the amount textbox, otherwise, it requeries the subform datasheet to show the new values.

This way, you can easily add as many fields to the datasheet from any other tables by building the query as needed. If you add or delete departments, the datasheet rows will adjust accordingly. You can easily negate any percent by setting it to zero, effectively obsoleting any department. If the subform seems daunting, you would probably do OK using a form wizard as long as you have your main form, subform and query built first.

BTW, this took so long to compose that I might seem out of sequence with the responses.

Am sure the second option will work fine, but unfortunately i don't really understand how to go about it.

thanks for your patience, it is very much appreciated.

 

[Micron]

Re: 1) - Keep in mind that I am saying I would not do it this way (55 controls on a form for this procedure). If you are not familiar with using the property sheet of an object, it is what you get when you right-click on a control and choose 'properties' from the resulting context menu, or choose to display the property sheet by choosing it from the Design tab/Tools on the ribbon. That is where you will find the textbox controlsource property when you select the textbox. Keep in mind that txtAmt is my name for the textbox on the form that contains the textbox with the $ amount to be distributed.

2)

The query for this subform datasheet would have a field to calculate the allocation (the percent times the textbox amount as done above) so that it gives you an allocation for each department.

You build this query and use it for the subform recordsource.

When you quote someone's post, use quote tags from the posting toolbar, OK? It makes it easier to figure out which text is yours and which is not.

 

[AOGFADUGBA]

Thanks so much Micron, will do as advised.

 

[AOGFADUGBA]

Hi again and thanks so much for your input. I have been able to distribute the lumpsum base on the percentage stored in the tblPercent. But the result is stored in the query field Expr2: [forms]![Lumpsumaie].[txtAmt]*[PCT] . can you help me figure out how to get the result in the Expr2 into the balance field of the allocation table (department table).
Thanks a million.

 

[sheetspread]

You can write a make table query or an update query to do this

 

[AOGFADUGBA]

Thanks sheetspread. Thought of that option too. how do i write a vba code to run the make table/update query.
thanks a lot

 

[sheetspread]

You can change the query type in design view without VBA. An update query will change the field values of a table, while a make table will give you another table as well as a query (similar to pasting as values in Excel).