444,124 Members | 1,945 Online
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

# Calculating Complex Fields via equations and lookups

 100+ P: 129 Hi i am trying to calculate two fields 'AcrossGrain' and 'WithGrain' both of which have equations assigned to them for example: Equations: Depth-BBackVoid-CarcT or Height-CarcT*2 I do have a parameter table (parmas) which has all the values assigned to them for example: BBackVoid = 22 CarcT = 18 Default Depth = 300 Calculation would be 300-22-18 = 260 (AcrossGrain) However when inputting the answer to the equation via the OrderLine fields, i need to lookup the equation via the table (stkbommas) and calculate the equation using the parmas values. To be honest i dont have a clue how to work this out. This is what someone has given me however i can't seem to get it to work plus i am not entirely sure what it is doing. I have tried calling the function to the fields however i am pretty sure the coding isn't all there as there is no lookup code. Expand|Select|Wrap|Line Numbers Public Function eqeval(equation As String) As Integer       Dim db As DAO.Database     Dim rst As DAO.Recordset       Dim check As Integer     Dim parameter As String     Dim value As String     Dim repeatcheck As Byte       Set db = CurrentDb       Set rst = db.OpenRecordset("SELECT * FROM parmas")       Do Until rst.EOF             parameter = LCase(rst!parameter)         value = rst!value checkval:           check = InStr(1, equation, parameter)           If check > 0 Then            equation = Left(equation, (check - 1)) + value + Mid(equation, check + Len(parameter), Len(equation))            repeatcheck = 1           Else               repeatcheck = 0           End If           If repeatcheck = 1 Then GoTo checkval           rst.MoveNext       Loop       rst.Close       Set db = Nothing       eqeval = Eval(equation)   End Function Any help or advice would be much appreciated, Thanks in advance! Regards Chris Aug 22 '08 #1
49 Replies

 Expert 5K+ P: 8,669 Given your Table and Field Names as you previously indicated, and the fact that you only have 2 Equations to deal with, I've revised the code so that you can simply pass the Name of the Equation to execute: Expand|Select|Wrap|Line Numbers Public Function fCalculateEquation(strEquationName As String) Dim lngDepth As Long Dim lngBBackVoid As Long Dim lngCarcT As Long Dim lngHeight As Long   Select Case strEquationName   Case "AcrossGrain"     lngDepth = DLookup("[value]", "parmas", "[parameter] = 'Default Depth'")     lngBBackVoid = DLookup("[value]", "parmas", "[parameter] = 'BBackVoid'")     lngCarcT = DLookup("[value]", "parmas", "[parameter] = 'CarcT'")       fCalculateEquation = FormatNumber(lngDepth - lngBBackVoid - lngCarcT, 0)   Case "WithGrain"     lngHeight = DLookup("[value]", "parmas", "[parameter] = 'Height'")     lngCarcT = DLookup("[value]", "parmas", "[parameter] = 'CarcT'")       fCalculateEquation = FormatNumber(((lngHeight - lngCarcT) * 2), 0)   Case Else     MsgBox "The Equation " & strEquationName & " does not exist!", _             vbExclamation, "No Equation" End Select End Function Sample OUTPUT: Expand|Select|Wrap|Line Numbers Debug.Print fCalculateEquation("AcrossGrain") 260 Expand|Select|Wrap|Line Numbers Debug.Print fCalculateEquation("WithGrain") 34 Expand|Select|Wrap|Line Numbers fCalculateEquation("Blah_Blah") Message Box ==> The Equation Blah_Blah does not exist! , Exclamation Mark, 'No Equation' Title Aug 22 '08 #2

 100+ P: 129 The only problem with this is that i have 37 parameter values and the equations are different based upon the Stock Details. I have something like 50 different equations. What would you suggest i do here? Aug 22 '08 #3

 100+ P: 129 Here are the parameter values: ParameterID ParameterShortDesc ParameterLongDesc Value 1 Width Width 300 2 Depth Depth 300 3 Height Height 570 4 BBackVoid Base Back Void 22 5 WBackVoid Wall Back Void 22 6 TBackVoid Tall BackVoid 22 7 WShelfSetBack Wall Shelf Set Back 10 8 BShelfSetBack Base Shelf Set Back 10 9 TShelfSetBack Tall Shelf Set Back 10 10 Groove Groove Depth 9 11 CarcT Carcass Thickness 18 12 DrawerST Drawer Side Thickness 140 13 RailT Rail Thickness 140 14 BackT Back Thickness 140 15 PlinthH Plinth Height 150 16 FrameSW Frame Style Width 36 17 FrameTH Frame Top Height 36 18 FrameBH Frame Bottom Height 36 19 FrameRH Frame Rail Height 36 20 DoorSW Door Style Width 72 21 DoorTH Door Top Height 72 22 DoorBH Door Bottom Height 72 23 DrawerSH Drawer Side Height 72 24 DrawerFH Drawer Front Height 140 25 DrawerBH Drawer Back Heigth 140 26 DrawerFasciaH Drawer Fascia Height 150 27 DrawerDeepFasciaH Drawer Deep Fascia Height 320 28 Tenon Tenon 9 29 Play Play 2 30 Adjustment Adjustment 1 36 MullionWid Mullion Width 90 37 MidRailWid Mid Rail Width 140 And here are some of the equations: SSBOMID StkID SubStkID Qty WithGrain AcrossGrain Edged MaterialID 1 135 148 1 Width-CarcT*2 Depth-Bbackvoid-CarcT Both 1 2 135 170 1 Height Depth Both 1 3 135 171 1 Height Depth Both 1 4 135 149 1 Width-CarcT*2 Depth-Bbackvoid-CarcT-BshelfSetBack Both 1 5 135 133 1 Height-CarcT Width-CarcT Both 1 6 137 148 1 Width-CarcT*2 Depth-Bbackvoid-CarcT Both 1 7 137 170 1 Height Depth Both 1 8 137 171 1 Height Depth Both 1 9 137 185 1 Width-CarcT*2 RailT Both 1 10 137 149 1 Width-CarcT*2 Depth-Bbackvoid-CarcT-BShelfSetBack Both 1 11 137 185 1 Width-Carct*2 RailT Both 1 12 137 173 1 Depth-Bbackvoid-Carct MidRailWid Both 1 13 137 173 1 Depth-Bbackvoid-carct MidrailWid Both 1 14 139 170 1 Height Depth Both 1 15 139 171 1 Height Depth Both 1 16 139 148 1 Width-CarcT*2 Depth-Bbackvoid-CarcT Both 1 17 139 185 1 Width-CarcT*2 RailT Both 1 18 139 149 1 Width-CarcT*2 Depth-BBackVoid-CarcT Both 1 19 136 170 1 Height Depth Both 1 20 136 171 1 Height Depth Both 1 21 136 148 1 Width-CarcT*2 Depth-Bbackvoid-CarcT Both 1 22 136 149 1 Width-CarcT*2 Depth-Bbackvoid-CarcT-BShelfSetBack Both 1 23 136 185 1 Width-CarcT*2 RailT Both 1 24 136 133 1 Width-CarcT*2 Height-CarcT Both 1 25 136 174 1 Height-CarcT*2 MullionWid Both 1 26 138 170 1 Height Depth Both 1 27 138 171 1 Height Depth Both 1 28 138 148 1 Width-CarcT-BBackVoid Width-CarcT-BBackVoid Both 1 29 138 150 1 Width-CarcT-BBackVoid Width-CarcT-BBackVoid Both 1 30 138 149 1 Width-CarcT-BBackVoid-BShelfSetBack Width-CarcT-BBackVoid-BShelfSetBack Both 1 31 138 133 1 Width-CarcT-BBackVoid Width-CarcT-BBackVoid-CarcT Both 1 32 138 133 1 Width-CarcT-BBackVoid Width-CarcT-BBackVoid-CarcT Both 1 What do you think i need to do? Aug 22 '08 #4

 Expert 5K+ P: 8,669 Here are the parameter values: ParameterID ParameterShortDesc ParameterLongDesc Value 1 Width Width 300 2 Depth Depth 300 3 Height Height 570 4 BBackVoid Base Back Void 22 5 WBackVoid Wall Back Void 22 6 TBackVoid Tall BackVoid 22 7 WShelfSetBack Wall Shelf Set Back 10 8 BShelfSetBack Base Shelf Set Back 10 9 TShelfSetBack Tall Shelf Set Back 10 10 Groove Groove Depth 9 11 CarcT Carcass Thickness 18 12 DrawerST Drawer Side Thickness 140 13 RailT Rail Thickness 140 14 BackT Back Thickness 140 15 PlinthH Plinth Height 150 16 FrameSW Frame Style Width 36 17 FrameTH Frame Top Height 36 18 FrameBH Frame Bottom Height 36 19 FrameRH Frame Rail Height 36 20 DoorSW Door Style Width 72 21 DoorTH Door Top Height 72 22 DoorBH Door Bottom Height 72 23 DrawerSH Drawer Side Height 72 24 DrawerFH Drawer Front Height 140 25 DrawerBH Drawer Back Heigth 140 26 DrawerFasciaH Drawer Fascia Height 150 27 DrawerDeepFasciaH Drawer Deep Fascia Height 320 28 Tenon Tenon 9 29 Play Play 2 30 Adjustment Adjustment 1 36 MullionWid Mullion Width 90 37 MidRailWid Mid Rail Width 140 And here are some of the equations: SSBOMID StkID SubStkID Qty WithGrain AcrossGrain Edged MaterialID 1 135 148 1 Width-CarcT*2 Depth-Bbackvoid-CarcT Both 1 2 135 170 1 Height Depth Both 1 3 135 171 1 Height Depth Both 1 4 135 149 1 Width-CarcT*2 Depth-Bbackvoid-CarcT-BshelfSetBack Both 1 5 135 133 1 Height-CarcT Width-CarcT Both 1 6 137 148 1 Width-CarcT*2 Depth-Bbackvoid-CarcT Both 1 7 137 170 1 Height Depth Both 1 8 137 171 1 Height Depth Both 1 9 137 185 1 Width-CarcT*2 RailT Both 1 10 137 149 1 Width-CarcT*2 Depth-Bbackvoid-CarcT-BShelfSetBack Both 1 11 137 185 1 Width-Carct*2 RailT Both 1 12 137 173 1 Depth-Bbackvoid-Carct MidRailWid Both 1 13 137 173 1 Depth-Bbackvoid-carct MidrailWid Both 1 14 139 170 1 Height Depth Both 1 15 139 171 1 Height Depth Both 1 16 139 148 1 Width-CarcT*2 Depth-Bbackvoid-CarcT Both 1 17 139 185 1 Width-CarcT*2 RailT Both 1 18 139 149 1 Width-CarcT*2 Depth-BBackVoid-CarcT Both 1 19 136 170 1 Height Depth Both 1 20 136 171 1 Height Depth Both 1 21 136 148 1 Width-CarcT*2 Depth-Bbackvoid-CarcT Both 1 22 136 149 1 Width-CarcT*2 Depth-Bbackvoid-CarcT-BShelfSetBack Both 1 23 136 185 1 Width-CarcT*2 RailT Both 1 24 136 133 1 Width-CarcT*2 Height-CarcT Both 1 25 136 174 1 Height-CarcT*2 MullionWid Both 1 26 138 170 1 Height Depth Both 1 27 138 171 1 Height Depth Both 1 28 138 148 1 Width-CarcT-BBackVoid Width-CarcT-BBackVoid Both 1 29 138 150 1 Width-CarcT-BBackVoid Width-CarcT-BBackVoid Both 1 30 138 149 1 Width-CarcT-BBackVoid-BShelfSetBack Width-CarcT-BBackVoid-BShelfSetBack Both 1 31 138 133 1 Width-CarcT-BBackVoid Width-CarcT-BBackVoid-CarcT Both 1 32 138 133 1 Width-CarcT-BBackVoid Width-CarcT-BBackVoid-CarcT Both 1 What do you think i need to do? Do the Variables within the Equations match exactly the Parameter Names in the params Table (ParameterShortDesc)? What determines which Equation is used and when? However when inputting the answer to the equation via the OrderLine fields, i need to lookup the equation via the table (stkbommas) and calculate the equation using the parmas values. Please describe in more detail... Aug 22 '08 #5

 100+ P: 129 Certain Stock ID's determine the Equations (Component Level Stock Items Only) This is detailed by the Stock type field as well. Aug 23 '08 #6

 Expert 5K+ P: 8,669 Certain Stock ID's determine the Equations (Component Level Stock Items Only) This is detailed by the Stock type field as well. The logic in the original code is sound. I just made what I thought were necessary changes in order to make it more readable and efficient. Simply pass the Equation itself to the Function which will: Reference the required Parameters as they exist in the params Table. Extract the Value for each Parameter in the params Table. Dynamically build a String Expression consisting of the Values relating to each Parameter as well as any Operators (+, -, *, /) involved. Evaluate this Expression using the Eval() Function. Return a Long Integer representing the Return Value of the Equation. This code requires exactness in that the Parameters (ParameterShortDesc) in Table params 'must' match 'exactly' with the Equation's Definitions which contain them. One mistyped character, either in a Parameter or Equation, will lead to undesirable results. Sample Function Calls along with their respective Outputs are listed below. All you need now, is to extract the proper Equation, determine whether it is to be 'Across' or 'With' grain, and pass it to the fCalcEquation() Function. The code will do the rest. 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 = LCase(!ParameterShortDesc)     strValue = CStr(!value)   CheckVal:     intParamPosition = InStr(1, strEquation, strParameter)       'Is the Parameter contained within the Equation?     If intParamPosition > 0 Then       'Start building the strEquation String       strEquation = Left(strEquation, (intParamPosition - 1)) + strValue + _                     Mid(strEquation, intParamPosition + _                     Len(strParameter), Len(strEquation))       blnRepeatLoop = True     Else        'Parameter not contained in the Equation       blnRepeatLoop = False     End If       .MoveNext   Loop End With   rstParameters.Close Set MyDB = Nothing   'Evaluate the String Expression using the Eval() Function fCalcEquation = Eval(strEquation) End Function   Expand|Select|Wrap|Line Numbers Dim lngRetValue As Long   lngRetValue = fCalcEquation("Depth-BBackVoid-CarcT")   Debug.Print lngRetValue ==> Returns 260   lngRetValue = fCalcEquation("Height-CarcT*2")   Debug.Print lngRetValue ==> Returns 534   lngRetValue = fCalcEquation("Depth-BBackVoid-CarcT-BShelfSetBack")   Debug.Print lngRetValue ==> Returns 250 Aug 23 '08 #7

 100+ P: 129 The logic in the original code is sound. I just made what I thought were necessary changes in order to make it more readable and efficient. Simply pass the Equation itself to the Function which will: Reference the required Parameters as they exist in the params Table. Extract the Value for each Parameter in the params Table. Dynamically build a String Expression consisting of the Values relating to each Parameter as well as any Operators (+, -, *, /) involved. Evaluate this Expression using the Eval() Function. Return a Long Integer representing the Return Value of the Equation. This code requires exactness in that the Parameters (ParameterShortDesc) in Table params 'must' match 'exactly' with the Equation's Definitions which contain them. One mistyped character, either in a Parameter or Equation, will lead to undesirable results. Sample Function Calls along with their respective Outputs are listed below. All you need now, is to extract the proper Equation, determine whether it is to be 'Across' or 'With' grain, and pass it to the fCalcEquation() Function. The code will do the rest. 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 = LCase(!ParameterShortDesc)     strValue = CStr(!value)   CheckVal:     intParamPosition = InStr(1, strEquation, strParameter)       'Is the Parameter contained within the Equation?     If intParamPosition > 0 Then       'Start building the strEquation String       strEquation = Left(strEquation, (intParamPosition - 1)) + strValue + _                     Mid(strEquation, intParamPosition + _                     Len(strParameter), Len(strEquation))       blnRepeatLoop = True     Else        'Parameter not contained in the Equation       blnRepeatLoop = False     End If       .MoveNext   Loop End With   rstParameters.Close Set MyDB = Nothing   'Evaluate the String Expression using the Eval() Function fCalcEquation = Eval(strEquation) End Function   Just one quick question how would i execute the function on the fields AcrossGrain and WithGrain. Would i call it or something else? Aug 23 '08 #8

 Expert 5K+ P: 8,669 Just one quick question how would i execute the function on the fields AcrossGrain and WithGrain. Would i call it or something else? You have still not specifically provided to me the exact mechanism by which a specific Function is to be executed so I'll assume it is a combination of Accross/With Grain (txtGrain), StkID (txtStockID), and SubStkID (txtSubStkID). Given all this, the logic would be something similar to: Expand|Select|Wrap|Line Numbers Dim strEquationToUse As String Dim lngRetVal As Long   Select Case Me![txtGrain]   Case "AccrossGrain"     strEquationToUse = DLookup("[AccrossGrain]", "stkbommas", "[StkID] = " & _                        Me![txtStockID] & " And [SubStkID] = " & Me![txtSubStkID])   Case "WithGrain"     strEquationToUse = DLookup("[WithGrain]", "stkbommas", "[StkID] = " & _                        Me![txtStockID] & " And [SubStkID] = " & Me![txtSubStkID]) End Select   lngRetVal = fCalcEquation(strEquationToUse)   Aug 23 '08 #9

 100+ P: 129 You have still not specifically provided to me the exact mechanism by which a specific Function is to be executed so I'll assume it is a combination of Accross/With Grain (txtGrain), StkID (txtStockID), and SubStkID (txtSubStkID). Given all this, the logic would be something similar to: Expand|Select|Wrap|Line Numbers Dim strEquationToUse As String Dim lngRetVal As Long   Select Case Me![txtGrain]   Case "AccrossGrain"     strEquationToUse = DLookup("[AccrossGrain]", "stkbommas", "[StkID] = " & _                        Me![txtStockID] & " And [SubStkID] = " & Me![txtSubStkID])   Case "WithGrain"     strEquationToUse = DLookup("[WithGrain]", "stkbommas", "[StkID] = " & _                        Me![txtStockID] & " And [SubStkID] = " & Me![txtSubStkID]) End Select   lngRetVal = fCalcEquation(strEquationToUse)   Thanks for your help so far, i have two fields AcrossGrain and WithGrain, i have slightly changed your coding, probably why it doesn't work. The code i have is as follows: Expand|Select|Wrap|Line Numbers     Dim lngRetVal As Long     Dim lngRetVal2 As Long       txtAcrossGrain = DLookup("[AcrossGrain]", "cmpbommas", "[CmpID] = " & Me![StkID] & " And [SubStkID] = " & Me![SubStkID])       lngRetVal = fCalcEquation(txtAcrossGrain)       txtWithGrain = DLookup("[WithGrain]", "cmpbommas", "[CmpID] = " & Me![StkID] & " And [SubStkID] = " & Me![SubStkID])       lngRetVal2 = fCalcEquation(txtWithGrain) This code has been attached to a Button however this does not work, Could you explain why if you know? Regards Chris Aug 24 '08 #10

 Expert 5K+ P: 8,669 Thanks for your help so far, i have two fields AcrossGrain and WithGrain, i have slightly changed your coding, probably why it doesn't work. The code i have is as follows: Expand|Select|Wrap|Line Numbers     Dim lngRetVal As Long     Dim lngRetVal2 As Long       txtAcrossGrain = DLookup("[AcrossGrain]", "cmpbommas", "[CmpID] = " & Me![StkID] & " And [SubStkID] = " & Me![SubStkID])       lngRetVal = fCalcEquation(txtAcrossGrain)       txtWithGrain = DLookup("[WithGrain]", "cmpbommas", "[CmpID] = " & Me![StkID] & " And [SubStkID] = " & Me![SubStkID])       lngRetVal2 = fCalcEquation(txtWithGrain) This code has been attached to a Button however this does not work, Could you explain why if you know? Regards Chris You previously indicated to me that your Equation Table was named stkbommas, but in the DLookup() you use cmpbommas as the Table Name. From what you have previously indicated, there is no [CmpID] Field in the Equation Table, so how can you reference it in the Criteria Clause of DLookup()? Aug 24 '08 #11

 100+ P: 129 You previously indicated to me that your Equation Table was named stkbommas, but in the DLookup() you use cmpbommas as the Table Name. From what you have previously indicated, there is no [CmpID] Field in the Equation Table, so how can you reference it in the Criteria Clause of DLookup()? Yeah sorry, i encountered an issue within my tables, the correct table to reference is the cmpbommas with CmpID it is basically the same as stkbommas table. The reason i changed the tables over is because i noticed there were components within the SubStock which is the stkbommas table, when it should only be within the cmpbommas table. Also the equations only apply to those at component level, which is another reason. Sorry for the changes it is my client, they keep changing their minds as usual. Thanks again! As the tables were the same no changes were really need either, except the names of the tables and ids. Aug 25 '08 #12

 Expert 5K+ P: 8,669 Yeah sorry, i encountered an issue within my tables, the correct table to reference is the cmpbommas with CmpID it is basically the same as stkbommas table. The reason i changed the tables over is because i noticed there were components within the SubStock which is the stkbommas table, when it should only be within the cmpbommas table. Also the equations only apply to those at component level, which is another reason. Sorry for the changes it is my client, they keep changing their minds as usual. Thanks again! As the tables were the same no changes were really need either, except the names of the tables and ids. This back-and-forth communication seems to be getting us nowhere. Can you send me the Database, or a subset of it, to my Private E-Mail Account as an Attachment. Is this is agreeable to you, I'll send you my E-Mail Address in a Private Message. Aug 25 '08 #13

 100+ P: 129 This back-and-forth communication seems to be getting us nowhere. Can you send me the Database, or a subset of it, to my Private E-Mail Account as an Attachment. Is this is agreeable to you, I'll send you my E-Mail Address in a Private Message. Yeah that is fine thanks! Aug 25 '08 #14

 100+ P: 129 Yeah that is fine thanks! Database sent thanks again! If you require anymore information just ask, I will be on here all day so anytime really. Aug 25 '08 #15

 Expert 5K+ P: 8,669 Database sent thanks again! If you require anymore information just ask, I will be on here all day so anytime really. Ok Constantine, major roadblock. The following steps were taken with no problem, once I cleared a few things up. I'll indicate the snafu below, then hopefully you can point me in the right direction. Eliminated Missing References, removed references to *.jpg Files which were no longer valid, Refreshed Table Links to VR-CATO System Database Data.mdb and VR-CATO System Database Transactional Data.mdb, changed the FilePath Variable referencing the *.txt Files to point to a valid location, remmed references to Access 2007 code syntax which will not now work, etc. The Main Menu now opens cleanly. [OK]. Selected a Customer, namely Elliot. [OK]. Clicked on the Search Command Button. [OK]. Selected an Order Number for Elliot. [OK]. Clicked on the Process Command Button within the Order Section. [OK]. Clicked on the Import CSV File Button, which is now functional since the FilePath Variable now points to a valid location. [OK]. The previous step now opens frmPreSOLine with details related to the previously selected Order. [OK]. The next logical step that you indicate is to somehow Open the Import CSV Details Form. Unless I am missing the obvious, which sometimes happens, there is no mechanism whatsoever built into the frmPreSOLine Form that would enable this action. Can you explain this to me? Aug 26 '08 #17

 100+ P: 129 Ok Constantine, major roadblock. The following steps were taken with no problem, once I cleared a few things up. I'll indicate the snafu below, then hopefully you can point me in the right direction. Eliminated Missing References, removed references to *.jpg Files which were no longer valid, Refreshed Table Links to VR-CATO System Database Data.mdb and VR-CATO System Database Transactional Data.mdb, changed the FilePath Variable referencing the *.txt Files to point to a valid location, remmed references to Access 2007 code syntax which will not now work, etc. The Main Menu now opens cleanly. [OK]. Selected a Customer, namely Elliot. [OK]. Clicked on the Search Command Button. [OK]. Selected an Order Number for Elliot. [OK]. Clicked on the Process Command Button within the Order Section. [OK]. Clicked on the Import CSV File Button, which is now functional since the FilePath Variable now points to a valid location. [OK]. The previous step now opens frmPreSOLine with details related to the previously selected Order. [OK]. The next logical step that you indicate is to somehow Open the Import CSV Details Form. Unless I am missing the obvious, which sometimes happens, there is no mechanism whatsoever built into the frmPreSOLine Form that would enable this action. Can you explain this to me? You have done right it, when you clicked on the Import CSV File button, this is the form you need to look at. It does not reference related data based on the order no, the reason i said go to any existing order is that you dont have to insert a new header. The frmPreSOLine is the correct form, it is looking at the csv file ready for importing. Even though it may already have an orderline you can still process the same thing. All the code for importing the csv file is within that form including the function we worked on. Tell you what search for FRE001, he has headers with no orederline at the mo and then click the Import CSV File button, You should have 5 buttons at the bottom of the form, 4 of which work. The Calculate Grain Size button is the one i require code for. Thanks again for looking into this! Aug 26 '08 #18

 Expert 5K+ P: 8,669 You have done right it, when you clicked on the Import CSV File button, this is the form you need to look at. It does not reference related data based on the order no, the reason i said go to any existing order is that you dont have to insert a new header. The frmPreSOLine is the correct form, it is looking at the csv file ready for importing. Even though it may already have an orderline you can still process the same thing. All the code for importing the csv file is within that form including the function we worked on. Tell you what search for FRE001, he has headers with no orederline at the mo and then click the Import CSV File button, You should have 5 buttons at the bottom of the form, 4 of which work. The Calculate Grain Size button is the one i require code for. Thanks again for looking into this! I'll check into again later. Aug 26 '08 #19

 Expert 5K+ P: 8,669 Hello Constantine. In order to pass a specific equation to the Function to be evaluated you need three values, namely: [SubStkID], [CmpID] and Across/WidthGrain]. Based on the Record Source for frmPreSOLine (qryPreSOLine), and the existing Table Relationships, I simply see no mechanism for retrieving this information. The best you could hope for would be a listing of Equations from which the User can select and then a prompt for either Across or With the Grain. Once selected, the appropriate Equation could then be passed to the Function. I'll look at the DB again with fresh eyes in a day or two, but as it stands now, you are simply out of luck. Aug 26 '08 #20

 100+ P: 129 Hello Constantine. In order to pass a specific equation to the Function to be evaluated you need three values, namely: [SubStkID], [CmpID] and Across/WidthGrain]. Based on the Record Source for frmPreSOLine (qryPreSOLine), and the existing Table Relationships, I simply see no mechanism for retrieving this information. The best you could hope for would be a listing of Equations from which the User can select and then a prompt for either Across or With the Grain. Once selected, the appropriate Equation could then be passed to the Function. I'll look at the DB again with fresh eyes in a day or two, but as it stands now, you are simply out of luck. Hi ADezii i have gotten further with this coding and have now assigned the correct equation to the WithGrain and AcrossGrain based on the SubStkID and CmpID and from checking it is correct. Now the only problem i have is assigning this equation to the function. When i do i receive an error stating that it can not find "Height". Here is my coding: Expand|Select|Wrap|Line Numbers Private Sub cmdCalcGrainMeasures_Click()       Dim db As Database     Dim rst As Recordset       DoCmd.SetWarnings False       Set db = CurrentDb()     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")       Do         rst.Edit         rst!WithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst!AcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst.update         rst.MoveNext     Loop Until rst.EOF     rst.Close     Me.Requery     Set rst = Nothing       DoCmd.SetWarnings True       cmdcalcgrainmeasures.Enabled = False       Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")       Do         rst.Edit         rst!WithGrain = Eval(rst!WithGrain)         rst!AcrossGrain = Eval(rst!AcrossGrain)         rst.update         rst.MoveNext     Loop Until rst.EOF     rst.Close     Me.Requery     Set rst = Nothing   End Sub What do you think i am doing wrong here? Regards Chris Aug 28 '08 #21

 Expert 5K+ P: 8,669 Hi ADezii i have gotten further with this coding and have now assigned the correct equation to the WithGrain and AcrossGrain based on the SubStkID and CmpID and from checking it is correct. Now the only problem i have is assigning this equation to the function. When i do i receive an error stating that it can not find "Height". Here is my coding: Expand|Select|Wrap|Line Numbers Private Sub cmdCalcGrainMeasures_Click()       Dim db As Database     Dim rst As Recordset       DoCmd.SetWarnings False       Set db = CurrentDb()     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")       Do         rst.Edit         rst!WithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst!AcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst.update         rst.MoveNext     Loop Until rst.EOF     rst.Close     Me.Requery     Set rst = Nothing       DoCmd.SetWarnings True       cmdcalcgrainmeasures.Enabled = False       Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")       Do         rst.Edit         rst!WithGrain = Eval(rst!WithGrain)         rst!AcrossGrain = Eval(rst!AcrossGrain)         rst.update         rst.MoveNext     Loop Until rst.EOF     rst.Close     Me.Requery     Set rst = Nothing   End Sub What do you think i am doing wrong here? Regards Chris Height is not listed in the Parameters Table (params). Aug 28 '08 #22

 100+ P: 129 Height is not listed in the Parameters Table (params). Just checked the parmas table the data within is as follows: ParameterID ParameterShortDesc ParameterLongDesc Value 1 Width Width 300 2 Depth Depth 300 3 Height Height 570 4 BBackVoid Base Back Void 22 5 WBackVoid Wall Back Void 22 6 TBackVoid Tall BackVoid 22 7 WShelfSetBack Wall Shelf Set Back 10 8 BShelfSetBack Base Shelf Set Back 10 9 TShelfSetBack Tall Shelf Set Back 10 10 Groove Groove Depth 9 11 CarcT Carcass Thickness 18 12 DrawerST Drawer Side Thickness 140 13 RailT Rail Thickness 140 14 BackT Back Thickness 140 15 PlinthH Plinth Height 150 16 FrameSW Frame Style Width 36 17 FrameTH Frame Top Height 36 18 FrameBH Frame Bottom Height 36 19 FrameRH Frame Rail Height 36 20 DoorSW Door Style Width 72 21 DoorTH Door Top Height 72 22 DoorBH Door Bottom Height 72 23 DrawerSH Drawer Side Height 72 24 DrawerFH Drawer Front Height 140 25 DrawerBH Drawer Back Heigth 140 26 DrawerFasciaH Drawer Fascia Height 150 27 DrawerDeepFasciaH Drawer Deep Fascia Height 320 28 Tenon Tenon 9 29 Play Play 2 30 Adjustment Adjustment 1 36 MullionWid Mullion Width 90 37 MidRailWid Mid Rail Width 140 Height is clearly within it. So what could be the problem? Aug 28 '08 #23

 Expert 5K+ P: 8,669 Just checked the parmas table the data within is as follows: ParameterID ParameterShortDesc ParameterLongDesc Value 1 Width Width 300 2 Depth Depth 300 3 Height Height 570 4 BBackVoid Base Back Void 22 5 WBackVoid Wall Back Void 22 6 TBackVoid Tall BackVoid 22 7 WShelfSetBack Wall Shelf Set Back 10 8 BShelfSetBack Base Shelf Set Back 10 9 TShelfSetBack Tall Shelf Set Back 10 10 Groove Groove Depth 9 11 CarcT Carcass Thickness 18 12 DrawerST Drawer Side Thickness 140 13 RailT Rail Thickness 140 14 BackT Back Thickness 140 15 PlinthH Plinth Height 150 16 FrameSW Frame Style Width 36 17 FrameTH Frame Top Height 36 18 FrameBH Frame Bottom Height 36 19 FrameRH Frame Rail Height 36 20 DoorSW Door Style Width 72 21 DoorTH Door Top Height 72 22 DoorBH Door Bottom Height 72 23 DrawerSH Drawer Side Height 72 24 DrawerFH Drawer Front Height 140 25 DrawerBH Drawer Back Heigth 140 26 DrawerFasciaH Drawer Fascia Height 150 27 DrawerDeepFasciaH Drawer Deep Fascia Height 320 28 Tenon Tenon 9 29 Play Play 2 30 Adjustment Adjustment 1 36 MullionWid Mullion Width 90 37 MidRailWid Mid Rail Width 140 Height is clearly within it. So what could be the problem? How can you evaluate an Equation from a Recordset Field based on the preordlin Table? Aren't the Equations themselves contained within the stkbommas Table? Aug 28 '08 #24

 100+ P: 129 How can you evaluate an Equation from a Recordset Field based on the preordlin Table? Aren't the Equations themselves contained within the stkbommas Table? I used the DLOOKUP feature to find the specific equation based upon the SubStkID and the CmpID and then assigned it to the values WithGrain and AcrossGrain within the preordlin to ordlin tables. At present it just displays what the equation is not the actual numeric value. This is the part i am now stuck with! Aug 28 '08 #25

 Expert 5K+ P: 8,669 I used the DLOOKUP feature to find the specific equation based upon the SubStkID and the CmpID and then assigned it to the values WithGrain and AcrossGrain within the preordlin to ordlin tables. At present it just displays what the equation is not the actual numeric value. This is the part i am now stuck with! Where are you executing the code from, the Click() Event of the Calculate Grain Command Button? Execute the following code and tell me what you see (Reference #1 & 2) Expand|Select|Wrap|Line Numbers Do   Debug.Print rst!WithGrain        (Reference #1)   Debug.Print rst!AcrossGrain      (Reference #2)     rst.Edit        rst!WithGrain = Eval(rst!WithGrain)        rst!AcrossGrain = Eval(rst!AcrossGrain)     rst.Update     rst.MoveNext Loop Until rst.EOF Aug 28 '08 #26

 100+ P: 129 Where are you executing the code from, the Click() Event of the Calculate Grain Command Button? Execute the following code and tell me what you see (Reference #1 & 2) Expand|Select|Wrap|Line Numbers Do   Debug.Print rst!WithGrain        (Reference #1)   Debug.Print rst!AcrossGrain      (Reference #2)     rst.Edit        rst!WithGrain = Eval(rst!WithGrain)        rst!AcrossGrain = Eval(rst!AcrossGrain)     rst.Update     rst.MoveNext Loop Until rst.EOF It just says the same, error code 2482; Can't find the name 'Height'. Could i send you this updated database and see wot you can see. Aug 28 '08 #27

 Expert 5K+ P: 8,669 It just says the same, error code 2482; Can't find the name 'Height'. Could i send you this updated database and see wot you can see. Yes, you can send it to me, but you still never answered Question 1. Aug 28 '08 #28

 100+ P: 129 Yes, you can send it to me, but you still never answered Question 1. I have sent it and yes it is being done from that button. The code is within and if you want to look at the Assign Component Details button, this will show you how i placed the SubStkID within the preordlin table. This helped greatly for the Equation button assigning the values using the dlookup procedure. Thanks again! Aug 28 '08 #29

 Expert 5K+ P: 8,669 I have sent it and yes it is being done from that button. The code is within and if you want to look at the Assign Component Details button, this will show you how i placed the SubStkID within the preordlin table. This helped greatly for the Equation button assigning the values using the dlookup procedure. Thanks again! I ran the code logic against Test Values (hard coded) and it performed flawlessly as indicated below: Expand|Select|Wrap|Line Numbers Dim strWithGrain As String Dim strAcrossGrain As String   strWithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & 137 & "And [CmpID] = " & 185) strAcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & 137 & "And [CmpID] = " & 185)   Debug.Print "With Grain Equation: " & strWithGrain & vbCrLf & _             "Across Grain Equation: " & strAcrossGrain & vbCrLf & vbCrLf & _             "Parameters: " & "Width = 300 | CarcT = 18 | RailT = 140" & vbCrLf & vbCrLf & _             "With Grain Equation Evaluation: " & fCalcEquation(strWithGrain) & vbCrLf & _             "Across Grain Equation Evaluation: " & fCalcEquation(strAcrossGrain)   You are not Evaluating the Equation (as a String Expression) directly via the Eval() Function, but indirectly via the fCalcEquation() Function as evidenced by Code Lines 10 and 11 in Item #1, and 29 and 30 in Item #2. Expand|Select|Wrap|Line Numbers Private Sub cmdCalcGrainMeasures_Click() Dim db As Database Dim rst As Recordset   DoCmd.SetWarnings False   Set db = CurrentDb() Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")   Do   rst.Edit     rst!WithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)     rst!AcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)   rst.update     rst.MoveNext Loop Until rst.EOF   rst.Close   Me.Requery   Set rst = Nothing     DoCmd.SetWarnings True     cmdcalcgrainmeasures.Enabled = False     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")     Do     rst.Edit       rst!WithGrain = fCalcEquation(rst!WithGrain)       rst!AcrossGrain = fCalcEquation(rst!AcrossGrain)       rst.update     rst.MoveNext   Loop Until rst.EOF   rst.Close Me.Requery Set rst = Nothing End Sub OUTPUT FROM ITEM #1: Expand|Select|Wrap|Line Numbers With Grain Equation: 300-18*2 Across Grain Equation: 140   Parameters: Width = 300 | CarcT = 18 | RailT = 140   With Grain Equation Evaluation: 264 Across Grain Equation Evaluation: 140 Aug 29 '08 #30

 100+ P: 129 I ran the code logic against Test Values (hard coded) and it performed flawlessly as indicated below: Expand|Select|Wrap|Line Numbers Dim strWithGrain As String Dim strAcrossGrain As String   strWithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & 137 & "And [CmpID] = " & 185) strAcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & 137 & "And [CmpID] = " & 185)   Debug.Print "With Grain Equation: " & strWithGrain & vbCrLf & _             "Across Grain Equation: " & strAcrossGrain & vbCrLf & vbCrLf & _             "Parameters: " & "Width = 300 | CarcT = 18 | RailT = 140" & vbCrLf & vbCrLf & _             "With Grain Equation Evaluation: " & fCalcEquation(strWithGrain) & vbCrLf & _             "Across Grain Equation Evaluation: " & fCalcEquation(strAcrossGrain)   You are not Evaluating the Equation (as a String Expression) directly via the Eval() Function, but indirectly via the fCalcEquation() Function as evidenced by Code Lines 10 and 11 in Item #1, and 29 and 30 in Item #2. Expand|Select|Wrap|Line Numbers Private Sub cmdCalcGrainMeasures_Click() Dim db As Database Dim rst As Recordset   DoCmd.SetWarnings False   Set db = CurrentDb() Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")   Do   rst.Edit     rst!WithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)     rst!AcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)   rst.update     rst.MoveNext Loop Until rst.EOF   rst.Close   Me.Requery   Set rst = Nothing     DoCmd.SetWarnings True     cmdcalcgrainmeasures.Enabled = False     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")     Do     rst.Edit       rst!WithGrain = fCalcEquation(rst!WithGrain)       rst!AcrossGrain = fCalcEquation(rst!AcrossGrain)       rst.update     rst.MoveNext   Loop Until rst.EOF   rst.Close Me.Requery Set rst = Nothing End Sub OUTPUT FROM ITEM #1: Expand|Select|Wrap|Line Numbers With Grain Equation: 300-18*2 Across Grain Equation: 140   Parameters: Width = 300 | CarcT = 18 | RailT = 140   With Grain Equation Evaluation: 264 Across Grain Equation Evaluation: 140 So what can i do to get this to work? Aug 29 '08 #31

 Expert 5K+ P: 8,669 So what can i do to get this to work? Make sure the preordlin Table is correctly populated (Line #8), and make the code changes in Lines 29 and 30, beyond these measures I can do no more. Aug 29 '08 #32

 100+ P: 129 Make sure the preordlin Table is correctly populated (Line #8), and make the code changes in Lines 29 and 30, beyond these measures I can do no more. Ok that is not a problem however i was just wondering what i change lines 29 and 30 with? Aug 30 '08 #33

 Expert 5K+ P: 8,669 Ok that is not a problem however i was just wondering what i change lines 29 and 30 with? Try changing: Expand|Select|Wrap|Line Numbers Do   rst.Edit     rst!WithGrain = Eval(rst!WithGrain)     rst!AcrossGrain = Eval(rst!AcrossGrain)   rst.Update   rst.MoveNext Loop Until rst.EOF To Expand|Select|Wrap|Line Numbers Do   rst.Edit     rst!WithGrain = fCalcEquation(rst!WithGrain)     rst!AcrossGrain = fCalcEquation(rst!AcrossGrain)   rst.Update   rst.MoveNext Loop Until rst.EOF Aug 30 '08 #34

 100+ P: 129 Try changing: Expand|Select|Wrap|Line Numbers Do   rst.Edit     rst!WithGrain = Eval(rst!WithGrain)     rst!AcrossGrain = Eval(rst!AcrossGrain)   rst.Update   rst.MoveNext Loop Until rst.EOF To Expand|Select|Wrap|Line Numbers Do   rst.Edit     rst!WithGrain = fCalcEquation(rst!WithGrain)     rst!AcrossGrain = fCalcEquation(rst!AcrossGrain)   rst.Update   rst.MoveNext Loop Until rst.EOF Thankyou so much, it works flawlessly now. Thankyou Aug 30 '08 #35

 Expert 5K+ P: 8,669 Thankyou so much, it works flawlessly now. Thankyou You are quite welcome. Aug 30 '08 #36

 100+ P: 129 You are quite welcome. Hi Adezii, sorry to bother you again regarding this post, but i have found an error within the function and was hoping if you new how to fix it. The problem is that the code errors when there maybe a fourth parameter to be looked upon. Example 1: WithGrain = Width-CarcT-BBackVoid-CarcT Result: Could NOT Find the Filed CarcT Example 2: WithGrain = Width-CarcT-BBackVoid Result: Works Fine I then realised that the function may not work if there are four parameters present. As i dont fully understand the code function i am hoping it is quite easy to add a fourth paramter setting within the function. Could you please help me again? Expand|Select|Wrap|Line Numbers     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 = LCase(!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) If you think the code is fine and should work with four parameters, could you tell me what maybe wrong instead? Thanks Sep 9 '08 #37

 Expert 5K+ P: 8,669 Hi Adezii, sorry to bother you again regarding this post, but i have found an error within the function and was hoping if you new how to fix it. The problem is that the code errors when there maybe a fourth parameter to be looked upon. Example 1: WithGrain = Width-CarcT-BBackVoid-CarcT Result: Could NOT Find the Filed CarcT Example 2: WithGrain = Width-CarcT-BBackVoid Result: Works Fine I then realised that the function may not work if there are four parameters present. As i dont fully understand the code function i am hoping it is quite easy to add a fourth paramter setting within the function. Could you please help me again? Expand|Select|Wrap|Line Numbers     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 = LCase(!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) If you think the code is fine and should work with four parameters, could you tell me what maybe wrong instead? Thanks Off the top of my head, it should work equally well with 4 Parameters, but I can't say for sure. I will look into it when I get a chance. P.S. - I just realized that I no longer have a fully functional copy of your Database, and I cannot do anything without it. Send me a copy of the DB as an Attachment when you get a chance, if you no longer have my E-Mail Address let me know in a Private Message. I will then give it to you again. Sep 9 '08 #38

 100+ P: 129 Off the top of my head, it should work equally well with 4 Parameters, but I can't say for sure. I will look into it when I get a chance. P.S. - I just realized that I no longer have a fully functional copy of your Database, and I cannot do anything without it. Send me a copy of the DB as an Attachment when you get a chance, if you no longer have my E-Mail Address let me know in a Private Message. I will then give it to you again. Ok Adezii, thanks again and i have sent you the database and a brief summary of what i did. Sep 9 '08 #39

 Expert 5K+ P: 8,669 Ok Adezii, thanks again and i have sent you the database and a brief summary of what i did. I actually just found the code in one of my Archives and diagnosed the problem. The problem does not lie with the number of Parameters being used in the Equation, actually an unlimited number of Parameters can be used, but the Equation cannot consist of multiple uses of the same Parameter(s), as in: "Depth-CarcT+BBackVoid-CarcT-BShelfSetBack" Notice that CarcT is being repeated in the Expression. The original Author who created this Algorithm did not anticipate this possibility, and it is not contained within the code logic. Here is what is happening: 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. Unfortunately if the same Parameter exists more than once, the explodes explodes. OK, maybe not that bad! The bad news is that I simply do not have the time to build a new algorithm which allows multiple uses of a Parameter, when I do, I'll see what I can do. The good news is that I do have a solution, not pretty, but it do work (LOL). If you will be using a Parameter more than once in an Equation, enter it a second time in the parmas Table, in a fashion that will not confuse the Instr() Function. Its Value of course will remain the same. If CarcT will be used on two occasions within an Equation, define 2 Instances of it in the Parameters Table (NOT CarcT and Carct_2). I'll post a sample Table, and an execution example, to illustrate this point: parmas Table Expand|Select|Wrap|Line Numbers ParameterShortDesc    Value      Comment Depth                 300     Same as Dpth_2 BBackVoid             22     CarcT                  18     Same as CrcT_2 Height                 570     BShelfSetBack         10     Dpth_2                 300      Same as Depth CrcT_2                  18      Same as CarcT The following Expression can now be evaluated: Expand|Select|Wrap|Line Numbers Dim lngRetValue As Long   lngRetValue = fCalcEquation("Depth-CrcT_2+BBackVoid-CarcT-BShelfSetBack+Dpth_2")   Me![txtResults] = lngRetValue Sep 9 '08 #40

 100+ P: 129 I actually just found the code in one of my Archives and diagnosed the problem. The problem does not lie with the number of Parameters being used in the Equation, actually an unlimited number of Parameters can be used, but the Equation cannot consist of multiple uses of the same Parameter(s), as in: "Depth-CarcT+BBackVoid-CarcT-BShelfSetBack" Notice that CarcT is being repeated in the Expression. The original Author who created this Algorithm did not anticipate this possibility, and it is not contained within the code logic. Here is what is happening: 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. Unfortunately if the same Parameter exists more than once, the explodes explodes. OK, maybe not that bad! The bad news is that I simply do not have the time to build a new algorithm which allows multiple uses of a Parameter, when I do, I'll see what I can do. The good news is that I do have a solution, not pretty, but it do work (LOL). If you will be using a Parameter more than once in an Equation, enter it a second time in the parmas Table, in a fashion that will not confuse the Instr() Function. Its Value of course will remain the same. If CarcT will be used on two occasions within an Equation, define 2 Instances of it in the Parameters Table (NOT CarcT and Carct_2). I'll post a sample Table, and an execution example, to illustrate this point: parmas Table Expand|Select|Wrap|Line Numbers ParameterShortDesc    Value      Comment Depth                 300     Same as Dpth_2 BBackVoid             22     CarcT                  18     Same as CrcT_2 Height                 570     BShelfSetBack         10     Dpth_2                 300      Same as Depth CrcT_2                  18      Same as CarcT The following Expression can now be evaluated: Expand|Select|Wrap|Line Numbers Dim lngRetValue As Long   lngRetValue = fCalcEquation("Depth-CrcT_2+BBackVoid-CarcT-BShelfSetBack+Dpth_2")   Me![txtResults] = lngRetValue Thanks for explaining the code, i understand what and how it works now. Also thanks for the suggestion, luckily i only had to creat a second CarcT as this parameter was the only one mentioned twice. Thanks again! Chris p.s. I will stop bothering you now! LOL Sep 9 '08 #41

 Expert 5K+ P: 8,669 Thanks for explaining the code, i understand what and how it works now. Also thanks for the suggestion, luckily i only had to creat a second CarcT as this parameter was the only one mentioned twice. Thanks again! Chris p.s. I will stop bothering you now! LOL Contents DELETED - Double Post! Sep 9 '08 #42

 Expert 5K+ P: 8,669 Thanks for explaining the code, i understand what and how it works now. Also thanks for the suggestion, luckily i only had to creat a second CarcT as this parameter was the only one mentioned twice. Thanks again! Chris p.s. I will stop bothering you now! LOL It's not a bother, Chris. The reason why the code was not working under certain conditions was not very obvious at first. I only found 5 Instances where more than 1 of the same Parameter was used in an equation, and it was always CarcT. I'll post where I made the changes and also show the new Parameter added which should resolve this problem: Make the following changes to Records in the smpbommas Table (the last CarcT change to CrcT_2): Expand|Select|Wrap|Line Numbers SCBOMID    WithGrain                        AcrossGrain 151        Width-CarcT-BBackVoid            Width-CarcT-BBackVoid-CrcT_2 152        Width-CarcT-BBackVoid            Width-CarcT-BBackVoid-CrcT_2 182        Width-CarcT-BBackVoid-CrcT_2        Width-CarcT-BBackVoid 183        Width-CarcT-BBackVoid-CrcT_2        Width-CarcT-BBackVoid-CrcT_2   Add the following Record to the parmas Table(Add 2nd Record, 1st for comparison only): Expand|Select|Wrap|Line Numbers ParameterID    ParameterShortDesc    ParameterLongDesc    Value 11            CarcT                   Carcass Thickness       18 39            CrcT_2               Carcass Thickness    18   All should now be well in the Equation world! P.S. - Kindly ignore the previous Post! I can solve Equations but not Single Post! (LOL). Sep 9 '08 #43

 100+ P: 129 It's not a bother, Chris. The reason why the code was not working under certain conditions was not very obvious at first. I only found 5 Instances where more than 1 of the same Parameter was used in an equation, and it was always CarcT. I'll post where I made the changes and also show the new Parameter added which should resolve this problem: Make the following changes to Records in the smpbommas Table (the last CarcT change to CrcT_2): Expand|Select|Wrap|Line Numbers SCBOMID    WithGrain                        AcrossGrain 151        Width-CarcT-BBackVoid            Width-CarcT-BBackVoid-CrcT_2 152        Width-CarcT-BBackVoid            Width-CarcT-BBackVoid-CrcT_2 182        Width-CarcT-BBackVoid-CrcT_2        Width-CarcT-BBackVoid 183        Width-CarcT-BBackVoid-CrcT_2        Width-CarcT-BBackVoid-CrcT_2   Add the following Record to the parmas Table(Add 2nd Record, 1st for comparison only): Expand|Select|Wrap|Line Numbers ParameterID    ParameterShortDesc    ParameterLongDesc    Value 11            CarcT                   Carcass Thickness       18 39            CrcT_2               Carcass Thickness    18   All should now be well in the Equation world! P.S. - Kindly ignore the previous Post! I can solve Equations but not Single Post! (LOL). Yeah thanks, the database works fine now! thanks again. Chris Sep 12 '08 #44

 Expert 5K+ P: 8,669 Yeah thanks, the database works fine now! thanks again. Chris You are quite welcome, after all it was quite a challenge! (LOL). Sep 12 '08 #45

 100+ P: 129 You are quite welcome, after all it was quite a challenge! (LOL). Hey Adezii, sorry to bother you again. I have encountered a minor problem and would like your opinion on the matter, it is still in regards to the equation formulae. I have some items of stock at component level they do not have equations assigned to them, this is because they have no sub-stock details assigned to them. So it is literally a standalone item. However my client has now stated they wish to input manual figures if the equation does not exist of those particular items. So within this code below (Taken from the Calculate Grain Button); Expand|Select|Wrap|Line Numbers     Dim db As Database     Dim rst As Recordset     Dim rst2 As Recordset     Dim strSQL As String     Dim Widthval As Integer     Dim Depthval As Integer     Dim Heightval As Integer       DoCmd.SetWarnings False       Set db = CurrentDb()     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")       Do         rst.Edit         rst!WithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst!AcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst!Edged = DLookup("Edged", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst.update         rst.MoveNext     Loop Until rst.EOF     rst.Close     Me.Requery     Set rst = Nothing       Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE WithGrain <> Null and AcrossGrain <> Null")       Do         rst.Edit           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Width'")         [Widthval] = rst!Width         db.Execute ("UPDATE parmas SET [Value] = " & [Widthval] & " WHERE ParameterShortDesc = 'Width';")           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Depth'")         [Depthval] = rst!Depth         db.Execute ("UPDATE parmas SET [Value] = " & [Depthval] & " WHERE ParameterShortDesc = 'Depth';")           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Height'")         [Heightval] = rst!Height         db.Execute ("UPDATE parmas SET [Value] = " & [Heightval] & " WHERE ParameterShortDesc = 'Height';")           rst!WithGrain = fCalcEquation(rst!WithGrain)         rst!AcrossGrain = fCalcEquation(rst!AcrossGrain)         rst.update         rst.MoveNext       Loop Until rst.EOF     rst.Close           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Width'")         db.Execute ("UPDATE parmas SET [Value] = 500 WHERE ParameterShortDesc = 'Width';")           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Depth'")         db.Execute ("UPDATE parmas SET [Value] = 300 WHERE ParameterShortDesc = 'Depth';")           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Height'")         db.Execute ("UPDATE parmas SET [Value] = 130 WHERE ParameterShortDesc = 'Height';")       Me.Requery     Set rst = Nothing       DoCmd.SetWarnings True       cmdcalcgrainmeasures.Enabled = False     cmdAssignDetails.Enabled = False     cmdUpdateInformation.Enabled = False     cmdImportintoOrderline.Enabled = True I would require an IF Statement basically saying if this recordset does not contain an equation THEN input the two values instead (WithGrain and AcrossGrain). I am pretty sure this will have to go within the loop procedure. the thing i am worried about is if there are items with and without grain equations! Wot are your thoughts on this? Could you point me in the right direction as well? Thanks again! Sep 16 '08 #46

 Expert 5K+ P: 8,669 Hey Adezii, sorry to bother you again. I have encountered a minor problem and would like your opinion on the matter, it is still in regards to the equation formulae. I have some items of stock at component level they do not have equations assigned to them, this is because they have no sub-stock details assigned to them. So it is literally a standalone item. However my client has now stated they wish to input manual figures if the equation does not exist of those particular items. So within this code below (Taken from the Calculate Grain Button); Expand|Select|Wrap|Line Numbers     Dim db As Database     Dim rst As Recordset     Dim rst2 As Recordset     Dim strSQL As String     Dim Widthval As Integer     Dim Depthval As Integer     Dim Heightval As Integer       DoCmd.SetWarnings False       Set db = CurrentDb()     Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE SubStkID > 0")       Do         rst.Edit         rst!WithGrain = DLookup("WithGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst!AcrossGrain = DLookup("AcrossGrain", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst!Edged = DLookup("Edged", "cmpbommas", "[SubStkID] = " & rst!SubStkID & "And [CmpID] = " & rst!StkID)         rst.update         rst.MoveNext     Loop Until rst.EOF     rst.Close     Me.Requery     Set rst = Nothing       Set rst = db.OpenRecordset("SELECT * FROM preordlin WHERE WithGrain <> Null and AcrossGrain <> Null")       Do         rst.Edit           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Width'")         [Widthval] = rst!Width         db.Execute ("UPDATE parmas SET [Value] = " & [Widthval] & " WHERE ParameterShortDesc = 'Width';")           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Depth'")         [Depthval] = rst!Depth         db.Execute ("UPDATE parmas SET [Value] = " & [Depthval] & " WHERE ParameterShortDesc = 'Depth';")           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Height'")         [Heightval] = rst!Height         db.Execute ("UPDATE parmas SET [Value] = " & [Heightval] & " WHERE ParameterShortDesc = 'Height';")           rst!WithGrain = fCalcEquation(rst!WithGrain)         rst!AcrossGrain = fCalcEquation(rst!AcrossGrain)         rst.update         rst.MoveNext       Loop Until rst.EOF     rst.Close           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Width'")         db.Execute ("UPDATE parmas SET [Value] = 500 WHERE ParameterShortDesc = 'Width';")           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Depth'")         db.Execute ("UPDATE parmas SET [Value] = 300 WHERE ParameterShortDesc = 'Depth';")           Set rst2 = db.OpenRecordset("SELECT * FROM parmas WHERE ParameterShortDesc = 'Height'")         db.Execute ("UPDATE parmas SET [Value] = 130 WHERE ParameterShortDesc = 'Height';")       Me.Requery     Set rst = Nothing       DoCmd.SetWarnings True       cmdcalcgrainmeasures.Enabled = False     cmdAssignDetails.Enabled = False     cmdUpdateInformation.Enabled = False     cmdImportintoOrderline.Enabled = True I would require an IF Statement basically saying if this recordset does not contain an equation THEN input the two values instead (WithGrain and AcrossGrain). I am pretty sure this will have to go within the loop procedure. the thing i am worried about is if there are items with and without grain equations! Wot are your thoughts on this? Could you point me in the right direction as well? Thanks again! I am on vacation now and there is really nothing I can do at this time. If you can, send me the Current Database as an E-Mail Attachment, and post some specific examples, along with detailed explanations, demonstrating some items which would not have associated Equations. List all relevant information and values. Are these Equations to be stored internally or are they a one-shot-deal? What is the mechanism by which these Equations are to be manually entered? Will these Equations use all the existing Parameters in the parmas Table?, etc... Sep 16 '08 #47