Another Automation Question (2000 (SR3))

Topic

New Reply

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

Reply | Quote

Replies

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

PN

Reply | Quote

To remove the last comma, try this

strCatDetails = left(strCatDetails,Len(strCatDetails)-2)

before

strCat = strCat & strCatDetails 

This code removes the last two characters from strCatDetails

Reply | Quote

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.

Reply | Quote

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

Reply | Quote