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.Applicatio n
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.applicati on")
If Err = 429 Then
Set appWord = New Word.Applicatio n
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(wdDia logFileSaveAs). 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("f ldFlare_Ref").R esult = Nz(rst![Forename])
..FormFields("f ldName").Result = Nz(Me!Forename & " " & Me!Surname)
..FormFields("f ldDOB").Result = Nz(Me![Date of Birth])
..FormFields("f ldonset_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]

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

"roz" <rozy@talk21.co m> wrote in message
news:1143798983 .152983.7420@i4 0g2000cwc.googl egroups.com...[color=blue]
> 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.Applicatio n
> 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.applicati on")
> If Err = 429 Then
> Set appWord = New Word.Applicatio n
> 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(wdDial ogFileSaveAs).S how
> 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("fl dFlare_Ref").Re sult = Nz(rst![Forename])
> .FormFields("fl dName").Result = Nz(Me!Forename & " " & Me!Surname)
> .FormFields("fl dDOB").Result = Nz(Me![Date of Birth])
> .FormFields("fl donset_Date").R esult = 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 ;-)[/color]


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@natpro.com]

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

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:[color=blue]
> 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.Applicatio n
> 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.applicati on")
> If Err = 429 Then
> Set appWord = New Word.Applicatio n
> 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(wdDial ogFileSaveAs).S how
> 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("fl dFlare_Ref").Re sult = Nz(rst![Forename])
> .FormFields("fl dName").Result = Nz(Me!Forename & " " & Me!Surname)
> .FormFields("fl dDOB").Result = Nz(Me![Date of Birth])
> .FormFields("fl donset_Date").R esult = 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 ;-)[/color]