It is actually really hard (not to say impossible) to follow the execution of this code without being able to debug it and if I understand well we do need Oracle 8 in order to be able to debug it! All that I can say for now is to use the debugger and step through the code line by line inspecting each variable that were updated after each line execution!


>I am working on a Access 97 database as a front end with Oracle 8 as a back end. In one of the reports I have code that uses a record set to pull the information together. When the report is ran there are four fields and if there is information in one of the fields twice the field with the total is added together for each one of those records. IE: If the vendor field is Cash and there is cash for 350.00 and one for 500.00 they will both show separately up with 850.00 as the amount.
>
>Here is the Code
>
>Thanks for the help.DoCmd.RunSQL "DELETE * FROM VCNHESTIMATER_TBL where VCNHEST is not null"
>
>varNoTrpSeats = 0
>varNofSS = 0
>TotalCost = 0
>TtlCstWBsVr = 0
>TtlCstWAgVr = 0
>Set dbs = CurrentDb
>
>sqlstr = "select * from trip_tbl where [tripid] =" & vartrp
>Set rstForTrpGrpTbl = dbs.OpenRecordset(sqlstr)
>If rstForTrpGrpTbl.RecordCount = 0 Then
>TotalVHIDPlan = 0
>ExpPlan = TotalVHIDPlan + ExpPlan
>Else
>rstForTrpGrpTbl.MoveLast
>varCntForTrGr = rstForTrpGrpTbl.RecordCount
>rstForTrpGrpTbl.MoveFirst
>For varTrGr = 1 To varCntForTrGr
>sqlstr = "select * from trip_group_tbl where [tripid] =" & vartrp
>Set rstForTrpGrpsts = dbs.OpenRecordset(sqlstr)
>If rstForTrpGrpTbl.RecordCount = 0 Then
>varNofSS = 0
>Else
>rstForTrpGrpsts.MoveLast
>varCntForTrGrsts = rstForTrpGrpsts.RecordCount
>rstForTrpGrpsts.MoveFirst
>For varTrGrsts = 1 To varCntForTrGrsts
>sqlstr = " select * from trip_group_tbl where [tripid] =" & vartrp & _
>" and [groupid] = " & rstForTrpGrpsts!GroupID
>Set rstForgrpsts = dbs.OpenRecordset(sqlstr)
>varNofSS = varNofSS + rstForgrpsts!NoofSeatsSold
>rstForTrpGrpsts.MoveNext
>Next varTrGrsts
>End If
>'********
>varNofSS = varseatssold
>'********
>'varTrpSeats = rstForTrpGrpTbl!NoofSeats
>'varTrpSeatsAvble = rstForTrpGrpTbl!SeatsAvailable
>'varNofSS = rstForTrpGrpTbl!NoofSeats - rstForTrpGrpTbl!SeatsAvailable
>'varNoTrpSeats = varNoTrpSeats + varNofSS
>sqlstr = " select * from tripmap where [tripid] =" & vartrp & _
>" and not (isnull(vhid)) "
>Set rstForGrpMapTbl = dbs.OpenRecordset(sqlstr)
>If rstForGrpMapTbl.RecordCount <> 0 Then
>rstForGrpMapTbl.MoveLast
>varCntForGrp = rstForGrpMapTbl.RecordCount
>rstForGrpMapTbl.MoveFirst
>For varGrpMap = 1 To varCntForGrp
>TtlCstWBsVr = 0
>TtlCstWAgVr = 0
>varVVHID = rstForGrpMapTbl!VHID
>sqlstr = " select * from variablecostnonhotel_tbl where [vhid] = " & varVVHID & " and [paxoption] = No"
>Set rstForVCNHTbl = dbs.OpenRecordset(sqlstr)
>If rstForVCNHTbl.RecordCount <> 0 Then
>varVID = rstForVCNHTbl!VendorID
>VHIDdesc = rstForVCNHTbl!FeatureDescription
>sqlstr = "select * from Agevariation_tbl where [TripID] = " & vartrp & _
>" and [VHID] = " & rstForGrpMapTbl!VHID
>Set rstForAgeVarTbl = dbs.OpenRecordset(sqlstr)
>If rstForAgeVarTbl.RecordCount <> 0 Then
>rstForAgeVarTbl.MoveLast
>varCntForAgVr = rstForAgeVarTbl.RecordCount
>rstForAgeVarTbl.MoveFirst
>For varAgVr = 1 To varCntForAgVr
>TtlCstWAgVr = TtlCstWAgVr + rstForAgeVarTbl!Cost * rstForAgeVarTbl!NumberofPax
>varNofSS = varNofSS - rstForAgeVarTbl!NumberofPax
>rstForAgeVarTbl.MoveNext
>Next varAgVr
>Else
>sqlstr = "select * from busvariation_tbl where [TripID] = " & vartrp & _
>" and [vhid] = " & rstForGrpMapTbl!VHID
>Set rstForBusVarTbl = dbs.OpenRecordset(sqlstr)
>If rstForBusVarTbl.RecordCount <> 0 Then
>rstForBusVarTbl.MoveLast
>varCntForBsVr = rstForBusVarTbl.RecordCount
>rstForBusVarTbl.MoveFirst
>Else
>For varBsVr = 1 To varCntForBsVr
>TtlCstWBsVr = TtlCstWBsVr + rstForBusVarTbl!Cost * rstForBusVarTbl!NumberofPax
>varNofSS = varNofSS - rstForBusVarTbl!NumberofPax
>rstForBusVarTbl.MoveNext
>Next varBsVr
>End If
>End If
>'varfeaturecode = rstForVCNHTbl!FeatureCode
>TotalCost = TotalCost + TtlCstWBsVr + TtlCstWAgVr + (rstForVCNHTbl!Cost * varNofSS)
>TotalVHIDPlan = TotalCost
>ExpPlan = TotalVHIDPlan + ExpPlan
>sqlstr = "Select * from [checkwriting_tbl] where [TripID] = " & vartrp & _
>" and [VendorID] =" & varVID
>'" and [VendorID] =" & varVID & " and [FeatureCode] = " & varfeaturecode
>Set rstForPrevAmt = dbs.OpenRecordset(sqlstr)
>If rstForPrevAmt.RecordCount <> 0 Then
>rstForPrevAmt.MoveLast
>varCntPrevAmt = rstForPrevAmt.RecordCount
>rstForPrevAmt.MoveFirst
>For VarAmt = 1 To varCntPrevAmt
>TotalVHIDActual = TotalVHIDActual + rstForPrevAmt!TotalPaymentAmt
>ExpActual = TotalVHIDActual + ExpActual
>VHIDDiff = TotalVHIDActual - TotalVHIDPlan
>rstForPrevAmt.MoveNext
>Next VarAmt
>Else
>TotalVHIDActual = 0
>ExpActual = TotalVHIDActual + ExpActual
>VHIDDiff = TotalVHIDActual - TotalVHIDPlan
>End If
>sqlstr = "Select * from [suppliers_tbl] where [vendorID] = " & varVID
>Set rstvname = dbs.OpenRecordset(sqlstr)
>varvendorname = rstvname!VendorName
>DoCmd.RunSQL " INSERT INTO VCNHESTIMATER_TBL (TRIPID, ProgramID, " & _
>" totalplan, totalactual, VID, VHID, vendorname, description) " & _
>" VALUES (" & vartrp & ", " & varprogramid & ", " & TotalVHIDPlan & ", " & TotalVHIDActual & _
>" , " & varVID & ", " & varVVHID & ", '" & varvendorname & "', '" & VHIDdesc & "')"
>TotalCost = 0
>TotalVHIDPlan = 0
>TotalVHIDActual = 0
>End If
>'optionVHID
>sqlstr = " select * from variablecostnonhotel_tbl where [vhid] = " & varVVHID & " and [paxoption] = Yes"
>Set rstForVCNHTbl = dbs.OpenRecordset(sqlstr)
>If rstForVCNHTbl.RecordCount <> 0 Then
>varVID = rstForVCNHTbl!VendorID
>VHIDdesc = rstForVCNHTbl!FeatureDescription
>varppoptions = 0
>sqlstr = "SELECT * FROM paxoptions_tbl " & _
>" WHERE accountid IN (SELECT accountID FROM payaccount_tbl where [tripid] =" & vartrp & ") " & _
>" and vhid = " & varVVHID
>Set rstppoptions = dbs.OpenRecordset(sqlstr)
>If rstppoptions.RecordCount = 0 Then
>varppoptions = 0
>Else
>rstppoptions.MoveLast
>varpaxoptions = rstppoptions.RecordCount
>rstppoptions.MoveFirst
>For options = 1 To varpaxoptions
>varppoptions = varppoptions + rstppoptions!Rate
>rstppoptions.MoveNext
>Next
>End If
>TotalCost = varppoptions
>TotalVHIDPlan = TotalCost
>ExpPlan = TotalVHIDPlan + ExpPlan
>sqlstr = "Select * from [checkwriting_tbl] where [TripID] = " & vartrp & _
>" and [VendorID] =" & varVID
>Set rstForPrevAmt = dbs.OpenRecordset(sqlstr)
>If rstForPrevAmt.RecordCount <> 0 Then
>rstForPrevAmt.MoveLast
>varCntPrevAmt = rstForPrevAmt.RecordCount
>rstForPrevAmt.MoveFirst
>For VarAmt = 1 To varCntPrevAmt
>TotalVHIDActual = TotalVHIDActual + rstForPrevAmt!TotalPaymentAmt
>ExpActual = TotalVHIDActual + ExpActual
>VHIDDiff = TotalVHIDActual - TotalVHIDPlan
>rstForPrevAmt.MoveNext
>Next VarAmt
>Else
>TotalVHIDActual = 0
>ExpActual = TotalVHIDActual + ExpActual
>VHIDDiff = TotalVHIDActual - TotalVHIDPlan
>End If
>sqlstr = "Select * from [suppliers_tbl] where [vendorID] = " & varVID
>Set rstvname = dbs.OpenRecordset(sqlstr)
>varvendorname = rstvname!VendorName
>DoCmd.RunSQL " INSERT INTO VCNHESTIMATER_TBL (TRIPID, ProgramID, " & _
>" totalplan, totalactual, VID, VHID, vendorname, description) " & _
>" VALUES (" & vartrp & ", " & varprogramid & ", " & TotalVHIDPlan & ", " & TotalVHIDActual & _
>" , " & varVID & ", " & varVVHID & ", '" & varvendorname & "', '" & VHIDdesc & "')"
>TotalCost = 0
>TotalVHIDPlan = 0
>TotalVHIDActual = 0
>End If
>'end of optionVHID
>rstForGrpMapTbl.MoveNext
>Next varGrpMap
>End If
>rstForTrpGrpTbl.MoveNext
>Next varTrGr
>End If
>
>TotalCost = 0
>varNofSS = 0