calculating a quoted price

[bbrazeau]

I have a tblQuote. It has multiple fields which the user selects choices from. These fields are combo box lookup fields that get their values from other tables. The user may select a size "8X8" (column2),(column1) is the ID, that has a corrisponding cost of $1500 (column3). The user then may select a type "3 plate,Hot" (column2), (column1) is the ID, that has a corrisponding multiplication factor of 1.625 (column3). Thus an intermediate calculation would be 1500*1.625. This is an example and suffice to say there are other choices that interact with intermediate calculations to arrive at a final Quoted price. I would like to make this calculation and store the number in a calculated field on the form frmQuote.


This calculation will be too intricate to do in the expression builder. Therefore, Is there an example out there similar enough for me to glean information from? If not, Could someone direct me in the right direction for implementing this? ie.. Do I write a function and then have to have a button to initiate the function or is there a way to have it run as the user moves from record to record. What is the best way to send the info to the function or if the formis open in the background would I be able to reference controls with formal call Forms![frmQuote]![cboSize]![column2]

[ssanfu]

I have a tblQuote. It has multiple fields which the user selects choices from. These fields are combo box lookup fields that get their values from other tables. The user may select a size "8X8" (column2),(column1) is the ID, that has a corrisponding cost of $1500 (column3). The user then may select a type "3 plate,Hot" (column2), (column1) is the ID, that has a corrisponding multiplication factor of 1.625 (column3). Thus an intermediate calculation would be 1500*1.625. This is an example and suffice to say there are other choices that interact with intermediate calculations to arrive at a final Quoted price. I would like to make this calculation and store the number in a calculated field on the form frmQuote.
This calculation will be too intricate to do in the expression builder. Therefore, Is there an example out there similar enough for me to glean information from? If not, Could someone direct me in the right direction for implementing this? ie.. Do I write a function and then have to have a button to initiate the function or is there a way to have it run as the user moves from record to record. What is the best way to send the info to the function or if the formis open in the background would I be able to reference controls with formal call Forms![frmQuote]![cboSize]![column2]

Not a whole lot to go on about the calculations, but I would start by writing a UDF. Do you need to see the intermediate answers on frmQuote?

One thing that will help is to rename the controls to something meaningful. Instead of Combo21, use something like "cboBaseSize". Also, don't use spaces or special characters in object names.

You can have the after update event of the controls call the sub or function to do the calculations. In the called code, you would check to see if all the required data has been entered. If data is missing, exit the sub or function.

[bbrazeau]

Thank you ssanfu, Some answers:
1).The exact formula for the calculations are still in the designing phase, but based on your response "cboBaseSize" I'm guessing you realize it will be for the quoting of an injection mold.
2).I don't need to see the intermediate calculations.
3). I'm going to write a simplified UDF and try to impliment it 1st to see if this is the correct direction to go. (Conceptually I'm thinking, some info will come from comboboxes ie. "FramePrice" because it is a specific value. Others will be values base on choices picked from an option group ie "FrameComplexity" which might multiply "FramePrice" by a factor based on the choice. I have decided (correct me if I'm wrong) to store the choices as fields in "tblQuote" as if I don't it seems the value of the option group is not carried over from record to record.
4). As far as having the AfterUpdate event of each control call the function, Will this create a rescource hog or something? I guess the answer to that will be "depends" right?

[bbrazeau]

Hi again, I have a very simple UDF up and running (see code). I have put the call for the function in the On Current event of the frmQuote. This works as long as there are default values for all the controls the UDF uses, (which there are) but it doesn't update the calculated control on the form until you move to a different record. Is there a way around this?

Option Compare Database
Private Function CalculateToolCost() As Currency
Dim FinalCost As Currency
Dim AddMoldClassCost As Currency
Dim AddFeedTypeCost As Currency
Dim AddFrameComplexityCost As Currency
Select Case Me.MoldClass 'Evaluate mold class
Case 1
AddMoldClassCost = 5000
Case 2
AddMoldClassCost = 4000
Case 3
AddMoldClassCost = 3000
Case 4
AddMoldClassCost = 2000
Case 5
AddMoldClassCost = 1000
End Select
Select Case Me.FeedType 'Evaluate feed type
Case 1
AddFeedTypeCost = 1000
Case 2
AddFeedTypeCost = 3000
Case 3
AddFeedTypeCost = 2000
End Select
Select Case Me.FrameComplexity 'Evaluate frame complexity
Case 1
AddFrameComplexityCost = 1500
Case 2
AddFrameComplexityCost = 3500
Case 3
AddFrameComplexityCost = 5500
End Select
FinalCost = Me.TotalFrameCost + Me.TotalComponentCost + Me.TotalMiscCost + AddMoldClassCost + AddFeedTypeCost + AddFrameComplexityCost
CalculateToolCost = FinalCost
End Function

Private Sub Form_Current()
Me.QuoteTotal = CalculateToolCost
End Sub

[ssanfu]

This is a good start. In the VBE, there is a setting that is "Require Variable Declaration". This should have a check mark. It adds a line to every NEW code page so that these two lines are at the (you should add the line to existing code pages):

Option Compare Database
Option Explicit


Add this line to the after update event of each control that will affect the cost:

Me.QuoteTotal = CalculateToolCost


You can add code to check if all the controls the UDF uses have a value. If a control is empty or NULL, return 0. Only when all controls have had a value entered or selected, will a tool cost be calculated.

[bbrazeau]

Thanks ssanfu, Could you give an example of what the code to check controls for exsistance of a value would be? Also, some info I need will come from other tables ie. I will use a value MeltFlowIndex (a field in tblMaterial which has a foreign key in tblPart) MFI is a measure of how easily a plastic material flows when in melted state along with MinimumWall thickness and MaxLengthOfFlow (tblPart) to evaluate ProcessingComplexityCost. Is that a deal breaker when using this method?

[ssanfu]

Thanks ssanfu, Could you give an example of what the code to check controls for exsistance of a value would be?

I would use a series of IF() functions to check if the controls have data. You could use

CalculateToolCost=0 'default return value to zero in case a control is null

If Isnull(Me.control1) Then....
Msgbox "Missing value from Control1"
Exit Sub
End If

If Me.control2 < 1 Then...
Msgbox "Please select an option from Control 2"
Exit Sub
End If
.
.
.
.

'start of calculations


depending what kind of data & control type (i.e. combo box, option group...)

Also, some info I need will come from other tables ie. I will use a value MeltFlowIndex (a field in tblMaterial which has a foreign key in tblPart) MFI is a measure of how easily a plastic material flows when in melted state along with MinimumWall thickness and MaxLengthOfFlow (tblPart) to evaluate ProcessingComplexityCost. Is that a deal breaker when using this method?

To get values from an unrelated table(s), you could write more UDFs: GetMeltFlowIndex(rs("PartID") that returns a value from tblMaterial.