Using Form for Query, then Query Results for SendObject For...Next

[roxannep]

I have code set up on a button that will email an individual message with unique record information based on a query pulling information based on a date range. It works fine when the dates are actually inserted in the code. What I need to do, however, is have a form come up requesting user input for the data range, then have the query pull that information and continue with the For Next SendObject code I have already set up.<br><br>Here's what I have and where it's stuck:<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>DoCmd.OpenForm [Forms]![Ship Date Range], , , , , acDialog<br>Set db = CurrentDb<br>SQL = &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]![Beginning Ship Date]&quot;# AND #&quot;[Forms]![Ending Ship Date]&quot;#));&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

 

[Chopper]

RoxanneP,<br><br>I see two problems that I think I can help with on your date problem.<br><br>First, when you are referencing the form, you must use the form name.&nbsp;&nbsp;The format is [Forms]![formName]![FieldOnForm]&nbsp;&nbsp;It looks to me like you are mising the form name.<br><br>Second, in your sql string, when you break the string you need to use the concatenation operator (&) to concatenate the strings together.&nbsp;&nbsp;Append your SQL string to resemble the following:<br>...Between #&quot; & [Forms]![InsertFormName]![Beginning Ship Date] & &quot;# AND #&quot;[Forms]]![InsertFormName]![Ending Ship Date] & &quot;#));&quot;<br><br>Give these a try.<br> <p>-Chopper<br><a href=mailto: > </a><br><a href= > </a><br>