none
Get data from query RRS feed

  • Question

  • Hi everyone,

    Can someone help me out with something...
    I have made a Query that get filled in with data depending of a form field.
    But I want to use data from the Query, from each line I need one value which is a hyper link that I want to use for putting attachments into an email.

    Can someone give me an example of a similar case?

    Thank you!

    Saturday, October 10, 2015 9:36 PM

Answers

  • Would it be easier to retrieve data from a table instead of a Query?
    What is the most simple way to get data from a table or query and list it row by row in for example a msgbox?

    Thanks

    Hi. That's a slightly different question but if you want to simply concatenate all the records from a table into a message box, you could try this simple function: SimpleCSV()

    MsgBox SimpleCSV("SELECT FieldName FROM TableName", vbCrLf)

    Hope that helps...

    • Marked as answer by YannickB90 Thursday, October 15, 2015 7:12 AM
    Wednesday, October 14, 2015 2:49 PM

All replies

  • Hi. You might have to provide additional information for us to be able to help you. Are you saying you want to copy these hyperlinks into the body of the email? Posting some sample data or screen images might clarify things as well. Cheers!
    Saturday, October 10, 2015 9:55 PM
  • I will add some information to clarify myself a little bit more.

    So I have a form which has a button to email a report in PDF.
    The only thing which is not included in the form are the images.
    Those images are stored in a depended table as a hyper link to the location of the image.

    Now I want to add those images to the email with the help of a query so I open a query and each image (row) must be added to the email as an attachment.

    The email part already works but I do not know how I can add the images because I do not know how I can retrieve data from the query...

    Normally when you look for just one record you can use the DLookup command but what if you want to retrieve more then just one record?

    The code must be something like this:

    DoCmd.OpenQuery "My_Query", acViewNormal, acEdit

    For each row in totalrowsofquery

    Get hyperlink and add this to the email

    Next

    DoCmd.Close acQuery, "My_Query", acSaveNo

    Sunday, October 11, 2015 4:28 PM
  • Okay, thanks for trying to clarify your needs. If I understand it correctly, you can create a recordset based on your query and add the attachment to your email one record at a time. For example:

    Dim rs As DAO.Recordset
    
    'Create email object here...
    
    Set rs = CurrentDb.OpenRecordset("QueryName")
    
    With rs
    
      Do While Not .EOF
        YourEmailObject.Attachments.Add !HyperlinkFieldName
        .MoveNext
    
      Loop
      .Close
    
    End With
    
    'Send email object here...
    
    Set rs = Nothing
    
    Hope that helps...

    Sunday, October 11, 2015 4:34 PM
  • Alternatively you could enhance the report with a subreport with the hyperlinks, so they would become part of the PDF document. No coding needed.


    -Tom. Microsoft Access MVP

    Sunday, October 11, 2015 5:16 PM
  • Hi Tom,

    Do you mean that the pictures will be included in the report?
    Because the hyper link can not be opened by the people who receives the report.

    Sunday, October 11, 2015 8:40 PM
  • No, a hyperlink is just a link to another document or image in your case.

    So the hyperlink would have to be valid for all recipients, and not for example point to your C-drive. If the recipients are in your company the images could be on a shared driver. If they are outside, they could be on a webserver or for example in a properly shared OneDrive folder.


    -Tom. Microsoft Access MVP

    Sunday, October 11, 2015 8:46 PM
  • Okay, thanks for trying to clarify your needs. If I understand it correctly, you can create a recordset based on your query and add the attachment to your email one record at a time. For example:

    Dim rs As DAO.Recordset
    
    'Create email object here...
    
    Set rs = CurrentDb.OpenRecordset("QueryName")
    
    With rs
    
      Do While Not .EOF
        YourEmailObject.Attachments.Add !HyperlinkFieldName
        .MoveNext
    
      Loop
      .Close
    
    End With
    
    'Send email object here...
    
    Set rs = Nothing
    Hope that helps...

    I have tried this solution but I get an error message: "Run-time error '3061'":
    Too few parameters. Expected 1.

    And it must be at the line: "Set rs = CurrentDb.OpenRecordset("Attachements_Query")"

    Any help?

    Monday, October 12, 2015 11:00 AM
  • Debug that by setting a breakpoint on that line. Then when you stop there, in the Immediate window (Ctrl+G) type:

    ?currentdb.querydefs("Attachements_Query").Parameters.Count
    1

    ?currentdb.querydefs("Attachements_Query").Parameters(0).Name
    ???

    Most likely it will print a query prompt or a form reference to a form that is not open. Fix the query, or supply the parameter using the querydef object.


    -Tom. Microsoft Access MVP

    Monday, October 12, 2015 1:28 PM
  • I have tried this solution but I get an error message: "Run-time error '3061'":
    Too few parameters. Expected 1.

    And it must be at the line: "Set rs = CurrentDb.OpenRecordset("Attachements_Query")"

    Any help?

    Hi. I agree with Tom. You must be using a parameter query. If so, you could try using Leigh's Generic Recordset.

    Try replacing that line with the following:

    Set rs = fDAOGenericRst("Attachments_Query")

    Hope that helps...

    Monday, October 12, 2015 3:15 PM
  • Hi Tom,

    The ??? gives ReportID so do I have to fix something then?

    Thank you

    Tuesday, October 13, 2015 10:56 AM
  • Hi. Did you try Leigh's function? I posted a link earlier, in case you missed it.
    Tuesday, October 13, 2015 12:58 PM
  • Hi. Did you try Leigh's function? I posted a link earlier, in case you missed it.

    Hi, 

    I have tried the function today and get an error in the function at the line: "prm.Value = Eval(prm.Name)"
    The errorcode is: 2482 and says that the database is not able to find the name "ReportID" in the expression?

    Any suggestions?

    Wednesday, October 14, 2015 10:49 AM
  • Would it be easier to retrieve data from a table instead of a Query?
    What is the most simple way to get data from a table or query and list it row by row in for example a msgbox?

    Thanks

    Wednesday, October 14, 2015 10:56 AM
  • Would it be easier to retrieve data from a table instead of a Query?
    What is the most simple way to get data from a table or query and list it row by row in for example a msgbox?

    Thanks

    Hi. That's a slightly different question but if you want to simply concatenate all the records from a table into a message box, you could try this simple function: SimpleCSV()

    MsgBox SimpleCSV("SELECT FieldName FROM TableName", vbCrLf)

    Hope that helps...

    • Marked as answer by YannickB90 Thursday, October 15, 2015 7:12 AM
    Wednesday, October 14, 2015 2:49 PM
  • I have tried this function and tailored it to my needs which works perfect.
    The other options gives me always error's, this may be caused by references or something like that.

    Instead of the csv format I used this to attach the file located with an URL to the email.

    Thanks a lot everyone for your support, there is more then just one option but the last did it.

    Yannick

    Thursday, October 15, 2015 7:14 AM
  • Hi Yannick. Glad to hear you got it sorted out. Good luck with your project.
    Thursday, October 15, 2015 3:27 PM