Help! Access to Word data using FormFields - Based on Query

[roz]

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

[Anthony England]

"roz" <ro**@talk21.com> wrote in message
news:11********************@i40g2000cwc.googlegrou ps.com...

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

Well here's a push in the right direction. There is too much code in one
lump to expect someone to debug it. It involves both recordsets and Word
automation (with a strange mix of early and late binding).

At a first glance, you are trying to access the results of a recordset after
you've closed it. That would generate an error, but you don't mention that
any error is being generated. If the point of opening and closing a
recordset is to get a single value out (strReportsTo) then a simple DLookup
would be much simpler. If you really want to use a recordset, then split
that bit of code out into a separate function so you can pinpoint the error
more easily

[lesperancer]

assuming that "MMR_individual_data" is your query, you're not
retrieving the column "Forename" so it won't be in your rst recordset

you need to SELECT all the fields from the query that you need

and why are you including data from the form ?

roz wrote:

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