Hi i am having a little problem with an equation function that was created from all your help previously. The function works fine itself but with a small glitch within it.
Here is the function code.
- 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
Here is the list of Parameters.
-
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
Here is an example of the problem i am having;
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!
