Here is the function code.
Expand|Select|Wrap|Line Numbers
- Public Function fCalcEquation(strEquation As String) As Long
- Dim MyDB As DAO.Database
- Dim rstParameters As DAO.Recordset
- Dim intParamPosition As Integer
- Dim strParameter As String
- Dim strValue As String
- Dim blnRepeatLoop As Boolean
- Set MyDB = CurrentDb
- Set rstParameters = MyDB.OpenRecordset("SELECT * FROM parmas")
- With rstParameters
- Do Until .EOF
- strParameter = !ParameterShortDesc
- strValue = CStr(!Value)
- CheckVal:
- intParamPosition = InStr(1, strEquation, strParameter)
- If intParamPosition > 0 Then
- strEquation = (Left(strEquation, (intParamPosition - 1)) + strValue + Mid(strEquation, intParamPosition + Len(strParameter), Len(strEquation)))
- blnRepeatLoop = True
- Else
- blnRepeatLoop = False
- End If
- .MoveNext
- Loop
- End With
- rstParameters.Close
- Set MyDB = Nothing
- fCalcEquation = Eval(strEquation)
- 'Note: Explanation
- 'A Recordset is based on the Parameters Table (parmas).
- 'The code iterates through each Parameter, seeing if this Parameter exists in the Equation.
- 'If the Parameter is contained within the Equation, its corresponding Value is extracted, and the actual Equation is build with these substituted values in place of the variables.
- 'The inner loop is now repeated if a Parameter is found. If a Parameter is not found, the code falls through and moves to the next Record in the Recordset (Parameter).
- 'When all Parameters have been evaluated against the Equation (.EOF = True) the code ends.
- '*_* Problem if the same Parameter exists more than once, the code explodes!
- '*_* Solution is to rename the Parameter for example; CarcT and Carct_2
- End Function
Expand|Select|Wrap|Line Numbers
- ParameterID ParameterShortDesc ParameterLongDesc Value
- 1 Width Width 500
- 2 Depth Depth 700
- 3 Height Height 800
- 4 BBackVoid Base Back Void 50
- 5 WBackVoid Wall Back Void 22
- 6 TBackVoid Tall BackVoid 50
- 7 WShelfSetBack Wall Shelf Set Back 48
- 8 BShelfSetBack Base Shelf Set Back 48
- 9 TShelfSetBack Tall Shelf Set Back 48
- 10 Groove Groove Depth 9
- 11 CarcT Carcass Thickness 18
- 12 DrawerST Drawer Side Thickness 140
- 13 RailT Rail Thickness 120
- 14 BackT Back Thickness 140
- 15 PlinthH Plinth Height 150
- 16 FrameSW Frame Style Width 40
- 17 FrameTH Frame Top Height 40
- 18 FrameBH Frame Bottom Height 40
- 19 FrameRH Frame Rail Height 26
- 20 DoorSW Door Style Width 80
- 22 DoorBH Door Bottom Height 80
- 23 DrawerSH Drawer Side Height 80
- 24 DrawerFH Drawer Front Height 140
- 25 DrawerBH Drawer Back Heigth 140
- 26 DrawerFasciaH Drawer Fascia Height 140
- 27 DrawerDeepFasciaH Drawer Deep Fascia Height
- 224
- 28 Tenon Tenon 9
- 29 Play Play 2
- 30 Adjustment Adjustment 1
- 36 MullionWid Mullion Width 90
- 37 MidRailWid Mid Rail Width 140
- 40 FrameTen Frame Tenon 20
- 41 DoorTen Door Tenon 30
- 42 FrameT Frame Thickness 26
- 43 DoorT Door Panel Thickness 10
- 44 DoorPanelTen Door Panel Tenon 10
- 45 CarcT_2 Carcass Thickness 18
- 46 DoorRH Door Rail Thickness 80
- 47 DoorSWC Door Style Closer 90
- 48 DoorTH Door Top Height 80
Equation = Height-PlinthH-FrameBH-FrameTH-DoorBH-DoorTH+(DoorPanelTen*2)
When the Equation reaches the DoorTH of the line, it goes through the entire Parameter table and uses the DoorT value instead, is there a away of telling it to match the entire name of DoorTH. The values are different you see and the function errors as it leaves Height-PlinthH-FrameBH-FrameTH-DoorBH-10H+(DoorPanelTen*2).
Could anyone please help me with this issue!