Procedure too big

[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

 

[mike]

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]

Whoa! I think I figured it out!!! My first stored
procedure! Thanks anyway!

 

[SteveS]

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

 

[mike]

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

.

 

[SteveS]

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.

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")

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

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, , ,

.