place the sum of an event procedure in a text box on a form

[Guest]

To explain briefly, I have two tables, one includes the amount of bonus paid
for each different product, the other contans the names of the employees.
I want to sum the bonus for all machines allocated to each employee
At present the user selects an employee from a combo box, there is a text
box on the form for the total bonus.
At present the value is calculated manually and typed in.
How can I automate this process?
I have tried all the 'DSum' examples I found in books and on this website.
I can get a total for all bonuses in the table, but when I try to get one
employee, based on the contents of the combo box, I get '#Error', or a blank
box.
I have tried using a query and using an event procedure.
I am using access 2002/3

 

[Steve Cartnal]

To explain briefly, I have two tables, one includes the amount of bonus
paid
for each different product, the other contans the names of the employees.
I want to sum the bonus for all machines allocated to each employee
At present the user selects an employee from a combo box, there is a text
box on the form for the total bonus.
At present the value is calculated manually and typed in.
How can I automate this process?
I have tried all the 'DSum' examples I found in books and on this website.
I can get a total for all bonuses in the table, but when I try to get one
employee, based on the contents of the combo box, I get '#Error', or a
blank
box.
I have tried using a query and using an event procedure.
I am using access 2002/3

Is there a field in the table for the amount of bonus paid to assign each
record to an employee? If so, create a query placing your two tables in the
QBE grid (design view of the query). Join them on the employee field. By
placing "=Forms![Name of Your Form]![Name of your Combobox]" in the criteria
blank of the appropriate field (Employee Name, maybe?), you should be able
to get the query to show only records for the employee selected in the
comboxbox. A DSum as the control source of the textbox on your form should
then give you what you're looking for.

It sounds like you may be trying to save the total bonus per employee, which
is neither necessary nor good design. (If you feel like you must do this
anyway, it can be done, but that is another issue.) Your form with the
combobox and the textbox should be unbound for this to work correctly.

 

[Guest]

Steve, thanks for the advice, however when I tried it, it blanked out the
main form, all I could see was the embedded picture.
You are correct, I am trying to save the total bonus for each employee.
My manager needs to see this figure every month, and does not want to keep
calculating manually when employees change customers or get new ones, (which
happens quite often.
The query links may be the problem, I have;-
Many products to one customer, the products table links to the customer table.
Many customers to one employee, the customer table links to the emplyee table
The bonus amount field is in the products table and the bonus total field is
in the employee table.
The problem may be, I am trying to link with a link between, if that makes
sense.
So, what I want in the text box is the bonus field from the employee table
when that employee is selected.
I am using recordsetclone to list the customers and machines, lists are no
problem, it is the sum of the list which gives the headache.
Thanks again for you help so far.

To explain briefly, I have two tables, one includes the amount of bonus
paid
for each different product, the other contans the names of the employees.
I want to sum the bonus for all machines allocated to each employee
At present the user selects an employee from a combo box, there is a text
box on the form for the total bonus.
At present the value is calculated manually and typed in.
How can I automate this process?
I have tried all the 'DSum' examples I found in books and on this website.
I can get a total for all bonuses in the table, but when I try to get one
employee, based on the contents of the combo box, I get '#Error', or a
blank
box.
I have tried using a query and using an event procedure.
I am using access 2002/3

Is there a field in the table for the amount of bonus paid to assign each
record to an employee? If so, create a query placing your two tables in the
QBE grid (design view of the query). Join them on the employee field. By
placing "=Forms![Name of Your Form]![Name of your Combobox]" in the criteria
blank of the appropriate field (Employee Name, maybe?), you should be able
to get the query to show only records for the employee selected in the
comboxbox. A DSum as the control source of the textbox on your form should
then give you what you're looking for.

It sounds like you may be trying to save the total bonus per employee, which
is neither necessary nor good design. (If you feel like you must do this
anyway, it can be done, but that is another issue.) Your form with the
combobox and the textbox should be unbound for this to work correctly.

 

[Steve Cartnal]

Mike,

Have you tried to add that third table to the query, and of course "linking"
(not really the correct term, I don't think) it correctly?

Often the solution is simply creating another query that gives you the
records you want to sum. Frequently, this involves "querying a query", which
for some reason we don't alway consider right away.

You are correct, I am trying to save the total bonus for each employee.
My manager needs to see this figure every month, and does not want to keep
calculating manually when employees change customers or get new ones
(which
happens quite often.

The primary reason that saving calculated dated is a bad idea is that if any
of the fields that are used in the calculation get changed by a user, your
totals could be incorrect. The rationale is that, anything that can be
calculated, can be calculated at anytime. You store the information for
making the calculation and not the result itself. If the data then changes,
the calculation will always be correct. Having said that, we probably all
have violated this rule a time or two. You just have to really consider how
likely the data used in the calculation will be changed at some point and
make sure the users know to change a stored calculation if they change the
data, or make sure Access recalculates whenever the data changes.

Steve, thanks for the advice, however when I tried it, it blanked out the
main form, all I could see was the embedded picture.
You are correct, I am trying to save the total bonus for each employee.
My manager needs to see this figure every month, and does not want to keep
calculating manually when employees change customers or get new ones,
(which
happens quite often.
The query links may be the problem, I have;-
Many products to one customer, the products table links to the customer
table.
Many customers to one employee, the customer table links to the emplyee
table
The bonus amount field is in the products table and the bonus total field
is
in the employee table.
The problem may be, I am trying to link with a link between, if that makes
sense.
So, what I want in the text box is the bonus field from the employee table
when that employee is selected.
I am using recordsetclone to list the customers and machines, lists are no
problem, it is the sum of the list which gives the headache.
Thanks again for you help so far.

To explain briefly, I have two tables, one includes the amount of bonus
paid
for each different product, the other contans the names of the
employees.
I want to sum the bonus for all machines allocated to each employee
At present the user selects an employee from a combo box, there is a
text
box on the form for the total bonus.
At present the value is calculated manually and typed in.
How can I automate this process?
I have tried all the 'DSum' examples I found in books and on this
website.
I can get a total for all bonuses in the table, but when I try to get
one
employee, based on the contents of the combo box, I get '#Error', or a
blank
box.
I have tried using a query and using an event procedure.
I am using access 2002/3

Is there a field in the table for the amount of bonus paid to assign each
record to an employee? If so, create a query placing your two tables in
the
QBE grid (design view of the query). Join them on the employee field. By
placing "=Forms![Name of Your Form]![Name of your Combobox]" in the
criteria
blank of the appropriate field (Employee Name, maybe?), you should be
able
to get the query to show only records for the employee selected in the
comboxbox. A DSum as the control source of the textbox on your form
should
then give you what you're looking for.

It sounds like you may be trying to save the total bonus per employee,
which
is neither necessary nor good design. (If you feel like you must do this
anyway, it can be done, but that is another issue.) Your form with the
combobox and the textbox should be unbound for this to work correctly.