Help with loop on recordset (1 Viewer)

[sroot]

I have this code here that sends an email out with all the needed info. The problem i ran into was that for my field "item" it is in a table called "test" and there can be more than one record in there. I just can't get it to loop and show me all of the records. Right now it shows me only the first record. Any ideas? Also it is showing the name of the table before the result so i am not sure what is wrong there... Thanks for the help!

Private Sub Command4_Click()
On Error GoTo Err_SendInfo_Click

    Dim varTo As Variant
    Dim varCC As Variant
    Dim stSubject As String
    Dim stItem As String
    Dim stQty As String
    Dim stPO As String
    Dim stFirstName As String
    Dim stLastName As String
    Dim stAddress1 As String
    Dim stAddress2 As String
    Dim stCity As String
    Dim stState As String
    Dim stZip As String
    Dim stPhoneInfo As String
    Dim stPhone As String
    Dim stRDC As String
    Dim rs As DAO.Recordset
    Dim strData As String
    
    
    
    strData = ("Test")
    Set rs = DBEngine(0)(0).OpenRecordset(strData)
    strData = strData & rs!item & " , " & rs!qty & vbCrLf
    
    
    rs.Close
    Set rs = Nothing
    
    varTo = DLookup("[Email]", "tblEmail")
    varCC = DLookup("[CC]", "tblEmail")
    stSubject = "RDC DTC Order Shipping Change"
    stItem = strData
    stPO = Me.PO
    stFirstName = Me.Firstname
    stLastName = Me.lastname
    stAddress1 = Me.address1
    stAddress2 = Me.address2
    stCity = Me.City
    stState = Me.State
    stZip = Me.zip
    stPhoneInfo = Me.phoneinfo
    stPhone = Me.phone
    stRDC = DLookup("rdc", "rdc")
        
    stText = "DTC PO Info for Heavy Goods Order for RDC " & stRDC & Chr$(13) & Chr$(13) & _
             "PO: " & stPO & Chr$(13) & _
             "First Name: " & stFirstName & Chr$(13) & _
             "Last Name: " & stLastName & Chr$(13) & _
             "Address #1: " & stAddress1 & Chr$(13) & _
             "Address #2: " & stAddress2 & Chr$(13) & _
             "City: " & stCity & Chr$(13) & _
             "State: " & stState & Chr$(13) & _
             "Zip Code: " & stZip & Chr$(13) & _
             "Phone Info: " & stPhoneInfo & Chr$(13) & _
             "Phone #: " & stPhone & Chr$(13) & _
             "Item #: " & stItem
           
  
    DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText, -1
    
    Do While Not rs.EOF
    rs.MoveNext
    Loop
    
    Resume Next
Exit_SendInfo_Click:
    Exit Sub
Err_SendInfo_Click:
    MsgBox Err.Description
    Resume Exit_SendInfo_Click

End Sub

 

[DavidAtWork]

try making strData = "SELECT test.* FROM test"

David

 

[sroot]

unfortunatley this didnt help. now on the item line in the email it shows "SELECT test.* FROM test1234, 1" i have 5 records in that table and its just showing the first line.

 

[pr2-eugin]

You have misplaced your Loop.

Private Sub Command4_Click()
On Error GoTo Err_SendInfo_Click

    Dim varTo, varCC
    Dim stSubject As String, stItem As String, stQty As String
    Dim stPO As String, stFirstName As String, stLastName As String
    Dim stAddress1 As String, stAddress2 As String, stCity As String
    Dim stState As String, stZip As String, stPhoneInfo As String
    Dim stPhone As String, stRDC As String, strData As String
    Dim rs As DAO.Recordset
   
    Set rs = DBEngine(0)(0).OpenRecordset([COLOR=Blue][B]"SELECT Test.* FROM Test"[/B][/COLOR])
    [COLOR=Red][B]Do While Not rs.EOF[/B][/COLOR]
        strData = strData & rs!item & " , " & rs!qty & vbCrLf
        [COLOR=Red][B]rs.Movenext
    Loop[/B][/COLOR]
    rs.Close
    Set rs = Nothing
    
    varTo = DLookup("[Email]", "tblEmail")
    varCC = DLookup("[CC]", "tblEmail")
    stSubject = "RDC DTC Order Shipping Change"
    stItem = strData
    stPO = Me.PO
    stFirstName = Me.Firstname
    stLastName = Me.lastname
    stAddress1 = Me.address1
    stAddress2 = Me.address2
    stCity = Me.City
    stState = Me.State
    stZip = Me.zip
    stPhoneInfo = Me.phoneinfo
    stPhone = Me.phone
    stRDC = DLookup("rdc", "rdc")
        
    stText = "DTC PO Info for Heavy Goods Order for RDC " & stRDC & Chr$(13) & Chr$(13) & _
             "PO: " & stPO & Chr$(13) & _
             "First Name: " & stFirstName & Chr$(13) & _
             "Last Name: " & stLastName & Chr$(13) & _
             "Address #1: " & stAddress1 & Chr$(13) & _
             "Address #2: " & stAddress2 & Chr$(13) & _
             "City: " & stCity & Chr$(13) & _
             "State: " & stState & Chr$(13) & _
             "Zip Code: " & stZip & Chr$(13) & _
             "Phone Info: " & stPhoneInfo & Chr$(13) & _
             "Phone #: " & stPhone & Chr$(13) & _
             "Item #: " & stItem
             
    DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText, -1    
Exit_SendInfo_Click:
    Exit Sub
Err_SendInfo_Click:
    MsgBox Err.Description
    Resume Exit_SendInfo_Click
End Sub

 

[sroot]

Perfect! Thank you so much!!!