?
Solved

Linking a form total's field to the table

Posted on 2012-08-21
12
Medium Priority
?
353 Views
Last Modified: 2012-08-21
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.
0
Comment
Question by:jsawicki
  • 6
  • 4
  • 2
12 Comments
 

Expert Comment

by:zuey
ID: 38317628
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38317697
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
 

Author Comment

by:jsawicki
ID: 38317708
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jsawicki
ID: 38317926
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
 

Assisted Solution

by:zuey
zuey earned 1000 total points
ID: 38318052
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38318063
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38318082
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38318101
see this simple sample
Database32.mdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1000 total points
ID: 38318178
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
 

Author Comment

by:jsawicki
ID: 38318217
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
 

Author Closing Comment

by:jsawicki
ID: 38318433
Thanks both of you.....i will determine the best method after testing both.....
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38318816
<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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question