469,647 Members | 1,680 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

Exporting Access 2003 text fields into Word.

Hi folks;
Hey wow! First post! Hope it bears fruit... Anyway, I've been teaching myself Access since March by writing an event/contact management database for the firm I work for. It's reasonably straightforward, it just records details on our clients among other thihgs. The old Lotus DB it (supposedly!) supercedes had a handy feature that I've so far had no luck in replicating in Access. It worked as follows:

1. The user browsed to a Client Contact record, which contained all of an individual person's contact data stored as separate fields.

2. The user could then click a handy button on the Contact form that would automatically capture the text from the relevant fields, collate it, and plonk them as an address block into the top of an automatically opened text document.

3. The user could then type a letter to the contact below the address as usual. When the time came to print the letter the address block would be in just the right place to be visible through a windowed envelope.

That's about it really. I've had a long search and found many ways to export from Access to Word, or import from to Word from Access, but none of them are so simple or straightforward. I know this must be possible somehow, but does anybody know how? The best I've been able to do so far is insert a button on the form that runs a query and displays the fields I picked in a table that pops up, but that's it.

I'm not an Access pro and I don't know how to code, so any replies starting with "Simple, just fire up C++ and xyz...." will probably leave me in tears! I'm fine with macros and queries and Microsoft packages in general, so if anyone has any ideas I'd love to hear them.

Thanks guys!
May 24 '07 #1
10 2577
JConsulting
603 Expert 512MB
Hi folks;
Hey wow! First post! Hope it bears fruit... Anyway, I've been teaching myself Access since March by writing an event/contact management database for the firm I work for. It's reasonably straightforward, it just records details on our clients among other thihgs. The old Lotus DB it (supposedly!) supercedes had a handy feature that I've so far had no luck in replicating in Access. It worked as follows:

1. The user browsed to a Client Contact record, which contained all of an individual person's contact data stored as separate fields.

2. The user could then click a handy button on the Contact form that would automatically capture the text from the relevant fields, collate it, and plonk them as an address block into the top of an automatically opened text document.

3. The user could then type a letter to the contact below the address as usual. When the time came to print the letter the address block would be in just the right place to be visible through a windowed envelope.

That's about it really. I've had a long search and found many ways to export from Access to Word, or import from to Word from Access, but none of them are so simple or straightforward. I know this must be possible somehow, but does anybody know how? The best I've been able to do so far is insert a button on the form that runs a query and displays the fields I picked in a table that pops up, but that's it.

I'm not an Access pro and I don't know how to code, so any replies starting with "Simple, just fire up C++ and xyz...." will probably leave me in tears! I'm fine with macros and queries and Microsoft packages in general, so if anyone has any ideas I'd love to hear them.

Thanks guys!

refine your search to something called a mail merge. it's not quite so push button as the old school stuff...but it's doable.

www.msdn.com (search for Access mail merge) that will give you an idea of what it's all about. Easier than me trying to type or paste it all in I think.
J
May 24 '07 #2
Thanks for the info J, all helpful stuff. I've hit another stumbling block though! After having trawled most of the charted Internet for an answer it's still sticking. The maddening thing is that I can see it working with jut a few tweaks, but because I don't really "understand" the inner workings of VB all I can do is poke around and see what happens.

So far I have the following code attached to the Click event of the Mail Merge button on my Access form:

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("S:\GRADUATIONS\Knowledge Management Database\Mark 2\mergedoc.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE tblContactDetails", _
SQLStatement:="Select firstname,lastname,jobtitle,department,address1,ad dress2,address3,city,lookupcounties,postcode,count ry from tblContactDetails WHERE ContactID=[frmContactDetails!contactID]"

' Execute the mail merge.
objWord.MailMerge.Execute

End Function

It's more or less copied from the MSDN article but with a few tugs to point it to the correct files. I think the problem is with my SQL statement. I can't find a way to make it pick only the address details on the current form rather than the entire database. I'm not sure it's allowing me to address "ContactID=[frmContactDetails!contactID]" as a valid WHERE statement. The original MSDN article had the SQL statement SQLStatement:="Select * from [table]", but that outputs every record in the table to Word and that's not what I need!

Anyway, if anybody has any ideas I'd love to hear them!

Hen
Jun 1 '07 #3
ADezii
8,800 Expert 8TB
Thanks for the info J, all helpful stuff. I've hit another stumbling block though! After having trawled most of the charted Internet for an answer it's still sticking. The maddening thing is that I can see it working with jut a few tweaks, but because I don't really "understand" the inner workings of VB all I can do is poke around and see what happens.

So far I have the following code attached to the Click event of the Mail Merge button on my Access form:

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("S:\GRADUATIONS\Knowledge Management Database\Mark 2\mergedoc.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE tblContactDetails", _
SQLStatement:="Select firstname,lastname,jobtitle,department,address1,ad dress2,address3,city,lookupcounties,postcode,count ry from tblContactDetails WHERE ContactID=[frmContactDetails!contactID]"

' Execute the mail merge.
objWord.MailMerge.Execute

End Function

It's more or less copied from the MSDN article but with a few tugs to point it to the correct files. I think the problem is with my SQL statement. I can't find a way to make it pick only the address details on the current form rather than the entire database. I'm not sure it's allowing me to address "ContactID=[frmContactDetails!contactID]" as a valid WHERE statement. The original MSDN article had the SQL statement SQLStatement:="Select * from [table]", but that outputs every record in the table to Word and that's not what I need!

Anyway, if anybody has any ideas I'd love to hear them!

Hen
'Assuming [ContactID] is a Numeric Value:
Expand|Select|Wrap|Line Numbers
  1. objWord.MailMerge.OpenDataSource _
  2. Name:=CurrentDb.Name, _
  3. LinkToSource:=True, _
  4. Connection:="TABLE tblContactDetails", _
  5. SQLStatement:="Select firstname,lastname,jobtitle,department,address1,ad dress2,address3,city,lookupcounties,postcode,count ry from tblContactDetails WHERE ContactID=" & Forms![frmContactDetails]![ContactID]
Jun 1 '07 #4
It is indeed a numeric value, I'll give that a try right now! Here goes... What does the & character do in an SQL query anyway?
Jun 1 '07 #5
Well, it's definitely an improvement! The old query string would output all 1000 or so records to Word, the new one only outputs one record. The only trouble is that this is what comes out when the Word .doc opens up:

1 1
1
1
1
1
1
1
1
1
1

Previously it would output

{page 1}

1 1
1
1
1
1
1
1
1
1
1

{page 2}
2 2
2
2
2
2
2
2
2
2
2

{page 3}
3 3
3
3
3
3
3
3
3
3
3

And so on... At least the numbers are appearing on top of the merge fields in the Word .doc (2 fields on first line, 1 on the remainder) but the actual content of the fields doesn't seem to be surviving the translation... How odd. Any ideas?
Jun 1 '07 #6
Anyone have any more ideas on this before I plunge my head into boiling oil and end it all?

Thanks!
Jun 13 '07 #7
ADezii
8,800 Expert 8TB
'Assuming [ContactID] is a Numeric Value:
Expand|Select|Wrap|Line Numbers
  1. objWord.MailMerge.OpenDataSource _
  2. Name:=CurrentDb.Name, _
  3. LinkToSource:=True, _
  4. Connection:="TABLE tblContactDetails", _
  5. SQLStatement:="Select firstname,lastname,jobtitle,department,address1,ad dress2,address3,city,lookupcounties,postcode,count ry from tblContactDetails WHERE ContactID=" & Forms![frmContactDetails]![ContactID]
It concatenates, appends, adds to as in:
"John" & " " & "Doe" ==> John Doe
Jun 13 '07 #8
Aha! I've cracked it! The plums from the support site where I got instructions for preparing the Word document had gotten it all wrong. They'd told me to have each field in the Word .doc marked as a MergeRec field, which simply places a marker in the .doc for Access to place the relevant record number. Geniuses. I changed them all to MergeField fields and now it works fine. Thanks all!
Jun 13 '07 #9
Can anybody tell me how I can get a sub form which displays data from a table to export the rows to a Word Document? I tried the lines in the post above this. But I'm getting a few errors related to Object Variable. I'm new to Access and I don't have a clue. Please help.

Could you also please tell me how I can make these values appear in the Word Document wherever I want?? I need help in setting the markers.
Jun 14 '07 #10
ADezii
8,800 Expert 8TB
Can anybody tell me how I can get a sub form which displays data from a table to export the rows to a Word Document? I tried the lines in the post above this. But I'm getting a few errors related to Object Variable. I'm new to Access and I don't have a clue. Please help.

Could you also please tell me how I can make these values appear in the Word Document wherever I want?? I need help in setting the markers.
Please do not cut in on someone else's Post. Create a New Thread and someone will attempt to answer your question.
Jun 15 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Fran Zablocki | last post: by
3 posts views Thread by Paolo | last post: by
6 posts views Thread by Peter Frost | last post: by
2 posts views Thread by Access Newbie with a headache via AccessMonster.co | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.