runtime error 20515

Posted on 2006-05-24
Last Modified: 2008-01-09
I am getting this error in vb6 when it is time to bring up the crystal report

Error in formula <Record_Selection>
'{CustAddr.AddrType} = "MAILING" and {ReqHdr.ReqNum} = 'R111892' and not {ReqItem.ReqItem} in [0,1,2,3,4,5,6,7,8,9,10,...]

I found where the error is coming from. It has to do with the not {ReqItem.ReqItem} in [0,1,...].
This error only comes up when there are 500 or more numbers in the not clause.

I am thinking the only way to correct this error is to change the way it gets which items are selected. the problem is if i change not equal to where it is equal it will have the same effect but now when they select more than 500. The current code is below. the error hits on the last line of the code posted. Any suggestions? Let me know if there is anything else i can provide to aid in you helping me

With flxP0Sheet
        .Redraw = False
        currrow = .Row
        mItem = "0"
        For rowctr = 1 To .Rows - 1
            .Row = rowctr
            .Col = goCFlex.ColByName(flxP0Sheet, "Select?")
            If .CellChecked = 1 Then
                Set rs = gdbOp.OpenRecordset("Select QuotePrice, QuoteMOQ, QuoteLead, Note1, Note2 from ReqItem Where ReqNum = '" & txtReqNum.Text & "' and ReqItem = " & .TextMatrix(rowctr, goCFlex.ColByName(flxP0Sheet, "ReqItem")), dbOpenDynaset, 0, dbOptimistic)
                rs(0) = .ValueMatrix(rowctr, goCFlex.ColByName(flxP0Sheet, "SalePrice"))
                rs(1) = .ValueMatrix(rowctr, goCFlex.ColByName(flxP0Sheet, "QuoteMOQ"))
                rs(2) = .TextMatrix(rowctr, goCFlex.ColByName(flxP0Sheet, "QuoteLead"))
                rs(3) = .TextMatrix(rowctr, goCFlex.ColByName(flxP0Sheet, "Note1"))
                rs(4) = .TextMatrix(rowctr, goCFlex.ColByName(flxP0Sheet, "Note2"))
                mItem = mItem & "," & .TextMatrix(rowctr, goCFlex.ColByName(flxP0Sheet, "ReqItem"))
            End If

 frmResources2.Report1.ReportFileName = App.Path & "\Quote.rpt"
    frmResources2.Report1.SelectionFormula = "{ReqHdr.ReqNum} = '" & txtReqNum.Text & "' and not ({ReqItem.ReqItem} in [" & mItem & "])"
    frmResources2.Report1.Destination = crptToWindow
    frmResources2.Report1.Action = 1

Question by:samme
    LVL 3

    Expert Comment

    could you add a new field to your recordset to flag if the record is selected or not, then alter your selection formula :-

    frmResources2.Report1.SelectionFormula = "{ReqHdr.ReqNum} = '" & txtReqNum.Text & "' and {ReqItem.Selected} = 0"
    LVL 1

    Author Comment

    got it to work by adding another sting exactly like mItem such as nItem and when the items went over 500 start adding them to nItem sting

    Finally on the selection formula i changed it to look like this:

     frmResources2.Report1.SelectionFormula = "{ReqHdr.ReqNum} = '" & txtReqNum.Text & "' and not ({ReqItem.ReqItem} in [" & mItem & "] and not ({ReqItem.ReqItem} in [" & nItem & "])"
    LVL 1

    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now