Solved The file operation was canceled. The filter would be too big! (1 Viewer)

[crawfordfr]

Hi Not even sure this is the correct area but not sure where else to look. I did do a search in the forums for similar topics but didnt find any. Did google searches and found matches but no fixes that actually help me. I was thinking mayb the report area as well. Feel free to move if it dont belong!

Anyways I have this form which lists all my Files/Clients in arrears. I cycle through a recordset and calculate all the balances and then display on this form!

If I click the Print to screen button it des the following:
1) Cycle through the listbox
2) Looks for all Bills matching the ID
3) Grabs the highest bill number
4) Builds a string containign all the Bills compared to the list (based on #3)
5) builds a where clause based on #4 to pass to the open report command
Problem is I get the following error. Is there another way I can achieve what I want?
NOTE: This works perfectly well with lets say 100 records but crashes when its a lot of records!

The VBA CODE is listed below.

Private Sub cmdPrintBills_Click()
Dim i As Integer
Dim strSQL As String
Dim strMaxBillId As String
Dim rstLookup As Recordset
Dim strWhereClause As String

On Error GoTo ErrorHandler

strMaxBillId = 0
strWhereClause = "1=1"

For i = 1 To Me.lstFiles.ListCount - 1 ' For each record in the listbox

' Select the newly inserted record
strSQL = "SELECT max(bil_id) as MaxBillId FROM TBILLS WHERE fil_id = " & Me.lstFiles.ItemData(i)
' MsgBox (strSQL)
' Open the RecordSet
Set rstLookup = dbsPBHr.OpenRecordset(strSQL, dbOpenSnapshot)

If rstLookup!MaxBillId > 0 Then
strMaxBillId = strMaxBillId & ", " & rstLookup!MaxBillId
End If

Next i

strWhereClause = strWhereClause & " AND bil_id IN (" & strMaxBillId & ")"
' lngPBWhereClause = "1=1 " & strWhereClause

DoCmd.OpenReport "repBillArrears", acViewPreview, , strWhereClause

ErrorHandler:
Select Case Err.Number

' no Real error
Case 0

' Pass the message to the user
Case Else
Call MsgBox("A fatal error occured, contact the IT Section! The error details will be displayed in the next message box!", 16, "System Error")
Call MsgBox("Form = frmrepFilesinArrears, Procedure = cmdPrintBills_Click, Error number = " & Err.Number & ", Error description = " & Err.Description, 16, "System Error")

End Select

End Sub

 

[June7]

Why are you building massive string as opposed to applying filter to bound report?

If you want to provide db for analysis, follow instructions at bottom of my post.

In future, please post code between CODE tags to preserve indentation and readability.

 

[crawfordfr]

Why are you building massive string as opposed to applying filter to bound report?

If you want to provide db for analysis, follow instructions at bottom of my post.

In future, please post code between CODE tags to preserve indentation and readability.

Not sure I understand BUT the string I am building is the filter for the report. The string is to identify the records! Remember each file can have many bills already generated so I must 1st identify the newest one!

If I just open report without building this list it will simply print a report for ALL the records in the bills table...whereas I am only interested in the newest one in relation to the ID in the listbox above!

Hopefully I am making myself clear if not please ask

 

[crawfordfr]

Why are you building massive string as opposed to applying filter to bound report?

If you want to provide db for analysis, follow instructions at bottom of my post.

In future, please post code between CODE tags to preserve indentation and readability.

I finally underestood what you were saying. I made a very complex query that basically achieved the same records I wanted in this report and then ran the report based on that. Made life so much easier not having to cycle through list nor build that file. Thanks. See I need someone too talk to . Being a lone programmer sucks lol

 

[June7]

I cycle through a recordset and calculate all the balances

Sorry, I skimmed your question too quickly. I had a different impression of what you were doing. My attention was snagged by that statement. Don't quite understand why you do that.

However, I do understand building filter criteria with VBA but possibly your resulting string was too long.

Glad you were able to resolve.

 

[crawfordfr]

Sorry, I skimmed your question too quickly. I had a different impression of what you were doing. My attention was snagged by that statement. Don't quite understand why you do that.

However, I do understand building filter criteria with VBA but possibly your resulting string was too long.

Glad you were able to resolve.

I know the question is solved but I wanna explain what I am doing since you say you don't understand.

Basically, my account balances, arrears, interest balances and arrears are calculated on the fly. Its on a main screen where you search and find the client/file and then you see the balances below. However, in this case, for management I need to calculate for ALL the accounts. Hence the reason I cycle through a recordset and calculate everything at that point. If there is another way to do this pray tell lol since cycling through a recordset is soooo slow!

 

[The_Doc_Man]

@crawfordfr

When you say "cycle through a recordset" it is not clear what you are actually doing, but your suggestion of tedium doesn't paint a pretty picture. Here is a hint that might make things a bit more orderly.

You can include order-by and group-by clauses in queries. If you use a group-by clause you could use domain aggregates such as SUM and COUNT and a few other statistical functions to get statistics for the group rather than the whole table.

If all you want is a summation by customer, build a query with GROUP BY on your customer info. If your credits and debits have opposite signs, you can treat them like transactions to add or subtract to a total, which would condone use of a SUM aggregate. Your balance would be there, computed by SQL rather than having to step through a recordset. Computing via SQL is WAY faster and cleaner than computing by iteration of a recordset.

Queries are your friends. You have one table with your customer transactions. You can have ANY NUMBER of different queries defined for the same table, though forms & reports only allow a single query as their .RecordSource properties. Queries help you by looking at your bulk data in many different ways, sorting or filtering or grouping according to your viewing needs - yet a SELECT query doesn't do ANYTHING to the actual table unless you manually or programmatically make change through the query (via form or recordset).

Aggregate Function Queries in Access - Instructions

Aggregate Function Queries in Access: An overview, instructions, and video about how to create queries using aggregate functions in Access.

www.teachucomp.com

If you need details AND aggregates, you might need more than one query since aggregation via GROUP BY suggests to Access that you won't want details. It is not an insurmountable issue, though, since there are such things as sub-forms that could be used for details, linked by customer number. And a sub-form can have a different .RecordSource than the main form, even though they might be related. So lots of options exist to make things very easy.

 

[June7]

Perhaps use RunningSum property of textbox on report.

Also, review http://allenbrowne.com/AppInventory.html