Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VB Procedure too large- error message 2

Status
Not open for further replies.
osi2301

osi2301

Programmer
Jul 3, 2004
75
AU
I have a large procedure with a number "if" statements. I get an error message that the "Procedure is too large". Is there anyway around this or do I have to reduce the size of the code?
 
Sort by date Sort by votes
You will have to reduce the size of the code.

How big is this procedure anyway?? I've never managed to write a procedure that produces this error - in fact I didn't even know there was a limit.

Ed Metcalfe.

Please do not feed the trolls.....
 
Upvote 0 Downvote
What I have is a rostering database. When I select the employee to assign a shift, the procedure looks at the annual leave requests and displays "A/L" into that specific days roster. The code I have used is:

If [LeaveEnd] > ([BeginningDate] + 13) Then
If [LeaveBegin] = [BeginningDate] Then
MsgBox [Title] & " " & [Crewmember] & " has " & [LeaveType] & " on " & [LeaveBegin] & " to " & [LeaveEnd], vbInformation, "RACS"
[Pattern14] = [leavesymbol]
[Date14] = ([BeginningDate] + 13)
[Pattern13] = [leavesymbol]
[Date13] = ([BeginningDate] + 12)
[Pattern12] = [leavesymbol]
[Date12] = ([BeginningDate] + 11)
[Pattern11] = [leavesymbol]
[Date11] = ([BeginningDate] + 10)
[Pattern10] = [leavesymbol]
[Date10] = ([BeginningDate] + 9)
[Pattern9] = [leavesymbol]
[Date9] = ([BeginningDate] + 8)
[Pattern8] = [leavesymbol]
[Date8] = ([BeginningDate] + 7)
[Pattern7] = [leavesymbol]
[Date7] = ([BeginningDate] + 6)
[Pattern6] = [leavesymbol]
[Date6] = ([BeginningDate] + 5)
[Pattern5] = [leavesymbol]
[Date5] = ([BeginningDate] + 4)
[Pattern4] = [leavesymbol]
[Date4] = ([BeginningDate] + 3)
[Pattern3] = [leavesymbol]
[Date3] = ([BeginningDate] + 2)
[Pattern2] = [leavesymbol]
[Date2] = ([BeginningDate] + 1)
[Pattern1] = [leavesymbol]
[Date1] = [BeginningDate]
End If
End If

This is just a small example. The code continues for every possible combination. If you can think of a way to reduce the size, please let me know?
 
Upvote 0 Downvote
How are ya osi2301 . . .

Totally agree with [blue]osi2301[/blue], thats a new error on me!

From the looks of it [blue]you need some type of looping structure![/blue] . . . However, it does appear your exceeding [blue]Access Specifications[/blue] for field/control count in tables and/or forms. I'm wondering how you got past construction of these items . . .

Calvin.gif
See Ya! . . . . . .
 
Upvote 0 Downvote
There must be some way to create a loop for this. Also look at Select Case as that may be more efficient, especially as you can group multiple cases

ie

Select Case myRef
Case 1, 3, 5, 7, 9
val="odd Number"
case 2, 4, 6, 8
Val ="Even Number"
end select
 
Upvote 0 Downvote
There must be some way to create a loop for this
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = Me![BeginningDate] Then
MsgBox [Title] & " " & [Crewmember] & " has " & [LeaveType] & " on " & [LeaveBegin] & " to " & [LeaveEnd], vbInformation, "RACS"
For i = 1 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
That loop worked well but by the time all possible combinations of dates are entered, I still get the "Procedure too large" error message. Even tried spliting the code betqween the 'After_Update' and the 'On_Change' and I still got the error.
 
Upvote 0 Downvote
osi2301,

Can you post the entire procedure on here so we can see (the modified version using PHV's solution)?

I've seen some hefty procedures during my time, but I've *never* come across this error message before.

Ed Metcalfe.

Please do not feed the trolls.....
 
Upvote 0 Downvote
I have been able to get the procedure to work.

At first I used the code suggested by PHV and continued to replicate the code for each day combination, including repeating the msgbox for each day. I got the same error even when I split the code between AFTER UPDATE and ON CHANGE.

I went back and rebuilt the code so that only the first line included the msgbox and all other days were just IF statements for that day combination.

However, even that wasn't enough and the code for the last few days generated the error message again. I was able to move those days to the ON CHANGE and it now works.

If you would like to see the code, I can send it, however it is very large.

 
Upvote 0 Downvote
osi,

If I can see the final code then I may be able to suggest further tweaks to reduce the length...

Ed Metcalfe.

Please do not feed the trolls.....
 
Upvote 0 Downvote
Ok, here goes

If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] >= Me![BeginningDate] Then
MsgBox [Title] & " " & [crewmember] & " has " & [LeaveType] & " on " & [LeaveBegin] & " to " & [LeaveEnd], vbInformation, "RACS"
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 7) Then
For i = 8 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 8) Then
For i = 9 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 9) Then
For i = 10 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 10) Then
For i = 11 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 11) Then
MsgBox [Title] & " " & [crewmember] & " has " & [LeaveType] & " on " & [LeaveBegin] & " to " & [LeaveEnd], vbInformation, "RACS"
For i = 12 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 12) Then
For i = 13 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 13) Then
For i = 14 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If


If Me![LeaveEnd] <= (Me![BeginningDate] + 13) And Me![LeaveBegin] < Me![BeginningDate] Then
MsgBox [Title] & " " & [crewmember] & " has " & [LeaveType] & " on " & [LeaveBegin] & " to " & [LeaveEnd], vbInformation, "RACS"
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 6) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 7
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 5) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 6
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 4) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 5
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 3) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 4
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 2) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 3
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 1) And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 2
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = Me![BeginningDate] And Me![LeaveBegin] < Me![BeginningDate] Then
For i = 1 To 1
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] <= (Me![BeginningDate] + 13) And Me![LeaveBegin] >= Me![BeginningDate] Then
MsgBox [Title] & " " & [crewmember] & " has " & [LeaveType] & " on " & [LeaveBegin] & " to " & [LeaveEnd], vbInformation, "RACS"
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 7) Then
For i = 8 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 8) Then
For i = 9 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 9) Then
For i = 10 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 10) Then
For i = 11 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 11) Then
For i = 12 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 12) Then
For i = 13 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 13) And Me![LeaveBegin] = (Me![BeginningDate] + 13) Then
For i = 14 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If



If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 7) Then
For i = 8 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 8) Then
For i = 9 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 9) Then
For i = 10 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 10) Then
For i = 11 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 11) Then
For i = 12 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 12) And Me![LeaveBegin] = (Me![BeginningDate] + 12) Then
For i = 13 To 13
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If




If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 7) Then
For i = 8 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 8) Then
For i = 9 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 9) Then
For i = 10 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 10) Then
For i = 11 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 11) And Me![LeaveBegin] = (Me![BeginningDate] + 11) Then
For i = 12 To 12
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If



If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 7) Then
For i = 8 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 8) Then
For i = 9 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 9) Then
For i = 10 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 10) And Me![LeaveBegin] = (Me![BeginningDate] + 10) Then
For i = 11 To 11
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If




If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 7) Then
For i = 8 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 8) Then
For i = 9 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 9) And Me![LeaveBegin] = (Me![BeginningDate] + 9) Then
For i = 10 To 10
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If





If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = (Me![BeginningDate] + 7) Then
For i = 8 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 8) And Me![LeaveBegin] = (Me![BeginningDate] + 8) Then
For i = 9 To 9
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If



If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 7) And Me![LeaveBegin] = (Me![BeginningDate] + 7) Then
For i = 8 To 8
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If




If Me![LeaveEnd] = (Me![BeginningDate] + 6) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 7
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 6) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 7
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 6) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 7
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 6) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 7
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 6) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 7
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 6) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 7
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 6) And Me![LeaveBegin] = (Me![BeginningDate] + 6) Then
For i = 7 To 7
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If




If Me![LeaveEnd] = (Me![BeginningDate] + 5) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 6
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 5) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 6
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 5) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 6
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 5) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 6
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 5) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 6
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 5) And Me![LeaveBegin] = (Me![BeginningDate] + 5) Then
For i = 6 To 6
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If




If Me![LeaveEnd] = (Me![BeginningDate] + 4) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 5
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 4) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 5
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 4) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 5
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 4) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 5
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 4) And Me![LeaveBegin] = (Me![BeginningDate] + 4) Then
For i = 5 To 5
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If




If Me![LeaveEnd] = (Me![BeginningDate] + 3) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 4
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 3) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 4
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 3) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 4
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 3) And Me![LeaveBegin] = (Me![BeginningDate] + 3) Then
For i = 4 To 4
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If



If Me![LeaveEnd] = (Me![BeginningDate] + 2) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 3
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 2) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 3
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 2) And Me![LeaveBegin] = (Me![BeginningDate] + 2) Then
For i = 3 To 3
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If

If Me![LeaveEnd] = (Me![BeginningDate] + 1) And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 2
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = (Me![BeginningDate] + 1) And Me![LeaveBegin] = (Me![BeginningDate] + 1) Then
For i = 2 To 2
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
If Me![LeaveEnd] = Me![BeginningDate] And Me![LeaveBegin] = Me![BeginningDate] Then
For i = 1 To 1
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
 
Upvote 0 Downvote
A starting point to shorten the 1st bloc:
If Me![LeaveEnd] > (Me![BeginningDate] + 13) And Me![LeaveBegin] >= Me![BeginningDate] Then
MsgBox [Title] & " " & [crewmember] & " has " & [LeaveType] & " on " & [LeaveBegin] & " to " & [LeaveEnd], vbInformation, "RACS"
For i = Me![LeaveBegin] - Me![BeginningDate]+ 1 To 14
Me("Pattern" & i) = Me![leavesymbol]
Me("Date" & i) = Me![BeginningDate] + i - 1
Next
End If
...
Same kind of shortening is possible for the "Me![LeaveEnd] <= (Me![BeginningDate] + 13)" bloc.

The others probably too but I'm lazzy today ;-)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MacroAlan
  • Locked
  • Question
Navigation Bar is too short to read
Replies
5
Views
568
dhookom
dhookom
TheresAlwaysAWay
  • Locked
  • Question
&quot;There isn't enough memory to perform this operation...&quot; error after upgrading from Access
Replies
2
Views
407
Axworthy
Axworthy
DB_Bowes
  • Locked
  • Question
Selection from Listbox to GoTo Record in Bound form Gives Error 2105
Replies
1
Views
311
DB_Bowes
DB_Bowes
iamchemist
  • Locked
  • Question
&quot;Access Application Not Responding&quot; Message
Replies
1
Views
206
Andrzejek
Andrzejek
bosk0
  • Locked
  • Question
Getting a syntax error in INSERT INTO statement 1
Replies
5
Views
332
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top