2 DAO recordsets..i'm having an issue... 1

[dpk136]

I'm working in Access 2003. This is my code

strSQL = "SELECT Transactions.TransDate AS SellDate, Allocations.ContractNum AS FIFOContractNum, Allocations.AllocatedMonth, Allocations.AllocatedYear AS FiscalYear, Sum([Allocations].[HoldPurchased]-[Allocations].[NumContractsAllocated]) AS NumContracts, ProductAdmin.UnitsPerContract, ProductAdmin.Group AS ProductGroup, Avg(Transactions.SellPrice) AS AvgSellPrice " & _
        "FROM (ProductAdmin INNER JOIN (TickerSymContract INNER JOIN Transactions ON TickerSymContract.ContractNum = Transactions.ContractNum) ON ProductAdmin.ProductID = TickerSymContract.TickerSym) INNER JOIN Allocations ON Transactions.TransNum = Allocations.TransNum " & _
        "GROUP BY Transactions.TransDate, Allocations.ContractNum, Allocations.AllocatedMonth, Allocations.AllocatedYear, ProductAdmin.UnitsPerContract, ProductAdmin.Group, Transactions.LockRecord " & _
        "HAVING (((Allocations.AllocatedYear)>='" & strHoldYear & "') AND ((Transactions.LockRecord)=True)) " & _
        "ORDER BY Transactions.TransDate, Allocations.AllocatedYear;"
        
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    Do While rst.EOF = False
        If Trim(rst!FIFOContractNum) <> strContract Or Trim(rst!FiscalYear) <> strFiscalYear Then
            strContract = Trim(rst!FIFOContractNum)
            strProductGroup = Trim(rst!ProductGroup)
            strFiscalYear = Trim(rst!FiscalYear)
            
            intJan = 0
            intFeb = 0
            intMar = 0
            intApr = 0
            intMay = 0
            intJun = 0
            intJul = 0
            intAug = 0
            intSep = 0
            intOct = 0
            intNov = 0
            intDec = 0
            intNumContracts = 0
            dblTotalSellPrice = 0
            
            bNewContract = True
        Else
            bNewContract = False
        End If
        
        Select Case Trim(rst!AllocatedMonth)
            Case "January"
                intJan = intJan + CInt(Trim(rst!NumContracts))
            Case "February"
                intFeb = intFeb + CInt(Trim(rst!NumContracts))
            Case "March"
                intMar = intMar + CInt(Trim(rst!NumContracts))
            Case "April"
                intApr = intApr + CInt(Trim(rst!NumContracts))
            Case "May"
                intMay = intMay + CInt(Trim(rst!NumContracts))
            Case "June"
                intJun = intJun + CInt(Trim(rst!NumContracts))
            Case "July"
                intJul = intJul + CInt(Trim(rst!NumContracts))
            Case "August"
                intAug = intAug + CInt(Trim(rst!NumContracts))
            Case "September"
                intSep = intSep + CInt(Trim(rst!NumContracts))
            Case "October"
                intOct = intOct + CInt(Trim(rst!NumContracts))
            Case "November"
                intNov = intNov + CInt(Trim(rst!NumContracts))
            Case "December"
                intDec = intDec + CInt(Trim(rst!NumContracts))
        End Select
        
        strSQL = "SELECT SellTemp.SellTransNum, SellTemp.AllocateYear, SellTemp.MonthNum, SellTemp.AllocateMonth, Avg(Transactions.SellPrice) AS AvgOfSellPrice " & _
            "FROM Transactions INNER JOIN SellTemp ON Transactions.TransNum = SellTemp.SellTransNum " & _
            "GROUP BY SellTemp.SellTransNum, SellTemp.AllocateYear, SellTemp.MonthNum, SellTemp.AllocateMonth " & _
            "HAVING (((SellTemp.AllocateYear)='" & strFiscalYear & "') AND ((SellTemp.AllocatedMonth)=" & Trim(rst!AllocatedMonth) & "));"
        Set rst2 = CurrentDb.OpenRecordset(strSQL, 2)
        If rst2.EOF = False Then
            dblTotalSellPrice = CDbl(Trim(rst2.Fields("AvgOfSellPrice")))
        End If
        rst2.Close
        
        intNumContracts = intNumContracts + CInt(Trim(rst!NumContracts))
        If bNewContract = True Then
            'Insert new
            strSQL = "INSERT INTO PositionSummaryReport (ProductGroup,FiscalYear,ContractNum,NumOfContracts,NumOfUnits,AvgPurchPrice,CurrentMarketPrice,CurrentMarketDate,GainLoss,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,JanUnit,FebUnit,MarUnit,AprUnit,MayUnit,JunUnit,JulUnit,AugUnit,SepUnit,OctUnit,NovUnit,DecUnit,IsSold) " & _
                "VALUES ('" & rst!ProductGroup & "','" & rst!FiscalYear & "','" & rst!FIFOContractNum & "'," & rst!NumContracts & "," & CLng(Trim(rst!NumContracts)) * CLng(Trim(rst!UnitsPerContract)) & ",0," & dblTotalSellPrice & ",'" & rst!SellDate & "',0," & intJan & "," & intFeb & "," & intMar & "," & intApr & "," & intMay & "," & intJun & "," & intJul & "," & intAug & "," & intSep & "," & intOct & "," & intNov & "," & intDec & "," & intJan * CLng(rst!UnitsPerContract) & "," & intFeb * CLng(rst!UnitsPerContract) & "," & intMar * CLng(rst!UnitsPerContract) & "," & intApr * CLng(rst!UnitsPerContract) & "," & intMay * CLng(rst!UnitsPerContract) & "," & intJun * CLng(rst!UnitsPerContract) & "," & intJul * CLng(rst!UnitsPerContract) & "," & intAug * CLng(rst!UnitsPerContract) & "," & intSep * CLng(rst!UnitsPerContract) & "," & intOct * CLng(rst!UnitsPerContract) & "," & intNov * CLng(rst!UnitsPerContract) & "," & intDec * CLng(rst!UnitsPerContract) & ",True)"
        Else
            'Update where product group fiscal year and contract num all equal the same
            strSQL = "UPDATE PositionSummaryReport Set NumOfContracts=" & intNumContracts & ",NumOfUnits=" & intNumContracts * CLng(rst!UnitsPerContract) & ",Jan=" & intJan & ",Feb=" & intFeb & ",Mar=" & intMar & ",Apr=" & intApr & ",May=" & intMay & ",Jun=" & intJun & ",Jul=" & intJul & ",Aug=" & intAug & ",Sep=" & intSep & ",Oct=" & intOct & ",Nov=" & intNov & ",Dec=" & intDec & ",JanUnit=" & intJan * CLng(rst!UnitsPerContract) & ",FebUnit=" & intFeb * CLng(rst!UnitsPerContract) & ",MarUnit=" & intMar * CLng(rst!UnitsPerContract) & ",AprUnit=" & intApr * CLng(rst!UnitsPerContract) & ",MayUnit=" & intMay * CLng(rst!UnitsPerContract) & ",JunUnit=" & intJun * CLng(rst!UnitsPerContract) & ",JulUnit=" & intJul * CLng(rst!UnitsPerContract) & ",AugUnit=" & intAug * CLng(rst!UnitsPerContract) & ",SepUnit=" & intSep * CLng(rst!UnitsPerContract) & ",OctUnit=" & intOct * CLng(rst!UnitsPerContract) & ",NovUnit=" & intNov * CLng(rst!UnitsPerContract) & ",DecUnit=" & _
                intDec * CLng(rst!UnitsPerContract) & " WHERE FiscalYear='" & rst!FiscalYear & "' AND ContractNum='" & rst!FIFOContractNum & "' AND IsSold=True"
        End If
        CurrentDb.Execute (strSQL)
        
        rst.MoveNext
    Loop
    
    strContract = ""
    strProductGroup = ""
    strFiscalYear = ""
    intNumContracts = 0
    rst.Close

The variables are all declared fine and the data is correct.

What i don't get is why i can't get two recordsets to work. one inside a loop. rst2 i get an error when setting it gives error 3061 too few parameters expecting 2. This is an extremely urgent issue. Please help


David Kuhn
------------------

http://www.davidpkuhn.com

 

[SkipVought]

Hi,

"too few parameters" usually means that you have one or more fields named incorrectly in your SQL.

Skip,

_[red][/red]

 

[dpk136]

You were correct...the Allocate month field is a string. and also it was spelled wrong one of the times.

Thanks for the quick response.

David Kuhn
------------------

http://www.davidpkuhn.com