Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    66

    Unanswered: Trying to email a report with specific data...automatically!

    Can someone help me on this? I am trying to write code to email a report with store-specific data to each store automatically...and I can't get it to work.

    I set up a form (getstore) to select the store. Then I set it up so it emails the whole report to that store's email address....

    the problem is...

    it emails the WHOLE report...there's no filter for the store.

    How can I handle this differently?

    Thanks for your help!


    Susie
    A Colorado mommy of 4, who's still learning access...

  2. #2
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    u need to write a query that gets u data only for that store, set the source of the report to the query and then email

  3. #3
    Join Date
    Apr 2004
    Posts
    66
    "set the source of the report to the query"

    That's the part I don't get...how do I set the source of the report when I'm doing a
    "DoCmd SendObject"

    Susie

  4. #4
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    i assume u have a button that says email report
    that button opens up a form that asks for store name and has an email button
    rite ?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Whatever method you have used to select the individual forms may also be used to filter the reports criteria.

    You could use a few methods: Query parameters, dynamic sql constructed in the on open event, pass a filter argument, dynamic where clause

    All sorts of stuff.

    What specific method are you using to trigger and generate this report?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Apr 2004
    Posts
    66
    Well, close. I have a pull down list of stores. The user selects one, then clicks a button. The code behind the button finds the email address, then sends the report.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    ok, in order to do that you probably have to go rooting around your store table looking for an id associated with that store right?

    The same sort of concept applies to the report. You could build a where clause into the report stating "SELECT * FROM yourStuff WHERE store_id = ?"

    then in query parameters, set "? = forms!yourform!store_id"

    Or you could dynamically tack on the where clause in the On Open event of the report:

    Me.Recordsource = Me.Recordsource & " WHERE store_id = " & forms!yourForm!store_id


    Does that make sense?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Apr 2004
    Posts
    66
    Okay - I'm probably in over my head here, but I really need to figure this out, so please bear with me.

    I set up a button with this code (thinking I knew what I was doing, ha ha! and based on some help another user had provided...)

    Private Sub Command10_Click()

    'creates strRecipient as a variable

    Dim strRecipient

    'assigns an email address to strRecipient depending on table information

    strRecipient = DLookup("EMailAddress", "Store", "StoreID='" & SelectStore & "'")

    'assigns a SQL string to the public variable strReportSource filtering by the sales man selected in combo box

    strReportSource = "SELECT DebtorListingTbl.* FROM DebtorListingTbl WHERE storeid=" & Chr(34) & SelectStore & Chr(34) & ";"

    'creates an email in outlook using the email address from the table and the report filtered by the salesman set last arg to True for editing before sending
    'or False to automatically send

    DoCmd.SendObject acSendReport, "DLTEST", acFormatRTF, strRecipient, "", "", "Report", "Hello", True

    End Sub

  9. #9
    Join Date
    Apr 2004
    Posts
    66
    I should add that neither part is wroking right now...I can't get the email address, and the report does not filter at all for me... Sigh....please please please keep trying to help - I'm desperate!!

  10. #10
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    can u post ur file

  11. #11
    Join Date
    Apr 2004
    Posts
    66
    Quote Originally Posted by fullymooned
    can u post ur file
    I'd have to clean out all the data cause it has SSN's in it...it will take me a bit of time...

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, the key is the [SelectStore] value. It obviously holds the primary key value for the store you'd like to report on.

    For starters, I'm assuming StoreId is a number, so you need to ditch the single quotes around it. With that in mind, change your DLookup to:

    DLookup("EMailAddress", "Store", "StoreID=" & SelectStore)

    Same thing with strReportSource:

    strReportSource = "SELECT DebtorListingTbl.* FROM DebtorListingTbl WHERE storeid=" & SelectStore

    I might add that this is a horrendous way to move data around, you CAN apply that directly to the combo box with:

    ComboBoxName.RowSource = "SELECT DebtorListingTbl.* FROM DebtorListingTbl WHERE storeid=" & SelectStore

    Coincidentally, that's very similar to what you could do for the report using the code I already supplied.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Apr 2004
    Posts
    66
    I'm sorry - I feel like an idiot here cause I just can't get this straight in my mind....

    I have tried changing this:
    strReportSource = "SELECT DebtorListingTbl.* FROM DebtorListingTbl WHERE storeid=" & SelectStore

    But nothing changes...the whole report still comes up.

    I've switched it from a combo box to a simple "enter the store name" box, but it still doesn't work.

    The report is pulled from a single table...is my sql statement wrong? How does the report know to be filtered by that statement?

    I'm used to doing OpenReport and then adding a filter on that command, but since I'm using SendObject, it doesn't seem to work.

    Any other ideas?

  14. #14
    Join Date
    Apr 2004
    Posts
    66
    Oh wait....it's working now....beautifully!!!! (I think I had an extra quote still, that once removed, let it work!!)

    Thank you so very very very much!!!!!!!!

    Susie

Posting Permissions

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