link to word document with info from database...clueless intern 1

[InternVirtuoso]

Hey,

I am an intern at a company creating database webpages.

My boss asked me to make a hyperlink on a website that automatically populates a microsoft word document(or another type of text document) with information from an Access 97 database.

Can anyone give me any advice on how to do this?

 

[JoyInOK]

Create a query in Access containing the data you want to see in the Word document. Set the Word document up as a mail merge document (there's a wizard for this-- Tools/Mail Merge). Set the datasource datatype to Access database, and browse to your query. The wizard will prompt you that there are no fields in your document; do you want to add them now? Say yes. Create the "shell" portion of the document -the part that's the same for every letter. A new toolbar should be present where you can "Insert Merge Field." Just place your cursor where you want the Merge Field to go and then insert it with this button.
Use your hyperlink or button to set the query used as the docuemnt's datasource to the specific record(s) in question.

 

[InternVirtuoso]

Thanks for the help so far. I am still a little confused as to how the hyperlink can open up the word document with the right information. Right now it just keeps opening up the first entry in the query. How can I set the query using the hyperlink?

 

[JoyInOK]

In a new module, place the following code. Note: you will need to set a reference to DAO if you don't already have one, and a reference to whatever version of Microsoft Word you have. From the code window, Tools/References

Public Sub SetQuery(strQueryName As String, strSQL As String)
'set the query from which the merge document will pull its info
Dim qdfNewQueryDef As QueryDef
Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
qdfNewQueryDef.Sql = strSQL
qdfNewQueryDef.Close
RefreshDatabaseWindow

End Sub
Public Sub OpenWordDoc(strDocName As String)
'Set the directory for any letters generated
Const strDir As String = "G:\Victims"
Dim gobjWord As Word.Application
Dim gobjDoc As Word.Document
strDocName = strDir & strDocName
'Open the letter in Word
On Error GoTo WordError

Set gobjWord = New Word.Application
gobjWord.Application.Visible = True
Set gobjDoc = gobjWord.Documents.Open(strDocName)
'Dim WordInstance As Object
'Make document visible for editing
gobjWord.Application.Visible = True
'reset the variables
Set gobjWord = Nothing
Set gobjDoc = Nothing
Exit Sub
WordError:
MsgBox "Err #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Word Error"
gobjWord.Quit

End Sub

A hyperlink will only open a document, you're right about that. Change the hyperlink to a label (you can even change its font and background settings to make it look like a hyperlink, if you want.
Call the SetQuery and OpenWordDoc code from the click event of the label. It will look something like this:

dim strSQL as string
strSQL = "SELECT * From MyTable WHERE MyTable.CustID = '" & NameOfForm.CustID & "'"
Call SetQuery ("NameOfMyQuery", strSQL)
Call OpenWordDoc("NameOfDocToOpen)