Merging joined data to MS Word

[phillyon]

I'm looking to print off a booking form based on the data in 3 linked tables by merging it in Word. Please if you know of a better way, let me know.

Bookings table contains summary, cost, breakdown of cost, booking id, client id
Clients table contains clientid, name, address, phone
Sessions table contains date, time, activity, booking id

So one client books many sessions through one booking.

I can make a query that can be easily merged to Word containing the boooking and client information, but how can i include the many sessions that comprise one booking?

My attempts so far have produced a new doc for each session, ie. too many.

It seems Word won't link to more than one query at a time, but I can't make one query return all the info per line that word would require?

help?

[maxamis4]

I am looking into your solution, i know it has been done, but never really had to do it my self. I have always been a fan of crystal reports or even the access reporting tool that comes with it. Have you tried creating a report in MS Access that looks like the word document? If it needs to be done in word, it is completely understandable.

let me know

I'm looking to print off a booking form based on the data in 3 linked tables by merging it in Word. Please if you know of a better way, let me know.

Bookings table contains summary, cost, breakdown of cost, booking id, client id
Clients table contains clientid, name, address, phone
Sessions table contains date, time, activity, booking id

So one client books many sessions through one booking.

I can make a query that can be easily merged to Word containing the boooking and client information, but how can i include the many sessions that comprise one booking?

My attempts so far have produced a new doc for each session, ie. too many.

It seems Word won't link to more than one query at a time, but I can't make one query return all the info per line that word would require?

help?

[maxamis4]

go there
[Link Removed]

I am looking into your solution, i know it has been done, but never really had to do it my self. I have always been a fan of crystal reports or even the access reporting tool that comes with it. Have you tried creating a report in MS Access that looks like the word document? If it needs to be done in word, it is completely understandable.

let me know

[MMcCarthy]

Sorry Maximis4 linking to competing forums is frowned on.

ADMIN

[phillyon]

Sorry Maximis4 linking to competing forums is frowned on.

ADMIN

That's all very well but by redirecting me to a solution someone else has written rather than writing it out again he's just using good db protocol and normalising the whole affair.

So I missed the link you posted, any other ideas?

[MMcCarthy]

That's all very well but by redirecting me to a solution someone else has written rather than writing it out again he's just using good db protocol and normalising the whole affair.

So I missed the link you posted, any other ideas?

Sorry I do understand. But I'm sure maximis4 will be able to help.

[MMcCarthy]

maximis4 the rule on this is...

You can copy in the solution and give full credit to the original poster but you can't provide a link to the site, sorry.

Mary

[maxamis4]

I apologize for the link. You will have to do this in 4 steps


FIRST CREATE MODULE, NAME IT ANYTHING YOU WANT. COPY THE CODE BELOW INTO IT

Function CreateIt()
 
 
Dim WordObj As Word.Application
Dim db As Database
Dim rs As New ADODB.Recordset
 
Set WordObj = CreateObject("Word.Application")
Set db = CurrentDb()
Dim ID, Criteria
 
With WordObj
.Visible = True
.Documents.Open ("C:\MyWord.doc") ' ** name and path for the document
 
rs.Open "Customers", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Criteria = "[CompanyName] = " & "'" & Forms!WordDemo!CompanyName & "'"
 
ID = DLookup("CustomerID", "Customers", Criteria)
 
rs.Find "[CustomerID]=" & "'" & ID & "'"
 
.ActiveDocument.Bookmarks("CoName").Select
.Selection.TypeText rs.Fields("CompanyName")
 
.ActiveDocument.Bookmarks("Address").Select
.Selection.TypeText rs.Fields("Address")
 
.ActiveDocument.Bookmarks("City").Select
.Selection.TypeText rs.Fields("City")
 
.ActiveDocument.SaveAs "C:\NewDoc.doc"
End With
 
rs.Close
Set rs = Nothing
Set WordObj = Nothing
End Function
 

Now before moving forward you need to understand that this works on the concept of bookmarks in word. Research those if you don't know how to use them. In your orginial document I recommend you create a template with bookmarks at ever insertion point where you want your document to have database data. For example like a mail merge create a bookmark called 'Last Name' which will create the document with a particular last name.

STEP 2 CREATE YOUR WORD TEMPLATE

STEP 3 CREATE A FORM WITH A BUTTON LIKE THE ONE LISTED BELOW

Private Sub MoveIt_Click()
On Error GoTo Err_MoveIt_Click
    Call CreateIt
 
Exit_MoveIt_Click:
    Exit Sub
 
Err_MoveIt_Click:
    MsgBox Err.Description
    Resume Exit_MoveIt_Click
 
End Sub
 

Now please under stand this will export any field you want. it all depends on queries that you write or the methods that you pass data.

STEP 4 MAKE SURE YOU HAVE THE MS WORD LIBRARIES PRESENT ON YOUR DATABASE.

you can finde the reference libraries under tools references in the code view. by default word should be in there, but i have seen times where it doesn't work.

I can email this example which I retrieved from Utter Access, but the rest of the development I leave in your hands.