runtime error 20515

Posted on 2006-05-24
Medium Priority
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

Expert Comment

ID: 16752306
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"

Author Comment

ID: 17042636
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 & "])"

Accepted Solution

DarthMod earned 0 total points
ID: 17100893
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month8 days, 13 hours left to enroll

621 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