Exporting Access 2003 text fields into Word.

[HenHouse]

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!

[JConsulting]

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

[HenHouse]

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:\G RADUATIONS\Know ledge Management Database\Mark 2\mergedoc.doc" , "Word.Document" )
' Make Word visible.
objWord.Applica tion.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMer ge.OpenDataSour ce _
Name:=CurrentDb .Name, _
LinkToSource:=T rue, _
Connection:="TA BLE tblContactDetai ls", _
SQLStatement:=" Select firstname,lastn ame,jobtitle,de partment,addres s1,address2,add ress3,city,look upcounties,post code,country from tblContactDetai ls WHERE ContactID=[frmContactDetai ls!contactID]"

' Execute the mail merge.
objWord.MailMer ge.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=[frmContactDetai ls!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

[ADezii]

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:\G RADUATIONS\Know ledge Management Database\Mark 2\mergedoc.doc" , "Word.Document" )
' Make Word visible.
objWord.Applica tion.Visible = True

' Set the mail merge data source as the current database.
objWord.MailMer ge.OpenDataSour ce _
Name:=CurrentDb .Name, _
LinkToSource:=T rue, _
Connection:="TA BLE tblContactDetai ls", _
SQLStatement:=" Select firstname,lastn ame,jobtitle,de partment,addres s1,address2,add ress3,city,look upcounties,post code,country from tblContactDetai ls WHERE ContactID=[frmContactDetai ls!contactID]"

' Execute the mail merge.
objWord.MailMer ge.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=[frmContactDetai ls!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:

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=" & Forms![frmContactDetails]![ContactID]

[HenHouse]

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?

[HenHouse]

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?

[HenHouse]

Anyone have any more ideas on this before I plunge my head into boiling oil and end it all?

Thanks!

[ADezii]

'Assuming [ContactID] is a Numeric Value:

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=" & Forms![frmContactDetails]![ContactID]

It concatenates, appends, adds to as in:
"John" & " " & "Doe" ==> John Doe

[HenHouse]

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!

[confused99]

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.