Code not running

[front.desk]

Hi, I have been searching through this and many forums to get an idea of what I need. I had literally never seen Access before this task got handed to me last week!

I need to generate automatic email reminders for due dates. From what I can figure it is easiest if I make it all work on a button/click feature rather than a timer, which is fine for now. My main issue right now is that I found a code in a forum that seems like it should work for what I am trying to do. I have a query that separates out the Due Dates that are approaching. The problem now is that the code I have in a module says it has "Invalid Outside Procedure" (After I close the error message it highlights the word SET)

I got the code from a post on this forum: 775884#post775884


Please help!

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CAPADb()
Set rsEmail = MyDb.OpenRecordset("qryDueDate", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(11)) = False Then
sToName = .Fields(11)
sSubject = "Open Action Item: " & .Fields(6)
sMessageBody = "Email Body Text " & vbCrLf & _
"Field 6: " & .Fields(6) & vbCrLf & _
"Field 7: " & .Fields(7) & vbCrLf & _
"Field 24: " & .Fields(24)

docmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing

 

[spikepl]

What is CAPADb()?

 

[front.desk]

That should be CurrentDb()
I pasted an old copy of my manipulations, either way it still comes up with the same error message when I fun it.

 

[spikepl]

does your query qryDueDate run fine by itself?

 

[front.desk]

Yes it does

 

[spikepl]

Hmm have you tried to compile all your code?

 

[front.desk]

What does that mean? I seriously never saw any of this stuff before a week ago, I'm flying totally blind

 

[spikepl]

In the window where you edit the code, at the top in menu Debug, select Compile

Compilation checks that variables are not declared mutiple times and whether Access can comrehend the code and things like that.

 

[front.desk]

Yes, that is how I got the error message "Invalid Outside Procedure"

 

[spikepl]

Ah I think I get it.. You have code lying around that is not wrapped in either

Sub
End Sub

or Function

End Function.

ALL the stuff in the module must be either in a Sub or a FUnction (apart from some declarations at the top).

So when you did copy/pastying from soemwhere you must have missed the Sub or Function bit.

Private Sub Mysubroutine
'all the code goes here
End Sub

or
Private Function MyFunction
'all code here
End Function

 

[front.desk]

I tried both of these at the end of my code (with the corrected CurrentDB)
I still get the same error message...was that the correct place to put them? Is there supossed to be something before the code?

 

[spikepl]

[COLOR=Red]Private Sub PageFooterSection_Format[/COLOR](Cancel As Integer, FormatCount As Integer)
Dim ctr As Control
      
For Each ctr In Me.Controls
       Debug.Print ctr.Name & " tag=" & ctr.Tag
       If ctr.Tag = "Totals" Then
       ctr.Visible = ([Page] = [Pages])
    End If
Next
If [Page] = [Pages] Then
    Me.Label45.Visible = True
Else
    Me.Label45.Visible = False
End If
[COLOR=Red]End Sub[/COLOR]

above is an example of a procedure.

Code CANNOT exist outside of a procedure.

 

[front.desk]

Thanksfor that. Now it is telling me the line: If IsNull(.Fields(11)) = False Then

Is not found in this collection

 

[spikepl]

it means what it says - there is no field with index 11. That means you table has 11 fields and not 12. (The index is 0-based)

 

[front.desk]

There are actually 25 fields, including the '0' and 11 is the correct value for the column I need.

Are the fields running from the database or the query?

 

[spikepl]

My bad: from the recordset you assigned to rsEmail. So apparently the query does not have 12 feilds.

 

[front.desk]

Thanks for the help, even though I'm kind of slow...talking it out made me figure out what I was missing. My boss just shouted yahoo! from the back of the office when he got his email from it!!!

 

[spikepl]

:d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d :d