Pulling in Query results to then run with SendoBject?

[roxannep]

Here is my current code to run a SendObject routine that sends individual emails to people who have placed and paid for orders on Ebay with our company. <br><br>Originally, when I designed this, I put the actual query string in and put in exact dates as the criteria in the SQL string, but now, I want it to reference a form that allows input from the user (they choose the date range), then have that be the criteria for the query records return. How can I do that? <br><br>Current code:<br><br>Private Sub Command17_Click()<br>Dim db As Database, rst As Recordset, SQL As String<br>Dim a As Integer, EmailInfo As String<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>Set db = CurrentDb<br>SQL = &quot;SELECT Contacts.EmailName, Transactions.ShipDate, Contacts.Name, Contacts.Address, Contacts.City, Contacts.StateorProvince, Contacts.PostalCode, Transactions.Item, Transactions.Title, Transactions.SerialNo FROM Contacts INNER JOIN Transactions ON Contacts.ContactID = Transactions.ContactID WHERE (((Transactions.ShipDate) Between #1/1/2000# And #1/2/2000#));&quot;<br>Set rst = db.OpenRecordset(SQL)<br>rst.MoveLast<br>rst.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>For a = 1 To rst.RecordCount<br>' Infomation could also change here<br>&nbsp;&nbsp;&nbsp;&nbsp;EmailInfo = &quot;Shipping Confirmation for &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;EmailInfo = EmailInfo & rst![Title] & &quot;, Ebay Confirmation No. &quot; & rst![Item] & &quot;.&nbsp;&nbsp;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;EmailInfo = EmailInfo & &quot;The product you ordered through E-Bay was shipped on &quot; & rst![ShipDate]<br>&nbsp;&nbsp;&nbsp;&nbsp;EmailInfo = EmailInfo & &quot; to &quot; & rst![Name] & &quot; at &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;EmailInfo = EmailInfo & rst![Address] & &quot;, &quot; & rst![City] & &quot;, &quot; & rst![StateorProvince] & &quot; &quot; & rst![PostalCode]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>DoCmd.SendObject acSendNoObject, &quot; &quot;, acFormatTXT, rst!EmailName, , , &quot;Shipping Confirmation&quot;, EmailInfo, False, &quot; &quot;<br><br>rst.MoveNext<br>Next<br>End Sub

 

[rochelle]

Assume your form is called A and you have two textboxes on it called [StartDate] and [EndDate].&nbsp;&nbsp;Change your SQL as follows:<br><br>SQL = &quot;SELECT Contacts.EmailName, Transactions.ShipDate, Contacts.Name, Contacts.Address, Contacts.City, Contacts.StateorProvince, Contacts.PostalCode, Transactions.Item, Transactions.Title, Transactions.SerialNo FROM Contacts INNER JOIN Transactions ON Contacts.ContactID = Transactions.ContactID WHERE (((Transactions.ShipDate) Between &quot; & Forms!A![StartDate] & &quot; And &quot; & Forms!A![EndDate] & &quot);&quot;<br>

 

[roxannep]

Using your example, I have substituted my form and field names as shown below, however, it keeps telling me that it cannot find my form. This is the same SQL I use for another report, but I have the Ship Date Range form pull up as an Event procedure.<br><br>I have checked and rechecked the form label, and the only other option I can think of is that I have not opened this form or pointed to it in the code earlier? <br><br>&nbsp;If so, how and where would I put this in? Here's the current SQL now:<br><br>

 

[roxannep]

Woops!&nbsp;&nbsp;Here's the code now:<br><br>&quot;SELECT Contacts.ContactID, Contacts.EmailName, Transactions.TransactionID, Transactions.Item, Transactions.Title, Transactions.ShipDate, Contacts.Name, Contacts.Address, Contacts.City, Contacts.StateorProvince, Contacts.PostalCode FROM Contacts INNER JOIN Transactions ON Contacts.ContactID = Transactions.ContactID WHERE (((Transactions.ShipDate) Between &quot; & Forms![Ship Date Range]![Beginning Ship Date] & &quot; And &quot; & Forms![Ship Date Range]![Ending Ship Date] & &quot);&quot;

 

[rochelle]

First of all make sure the form is open when your run your code and that there is data in the appropriate text boxes.&nbsp;&nbsp;You don't have to point to it in your code.&nbsp;&nbsp;Can you give me the exact error message you're getting?<br><br>One thing I would try is to paste the SQL into the SQL view of a new query and then try to execute it.&nbsp;&nbsp;I haven't actually tested this but I've done it many times in the past.&nbsp;&nbsp;It should work.&nbsp;&nbsp;<br><br>My turn for a Whoops!&nbsp;&nbsp;I think you need to put # inside the quotation marks before and after each date.&nbsp;&nbsp;Maybe that's the problem... But then, that doesn't seem to have anything to do with the error message you described.