Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2012
    Posts
    4

    Unanswered: help with recordset

    Hi Guys! I have a question i hope someone can help with. I have a form that pulls information that you hit a button and it emails all the information. The problem i have is that it pulls 2 fields from a table called "test" i have a dlookup that works fine unless there is more than one record there then it only pulls the first. I know i need to use a recordset to get all the records but i have no idea how to do this. Here is my code i have now... any thoughts on how and what to change? Thanks

    Code:
    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
    
    
    
        
        varTo = DLookup("[Email]", "tblEmail")
        varCC = DLookup("[CC]", "tblEmail")
        stSubject = "RDC DTC Order Shipping Change"
        stItem = DLookup("Item", "Test")
        stQty = DLookup("Qty", "Test")
        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
        
            
        stText = 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 & Chr$(13) & _
                 "Item #: " & stQty
      
        DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText, -1
        
        On Error GoTo Err_Execute
        CurrentDb.Execute strSQL, dbFailOnError
        On Error GoTo 0
    
         Exit Sub
    
    Err_Execute:
        Resume Next
    Exit_SendInfo_Click:
        Exit Sub
    Err_SendInfo_Click:
        MsgBox Err.Description
        Resume Exit_SendInfo_Click
    
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Although I don't know for certain Im pretty certain that a dlookup (like other domain functions such as dsum,dcount etc) is implemented as a native SQL recordset underneath the covers.
    effectively the dlookup requires 3 parameters, although you can 'get away' with just 2
    https://support.office.com/en-us/art...b-bed10dca5937
    avalue = dlookup(value_or_column_you_want, table_or_query_its_coming_from, optional_filter)
    so say you wanted the username from a table called users for a user called sroot , that would be somethign like
    Code:
    myUserID = dlookup("username", "users", "userid = 'sroot'")
    or the sme thing written as a SQL statement
    Code:
    select username from users where userid = 'sroot'
    in your case your dlookups seem to be missing the filter bit so it will return the first row that matches (if no match/filter is specified then it will return the first row it 'finds'). if no match is found then NULL is returned.
    your code needs to handle a dlookup which returns NULL. you cant send an email to a null emailaddress

    have a look at the help system on creating a recordset
    generally you should avoid using domain functions if you need to retrieve a lot of data, eg more than one column from the same row (the same filter), or if you need to process several rows. in your case you need two columns (presumably from the same row in tblemail
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Although I don't know for certain Im pretty certain that a dlookup (like other domain functions such as dsum,dcount etc) is implemented as a native SQL recordset underneath the covers.
    effectively the dlookup requires 3 parameters, although you can 'get away' with just 2
    https://support.office.com/en-us/art...b-bed10dca5937
    avalue = dlookup(value_or_column_you_want, table_or_query_its_coming_from, optional_filter)
    so say you wanted the username from a table called users for a user called sroot , that would be somethign like
    Code:
    myUserID = dlookup("username", "users", "userid = 'sroot'")
    or the sme thing written as a SQL statement
    Code:
    select username from users where userid = 'sroot'
    in your case your dlookups seem to be missing the filter bit so it will return the first row that matches (if no match/filter is specified then it will return the first row it 'finds'). if no match is found then NULL is returned.
    your code needs to handle a dlookup which returns NULL. you cant send an email to a null emailaddress

    have a look at the help system on creating a recordset
    generally you should avoid using domain functions if you need to retrieve a lot of data, eg more than one column from the same row (the same filter), or if you need to process several rows. in your case you need two columns (presumably from the same row in tblemail
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2012
    Posts
    4
    i see how and have examples for the recordset, i just don't know where i would put it in the code to make it write to the email.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a the domain fucntion ius a surrogate for using a recordset, then it needs to go in the same area as the dlookups.
    whether you need to refine your process is a differnet questions. you've not given a lo9t fo dertail

    bu a recordset can return no, one or more rows. so if you are using a recordset with multiple rows then you need soem form of loop processing OR use that recorset as a feedstock to a report or form
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2012
    Posts
    4
    Here is what i have, but i think i have the loop wrong because it is only giving me one record still (also it is giving me the name of my table not sure why) but i think this is close... do you see what i am doing wrong?

    Code:
    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
        
        
        
        
        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
        rs.Close
        Set rs = Nothing
        
        Resume Next
    Exit_SendInfo_Click:
        Exit Sub
    Err_SendInfo_Click:
        MsgBox Err.Description
        Resume Exit_SendInfo_Click
    
    End Sub

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •