Using Recordsets to resolve "Procedure too large" Error.

Here is the complete string SQL

Public Function MyRptYear() As String
Dim SQLstr As String
SQLstr = "SELECT tblTPlant.TPlant AS TPlantID, "
        SQLstr = SQLstr & "Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=1 And IsNull([SID])=False " & _
        "And ((tblSewer.SanFlow<>0)),1,0)) AS NCCountQ1, Sum(IIf([SConnTypeID]='NC' " & _
        "And DatePart('q',[permitedate])=1,[SanFlow],0))AS [San FlowQ1], Sum(IIf([SConnTypeID]='PL' " & _
        "And DatePart('q',[permitedate])=1 And IsNull([SID])=False And ((tblSewer.PlugFlow<>0)),1,0)) AS PLCountQ1, " & _
        "Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=1,[PlugFlow],0)) AS [Plug FlowQ1], " & _
        "([San FlowQ1]*0.65)-([Plug FlowQ1]*0.65) AS [Net FlowQ1], "
    SQLstr = SQLstr & "Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=2 And IsNull([SID])=False " & _
        "And ((tblSewer.SanFlow<>0)),1,0)) AS NCCountQ2, Sum(IIf([SConnTypeID]='NC' " & _
        "And DatePart('q',[permitedate])=2,[SanFlow],0))AS [San FlowQ2], Sum(IIf([SConnTypeID]='PL' " & _
        "And DatePart('q',[permitedate])=2 And IsNull([SID])=False And ((tblSewer.PlugFlow<>0)),1,0)) AS PLCountQ2, " & _
        "Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=2,[PlugFlow],0)) AS [Plug FlowQ2], " & _
        "([San FlowQ2]*0.65)-([Plug FlowQ2]*0.65) AS [Net FlowQ2],"
    SQLstr = SQLstr & "Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=3 And IsNull([SID])=False " & _
        "And ((tblSewer.SanFlow<>0)),1,0)) AS NCCountQ3, Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=3, " & _
        "[SanFlow],0))AS [San FlowQ3], Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=3 And IsNull([SID])=False " & _
        "And ((tblSewer.PlugFlow<>0)),1,0)) AS PLCountQ3, Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=3,[PlugFlow],0)) " & _
        "AS [Plug FlowQ3], ([San FlowQ3]*0.65)-([Plug FlowQ3]*0.65) AS [Net FlowQ3],"
    SQLstr = SQLstr & "Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=4 And IsNull([SID])=False And ((tblSewer.SanFlow<>0)),1,0)) " & _
        "AS NCCountQ4, Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=4,[SanFlow],0))AS [San FlowQ4], Sum(IIf([SConnTypeID]='PL' " & _
        "And DatePart('q',[permitedate])=4 And IsNull([SID])=False And ((tblSewer.PlugFlow<>0)),1,0)) AS PLCountQ4, Sum(IIf([SConnTypeID]='PL' " & _
        "And DatePart('q',[permitedate])=4,[PlugFlow],0)) AS [Plug FlowQ4], ([San FlowQ4]*0.65)-([Plug FlowQ4]*0.65) AS [Net FlowQ4] "
    SQLstr = SQLstr & "FROM tblTPlant INNER JOIN tblSewer ON tblTPlant.TPlantID = tblSewer.TPlantID "
    SQLstr = SQLstr & "WHERE (((tblSewer.SewerTypeID) = 'SA' Or (tblSewer.SewerTypeID) = 'CM') " & _
        "And ((DatePart('yyyy', [PermitEDate])) = " & [Forms]![Report Date Range]![input year] & ") And ((tblSewer.TPlantID) Is Not Null) " & _
        "And ((tblSewer.PermitEDate) Is Not Null) And ((tblSewer.CIDate) Is Not Null) And ((tblSewer.PermitNo) Is Not Null)) "
    SQLstr = SQLstr & "GROUP BY tblTPlant.TPlant; "
   
MyRptYear = SQLstr
Exit Function
End Function
' *********

Private Sub Report_Open(Cancel As Integer)
   Me.RecordSource = MyRptYear()
End Sub

Which line of code is highlighted when you get the error? The size limit for a procedure is 64K which you don't reach here.

There is a line continuation limit in VB. What version of Access are you running?

Nevermind, I see the lines are broken up.

MikeToole,
I have no problem with my home Access 2003. My office is still glued to Access 2K. When I run the same code in the office I get the "Procedure too large error"

on...

Me.RecordSource = MyRptYear()


I am hoping to try a work around ...may be using a recordset might reslove the problem. ..

I noticed that "rockiroads" used same technique very recently at the link below:
http://www.experts-exchange.com/Databases/MS_Access/Q_21983364.html

Regards
Bill

Bill,
Unfortunately, for some strange reason, the Recordset property can only be used for reports in an access project - it's not supported in an mdb.
I'm still a little puzzled that you hit the 64K limit. Is there any other code in Report_Open() ?
One thing you can try is to move the assignment of the recordsource from Report_Open() to the MyRptYear function.
Mike

MikeToole,
The report has a sub report as welll...but please post the entire string SQL with your suggested assignment of the recordsource to "MyRptYear" Function.

Regards
Bill

mbizub,
I will test your suggestion in the office today and let you know later.

Regards
Bill

Mike,

Should I place this in a standard module or in the report?

Regards
Bill

Also, what if the report has a sub report with string as well...so opening "two" AssignmentRecordSource at the same time might not work..?

For example in the current design of my subreport..I have the following code :

Private Sub Report_Open(Cancel As Integer)

Static Started As Boolean                            '<<----- Note this lines
If Not Started Then                                    '<<----- Note this lines
        Me.RecordSource = MyRptSubYear()    '<<----- Note this lines
        Started = True                                  '<<----- Note this lines
        Debug.Print MyRptSubYear()
End If
End Sub
' *********


Bill

Bill,
My assumption was that MyRptYear() was in a standard module, that's why the report object is passed as a parameter in the sub AssignRecordSource(). If you put AssignRecordSource in the Report module, you don't need to pass the parameter

You can do the same thing for the sub-report if you convert MyRptSubYear to the sub AssignSubRecordSource in the same way as was done for the main report:

Private Sub Report_Open(Cancel As Integer)

Static Started As Boolean                            '<<----- Note this lines
If Not Started Then                                    '<<----- Note this lines
        AssignSubRecordSource me               '<<----- Note this lines
        Started = True                                  '<<----- Note this lines
        Debug.Print MyRptSubYear()
End If
End Sub
'

mbizub,
I tested your suggestion on Access 2003. It worked. I will definitely test it on Access2K today and let you know later.

Regards
Bill

Mike,
I first converted the sub report to :"AssignSubRecordSource" then posted each code below in the Sub Report open at different times, then tried running the report in Access 2003. There were no errors.."only" the main report showed the sub did not show.

Private Sub Report_Open(Cancel As Integer)
Static Started As Boolean                            
If Not Started Then                                  
        AssignSubRecordSource me              
        Started = True                                
End If
End Sub

...and...this...

Private Sub Report_Open(Cancel As Integer)
        AssignSubRecordSource me            
End Sub
' *****

Regards
Bill

mbizub / Mike,
I tested both suggestions under Access 2K today and both suggested code returned a Run-time Error '2176':
"The settings for this property is too long"

on....
Private Sub Report_Open(Cancel As Integer)
   AssignRecordSource Me                               '<<--- Error line
End Sub

...and  on...

Private Sub Report_Open(Cancel As Integer)
   Me.RecordSource = MyRptYear1  &  MyRptYear2  &  MyRptYear3 &  MyRptYear4  &  MyRptYear5    '<<-- Error line
End Sub
' *******

Regards
Bill

mbizub / Mike,
Is it fair to conclude that there is no solution available to resolve the problem above. If so, then can I request to close this question and refund points?

Regards
Bill

Bill,
Sorry, I've been out of the loop for a few days.
So far we've been trying to fix the problem without changing the approach too much. Another approach is to use your SQL to create an Access query, then use that as the record source for your report. A query can refer to controls in an open form the same way as in your SQL string:

And ((DatePart('yyyy', [PermitEDate])) =  [Forms]![Report Date Range]![input year]

Mike,
I didnt quite follow your suggestion on the SQL

Regards
Bill

Mike,
I will appreciate a clearer suggestion and how to implement it.

Regards
Bill

Bill,
Sorry for the delay.
What I mean is this...
Your code builds up a SQL string which it then assigns as the source for the report. There is nothing, so far as I can see, that that changes in the SQL apart from the value returned by the reference to the Input Year on the form. This means that the same SQL string could be used in an Access query as follows:
- Create a new Query in design view
- Close the Show Table dialog without selecting a table
- Choose the SQL view from the View combo-box on the left end of the design toolbar
- Paste your query string into the sql edit box that is displayed, overwriting what's already there (it should just contain SELECT;)

The only difference between the dtring you built-up in your code and what you need to paste-in here is that [Forms]![Report Date Range]![input year] needs to be a literal in the string itself, rather than a value concatenated at run-time. (I've pasted the string at the end of this post)

- Save the query using a name of your choice - let's say qryReport
- Use qryReport as the source of your report

Note: the query will only work when the form Report Date Range is open - it needs to get the year criteria from there

Hope this helps,
Mike

The string should look like this:
SELECT tblTPlant.TPlant AS TPlantID, Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=1 And IsNull([SID])=False And ((tblSewer.SanFlow<>0)),1,0)) AS NCCountQ1, Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=1,[SanFlow],0))AS [San FlowQ1], Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=1 And IsNull([SID])=False And ((tblSewer.PlugFlow<>0)),1,0)) AS PLCountQ1, Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=1,[PlugFlow],0)) AS [Plug FlowQ1], ([San FlowQ1]*0.65)-([Plug FlowQ1]*0.65) AS [Net FlowQ1], Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=2 And IsNull([SID])=False And ((tblSewer.SanFlow<>0)),1,0)) AS NCCountQ2, Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=2,[SanFlow],0))AS [San FlowQ2], Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=2 And IsNull([SID])=False And ((tblSewer.PlugFlow<>0)),1,0)) AS PLCountQ2, Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=2,[PlugFlow],0)) AS [Plug FlowQ2], ([San FlowQ2]*0.65)-([Pl
ug FlowQ2]*0.65) AS [Net FlowQ2],Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=3 And IsNull([SID])=False And ((tblSewer.SanFlow<>0)),1,0)) AS NCCountQ3, Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=3, [SanFlow],0))AS [San FlowQ3], Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=3 And IsNull([SID])=False And ((tblSewer.PlugFlow<>0)),1,0)) AS PLCountQ3, Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=3,[PlugFlow],0)) AS [Plug FlowQ3], ([San FlowQ3]*0.65)-([Plug FlowQ3]*0.65) AS [Net FlowQ3],Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=4 And IsNull([SID])=False And ((tblSewer.SanFlow<>0)),1,0)) AS NCCountQ4, Sum(IIf([SConnTypeID]='NC' And DatePart('q',[permitedate])=4,[SanFlow],0))AS [San FlowQ4], Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=4 And IsNull([SID])=False And ((tblSewer.PlugFlow<>0)),1,0)) AS PLCountQ4, Sum(IIf([SConnTypeID]='PL' And DatePart('q',[permitedate])=4,[PlugFlow],0)) AS [Plug FlowQ4], ([San FlowQ4]*0.65)-([Plug F
lowQ4]*0.65) AS [Net FlowQ4] FROM tblTPlant INNER JOIN tblSewer ON tblTPlant.TPlantID = tblSewer.TPlantID WHERE (((tblSewer.SewerTypeID) = 'SA' Or (tblSewer.SewerTypeID) = 'CM') And ((DatePart('yyyy', [PermitEDate])) = [Forms]![Report Date Range]![input year] ) And ((tblSewer.TPlantID) Is Not Null) And ((tblSewer.PermitEDate) Is Not Null) And ((tblSewer.CIDate) Is Not Null) And ((tblSewer.PermitNo) Is Not Null)) GROUP BY tblTPlant.TPlant;

Mike,
Sorry...that I did not understand you earlier. I already have an SQL version. What I am trying to do is to hide the sql string behind a report -- the reason I went through the other procedure that is not working.

Regards
Bill

Mike,
Did you receive my last posted comment?

Bill

Bill,
OK, what is the aim of 'hiding the SQL behind the report'? Maybe there's another way to achieve it.
Otherwise I think that you should maybe cancel the question - I don't have any more ideas for your exact original question.
Regards,
Mike