Help! Access to Word data using FormFields - Based on Query (1 Viewer)




I'm fairly new to VB programming and I've been going round the bend with this problem, so any nudges or shoves in the right direction would be greatly appreciated.

I have a template form in Word. The information that I need to populate this form is from 2 tables in an Access 2002 database (patient details and GP details). Once the user has entered the patient and GP etc details, they need to produce a case summary (including both GP and patient details) in Word.

I have a SQL query that joins the fields I want from the 2 tables (herein lies my problem) but I cannot get Access to use this query as the basis to populate my Word form.

Here's the code for the button to prompt creation of the Form:

Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strReportsTo As String

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
Set appWord = New Word.Application
Err = 0
End If

With appWord
Set doc = .Documents(DOC_NAME)
If Err = 0 Then
If MsgBox("Do you want to save the current document " _
& "before updating the data?", vbYesNo) = vbYes Then
End If
doc.Close False
End If

On Error GoTo ErrorHandler

Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)
Set rst = New ADODB.Recordset

If Not IsNull(Me![ID]) Then
strSQL = "SELECT [forename] & "" "" & [surname] AS Name FROM " _
& "MMR_individual_data WHERE [id]=" & Nz(Me![ID])
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
If Not rst.EOF Then
strReportsTo = Nz(rst.Fields(0).Value)
End If
End If

With doc
.FormFields("fldFlare_Ref").Result = Nz(rst![Forename])
.FormFields("fldName").Result = Nz(Me!Forename & " " & Me!Surname)
.FormFields("fldDOB").Result = Nz(Me![Date of Birth])
.FormFields("fldonset_Date").Result = Nz(Me![Onset Date])

End With
.Visible = True
End With

Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub

MsgBox Err & Err.Description

End Sub

At the moment it only displays the patient details, and not the GP details - it's so frustrating! Any ideas? Thank you! Roz ;-)

Users who are viewing this thread

Top Bottom