"Compile Error: Procedure too Large"

J

Jhart22889

Registered User.
Local time
Today, 14:45
Joined
Feb 22, 2013
Messages
13
I have some code that allows fields on a form to be show or hidden based on the start and end date chosen. The code is called through the after update of the end date. I've also tried creating a module with just the hidden/not hidden criteria and calling the module with the if then statement with no luck. Using this method I get an error that says it expects a variable or procedure not a module. Here's a portion of the code:
Code: 
Dim ststartdate As Date
Dim stenddate As Date
Dim intdatediff As Integer
Dim strdatelookup As String
Dim Xstringstartday As String
 
ststartdate = Me.StartDate.Value
stenddate = Me.ProjectedEndDate.Value
 
'MsgBox ststartdate
'MsgBox stenddate
 
'If ststartdate Is Null Then
'MsgBox "Please fill in a start date"
'End If
 
intdatediff = DateDiff("d", ststartdate, stenddate)
 
strdatelookup = Left(ststartdate, 2)
strenddatelookup = Left(stenddate, 2)
 
If strdatelookup = "1/" And strenddatelookup = "2/" And intdatediff > 31 And intdatediff <= 59 Then
[tblDirectLaborCost subform].[Form]![January].ColumnHidden = False
 [tblDirectLaborCost subform].[Form]![February].ColumnHidden = False
 [tblDirectLaborCost subform].[Form]![March].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![March] = 0
[tblDirectLaborCost subform].[Form]![April].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![April] = 0
[tblDirectLaborCost subform].[Form]![May].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![May] = 0
[tblDirectLaborCost subform].[Form]![June].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![June] = 0
[tblDirectLaborCost subform].[Form]![July].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![July] = 0
[tblDirectLaborCost subform].[Form]![August].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![August] = 0
[tblDirectLaborCost subform].[Form]![September].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![September] = 0
[tblDirectLaborCost subform].[Form]![October].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![October] = 0
 [tblDirectLaborCost subform].[Form]![November].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![ November] = 0
 [tblDirectLaborCost subform].[Form]![December].ColumnHidden = True
[tblDirectLaborCost subform].[Form]![December] = 0


Each month has 12 entries so the code above is repeated 12 times for January, 12 times for February, etc...

Any suggestions?
 
I think what i'm looking for is a way to cut down on the size (or amount) of the code. I'm not sure decompiling would help.
 
As a general rule, there are two ways to get this error:

  • The procedure has too many lines of code
The code that you have provided does not have enough lines for this to be considered as the case. Even 12 times the code that is you provided (One block for each month) should not be too much.
​
  • The procedure has at least one line of code that contains too many continuation lines.
The code that you have provided does not have any lines of code with continuations. It does have what could be a complex If Statement, assuming the remaining months are handled in ElseIf statements. While it is possible that there is a limit to the ultimate size of an If statement, I am not aware of the specifics. Changing the If Statement to a Select..Case Statement might be worth looking into as an option.
​
If there are additional ways, they will probably be similar in structure or intent.

-- Rookie
 
Jhart22889 said:
I think what i'm looking for is a way to cut down on the size (or amount) of the code.
Click to expand...

My largest A2007/A2010 application consists of:

95,461 LOCs (Lines Of Code), 334 Modules, 235 Stored Procedures (on the SQL BE DB), and counting...

I go through that process each time I promote up a new version of the application, at least. Sometimes I have to run through the steps several times on a busy development day.

Jhart22889 said:
I'm not sure decompiling would help.
Click to expand...

"Can lead a horse to water..."
 
MSAccessRookie, I agree that the specs aren't even close to the max limitations but I'm not familiar with Select cast statements but I think that could be an option, I would assume the datediff would be the case and count the number of days up to 365 but in monthly intervals like I have currently? but then the question is how will it know where to start as far as dates? Say we are not using a calendar date year but a fiscal year?
 
so with your solution mdlueck I can essentially keep the code I have the exact way that it is?
 
Jhart22889 said:
so with your solution mdlueck I can essentially keep the code I have the exact way that it is?
Click to expand...

Some times (frequently) Access needs the decompile process in order to accept valid code... Access has a "memory" of not liking XYZ and requires decompile to forget not liking XYZ.
 
The code appears to be of the copy/paste variety which just gets bigger with time.

At the moment it is setting two columns to visible and repeating the code to set the remainder to invisible. Try to swing your thinking around to setting all columns to invisible and only setting to visible those two columns required.

So, the first thing to do would be to hide all the columns with Hide in their Tag property:-
Code: 
Private Sub HideColumns()
    Dim ctl As Control
    
    For Each ctl In Me.[tblDirectLaborCost subform].Form
        If ctl.Tag = "Hide" Then
            ctl.ColumnHidden = True
        End If
    Next ctl

End Sub

Then conditionally unhide the columns required.

A sample database, in Access 2003, would be required for me to take this further.

Chris.
 
ChrisO, I've added this code (tried under the main form and the actual subform) and set the tag properties to Hide but the columns are still visible so I've hard wired it into the main form's on load event for the columns to work and began conditionally unhiding the columns which works.....the problem arises that if I go back and want to change my end and shorten the date difference the months that should be now hidden are still there. I think your suggestion of the custom function is best but I just cant seem to get it to work.

------Edit---Changed the placement of the custom function (duh) and it works but the problem with the months not changing back to hide if the end date is edited remains.
 
Last edited:
Hide all the columns each time you want to show some of the columns.

Chris.
 
I've tried all of this and the procedure is "still too big"....can anyone assist in the right direction for maybe a SELECT Case statement?
 
One thing that you have not tried is to upload the Database for us to review. Of course you should strip out any confidential data first. The Version of Access that is required will affect the number of people that can assist, but Access 2003 and Access 2007 seem to be available to a reasonable number of the members here
 
I fixed my problem, a combination of Case statements and the "debugging" code provided help alot thanks guys.
 
You must log in or register to reply here.

Similar threads

C
formulae in string
Replies
9
Views
241
ClaraBarton
C
A
Filter Records
Replies
1
Views
261
June7
June7
oxicottin
Solved Run-time error '3061'. Too few parameters. Expected 1
Replies
7
Views
1,307
theDBguy
theDBguy
C
Divide code to avoid "Procedure too large error"
Replies
18
Views
2,439
The_Doc_Man
The_Doc_Man
Sam Summers
Solved Filtering records on a form by month selected from a combobox on another form
Replies
2
Views
978
Sam Summers
Sam Summers

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom