Access copy to word - should be easy?

[mhammond]

Hi Guys

I'm really really stuck on something at work, I've set up a customer database and forms to input data etc which all works nice but the boss wants a button to send a single record either to the clipboard or straight to word so that when a customer phones in the detials are entered in to the database then the cursor is placed in a certain place on a word document and pasted in. I know its a rubbish system but thats how he wants it for now.

I've spent most of today playing around with code on the net that uses ADO and SQL to no avail, is there an easier way of doing it? Or even better has anyone done it before and got some samples? ps I have using forms in word.

Basically is there an ADO - paste record to clipboard command?

I've attatched the database (minus records for data protection), I would really appreciate any help I'm begining to look like a right div in the third week of my new job!

[us-media]

Easiest and quickest way, is to use Access to launch a Word template (.dot) containing a Word macro that will update one or more Word bookmarks (or form fields for that matter) with data from a mail merge source (either a database table or from a temporary text file (written by the database to say, the root of the c:\ drive)

Other way of doing it is to use DAO to connect to the database directly from Word

In Word 2000 hit ALT+F11 > Tools > References and make sure DAO... is ticked

Rob.

[mhammond]

Hey, cheers, thought I had it sussed then with the mail merge way but then I realised the problem I'd come up against before - I can only point to one record at a time in word I need some way of having say 6 instances of <<lastname>> etc and being able to make each one point to a different record - or copy and paste the words and not the <<bits>> underneath - its not possible is it?

[mhammond]

I've just noticed that you can point it to the next record, that won't work though because jobs in teh database order won't necessarily be completed in order - if that could be a 'specify record' button or simmilar that would work?

[us-media]

I generate a query that contains all the info I might need e.g. Name, Address Line(s), Reference numbers etc and have this exported to a comma delimited (CSV) file

e.g. c:\temp.txt

Word can pick this up as a data source, and use it to create one or more pages (one record would create just one page)

As soon as the .dot file gets loaded the Macro forces it to perform said mail merge

I will see if I can paste up the code (it's pretty simple)

OR

I recently needed to jump around a Word file, modding every third page or so. For this I used DAO. You might want to use the same method to jump to the most recently created record and pull some data that way.

OR

For a quick fix, maybe think about using the Record Selector on the Access form to just dump data to the clipboard and get the user to paste it into a Word template

HTH

Rob.

[us-media]

Dim objWord

' create object
Set objWord = CreateObject("Word.Application")

' open word template
With objWord
.Visible = True
.Documents.Add CurrentProject.Path & "Test.dot"
End With

' grab a field from a form in access
strTest = Me.Text1 ' or whatever

' insert into word bookmark
With objWord.ActiveDocument.Bookmarks
.Item(test1).Range.Text = strTest
End With

' destroy object
Set objWord = Nothing

Rob.

[mhammond]

hmm was still stuck on it but finally worked it out, thanks for your help guys. This was the little tiny solution!

Screen.ActiveForm.LastName.SetFocus
DoCmd.RunCommand acCmdCopy