Link to home
Start Free TrialLog in
Avatar of jsawicki
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.
Avatar of zuey
zuey

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.
Avatar of Jeffrey Coachman
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
Avatar of jsawicki

ASKER

So below is how the addition of fields i coded looks like.  

=[Stylus]+[Extended_Battery]+[Media_Base]+[AC_Power_Adapter]+[Mobile_Charger]+[Air_Card]+[Case]+[KB]+[Mouse]+[Scanner]+[E_Port_Rep]+[Mobile_Printer]+[Swiss_Case]+[External_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.
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
Avatar of zuey
zuey

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
see this simple sample
Database32.mdb
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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 ...?
;-)