Invalid Operation Error 
Author Message
 Invalid Operation Error

I have a sub where I open a record set , calculate some pricing levels and
then open another record set to update a  field in a table with each of the
values as they are calculated.
I get an Invalid operation error. I did not open either recordset as read
only.

I had a lot of trouble with the syntax in the second SQL statement but I
think its right. I think there must be a better way . Any suggestions are
greatly appreciated!

Public Sub AssignSingleLevels()

    Dim curDiscDNYtd As Currency

    Dim strSinglePurchaseLevel As String
    Dim curYtdSingleLevelTwoDollars As Currency
    Dim curYtdSingleLevelOneDollars As Currency
    Dim curYtdSingleLevelThreeDollars As Currency

        Dim dbs As Database
            Dim rs As Recordset       ' FIRST RECORD SET , LOOP THRU TO
ASSIGN PRICING LEVELS
                Dim dbsSecond As Database
                    Dim rsSecond As Recordset  ' SECOND RECORD SET, THE IDEA
IS TO OPEN A TABLE
                                                                    'AND ADD
THE FIRST PRICING LEVEL TO THE FIRST RECORD

            ' strings
            Dim strDealerGroup As String
            Dim strDealerCode As String
            Dim strProgram As String
            Dim strSQL As String

            ' singles
            Dim sngYtdFactor As Single

            sngYtdFactor = YearToDateFactor

             strSQL = "SELECT tblCommitAssignSingleLevel.DealerGroup,
tblCommitAssignSingleLevel.DealerCode, tblCommitAssignSingleLevel.Program,
Sum(tblCommitAssignSingleLevel.DiscDNYtd) AS SumOfDiscDNYtd,
tblCommitAssignSingleLevel.Level1S, tblCommitAssignSingleLevel.Level2S,
tblCommitAssignSingleLevel.LEVEL3S " _
                   & " FROM tblCommitAssignSingleLevel GROUP BY
tblCommitAssignSingleLevel.DealerGroup,
tblCommitAssignSingleLevel.DealerCode, tblCommitAssignSingleLevel.Program,
tblCommitAssignSingleLevel.Level1S, tblCommitAssignSingleLevel.Level2S,
tblCommitAssignSingleLevel.LEVEL3S "

            Set dbs = OpenDatabase("C:\Commit\CommitDevelopmentJune30.mdb")

            Set rs = dbs.OpenRecordset(strSQL)

            With rs

            If .RecordCount = 0 Then

            MsgBox "Empty Table"
            End If

            rs.MoveFirst

            Do While Not .EOF

            ' fill variables, factor for year to date.

            curDiscDNYtd = rs!SumOfDiscDNYtd

            curYtdSingleLevelOneDollars = (rs!Level1S) * sngYtdFactor
            curYtdSingleLevelTwoDollars = (rs!Level2S) * sngYtdFactor
            curYtdSingleLevelThreeDollars = (rs!Level3S) * sngYtdFactor

             ' Calculate year to date Single levels based on ytd purchases

    If curDiscDNYtd >= curYtdSingleLevelThreeDollars Then

       strSinglePurchaseLevel = 3

        ElseIf curDiscDNYtd >= curYtdSingleLevelTwoDollars Then

            strSinglePurchaseLevel = 2

              ElseIf curDiscDNYtd >= curYtdSingleLevelOneDollars Then

               strSinglePurchaseLevel = 1

                  ElseIf curDiscDNYtd < curYtdSingleLevelOneDollars Then

                       strSinglePurchaseLevel = 0

     End If

             strSQL = "UPDATE tblRenameThis SET NewSingleLevel = '" &
strSinglePurchaseLevel & "'"

             Set dbsSecond =
OpenDatabase("C:\Commit\CommitDevelopmentJune30.mdb")

            Set rsSecond = dbs.OpenRecordset(strSQL)

            .MoveNext

            Loop

    End With

        rs.Close
        dbs.Close
        Set rs = Nothing
        Set dbs = Nothing

            rsSecond.Close
            dbsSecond.Close
                Set rsSecond = Nothing
                Set dbsSecond = Nothing

End Sub



Wed, 25 Dec 2002 03:00:00 GMT  
 Invalid Operation Error
Sorry, I sent too much code to read thru. The code that causes my problem is
at the bottom of the original posting :

strSQL = "UPDATE tblRenameThis SET NewSingleLevel = '" &
strSinglePurchaseLevel & "'"

             Set dbsSecond =
OpenDatabase("C:\Commit\CommitDevelopmentJune30.mdb")

            Set rsSecond = dbs.OpenRecordset(strSQL)

Any suggetions or comments are greatly appreciated.



Wed, 25 Dec 2002 03:00:00 GMT  
 Invalid Operation Error

"Action queries" don't create recordsets: only a Select does that. Use the
RunSQL method:

   DoCmd.RunSql strSQL

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/


Quote:
> Sorry, I sent too much code to read thru. The code that causes my problem
is
> at the bottom of the original posting :

> strSQL = "UPDATE tblRenameThis SET NewSingleLevel = '" &
> strSinglePurchaseLevel & "'"

>              Set dbsSecond =
> OpenDatabase("C:\Commit\CommitDevelopmentJune30.mdb")

>             Set rsSecond = dbs.OpenRecordset(strSQL)

> Any suggetions or comments are greatly appreciated.



Thu, 26 Dec 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. field.properties.append -- Invalid Operation error?

2. error 3219: invalid operation

3. Error 40020: Invalid operation at current state.

4. ADO Command Object Error 3711 Invalid Operation

5. "Operation invalid without current index"

6. Operation invalid without a current index

7. Run-Time Error 2247: Invalid use of dot operator or invalid parentheses

8. FE/BE split generates invalid operation, yes I read the help files :)

9. Invalid operation on closed object

10. Err 3129 Invalid Operation??? (DAO)

11. Invalid operation at current state.

12. Invalid operation with crystl32.ocx?

 

 
Powered by phpBB® Forum Software