jsawicki
asked on
Linking a form total's field to the table
I created a form to track the cost for each piece of stolen/lost equipment. I then created a totals text box within the form to produce a total value for all equipment. I am able to get the total field to populate in the form by inputting =[field1] + [Field2] in the data control source; however, the problem i can't figured out now is how to link this field to the table. This would typically occur though the same control source, but now that it is filled with the adding of fields i am not sure how to get this total to populate in the table.
Let me know if you have any questions.
Let me know if you have any questions.
Change the control source for the Totals textbox to the Totals field name from the table and then in either Field1 or Field2 (which ever field is the last to update) add an event (On Change) that sums the 2 fields and sets that equal to the Totals textbox. That should work.
You don't "Link" calculated data to the table.
Can you first take a step back and explain your ultimate need please?
...perhaps there is a more efficient solution.
;-)
Typically this data is calculated and "displayed" in a form (as you are doing now) ...or a report.
There is no real need to "Link" it to the table.
Typically a query is created that does the calculation:
SELECT Field1, Field2, Field1+Field1 AS Total
FROM YourTable
Then you can make a form or report from this query and the Total will be included automatically.
JeffCoachman
Can you first take a step back and explain your ultimate need please?
...perhaps there is a more efficient solution.
;-)
Typically this data is calculated and "displayed" in a form (as you are doing now) ...or a report.
There is no real need to "Link" it to the table.
Typically a query is created that does the calculation:
SELECT Field1, Field2, Field1+Field1 AS Total
FROM YourTable
Then you can make a form or report from this query and the Total will be included automatically.
JeffCoachman
ASKER
So below is how the addition of fields i coded looks like.
=[Stylus]+[Extended_Batter y]+[Media_ Base]+[AC_ Power_Adap ter]+[Mobi le_Charger ]+[Air_Car d]+[Case]+ [KB]+[Mous e]+[Scanne r]+[E_Port _Rep]+[Mob ile_Printe r]+[Swiss_ Case]+[Ext ernal_DVD] +[Camera]
The table total field is called Total so what would the code in the on change event for Camera look like given your explanation.
I know how to enter subs and functions in the event procedure, but not sure how this one would be created.
=[Stylus]+[Extended_Batter
The table total field is called Total so what would the code in the on change event for Camera look like given your explanation.
I know how to enter subs and functions in the event procedure, but not sure how this one would be created.
ASKER
I am creating a form to track all lost/stolen computer devices and then i will have a button on this main form that will open another form that lists all the computer equipment. Both of these tables are tied together through the relationship function. The equipment form is where all cost values will be inserted for each item that is lost or stolen. I was hoping to create a total column that adds up all item values. I have created this column on the form and it functions perfectly, but based on your explanation, you can't have a calculated column in the table. So i guess i will continue as i have been unless you see a different approach Coachman. Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The query approach is a way to "Simulate" a calculated field in a table.
Once the calculated field is created in the query, then you simply use the query just like you would use a table.
;-)
Now, ...to be totally fair, ...you CAN create a calculated field in a table using Access 2010.
However this feature is only available in Access, and is not available in other database systems.
The query approach is the standard way of doing what you are requesting.
;-)
Jeff
Once the calculated field is created in the query, then you simply use the query just like you would use a table.
;-)
Now, ...to be totally fair, ...you CAN create a calculated field in a table using Access 2010.
However this feature is only available in Access, and is not available in other database systems.
The query approach is the standard way of doing what you are requesting.
;-)
Jeff
Oh, ... and also note that you should add in a protection against errors if any of the fields in the calculation is null (empty)
SELECT Field1, Field2, Nz(Field1)+Nz(Field1) AS Total
FROM YourTable
SELECT Field1, Field2, Nz(Field1)+Nz(Field1) AS Total
FROM YourTable
see this simple sample
Database32.mdb
Database32.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Zuey: Yes, all the form fields are being stored in a table. And thanks for the coding, i will try shortly.
Coachman: With your example with the form being created from a query, would i still be able to input and create new entries in the form?
Coachman: With your example with the form being created from a query, would i still be able to input and create new entries in the form?
ASKER
Thanks both of you.....i will determine the best method after testing both.....
<With your example with the form being created from a query, would i still be able to input and create new entries in the form? >
...I posted a full functioning sample...
Why not try it ...?
;-)
...I posted a full functioning sample...
Why not try it ...?
;-)