Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Another Automation Question (2000 (SR3))

    I have a database for a directory and we want to send the listings to those concerned for proofreading before we give the data to our book designer. I have the code to dump from Access into Outlook and generate an email just fine, but I have one little formatting problem.

    A listing will look like the following:
    From Client table: Joe Smith
    From Listing main table: Category Type
    From Listing details table: Category Details

    The formatting of the email will be:

    Joe Smith
    Category 1 ~ detail 1, detail 2, detail 3
    Category 2 ~ detail 1, detail 2, detail 3

    My problem is my formatting is coming out as follows:

    Joe Smith
    Category 1 ~ detail 1, detail 2, detail 3, with a comma after each detail including the final one.

    Can someone suggest some syntax that will give me the commas, but not at the end. I know it isn't a big problem, but I am already at the end of my VBA skills. It *is* a minor detail and can easily be dealt with when I do a text dump to the book designer into Word with a good, old-fashioned find and replace. I'm so close, though, that I'd like to have the emails look clean. Here is the code (feel free to suggest any cleanup needed elsewhere as well).

    three notes: 1) this is adapted from Smith and Sussman Beginning Access 2000 VBA 2) pay no attention to the nonsensical email message. I haven't finessed the details yet. I just wanted the concept to work! 3) there may be some dead wood recundancies. I have been working on it for a couple of hours and haven't proofread out the dead ends... Some day this will make sense!

    Peter N
    *************
    code attached as a text file

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Automation Question (2000 (SR3))

    Here's the file. I think I lost it when I posted.

    PN
    Attached Files Attached Files

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Another Automation Question (2000 (SR3))

    To remove the last comma, try this
    <pre>strCatDetails = left(strCatDetails,Len(strCatDetails)-2)</pre>

    before
    <pre>strCat = strCat & strCatDetails </pre>


    This code removes the last two characters from strCatDetails
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Another Automation Question (2000 (SR3))

    Another way to do this is to define strCatDetails as a Variant and replace the statement in bold:

    strCatDetails = ""
    While Not recListDetails.EOF
    strCatDetails = strCatDetails & recListDetails("NewDirectorySpecialties") & ", "
    recListDetails.MoveNext
    Wend

    with:

    strCatDetails = Null
    While Not recListDetails.EOF
    strCatDetails = (strCatDetails + ", ") & recListDetails("NewDirectorySpecialties")
    recListDetails.MoveNext
    Wend

    Then there is no need to delete the comma at the end because there isn't one. Also the + in this statement is another form of concatenation but the + inside the round brackets tells it that if any variable inside the expression (strCatDetails + ", ") is enumerated to Null the whole expression is enumerated to Null. eg. First time thru the variant strCatDetails is Null.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Automation Question (2000 (SR3))

    As always, thanks to both of you.

    Both solutions are familiar to me from using them in contexts outside of VBA, it just would have taken me hours to get there.

    Peter <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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