Word doc from Access

[PNGBill]

Hi There, I have an ms access 2000 field with email addresses and want to out put these records to a word document in the format "xxx@xxx.com, vvv@rrr.com, uuu@sss.com". there are 750 odd email addresses. I can out put the records ok and have concact'd the comma after each one but they are listed instead of as a sentence accross the page. Appreciate any suggestions.

 

[boblarson]

How are you currently trying to do this?

 

[PNGBill]

I have a query listing the records in the field and adding a comma to each one. When I output the result it always comes as a list even if I use the access automated option and specify Comma Delimited. This is why I have added the commas. I can out put to excell and as a text file but the best I get is a list in a text doc.

 

[boblarson]

You won't be able to use the query directly. You need to build the string by iterating through the query using a recordset. If the query adds the commas, then you should be able to do this:

Dim rst As DAO.Recordset
Dim strOutput As String

Set rst = CurrentDb.OpenRecordset("YourQueryNameHere")

Do Until rst.EOF
  strOutput = strOutput & rst("YourFieldNameFromQueryHere")
  rst.MoveNext
Loop

You'll then have strOutput which you can use to send to Word.

 

[PNGBill]

Thanks Bob, Will try now. Regards,
Bill

 

[PNGBill]

Hi Bob,

Something wrong with the code below on line strOutput=strOutput....

Private Sub CmdHordeeMailList_Click()
On Error GoTo Err_CmdHordeeMailList_Click
Dim rst As DAO.Recordset
Dim strOutput As String

Set rst = CurrentDb.OpenRecordset(QryEmailList)

DoUntil rst.EOF

strOutput=strOutput&rst(eMail)

rst.MoveNext

Loop

Exit_CmdHordeeMailList_Click:
Exit Sub
Err_CmdHordeeMailList_Click:
MsgBox Err.Description
Resume Exit_CmdHordeeMailList_Click

End Sub

Any ideas where I have erred???
Regards,
bill

 

[boblarson]

strOutput = strOutput & rst("eMail")

And make sure you have spaces where they are needed:

Do Until rst.EOF
...^

 

[PNGBill]

Hi Bob, Have corrected code and no more error messages but nothing happens. I changed the field name to cause an error and this indicated the code found the query and field. I also restricted the records to 70 but still no output on the screen.
Any ideas??
Regards,
Bill

 

[PNGBill]

Hi Bob, I have added a message box to the code and I had to click the %%% thing 70 times as the loop was performed but all I saw was the message box.
Rgards,
Bill

 

[boblarson]

So, are you sure that the field you want the value returned for is actually named eMail?

Also, for easier debugging, just use

Debug.Print strOutput

where you were using the message box and then it will print out in the IMMEDIATE WINDOW (the window in the VBA code area below the code) and you can see the results while/after it runs without having to hit the message box 70 times.

Also, I noticed you didn't put the query name in quotes either:

Set rst = CurrentDb.OpenRecordset("QryEmailList")

 

[PNGBill]

Hi Bob, "QryEmailList" and "eMail" I fixed the code and all should work?? Maybe I am in the wrong place.The query displays a list of emails with commas attached. I made a form and put a button on same that opens the query. I edited the onclick event for the button to include the code you supplied. Is this the correct place for the code to be??
Regards,
Bill

 

[boblarson]

I think you may want to post the database at this point.

See here for how:http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[PNGBill]

Hi Bob, Sample Database attached. When I receated the DB the code became in error but I guess that will not be the real issue.
Thanks for your assistance and regards,
Bill

 

[boblarson]

Okay, a bunch of problems:

1. Don't bind the text box to a field.

2. You had several coding errors
a. Dim rstAsDAO.Recordset Should have spaces Dim rst As DAO.Recordset
b. You have Dim stOutput As String but you were using stroutput in the rest of the code.
c. You have st rst = which needs to be Set rst =
d. Last, make sure that you go to TOOLS > REFERENCES and that Microsoft DAO 3.x (x being the highest number available in the list) is selected.

So, anyway your major problem is coding errors (which some you can overcome by making sure to add Option Explicit just under Option Compare Database.

 

[PNGBill]

Hi Bob,
Thanks very much for fixing the code. I note an extra line re output. Mid night here in PNG. Will study more in morning and put code to good use.
Appreciate your work and advice. Best regards,
Bill