Loop Query Trouble with form Parameters

[Mmccl]

Thanks to Mile-O-Phile, I have this great little form that will run a query that loops thru my table and gets every email address to paste in the BCC field of my email client (Outlook Express).

So far So good!

But I need the query to run based on parameters I chose on a form from unbound combo boxes. ie. send email (yes/no), State (list of states), etc.

I can get the query to open in datasheet view using the form as the basis for any combination of parameters, . .

. . . BUT when I go to run the code to open the email client and paste the email addresses in the BCC field, using the form as the basis for the parameters, the code crashes. As long as i open the query in design veiw and manually type in the criteria it works, but if a set the parameters for the query from the form, NO GO.

I have posted a sample of what works (and does not work) so far. Please anyone, have a go at it and post improvements back.

Can parameters be set thur a form in a loop query?

Thanks everyone the help. This forum has been a "God-Send"

Mike

 

[Mmccl]

The zip file did not post

 

[Jon K]

Private Sub Command2_Click()
   Dim cat As New ADOX.Catalog
   Dim cmd As ADODB.Command
   Dim rs As ADODB.Recordset
    
   Dim strEmail As String

   cat.ActiveConnection = CurrentProject.Connection
   Set cmd = cat.Procedures("qryEmailAddress").Command
   cmd.Parameters("[Forms]![dlgBulkEmailTest]![Tit]").Value = _
      [Forms]![dlgBulkEmailTest]![Tit]
   cmd.Parameters("[Forms]![dlgBulkEmailTest]![YesNo]").Value = _
      [Forms]![dlgBulkEmailTest]![YesNo]
   cmd.Parameters("[Forms]![dlgBulkEmailTest]![ST]").Value = _
      [Forms]![dlgBulkEmailTest]![ST]
      
   Set rs = cmd.Execute
   With rs
      Do While Not .EOF
         strEmail = strEmail & .Fields("PrimaryEmailAddress") & ";"
         .MoveNext
      Loop
      .Close
    End With

    strEmail = Left(strEmail, Len(strEmail) - 1)
    
    Set cmd = Nothing
On Error GoTo Err_Command2_Click

    DoCmd.SendObject _
    , _
    , _
    , _
    , _
    , _
    ("" & strEmail), _
    , _
    , _
    True
    
Exit_Command2_Click:
    Exit Sub
    
Err_Command2_Click:
    MsgBox Err.Description

    Resume Exit_Command2_Click
    
End Sub

Note  To use ADOX, you must reference the Microsoft ADO Ext. 2.1 for DDL and Security.

 

[Mmccl]

Problem Solved

Thanks Jon K,

That's it to a 'T' ! Perfect.