Calculated fields on form not showing up on table or query

[pcprincess77]

Hi....
I have a database that has over 450 records in it. There are two fields i created on the form that are calculated values derived from different values on the form. For some reason when I switch back to my table view the calculated values are not showing. And the same goes for the query.

Originally I had imported the database from excel. All of the records that were imported showed up correctly. However, when I add new records the calculated values do not show up. PLEASE HELP ;-(

 

[BNPMike]

The fields on a form must be bound to an underlying table/query field in order to store information. It's therefore not straightforward to have a form textbox that is simultaneously showing the table value and a value calculated from other sources. How have you got this all organised at the moment?

 

[pcprincess77]

Thanks for responding.
I have the fields bound to the table. All of the data is showing properly on the table EXCEPT the calculated values. So for instance, say I have 15 fields, 2 are calculated, they are all bound to the table but only 13 of the columns in the table are displaying the data entered into the form. the 2 calculated are not.

I have a feeling it has to do with the way the control source is set up. I have it as an expression, basically, x+x=y, etc. But when I do the expression..i notice that there is no way to link that field to the table if you are using an expression...or is there??? I hope this all makes sense. Basically, if i can just figure out how to create an expression and then bind that expression to the column in the table I will be all set...any idea how i could do this??

 

[jimb0ne]

you could update your table manually using the recordset object like so....


dim rs as dao.recordset
dim db.as dao.recordset

set db = currentdb
set rs = db.openrecordset("SELECT * FROM tablename;", dbopendynaset)

rs.findfirst = "somefield = " & uniquevalue

with rs
.edit
!calculatedvalue1 = iCalculatedValue1
!calculatedvalue2 = iCalculatedValue2
.update
end with

might be a bit cumbersome but it should ensure that the values get entered properly. The findfirst is just basically a lookup using a unique value from the form for the record so you ensure you are entering the values into the proper record. hope I'm making sense, lemme know if this helps at all...

 

[randy700]

Why would you want to store calculated values in the table?

 

[jimb0ne]

randy700's point is well taken, if the values are derived from values already present in the table, and you already have all the calculations coded in you could just calculate the values everytime you changed records on the form, that would reduce the number of fields you'd need in your table.... plus it would solve this problem.

 

[pcprincess77]

Let me try and explain this a little better.
Basically...the database I created is being used to track jobs that we process on a daily basis as well as estimated revenue. It includes a specific month code, customer name, total units, permit cost, stamp cost, and meter charges (permit, stamp & meter, become calculated on the form into a field labeled "combinded postage amount, then I have fields for sales reps, commission, invoice #, invoice amount, and estimated revenue ( calculation based on invoice amount - commission - combined postage, this give me my estimated revenue for each job.)

Then i wanted to create a query that took the specific month code, added all the estimated revenue values to give me a grand total for that month.

The table basically has all the above fields and i wanted all the calculated values to show as well as the other data. Then i would create querys based on different needs but always being able to view my monthly estimated revenue.

Does this make sense? Is there another way that i am over looking??

 

[jimb0ne]

by the sounds of it, it probably would be simpler to store the estimated revenues then create queries using the stored numbers, if it was just for viewing purposes on the form, or on a report, a calculation would be fine, but it would get reasonably convoluded if you were wanting to use the calculated values for other calculations/queries. Well you can try the method I've described above for storing you values, again, cumbersome but it'll work. like chopping down a tree with a dull hatchet.

 

[randy700]

I, personally, would never store calculated data in my tables unless it was needed for historical purposes. The preferred method would be to perform the calculaiton (probably in a query) whenever the values are needed.

That being said, if you are still intent on saving the values, try using an update query to edit the tables. I haven't tried it but can't think of any reason why it would not work.

 

[pcprincess77]

Well, for simplicity sake, is there another way that I can take the information I have entered into the form and create a query that will calculate the sum of the values entered for that particular month code? The query will be used for others who need to access monthly estimated revenue values. I'd like to keep this as simple as possible. Originally this data was kept on an extremely large excel spreadsheet and I wanted to make an easily accesible database. If you have any ideas please share....