By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

How can I get all recordset results into email body

P: 37
Hi Everybody,
I'm having trouble getting all recordset records into the body of an email. I just get one when there should be more. Could you please checkout my code to see where I'm going wrong. Thank you. Eddie...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.   Dim rs As dao.Recordset
  3.   Dim strSql As String
  4.  
  5. strSql = "SELECT tblRRCustomers.ArriveDate, tblRRCustomers.Property, [CustFirstName] & "" "" & [CustLastName] AS Name, tblRRCustomers.ArriveTime, tblRRCustomers.DepartDate, tblRRCustomers.DepartTime, tblRRCustomers.Adults, tblRRCustomers.Children, Format([ArriveDate],""dddd  mmm dd"") & "",  "" & Format([ArriveTime],""hh\.nn\.AM/PM"") & "" to "" & Format([DepartDate],""dddd mmm dd yyyy"") & "",  "" & Format([DepartTime],""hh\.nn\.AM/PM"") AS DateLine, tblRROwners.IDOwners, tblRROwners.OwnerFirstName, tblRROwners.OwnerLastName, [OwnerFirstName] & "" "" & [OwnerLastName] AS Owner " & vbCrLf & _
  6. "FROM tblRROwners INNER JOIN (tblRRProperty INNER JOIN tblRRCustomers ON tblRRProperty.Property = tblRRCustomers.Property) ON tblRROwners.IDOwners = tblRRProperty.IDOwners " & vbCrLf & _
  7. "WHERE (((tblRRCustomers.ArriveDate)>=Now()) AND ((tblRROwners.IDOwners)=10) AND ((tblRRCustomers.BookingStatus)=2 Or (tblRRCustomers.BookingStatus)=3)) " & vbCrLf & _
  8. "ORDER BY tblRRCustomers.ArriveDate, tblRRCustomers.Property;"
  9.  
  10.  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  11.  Do While Not rs.EOF
  12.  
  13.      strMSg = rs!DateLine & ", " & rs!Property & ", " & rs!Property & vbCrLf
  14.  
  15.  
  16.      rs.MoveNext
  17.      Loop
  18.  
  19. DoCmd.SendObject acSendNoObject, , , "heriage@oyster.net.ck", , , "Confirmation Test", strMSg, True
  20.  
  21. End Sub
Mar 27 '12 #1

✓ answered by NeoPa

I suspect your line #13 wants to say :
Expand|Select|Wrap|Line Numbers
  1. strMsg = strMsg & {some separator character} & rs!DateLine ...
It also appears clear that this code has never been compiled properly. Please see, and follow the instructions in, Before Posting (VBA or SQL) Code before next posting code.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,491
I suspect your line #13 wants to say :
Expand|Select|Wrap|Line Numbers
  1. strMsg = strMsg & {some separator character} & rs!DateLine ...
It also appears clear that this code has never been compiled properly. Please see, and follow the instructions in, Before Posting (VBA or SQL) Code before next posting code.
Mar 27 '12 #2

P: 37
Thank you so much Neo, that was the nail on the head,
Cheers Eddie
Mar 27 '12 #3

NeoPa
Expert Mod 15k+
P: 31,491
Good to hear :-)
Mar 27 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.