Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    35

    Unanswered: E-mail macro question

    I've got a macro set up to send out an e-mail and the subject line uses "=[Forms]![ECN Form]![ECN#]" to grab the record number of the form. I'd like to expand on this to show the record number and whatever line it applies too. For example: "123 - Vocational". The line is another control on my form, but the ways I've tried to get it working Access yells at me about not being able to find my ECN form...however the autonumber formula above will work fine once I put it back to just that. Any ideas?

    I'd also like to place the subform that's on the form in the message body, but just the records it currently shows as opposed to the entire table or records. Is that possible?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What did you try when it yelled at you?

    Almost anything is possible, you could have a report/subreport showing exactly what you want, then create a PDF file from that and attach the PDF to an e-mail and send that. That's one way.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2008
    Posts
    35
    When I have "=[Forms]![ECN Form]![ECN#] And [Forms]![ECN Form]![Line]" it always returns a value of -1, no matter what the ECN number is. The line control is a combo box that pulls it's list from another table, not a check box.

    When I have "=[Forms]![ECN Form]![ECN#], [Forms]![ECN Form]![Line]", Acces says it can't parse the string. It tells me to check my syntax, so obviously that's not a properly formed string.

    I thought the first choice would have worked alright, but I guess not. I tried attaching a subform to the e-mail as well, and while it was a simple thing to do via a macro, it pulled every record from the subform's table. I need it to filter the subform and only show the results that match the ECN number. I'm going to try and run a query instead and attach that today.

  4. #4
    Join Date
    Oct 2008
    Posts
    35
    I did some more research and found out the proper way to set it up is:
    =[Forms]![ECN Form]![ECN#] & " - " & [Forms]![ECN Form]![Line]. So that issue is fixed. If you have any ideas on the automatically filtered subform or query I'd love to hear them. I've got the base query set up but I can't figure out how to make it filter automatically and take those results and attach them to the e-mail.

  5. #5
    Join Date
    Sep 2005
    Posts
    19
    try this for your subject line

    "=[Forms]![ECN Form]![ECN#]" & " - " & "=[Forms]![ECN Form]![Line]"

    edited to add: Guess you beat me to it

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by punkn00dlez
    I tried attaching a subform to the e-mail as well, and while it was a simple thing to do via a macro, it pulled every record from the subform's table. I need it to filter the subform and only show the results that match the ECN number.
    Quote Originally Posted by punkn00dlez
    ...If you have any ideas on the automatically filtered subform or query I'd love to hear them. I've got the base query set up but I can't figure out how to make it filter automatically and take those results and attach them to the e-mail.
    Attaching a subform is not what I had in mind. Either build a report/subreport to convert or send... or you have to use VBA to loop through the data in your query and add the content to the message body.

    BTW, I have no idea how you would do this with macros, I only use macros for AutoExec and custom toolbars/menus.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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