Scroll through and send notice to routing recipients

You get this error message when the "source" cannot be identified. It expects a table name, a query name, or a full SQL sentence, but the message assumes only the last case, which is confusing.

Anyway, the most obvious explanation is that you don't have a query named qryRoutingNotice, the most likely is that your query uses parameters (and that includes any reference to a control on a form). In that case, you need to provide the values to a command object:

    <command>.Parameters(0) = Forms!Form1!Text1

For a full example, please visit:

Opening a Recordset Object by Using a Saved Query
http://msdn.microsoft.com/en-us/library/aa189623(office.10).aspx

Cheers!
(°v°)

Here is a procedure that will send emails to all records in a query that have an email address (it uses a DAO recordset, so you would need to modify it somewhat to use a ADO recordset instead):

Public Sub EMailAllContacts()
'Created by Helen Feddema 31-Oct-2009
'Last modified by Helen Feddema 31-Oct-2009
 
On Error GoTo ErrorHandler
 
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim appOutlook As Outlook.Application
   Dim msg As Outlook.MailItem
   Dim strEmail As String
   
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("qryContacts")
   Set appOutlook = GetObject(, "Outlook.Application")
   
   Do While Not rst.EOF
      strEmail = Nz(rst![EmailName])
      If strEmail <> "" Then
         'Create email
         Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEmail
         msg.Subject = "Subject"
         msg.Body = "Message"
         
         'Comment out next line and uncomment Send line
         'to send automatically
         msg.Display
         'msg.Send
      End If
      rst.MoveNext
   Loop
   
ErrorHandlerExit:
   rst.Close
   Set rst = Nothing
   Set appOutlook = Nothing
   Exit Sub
 
ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in EMailAllContacts procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
 
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:

I use GetObject, with a fallback to CreateObject in the error handler, so as to use the current instance of Outlook, if there is one, and only create a new instance if Outlook is not running.

wow, great feedback..  thanks all.  I'll check out some of these ideas Monday morning and post back.  

Sorry it took so long to respond.  Was here most of the weekend working on a separate project.  Anyway.. , Yes  myRecordSet.Open "[qryRoutingNotice]" is the highlighted statement on debug and there is a query called qryRoutingNotice.   The query does call out a parameter: ECNNo = me.ECNNo.
I only want to email the routing list if the routing record matches the ECN number of the form it is called from.  I read Harfang's post and link about the command object but I'm confused on where and how to use it.  That said, I'm assuming this is the underlying issue?  

Boag, "I also notice that you are not closing your objects and setting them to Nothing"  Is that just good practice or is it necessary to set them to nothing?  Just wondering..  I'm using this same piece of code in other areas, without specific parameters, and it appears to work ok.  

In fact, you can't use directly "ECNNo = Me.ECNNo" in a query. Does your query run from the interface? If so, show us what you really use.

Anyway, when Jet encounters something like "ECNNo = <something>" and it can't resolve <something>, it will ask the current environment. Access when run from the interface, only VB when run from code. If it stays unresolved, it becomes an implicit parameter, which will be "missing".

If the query is reasonably simple, and if the code is run from the form where Me.ECNNo is defined, you can use a string variable to create the query:

    strSQL = "SELECT * FROM Table1 WHERE ECNNo=" & Me.ECNNO

And feet that to myRecordSet.Open. That way, it's not a parameter, but a constant value.

If the query is best stored as a saved query, then use the method from the link to supply the values of the parameters.

(°v°)

Hi.  Form is based on a query and an on-click event calls out the recordset query.  Yes, the query is simple and is attached.  What you're saying makes sense in theory but I'm not sure how to apply it.  Do I add the StrSQL statement to the SQL design of my query?   I'm not sure what you mean by "feed that to myrecordset.open.."  Sorry Harfang,  I'm a self taught programmer (ish) and I think I'm getting there but I have a long way to go...   thanks

Hi Harfang,  Had some issues with Outlook but I figured it out.  Thanks for the help.  One question though:  Why can't I call out my field by name as a recipient.  it works as is but I used:
 .Recipients.Add (myRecordSet.Fields(2).Value)
shouldnt I be able to use :
 .Recipients.Add ([Name])

see the attached code...

Well, [Name] is a field of a recordset, you need to tell VB where to find it. However, you can use the name [Name] to refer to it:

     .Recipients.Add myRecordSet.Fields(2).Value
     .Recipients.Add myRecordSet.Fields("Name").Value
     .Recipients.Add myRecordSet.Fields("Name")

These work, but are confusing...

     .Recipients.Add myRecordSet!Name
     .Recipients.Add myRecordSet.[Name]

... because this means something different

     .Recipients.Add myRecordSet.Name

It's the name of the recordset, not the field [Name]. "Name" is a bad name because everything has names (fields, tables, recordsets, forms, modules...)

(°v°)