Hello,

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
.Dialogs(wdDialogFileSaveAs).Show
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)
rst.Close
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
.Activate
End With

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

ErrorHandler:
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 ;-)