Help with runtime error '7769'

[associates]

Hi,

I've got this error '7769' when trying to send 6000 records to a report for preview. The error says "The filter operation was cancelled. The filter would be too long". This is to do with the stlinkcriteria. Is there a limit in size as to how long the text is allowed to be put into stlinkcriteria.

What happen here is i have a form that has a listbox and a button called "Summary report". This button is to pass whatever records shown in the listbox onto a report. At the moment, there are about 6000 items there and i pass them by ID which is made up of 4 digits eg.0000.

Here is my code written as follows:

Private Sub CM_SumReport_Click()
Dim stDocName As String
Dim stLinkCriteria As String

nList = Me.myList.ListCount

For ncount = 0 To nList - 1
stLinkCriteria = "," & Me.myList.ItemData(ncount) & stLinkCriteria
Next ncount

If Len(stLinkCriteria) > 0 Then
stLinkCriteria = "[TimeSheet_ID] In (" & Mid(stLinkCriteria, 2) & ")"
End If

'MsgBox stLinkCriteria
stDocName = "Timesheet_SumReport" ' for producing timesheet summary report

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
End Sub

Is there any way to get around this problem?

Thank you very much in advance and look forward to hearing from you.

 

[TheAceMan1]

How are ya associates . . .

Post the [blue]RowSource[/blue] of the Listbox and [blue]RecordSource[/blue] of the report.

See Ya! . . . . . .

 

[associates]

Hi, TheAceMan1. Thank you for your reply.

The RowSource of the Listbox is

SELECT [Time Sheet].TimeSheet_ID, Staff_register.First_Name, [Time Sheet].WorkDate, [Time Sheet].Industry_No, [Time Sheet].Client_No, [Time Sheet].Job_No, [Time Sheet].SubJob_No, [Time Sheet].Schedule_ID FROM [Time Sheet] INNER JOIN Staff_register ON [Time Sheet].Staff_ID=Staff_register.Staff_ID WHERE ((([Time Sheet].TimeSheet_ID) Like Forms!Timesheetlist_form!TB_TimesheetIDQuery) And ((Staff_register.First_Name) Like Forms!Timesheetlist_form!TB_StaffQuery) And (([Time Sheet].WorkDate) Between Forms!Timesheetlist_form!TB_DateFromquery And Forms!Timesheetlist_form!TB_DateToquery)) ORDER BY [Time Sheet].WorkDate;

As for the recordsource of the report is a join between Timesheet table and Staff_register table on the Staff_ID field. So what fields being shown on the report are Timesheet_ID, First_Name, WorkDate, EntryDate, Industry_No, Client_No, Job_No and other fields

Sorry, Associates for giving you this much information but is this what you want or? is there anyway to pass 6000 item records over to report via stlinkcriteria. Any size limit over the length for passing?

Thank you once again, TheAceMan1

 

[PHV]

Create a temporary table with 6000 rows holding the selected values and join it in the report's underlaying query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886