Calculated Form Control

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

I have a tabbed main form for maintaining data about
construction projects that we estimate at multiple stages
of design. The three pages correspond to Project Header
Information, Estimate and Bid Details (what we estimated
each round, and the Hi, Lo, and Avg actual bids), and Bid
Breakdown (HVAC, Plumbing, Electrical, etc.)

From our last estimate and the awarded bid (a checkbox in
the Bid Detail form), I would like to calculate a Delta,
i.e., the percentage our opinion differed from the awarded
bid. The calculation is easy, my question is one of how
to refer to the values I need.

The following subforms are on the fpagEstimateBidData page
(the second main form tab):

fsubEstimateData (Child Fields Link = ProjectNumber)
-----------
EstimateNumber
EstimateType
EstimateAmount
SquareFootage
Note

fsubBidData (Child Fields Link = ProjectNumber)
----------------
BidType
BidAmount
Note
AwardedBid

The Delta is to be calculated from the EstimateAmount
corresponding to the estimate with the highest
EstimateNumber and the BidAmount which has the AwardedBid
field checked. Form code ensures defaults the check to
the Low Bid and ensures one and only one of the bid
entries are checked.

Can anyone tell me how to refer to these values?

Thank you.

Kevin Sprinkel
Becker & Frondorf
 
Your data is in the subform and you want a control to appear in the main
form?

Then in the text box in the main form type

=[YourSubFormName].Form.[YourControlName]

The text box will now show what is in that control in the subform.

To do arithmatic with it eg:

=[YourSubFormName].Form.[YourControlName]+[YourSubFormName].Form.[YourOtherC
ontrol]

Evi
 
Thanks for your response.

Yes, I want to display the result of a calculation on the
main form whose arguments are in subforms, however, there
are other requirements.

Each subform contains multiple detail records linked to
the project number, but I wish to use specific records
from each, based on the following criteria:

Subform Criteria
---------------------- ----------------------------------
BidDetail AwardedBid = True
EstimateDetail Max(EstimateNumber)

In other words, I wish to compare the selected bid
(usually, but not always the lowest bid) to our last
estimate.

Thanks for any help you can provide.

Kevin Sprinkel
-----Original Message-----
Your data is in the subform and you want a control to appear in the main
form?

Then in the text box in the main form type

=[YourSubFormName].Form.[YourControlName]

The text box will now show what is in that control in the subform.

To do arithmatic with it eg:

=[YourSubFormName].Form.[YourControlName]+ [YourSubFormName].Form.[YourOtherC
ontrol]

Evi

I have a tabbed main form for maintaining data about
construction projects that we estimate at multiple stages
of design. The three pages correspond to Project Header
Information, Estimate and Bid Details (what we estimated
each round, and the Hi, Lo, and Avg actual bids), and Bid
Breakdown (HVAC, Plumbing, Electrical, etc.)

From our last estimate and the awarded bid (a checkbox in
the Bid Detail form), I would like to calculate a Delta,
i.e., the percentage our opinion differed from the awarded
bid. The calculation is easy, my question is one of how
to refer to the values I need.

The following subforms are on the fpagEstimateBidData page
(the second main form tab):

fsubEstimateData (Child Fields Link = ProjectNumber)
-----------
EstimateNumber
EstimateType
EstimateAmount
SquareFootage
Note

fsubBidData (Child Fields Link = ProjectNumber)
----------------
BidType
BidAmount
Note
AwardedBid

The Delta is to be calculated from the EstimateAmount
corresponding to the estimate with the highest
EstimateNumber and the BidAmount which has the AwardedBid
field checked. Form code ensures defaults the check to
the Low Bid and ensures one and only one of the bid
entries are checked.

Can anyone tell me how to refer to these values?

Thank you.

Kevin Sprinkel
Becker & Frondorf
Click to expand...


.
Click to expand...
 
I'm not sure I understand what you mean but if you want to compare a bid to
the 'last estimate' then you must have some field or fields which decides
which is the last estimate, it may be a date+time field which is entered
with a Default Value of Now()
or an autonumber field. In that case, use DMax with the criteria that the
field <" & [YourCriteriaField]

Have a look at Dmax in Help and see if that is what you had in mind. You can
perform the sum in the query on which the subform is based and then display
it in the main form using the method I described below.
Evi

Kevin Sprinkel said:
Thanks for your response.

Yes, I want to display the result of a calculation on the
main form whose arguments are in subforms, however, there
are other requirements.

Each subform contains multiple detail records linked to
the project number, but I wish to use specific records
from each, based on the following criteria:

Subform Criteria
---------------------- ----------------------------------
BidDetail AwardedBid = True
EstimateDetail Max(EstimateNumber)

In other words, I wish to compare the selected bid
(usually, but not always the lowest bid) to our last
estimate.

Thanks for any help you can provide.

Kevin Sprinkel
-----Original Message-----
Your data is in the subform and you want a control to appear in the main
form?

Then in the text box in the main form type

=[YourSubFormName].Form.[YourControlName]

The text box will now show what is in that control in the subform.

To do arithmatic with it eg:

=[YourSubFormName].Form.[YourControlName]+ [YourSubFormName].Form.[YourOtherC
ontrol]

Evi

I have a tabbed main form for maintaining data about
construction projects that we estimate at multiple stages
of design. The three pages correspond to Project Header
Information, Estimate and Bid Details (what we estimated
each round, and the Hi, Lo, and Avg actual bids), and Bid
Breakdown (HVAC, Plumbing, Electrical, etc.)

From our last estimate and the awarded bid (a checkbox in
the Bid Detail form), I would like to calculate a Delta,
i.e., the percentage our opinion differed from the awarded
bid. The calculation is easy, my question is one of how
to refer to the values I need.

The following subforms are on the fpagEstimateBidData page
(the second main form tab):

fsubEstimateData (Child Fields Link = ProjectNumber)
-----------
EstimateNumber
EstimateType
EstimateAmount
SquareFootage
Note

fsubBidData (Child Fields Link = ProjectNumber)
----------------
BidType
BidAmount
Note
AwardedBid

The Delta is to be calculated from the EstimateAmount
corresponding to the estimate with the highest
EstimateNumber and the BidAmount which has the AwardedBid
field checked. Form code ensures defaults the check to
the Low Bid and ensures one and only one of the bid
entries are checked.

Can anyone tell me how to refer to these values?

Thank you.

Kevin Sprinkel
Becker & Frondorf
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks for all of your help, Evi. Your comments got me
pointed on the right track, and it's now solved.
 
Back
Top