Figuring Calulated fields in a form

[Chip]

Howdy from Oklahoma!!

I am new to ACCESS and VB for that matter but i am trying
to build a set of forms to show a Project funding and
debits (or outgoing costs) for the Project. So i have a
table (tbl_projhead) with the general project information
including a field for the overall funding amount
(ProjFunding). Now i also have a table of the Project Line
Items (tbl_ProjDetail) that is the breakout of the project
costs (like labor, materials, insurance). And for each
Line-Item I have another table of debits (tbl_ProjDebits)
that subtract out the items. For Example: Project ABC is
funded for $1000 (ProjFunding) and it has a line-item of
materials that has debits of $120, $180 for an overall
calculated cost of $300 for materials for project ABC.
Now comes the fun part!! I need to be able to show the
calculated cost of materials on the Main Form that shows
the General Information for Project ABC. The cost is
figured from another table and i have heard that it is not
a good idea to save calculated feilds in the database.
What can I do?? I am at a Loss...

Any Help would be graetly appreciated!!!

And THANKS IN ADVANCE!!!!

 

[Treebeard]

Howdy from Oklahoma!!

I am new to ACCESS and VB for that matter but i am trying
to build a set of forms to show a Project funding and
debits (or outgoing costs) for the Project. So i have a
table (tbl_projhead) with the general project information
including a field for the overall funding amount
(ProjFunding). Now i also have a table of the Project Line
Items (tbl_ProjDetail) that is the breakout of the project
costs (like labor, materials, insurance). And for each
Line-Item I have another table of debits (tbl_ProjDebits)
that subtract out the items. For Example: Project ABC is
funded for $1000 (ProjFunding) and it has a line-item of
materials that has debits of $120, $180 for an overall
calculated cost of $300 for materials for project ABC.
Now comes the fun part!! I need to be able to show the
calculated cost of materials on the Main Form that shows
the General Information for Project ABC. The cost is
figured from another table and i have heard that it is not
a good idea to save calculated feilds in the database.
What can I do?? I am at a Loss...

Any Help would be graetly appreciated!!!

And THANKS IN ADVANCE!!!!

Chip,

A little more information of the structure of your detail tables.

It looks like you have 4 tables: tbl_projhead, ProjFunding, tbl_ProjDetail,
AND tbl_ProjDebits.

Could you list the important fields for each of these tables and the keys?

In "tbl_ProjDetail", do you have a seperate fields for labor, materials &
insurance? Or are these one field (i.e. CostType ) ? The reason I ask is
because whenever you want to create a summation field for a record with
multiple fields, it's best to do this in a query.

Sounds like you are going to have multiple subforms in your form. For each
of the subforms you need to create a summantion field in the footer of the
subform with the control source equal to the sum function (i.e. =Sum([Cost])
.. The next step is to make these visible from the main form. Create a Text
Box for each subform and set the control source of the text box to the
summation field you created on the subforms
"=[YourSubFormName].Form.YourSummationFieldName"

jack

 

[John S]

Hi
Run a query on the other table, and place the results in your main form. Use
the form load event.

Here is what sample code looks like:

Dim a_count As Integer
Dim h_count As Integer

Dim rs1 As Variant
Dim rs2 As Variant

Set rs1 = CurrentDb.OpenRecordset("q-ayl-count")
Set rs2 = CurrentDb.OpenRecordset("q-hull-count")

a_count = rs1("CountofANIMAL")
h_count = rs2("CountofANIMAL")

txtHullCount.Value = h_count
txtAylmerCount.Value = a_count

We use the code to keep a running total of pet licences sold in the main
form.

You might want to change the opening definition of the recordsets, though
the type variant does work (variants truly can be anything) . Usually its
"Dim rs As New ADODB.Recordset" if you are using ADO.

John S
Aylmer, PQ