Procedure too big

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Hi. I have a procedure that sends emails out to some
people here. The code is behind a form. On the form are a
clock and four controls called SendEast, SendCentral,
SendMountain, SendWest. The controls are defaulted to
6:00:00 AM, 7:00:00 AM, 8:00:00 AM, 9:00:00 AM,
respectively. When the clock strikes any of those numbers,
code runs that sends to a message to people in that time
zone.

It runs great if I only have code behind the form that
sends to people at 6AM, for example, but if I copy that
code and paste it in to send at 7,8,9 then Access says my
code is too large. The code is almost identical so i could
probably break it up into modules but I'm not sure how.

Here's how my code looks:

Private Sub Form_Timer()

Me.Clock = Format(Now, "h:nn:ss AM/PM")

If Me.Clock = Me!SENDEAST Then

Set Db = CurrentDb

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
"WHERE [SendTIME]=#6:00:00 AM#")

If Not rs.EOF Then
Do While Not rs.EOF

....there's a whole bunch of code in here at this point
that works fine so I won't repeat it here, but this is
what's causing the problem. It's the same exact code for
the procedure that executes at 7,8, and 9 so I was hoping
to store the code in a module and look it up instead of
having it sitting here...

DoCmd.SendObject acSendNoObject, , , rs!EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

End If

THEN I REPEAT BUT USE THE SENDCENTRAL CONTROL ON MY FORM
TO TRIGGER THE PROCEDURE AND FILTER MY RECORDSET BY
[SendTIME]=#7:00:00 AM#".

If Me.Clock = Me!SENDCENTRAL Then

Set Db = CurrentDb

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
"WHERE [SendTIME]=#7:00:00 AM#")

If Not rs.EOF Then
Do While Not rs.EOF

....there's a whole bunch of code in here that works fine
so I won't repeat it here...

DoCmd.SendObject acSendNoObject, , , rs!EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

End If



End Sub
 
I was thinking something like this might work but I really
don't know.

If Me.Clock = Me!SENDENDOMOUNTAIN Then

Call basSendDailyNumbers08AM

End If

....and put the code that applies a filter to the recordset
qryMain that pulls only people who have [SendTIME]
=#6:00:00 AM#", does the calculations, and sends the
emails to those people.

What do you think? Any suggestions would be great!
 
Mike,

If the code inside the do loop is the same for the 4 time zones, why not
use a Select Case...End Case or If...End If construct?

Here is the If..ElseIf..End If format: (This is AIR CODE!)


Private Sub Form_Timer()

Me.Clock = Format(Now, "h:nn:ss AM/PM")

Set Db = CurrentDb
If Me.Clock = Me!SENDEAST Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#6:00:00 AM#")

ElseIf Me.Clock = Me!SENDCENTRAL Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#7:00:00 AM#")

ElseIf Me.Clock = Me!SENDMOUNTAIN Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#8:00:00 AM#")

ElseIf Me.Clock = Me!SENDWEST Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#9:00:00 AM#")

Else
'not time yet - do cleanup and exit sub
Set Db = Nothing
Exit Sub
End If

'it's time - now check for records...

'need to check for *BOTH* rs.BOF and rs.EOF!!!!
If Not rs.BOF AND Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF

...there's a whole bunch of code in here at this point
that works fine so I won't repeat it here, but this is
what's causing the problem. It's the same exact code for
the procedure that executes at 7,8, and 9 so I was hoping
to store the code in a module and look it up instead of
having it sitting here...

DoCmd.SendObject acSendNoObject, , , rs!EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

rs.Close
Set rs = Nothing
Set Db = Nothing

End Sub


The Select Case would be set up the same as the If..ElseIf..End If format.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Hi. I have a procedure that sends emails out to some
people here. The code is behind a form. On the form are a
clock and four controls called SendEast, SendCentral,
SendMountain, SendWest. The controls are defaulted to
6:00:00 AM, 7:00:00 AM, 8:00:00 AM, 9:00:00 AM,
respectively. When the clock strikes any of those numbers,
code runs that sends to a message to people in that time
zone.

It runs great if I only have code behind the form that
sends to people at 6AM, for example, but if I copy that
code and paste it in to send at 7,8,9 then Access says my
code is too large. The code is almost identical so i could
probably break it up into modules but I'm not sure how.

Here's how my code looks:

Private Sub Form_Timer()

Me.Clock = Format(Now, "h:nn:ss AM/PM")

If Me.Clock = Me!SENDEAST Then

Set Db = CurrentDb

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
"WHERE [SendTIME]=#6:00:00 AM#")

If Not rs.EOF Then
Do While Not rs.EOF

...there's a whole bunch of code in here at this point
that works fine so I won't repeat it here, but this is
what's causing the problem. It's the same exact code for
the procedure that executes at 7,8, and 9 so I was hoping
to store the code in a module and look it up instead of
having it sitting here...

DoCmd.SendObject acSendNoObject, , , rs!EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

End If

THEN I REPEAT BUT USE THE SENDCENTRAL CONTROL ON MY FORM
TO TRIGGER THE PROCEDURE AND FILTER MY RECORDSET BY
[SendTIME]=#7:00:00 AM#".

If Me.Clock = Me!SENDCENTRAL Then

Set Db = CurrentDb

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
"WHERE [SendTIME]=#7:00:00 AM#")

If Not rs.EOF Then
Do While Not rs.EOF

...there's a whole bunch of code in here that works fine
so I won't repeat it here...

DoCmd.SendObject acSendNoObject, , , rs!EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

End If



End Sub
Click to expand...
 
Steve, Thanks for the suggestion. That's actually how I
had it but I hit an error when I took the code at 6 AM and
pasteded it for 7,8 and 9. It said the code was too large.
So, I put the repetitive part of the code in a Module and
wrote some code behind my form as follows:

FYI: Clock, SENDEAST, SENDCENTRAL etc. are time controls
on my form, and the form is called Dashboard.

If Me.Clock = Me!SENDEAST Then
Call basSendDailyNumbers.SendDailyNumbers
End If

If Me.Clock = Me!SENDCENTRAL Then
Call basSendDailyNumbers.SendDailyNumbers
End If

If Me.Clock = Me!SENDMOUNTAIN Then
Call basSendDailyNumbers.SendDailyNumbers
End If

If Me.Clock = Me!SENDWEST Then
Call basSendDailyNumbers.SendDailyNumbers
End If

The module looks like this:

Public Sub SendDailyNumbers()
Set Db = CurrentDb
Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [tblMain]" & _
"WHERE [SENDTIME]=Forms!Dashboard!Clock")

If Not rs.EOF Then
Do While Not rs.EOF...etc

This looks up people in my table, tblMain, and sends a
message to them if their [SENDTIME] is the same as the
clock on the form called Dashboard. It all works fine
except that I part of the code in the module that reads
[SENDTIME]=Forms!Dashboard!Clock
is causing an error. If I replace Forms!Dashboard!Clock
with #6:00:00 AM# for example, it works fine, so I guess I
need to figure out how to convert the refrence to Forms!
Dashboard!Clock to time format. I'll post that
question...Thanks.
-----Original Message-----
Mike,

If the code inside the do loop is the same for the 4 time zones, why not
use a Select Case...End Case or If...End If construct?

Here is the If..ElseIf..End If format: (This is AIR CODE!)


Private Sub Form_Timer()

Me.Clock = Format(Now, "h:nn:ss AM/PM")

Set Db = CurrentDb
If Me.Clock = Me!SENDEAST Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#6:00:00 AM#")

ElseIf Me.Clock = Me!SENDCENTRAL Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#7:00:00 AM#")

ElseIf Me.Clock = Me!SENDMOUNTAIN Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#8:00:00 AM#")

ElseIf Me.Clock = Me!SENDWEST Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#9:00:00 AM#")

Else
'not time yet - do cleanup and exit sub
Set Db = Nothing
Exit Sub
End If

'it's time - now check for records...

'need to check for *BOTH* rs.BOF and rs.EOF!!!!
If Not rs.BOF AND Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF

...there's a whole bunch of code in here at this point
that works fine so I won't repeat it here, but this is
what's causing the problem. It's the same exact code for
the procedure that executes at 7,8, and 9 so I was hoping
to store the code in a module and look it up instead of
having it sitting here...

DoCmd.SendObject acSendNoObject, , , rs! EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

rs.Close
Set rs = Nothing
Set Db = Nothing

End Sub


The Select Case would be set up the same as the If..ElseIf..End If format.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Hi. I have a procedure that sends emails out to some
people here. The code is behind a form. On the form are a
clock and four controls called SendEast, SendCentral,
SendMountain, SendWest. The controls are defaulted to
6:00:00 AM, 7:00:00 AM, 8:00:00 AM, 9:00:00 AM,
respectively. When the clock strikes any of those numbers,
code runs that sends to a message to people in that time
zone.

It runs great if I only have code behind the form that
sends to people at 6AM, for example, but if I copy that
code and paste it in to send at 7,8,9 then Access says my
code is too large. The code is almost identical so i could
probably break it up into modules but I'm not sure how.

Here's how my code looks:

Private Sub Form_Timer()

Me.Clock = Format(Now, "h:nn:ss AM/PM")

If Me.Clock = Me!SENDEAST Then

Set Db = CurrentDb

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
"WHERE [SendTIME]=#6:00:00 AM#")

If Not rs.EOF Then
Do While Not rs.EOF

...there's a whole bunch of code in here at this point
that works fine so I won't repeat it here, but this is
what's causing the problem. It's the same exact code for
the procedure that executes at 7,8, and 9 so I was hoping
to store the code in a module and look it up instead of
having it sitting here...

DoCmd.SendObject acSendNoObject, , , rs! EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

End If

THEN I REPEAT BUT USE THE SENDCENTRAL CONTROL ON MY FORM
TO TRIGGER THE PROCEDURE AND FILTER MY RECORDSET BY
[SendTIME]=#7:00:00 AM#".

If Me.Clock = Me!SENDCENTRAL Then

Set Db = CurrentDb

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
"WHERE [SendTIME]=#7:00:00 AM#")

If Not rs.EOF Then
Do While Not rs.EOF

...there's a whole bunch of code in here that works fine
so I won't repeat it here...

DoCmd.SendObject acSendNoObject, , , rs! EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

End If



End Sub
Click to expand...
.
Click to expand...
 
Mike,
had it but I hit an error when I took the code at 6 AM and
pasteded it for 7,8 and 9. It said the code was too large.
Click to expand...

I didn't see where you closed the recordsets anywhere; that might be
why you received the "Procedure too large" error.


Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [tblMain]" & _
"WHERE [SENDTIME]=Forms!Dashboard!Clock")
Click to expand...

The problem here is that when Access tries to resolve the SQL Select
statement, it doesn't know what Forms!Dashboard!Clock is or that it
refers to a control because it is within the quotes.



BTW, in [tblMain], is [SENDTIME] a text field?



The solution is to set a variable to the SQL statement, then use the
variable in the OpenRecordset command:

Dim strSQL as String

'this is one line
strSQL = "SELECT * FROM [tblMain] WHERE [SENDTIME] = " & Me.SENDEAST

Set rs = Db.OpenRecordset(strSQL)


Try putting the following code behind your form. Put in a breakpoint and
step thru the code watching the variables in the Locals pane.
Note: the strSQL = "Select ...." should be on one line

'-----------------------------------------------------
Private Sub Form_Timer()
Dim strSQL As String
Dim strWhere As String
Dim Db As database

Set Db = CurrentDb

Me.CLOCK = Format(Now, "h:nn:ss AM/PM")
If Me.CLOCK = Me.SENDEAST Then
strWhere = Me.SENDEAST

ElseIf Me.CLOCK = Me.SENDCENTRAL Then
strWhere = Me.SENDCENTRAL
ElseIf Me.CLOCK = Me.SENDMOUNTAIN Then
strWhere = Me.SENDMOUNTAIN
ElseIf Me.CLOCK = Me.SENDWEST Then
strWhere = Me.SENDWEST
Else
'not time yet - do cleanup and exit sub
Set Db = Nothing
Exit Sub
End If

'Its Time - Now open the recordset
strSQL = "SELECT * FROM [tblMain] WHERE [SENDTIME] = " & strWhere

'*******************************
' these are for debugging
' Comment out the next 3 lines to run the rest of the sub
MsgBox "strSQL = " & strSQL
Set Db = Nothing
Exit Sub
'*******************************


Set rs = Db.OpenRecordset(strSQL)

' now check for records in rs ...

'need to check for *BOTH* rs.BOF and rs.EOF!!!!
If Not rs.BOF AND Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF

...there's a whole bunch of code in here at this point
that works fine so I won't repeat it here, but this is
what's causing the problem. It's the same exact code for
the procedure that executes at 7,8, and 9 so I was hoping
to store the code in a module and look it up instead of
having it sitting here...

DoCmd.SendObject acSendNoObject, , , rs!EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

rs.Close
Set rs = Nothing
Set Db = Nothing

End Sub
'-----------------------------------------------------


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve, Thanks for the suggestion. That's actually how I
had it but I hit an error when I took the code at 6 AM and
pasteded it for 7,8 and 9. It said the code was too large.
So, I put the repetitive part of the code in a Module and
wrote some code behind my form as follows:

FYI: Clock, SENDEAST, SENDCENTRAL etc. are time controls
on my form, and the form is called Dashboard.

If Me.Clock = Me!SENDEAST Then
Call basSendDailyNumbers.SendDailyNumbers
End If

If Me.Clock = Me!SENDCENTRAL Then
Call basSendDailyNumbers.SendDailyNumbers
End If

If Me.Clock = Me!SENDMOUNTAIN Then
Call basSendDailyNumbers.SendDailyNumbers
End If

If Me.Clock = Me!SENDWEST Then
Call basSendDailyNumbers.SendDailyNumbers
End If

The module looks like this:

Public Sub SendDailyNumbers()
Set Db = CurrentDb
Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [tblMain]" & _
"WHERE [SENDTIME]=Forms!Dashboard!Clock")

If Not rs.EOF Then
Do While Not rs.EOF...etc

This looks up people in my table, tblMain, and sends a
message to them if their [SENDTIME] is the same as the
clock on the form called Dashboard. It all works fine
except that I part of the code in the module that reads
[SENDTIME]=Forms!Dashboard!Clock
is causing an error. If I replace Forms!Dashboard!Clock
with #6:00:00 AM# for example, it works fine, so I guess I
need to figure out how to convert the refrence to Forms!
Dashboard!Clock to time format. I'll post that
question...Thanks.

-----Original Message-----
Mike,

If the code inside the do loop is the same for the 4 time
Click to expand...

zones, why not
use a Select Case...End Case or If...End If construct?

Here is the If..ElseIf..End If format: (This is AIR CODE!)


Private Sub Form_Timer()

Me.Clock = Format(Now, "h:nn:ss AM/PM")

Set Db = CurrentDb
If Me.Clock = Me!SENDEAST Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#6:00:00 AM#")

ElseIf Me.Clock = Me!SENDCENTRAL Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#7:00:00 AM#")

ElseIf Me.Clock = Me!SENDMOUNTAIN Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#8:00:00 AM#")

ElseIf Me.Clock = Me!SENDWEST Then

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
" WHERE [SendTIME]=#9:00:00 AM#")

Else
'not time yet - do cleanup and exit sub
Set Db = Nothing
Exit Sub
End If

'it's time - now check for records...

'need to check for *BOTH* rs.BOF and rs.EOF!!!!
If Not rs.BOF AND Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF

...there's a whole bunch of code in here at this
point

that works fine so I won't repeat it here, but
Click to expand...

this is
what's causing the problem. It's the same exact
Click to expand...

code for
the procedure that executes at 7,8, and 9 so I
Click to expand...

was hoping
to store the code in a module and look it up
Click to expand...

instead of
having it sitting here...

DoCmd.SendObject acSendNoObject, , , rs!
Click to expand...

EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

rs.Close
Set rs = Nothing
Set Db = Nothing

End Sub


The Select Case would be set up the same as the
Click to expand...

If..ElseIf..End If format.
HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Hi. I have a procedure that sends emails out to some
people here. The code is behind a form. On the form are
Click to expand...
a
clock and four controls called SendEast, SendCentral,
SendMountain, SendWest. The controls are defaulted to
6:00:00 AM, 7:00:00 AM, 8:00:00 AM, 9:00:00 AM,
respectively. When the clock strikes any of those
Click to expand...
numbers,
code runs that sends to a message to people in that
Click to expand...
time
zone.

It runs great if I only have code behind the form that
sends to people at 6AM, for example, but if I copy that
code and paste it in to send at 7,8,9 then Access says
Click to expand...
my
code is too large. The code is almost identical so i
Click to expand...
could
probably break it up into modules but I'm not sure how.

Here's how my code looks:

Private Sub Form_Timer()

Me.Clock = Format(Now, "h:nn:ss AM/PM")

If Me.Clock = Me!SENDEAST Then

Set Db = CurrentDb

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
"WHERE [SendTIME]=#6:00:00 AM#")

If Not rs.EOF Then
Do While Not rs.EOF

...there's a whole bunch of code in here at this point
that works fine so I won't repeat it here, but this is
what's causing the problem. It's the same exact code
Click to expand...
for
the procedure that executes at 7,8, and 9 so I was
Click to expand...
hoping
to store the code in a module and look it up instead of
having it sitting here...

DoCmd.SendObject acSendNoObject, , , rs!
Click to expand...
Click to expand...

EmailAddress, , ,
Subject, Message, False

rs.MoveNext

Loop
End If

End If

THEN I REPEAT BUT USE THE SENDCENTRAL CONTROL ON MY
Click to expand...
FORM
TO TRIGGER THE PROCEDURE AND FILTER MY RECORDSET BY
[SendTIME]=#7:00:00 AM#".

If Me.Clock = Me!SENDCENTRAL Then

Set Db = CurrentDb

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [qryMain]" & _
"WHERE [SendTIME]=#7:00:00 AM#")

If Not rs.EOF Then
Do While Not rs.EOF

...there's a whole bunch of code in here that works
Click to expand...
fine
so I won't repeat it here...

DoCmd.SendObject acSendNoObject, , , rs!
Click to expand...
Click to expand...

EmailAddress, , ,
.
Click to expand...
Click to expand...
 
Back
Top