UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ms Access Run-time Error 7769 The Filter Operation Was Canceled. The Filter Would Be Too Long, Access 2016    
 
   
Jpalmer
post Dec 13 2019, 04:40 PM
Post#1



Posts: 5
Joined: 16-April 19



I have a multiselect listbox in a form that I am using to filter a report. Everything works fine until I try to run the report with a large record set. The "On Click" event I have the following code.

CODE
Private Sub cmdViewReport_Click()
    Call SelectAll(Forms!frmPartsManager!lstPartsManager)
    
On Error GoTo Err_cmdOpenReport_Click
    Dim strWhere      As String
    Dim ctl           As Control
    Dim varItem       As Variant
  
    
    If Me.lstPartsManager.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 Part"
    Exit Sub
    End If

    Set ctl = Me.lstPartsManager
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
    Next varItem
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)
    'open the report, restricted to the selected items
    DoCmd.OpenReport "PartsListDataReport", acPreview, , "PartID IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
    Exit Sub

Err_cmdOpenReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenReport_Click
    
End Sub


I am newbie and my skillsets are limited. So if I can't find the solution on google, I am lost.
Go to the top of the page
 
projecttoday
post Dec 13 2019, 07:11 PM
Post#2


UtterAccess VIP
Posts: 11,439
Joined: 10-February 04
From: South Charleston, WV


welcome2UA.gif
What is this recordset? Does the problem occur when you pick too many parts? Is PartID indexed in the table?

--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Dec 13 2019, 07:19 PM
Post#3


UtterAccess VIP
Posts: 10,177
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

There are multiple ways to deal with this, including posting the selections to a temp table and using the approach something like ...

...,"PartID IN(SELECT PartID FROM tblTempPartList)"

And there are different ways to post the items to the temp table.

Or you can add a checkbox to the main parts list table and select that way from a subform, and use the checkbox as the criteria.

See if you can use one of these approaches. If you have problems with this, perhaps you could post a db (no sensitive data, complete examples, relevant objects, zipped) and someone could take a look and see what might fit your requirements.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Jpalmer
post Dec 13 2019, 09:28 PM
Post#4



Posts: 5
Joined: 16-April 19



Robert, has
Thanks for replying.
Yes the error occurs when I select too many partIds. Yes partID is indexed, it is the primary key and is an auto number. Each row in the listbox represents 1 partID. I have filtered the listbox to as many as +/- 300 rows with no error. When I filter the listbox to 600 or so I receive the error.
This post has been edited by Jpalmer: Dec 13 2019, 09:31 PM
Go to the top of the page
 
Jpalmer
post Dec 13 2019, 09:40 PM
Post#5



Posts: 5
Joined: 16-April 19



Joe,
I will try to post a sample database when I am in my office tomorrow. I’m not worried about sensitive info, just have pull out this small section of the database. Don’t think I want to send you a 30MB file smile.gif
Go to the top of the page
 
RJD
post Dec 13 2019, 09:46 PM
Post#6


UtterAccess VIP
Posts: 10,177
Joined: 25-October 10
From: Gulf South USA


Hi: I will look in tomorrow. Remember, the zip file should be 2 MB or smaller to attach.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Dec 13 2019, 10:01 PM
Post#7


UtterAccess VIP
Posts: 11,439
Joined: 10-February 04
From: South Charleston, WV


600 parts? You manually select 600 parts? How long does that take?

SQL code isn't unlimited. If you really need to filter a query result by 600 individually specified values then you need to join the query to a table of the values. A humongous WHERE clause isn't going to work.

--------------------
Robert Crouser
Go to the top of the page
 
Jpalmer
post Dec 14 2019, 12:10 AM
Post#8



Posts: 5
Joined: 16-April 19



There’s a SelectAll Module that runs during the doCmd.Openreport event. No, I didn’t manually select 600 listbox lines.
Go to the top of the page
 
theDBguy
post Dec 14 2019, 10:07 AM
Post#9


UA Moderator
Posts: 77,294
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

Just FYI, a "SelectAll" filter/criteria is the same as "no" filter/criteria. Also, if the In() list could be too long, the query result will be the same if you reverse the criteria using Not In(). In other words, In(Selected Items) is the same as Not In(Not Selected Items). So, maybe you can use whichever one is shorter.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Jpalmer
post Dec 20 2019, 10:38 AM
Post#10



Posts: 5
Joined: 16-April 19



I have uploaded a sample database with all the relevant data. it is an exact copy of the section in my full database that I get the Run-Time Error 7769.
It should be pretty easy to understand how to navigate through. If not, just let me know.

Attached File  Parts_List.zip ( 251.41K )Number of downloads: 1
Go to the top of the page
 
RJD
post Dec 20 2019, 01:26 PM
Post#11


UtterAccess VIP
Posts: 10,177
Joined: 25-October 10
From: Gulf South USA


Hi again: Well, IMHO, I think you are going about this the VERY hard way. Look at my revision. I changed the form to bound and made another query to feed the form (like the current query, but with filters to reflect PartNumber, Company and the date span). Then I changed the report to simply use the new query.

You still have some cleanup to do, and adding column headers to the form, but see if this looks better to you. This way you can avoid the IN filtering approach altogether and no stepping through the displayed records to see what is there, and, again IMHO, make this simpler.

HTH
Joe
Attached File(s)
Attached File  Parts_List_Rev1.zip ( 168.71K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th January 2020 - 01:18 PM