J
jhs2rcv
Guest
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 ;-)
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 ;-)