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

    Unanswered: Really need some help- I am stumped!!!

    Can someone help me on this...I've been battling it myself now for a couple of weeks...searched for hours on the web...spent nearly $150 on two new VBA books...and still can't get it to work...

    My problem is with a multiselect on a list.

    I want to be able to select multiple stores.

    Then, for each store, I want to be able to pull that store's email, and email them their report.

    I understand the theory in how to set it up...but can't figure out the code to make it work.

    I do have code which pulls the email and sends the report working. My problem is in the code to use multiselect and fitting that in.

    My code that pulls the report (when a single storeid is selected) looks like this:

    Dim strRecipient
    strRecipient = DLookup("EMailAddress", "Store", "StoreID=[SelectStore]")
    Dim strCCRecipient
    strCCRecipient = DLookup("CCEMail", "Store", "StoreID=[SelectStore]")
    Dim strBCCRecipient
    strBCCRecipient = DLookup("Email", "OurInfo", "SetupID=1")
    Dim StrEmailMsg As String
    StrEmailMsg = "Attached please find your Report for this month."
    DoCmd.SendObject acSendReport, "DlbyStore", acFormatRTF, strRecipient, strCCRecipient, strBCCRecipient, "Report", StrEmailMsg, True

    The part I can't figure out is how to loop through each storeid (selected in the listbox named SelectStore) and run this code.

    Can anyone help with this??? If you could give me an example, that would be great...if you could help with the code that wraps around this, it would be even better!!

    Thanks so very much!!

    Susie

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try something like that:

    PHP Code:
    Dim SelectedItem As Variant
          
    For Each selectedItem In yourListBox.ItemsSelected
              your code to be repeated
          Next 

  3. #3
    Join Date
    Apr 2004
    Posts
    66
    Quote Originally Posted by hammbakka
    Try something like that:

    PHP Code:
    Dim SelectedItem As Variant
          
    For Each selectedItem In yourListBox.ItemsSelected
              your code to be repeated
          Next 

    Thank you so very much!!!

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Another idea

    Rather than do this:
    Dim strRecipient
    strRecipient = DLookup("EMailAddress", "Store", "StoreID=[SelectStore]")
    Dim strCCRecipient
    strCCRecipient = DLookup("CCEMail", "Store", "StoreID=[SelectStore]")
    Dim strBCCRecipient
    strBCCRecipient = DLookup("Email", "OurInfo", "SetupID=1")
    Dim StrEmailMsg As String
    StrEmailMsg = "Attached please find your Report for this month."


    You can set the rowsource control for the combo box for more than one column to be included but to only show 1.

    To do this set the column count for the combo box to 3.
    Set the column width to 1,0,0,0

    The final three columns will be hid.

    SELECT DisplayedField, emailaddress, ccemail, email FROM store JOIN ourinfo ON store.storeid = ourinfo.storeid WHERE storeid = [selectstore]

    Then you can tweak your code a little and include what hammbakka included


    Dim SelectedItem as Variant
    for each selecteditem in yourListBox.selectedItems
    with selecteditem
    DoCmd.SendObject acSendReport, "DlbyStore", acFormatRTF, .column(1), .column(2), .column(3), "Report", StrEmailMsg, True
    end with
    next


    One other thing. I think that this may eliminate your selectstore procedure, but I can't say for sure without seeing the code.

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    That is also a fine idea

Posting Permissions

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