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.
jsawickiAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Oh and finally, consider a more "Normalized" design.

With your current system it will be a nightmare, because each time you Delete, Edit or Add a new filed, you will have to edit the query accordingly

Your current formula is this:
=[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]

If you "normalized" you design all you would need is: =Sum([Cost]) in the footer of a form

Sample attached
Database33.mdb
0
 
zueyCommented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
jsawickiAuthor Commented:
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.
0
 
jsawickiAuthor Commented:
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
0
 
zueyConnect With a Mentor Commented:
I have a few questions;
1.      Are you storing all the fields from the form in the table?
2.      Including the Total field?
Either way open the property sheet for the Camera textbox, select the event tab, select “On Change” from the list then click on the 3 dots on the right. Select the Code Builder. This will open the VBA editor and create the
Private Sub Camera _Change()
Dim dblTotal As Double
dblTotal=[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]
Me.Total .Value = dblTotal
End Sub
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
see this simple sample
Database32.mdb
0
 
jsawickiAuthor Commented:
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?
0
 
jsawickiAuthor Commented:
Thanks both of you.....i will determine the best method after testing both.....
0
 
Jeffrey CoachmanMIS LiasonCommented:
<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 ...?
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.