|
|
I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
Server). I have a series of Queries that manipulate the data and populate
an ACCESS Form. This Form has the following features:
1. A Main Form, with several pages (Tabs) which display data from one
(OverallData) Table
a. One of these pages (Tabs) contains a Sub-Form which displays data
from another (SpecificData) Table
Here's my quandry ... I'm trying to display (in the WORD template) a field
from the Sub-Form -- how do I modify the code-syntax to accomplish this?
Example: .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which is
populated from a seperate table) -- what do I need to modify to accomplish
this?
I have the following code which I use to display the data (via Text Form
Fields) in my WORD document:
======================================
Option Compare Database
Option Explicit
Dim path As String
Const DOC_PATH1 As String = "\\Fileserver\Products\ "
Const DOC_NAME1 As String = _
"Products1.dot"
Const DOC_PATH2 As String = "\\Fileserver\Products\ "
Const DOC_NAME2 As String = _
" Products2.dot "
Const DOC_PATH3 As String = "\\Fileserver\Products\ "
Const DOC_NAME3 As String = _
" Products3.dot "
Private Sub AddPicture_Click()
' Use the Office File Open dialog to get a file name to use
' as an employee picture.
getFileName
End Sub
Private Sub cmdPrint Products1_Click()
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_NAME1)
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_PATH1 & DOC_NAME1, , True)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM PRODUCTS"
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
If Not rst.EOF Then
strReportsTo = Nz(rst.Fields(0).Value)
rst.Close
End If
With doc
.FormFields("fldCompanyName").result = Nz(Me!CompanyName)
.FormFields("fldAddress1").result = Nz(Me!Address1)
.FormFields("fldAddress2").result = Nz(Me!Address2)
.FormFields("fldCity").result = Nz(Me!City)
.FormFields("fldRegion").result = Nz(Me!Region)
.FormFields("fldPostalCode").result = Nz(Me!PostalCode)
.FormFields("fldProductName").result = Nz(Me!ProductName)
.FormFields("fldQty").result = Nz(Me!Qty)
.FormFields("fldPrice").result = Nz(Me!Price)
.FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
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
Private Sub cmdPrintProducts2 _Click()
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_NAME2)
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_PATH2 & DOC_NAME2, , True)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM PRODUCTS"
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
If Not rst.EOF Then
strReportsTo = Nz(rst.Fields(0).Value)
rst.Close
End If
With doc
.FormFields("fldCompanyName").result = Nz(Me!CompanyName)
.FormFields("fldAddress1").result = Nz(Me!Address1)
.FormFields("fldAddress2").result = Nz(Me!Address2)
.FormFields("fldCity").result = Nz(Me!City)
.FormFields("fldRegion").result = Nz(Me!Region)
.FormFields("fldPostalCode").result = Nz(Me!PostalCode)
.FormFields("fldProductName").result = Nz(Me!ProductName)
.FormFields("fldQty").result = Nz(Me!Qty)
.FormFields("fldPrice").result = Nz(Me!Price)
.FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
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
Private Sub cmdPrintProducts3_Click()
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_NAME3)
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_PATH3 & DOC_NAME3, , True)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM PRODUCTS"
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
If Not rst.EOF Then
strReportsTo = Nz(rst.Fields(0).Value)
rst.Close
End If
With doc
.FormFields("fldCompanyName").result = Nz(Me!CompanyName)
.FormFields("fldAddress1").result = Nz(Me!Address1)
.FormFields("fldAddress2").result = Nz(Me!Address2)
.FormFields("fldCity").result = Nz(Me!City)
.FormFields("fldRegion").result = Nz(Me!Region)
.FormFields("fldPostalCode").result = Nz(Me!PostalCode)
.FormFields("fldProductName").result = Nz(Me!ProductName)
.FormFields("fldQty").result = Nz(Me!Qty)
.FormFields("fldPrice").result = Nz(Me!Price)
.FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
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
================================================================
The REST of the code is deleted for ease-of-viewing
================================================================
Private Sub Form_RecordExit(Cancel As Integer)
' Hide the errormsg label to reduce flashing when navigating
' between records.
errormsg.Visible = False
End Sub
Private Sub RemovePicture_Click()
' Clear the file name for the employee record and display the
' errormsg label.
Me![ImagePath] = ""
hideImageFrame
errormsg.Visible = True
End Sub
Private Sub Form_AfterUpdate()
' Requery the ReportsTo combo box after a record has been changed.
' Then, either show the errormsg label if no file name exists for
' the employee record or display the image if there is a file name that
' exists.
'Me!ReportsTo.Requery
On Error Resume Next
showErrorMessage
showImageFrame
If (IsRelative(Me!ImagePath) = True) Then
Me![ImageFrame].Picture = path & Me![ImagePath]
Else
Me![ImageFrame].Picture = Me![ImagePath]
End If
End Sub
Private Sub ImagePath_AfterUpdate()
' After selecting an image for the employee, display it.
On Error Resume Next
showErrorMessage
showImageFrame
If (IsRelative(Me!ImagePath) = True) Then
Me![ImageFrame].Picture = path & Me![ImagePath]
Else
Me![ImageFrame].Picture = Me![ImagePath]
End If
End Sub
Private Sub Form_Current()
' Display the picture for the current employee record if the image
' exists. If the file name no longer exists or the file name was blank
' for the current employee, set the errormsg label caption to the
' appropriate message.
Dim res As Boolean
Dim fName As String
path = CurrentProject.path
On Error Resume Next
errormsg.Visible = False
If Not IsNull(Me!Photo) Then
res = IsRelative(Me!Photo)
fName = Me![ImagePath]
If (res = True) Then
fName = path & "\" & fName
End If
Me![ImageFrame].Picture = fName
showImageFrame
Me.PaintPalette = Me![ImageFrame].ObjectPalette
If (Me![ImageFrame].Picture <> fName) Then
hideImageFrame
errormsg.Caption = "Picture not found"
errormsg.Visible = True
End If
Else
hideImageFrame
errormsg.Caption = "Click Add/Change to add picture"
errormsg.Visible = True
End If
End Sub
Sub getFileName()
' Displays the Office File Open dialog to choose a file name
' for the current employee record. If the user selects a file
' display it in the image control.
Dim fileName As String
Dim result As Integer
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Employee Picture"
.Filters.Add "All Files", "*.*"
.Filters.Add "JPEGs", "*.jpg"
.Filters.Add "Bitmaps", "*.bmp"
.FilterIndex = 3
.AllowMultiSelect = False
.InitialFileName = CurrentProject.path
result = .Show
If (result <> 0) Then
fileName = Trim(.SelectedItems.Item(1))
Me![ImagePath].Visible = True
Me![ImagePath].SetFocus
Me![ImagePath].Text = fileName
Me![FirstName].SetFocus
Me![ImagePath].Visible = False
End If
End With
End Sub
Sub showErrorMessage()
' Display the errormsg label if the image file is not available.
If Not IsNull(Me!Photo) Then
errormsg.Visible = False
Else
errormsg.Visible = True
End If
End Sub
Function IsRelative(fName As String) As Boolean
' Return false if the file name contains a drive or UNC path
IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") = 0)
End Function
Sub hideImageFrame()
' Hide the image control
Me![ImageFrame].Visible = False
End Sub
Sub showImageFrame()
' Display the image control
Me![ImageFrame].Visible = True
End Sub
|
|
What happens when you run your code now?
If you put a
MsgBox Nz(Me!DeliveryFee)
command in your code, what does it display?
Also, what does MsgBox Me!GrossPurchaseTotal display?
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
"Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> wrote in message
news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] >I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
> Server). I have a series of Queries that manipulate the data and populate
> an ACCESS Form. This Form has the following features:
>
> 1. A Main Form, with several pages (Tabs) which display data from one
> (OverallData) Table
> a. One of these pages (Tabs) contains a Sub-Form which displays data
> from another (SpecificData) Table
>
> Here's my quandry ... I'm trying to display (in the WORD template) a field
> from the Sub-Form -- how do I modify the code-syntax to accomplish this?
> Example: .FormFields("fldDeliveryFee").result =
> Nz(Me!DeliveryFee)
>
> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which is
> populated from a seperate table) -- what do I need to modify to accomplish
> this?
>
>
>
> I have the following code which I use to display the data (via Text Form
> Fields) in my WORD document:
> ======================================
> Option Compare Database
> Option Explicit
>
> Dim path As String
>
> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>
> Const DOC_NAME1 As String = _
>
> "Products1.dot"
>
> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>
> Const DOC_NAME2 As String = _
>
> " Products2.dot "
>
> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>
> Const DOC_NAME3 As String = _
>
> " Products3.dot "
>
> Private Sub AddPicture_Click()
>
> ' Use the Office File Open dialog to get a file name to use
>
> ' as an employee picture.
>
> getFileName
>
> End Sub
>
> Private Sub cmdPrint Products1_Click()
>
> 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_NAME1)
>
> 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_PATH1 & DOC_NAME1, , True)
>
> Set rst = New ADODB.Recordset
>
>
>
> strSQL = "SELECT * FROM PRODUCTS"
>
> rst.Open strSQL, CurrentProject.Connection, _
>
> adOpenStatic, adLockReadOnly
>
> If Not rst.EOF Then
>
> strReportsTo = Nz(rst.Fields(0).Value)
>
> rst.Close
>
> End If
>
>
>
> With doc
>
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>
> .FormFields("fldAddress1").result = Nz(Me!Address1)
>
> .FormFields("fldAddress2").result = Nz(Me!Address2)
>
> .FormFields("fldCity").result = Nz(Me!City)
>
> .FormFields("fldRegion").result = Nz(Me!Region)
>
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>
> .FormFields("fldProductName").result = Nz(Me!ProductName)
>
> .FormFields("fldQty").result = Nz(Me!Qty)
>
> .FormFields("fldPrice").result = Nz(Me!Price)
>
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>
>
>
> 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
>
>
>
> Private Sub cmdPrintProducts2 _Click()
>
> 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_NAME2)
>
> 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_PATH2 & DOC_NAME2, , True)
>
> Set rst = New ADODB.Recordset
>
>
>
> strSQL = "SELECT * FROM PRODUCTS"
>
> rst.Open strSQL, CurrentProject.Connection, _
>
> adOpenStatic, adLockReadOnly
>
> If Not rst.EOF Then
>
> strReportsTo = Nz(rst.Fields(0).Value)
>
> rst.Close
>
> End If
>
>
>
> With doc
>
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>
> .FormFields("fldAddress1").result = Nz(Me!Address1)
>
> .FormFields("fldAddress2").result = Nz(Me!Address2)
>
> .FormFields("fldCity").result = Nz(Me!City)
>
> .FormFields("fldRegion").result = Nz(Me!Region)
>
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>
> .FormFields("fldProductName").result = Nz(Me!ProductName)
>
> .FormFields("fldQty").result = Nz(Me!Qty)
>
> .FormFields("fldPrice").result = Nz(Me!Price)
>
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>
> 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
>
>
>
>
>
> Private Sub cmdPrintProducts3_Click()
>
> 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_NAME3)
>
> 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_PATH3 & DOC_NAME3, , True)
>
> Set rst = New ADODB.Recordset
>
>
>
>
>
> strSQL = "SELECT * FROM PRODUCTS"
>
> rst.Open strSQL, CurrentProject.Connection, _
>
> adOpenStatic, adLockReadOnly
>
> If Not rst.EOF Then
>
> strReportsTo = Nz(rst.Fields(0).Value)
>
> rst.Close
>
> End If
>
>
>
> With doc
>
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>
> .FormFields("fldAddress1").result = Nz(Me!Address1)
>
> .FormFields("fldAddress2").result = Nz(Me!Address2)
>
> .FormFields("fldCity").result = Nz(Me!City)
>
> .FormFields("fldRegion").result = Nz(Me!Region)
>
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>
> .FormFields("fldProductName").result = Nz(Me!ProductName)
>
> .FormFields("fldQty").result = Nz(Me!Qty)
>
> .FormFields("fldPrice").result = Nz(Me!Price)
>
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>
> 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
>
> ================================================================
>
> The REST of the code is deleted for ease-of-viewing
>
> ================================================================
>
>
>
> Private Sub Form_RecordExit(Cancel As Integer)
>
> ' Hide the errormsg label to reduce flashing when navigating
>
> ' between records.
>
> errormsg.Visible = False
>
> End Sub
>
> Private Sub RemovePicture_Click()
>
> ' Clear the file name for the employee record and display the
>
> ' errormsg label.
>
> Me![ImagePath] = ""
>
> hideImageFrame
>
> errormsg.Visible = True
>
> End Sub
>
>
>
> Private Sub Form_AfterUpdate()
>
> ' Requery the ReportsTo combo box after a record has been changed.
>
> ' Then, either show the errormsg label if no file name exists for
>
> ' the employee record or display the image if there is a file name that
>
> ' exists.
>
> 'Me!ReportsTo.Requery
>
> On Error Resume Next
>
> showErrorMessage
>
> showImageFrame
>
> If (IsRelative(Me!ImagePath) = True) Then
>
> Me![ImageFrame].Picture = path & Me![ImagePath]
>
> Else
>
> Me![ImageFrame].Picture = Me![ImagePath]
>
> End If
>
> End Sub
>
>
>
> Private Sub ImagePath_AfterUpdate()
>
> ' After selecting an image for the employee, display it.
>
> On Error Resume Next
>
> showErrorMessage
>
> showImageFrame
>
> If (IsRelative(Me!ImagePath) = True) Then
>
> Me![ImageFrame].Picture = path & Me![ImagePath]
>
> Else
>
> Me![ImageFrame].Picture = Me![ImagePath]
>
> End If
>
> End Sub
>
> Private Sub Form_Current()
>
> ' Display the picture for the current employee record if the image
>
> ' exists. If the file name no longer exists or the file name was blank
>
> ' for the current employee, set the errormsg label caption to the
>
> ' appropriate message.
>
> Dim res As Boolean
>
> Dim fName As String
>
>
>
> path = CurrentProject.path
>
> On Error Resume Next
>
> errormsg.Visible = False
>
> If Not IsNull(Me!Photo) Then
>
> res = IsRelative(Me!Photo)
>
> fName = Me![ImagePath]
>
> If (res = True) Then
>
> fName = path & "\" & fName
>
> End If
>
>
>
> Me![ImageFrame].Picture = fName
>
> showImageFrame
>
> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>
> If (Me![ImageFrame].Picture <> fName) Then
>
> hideImageFrame
>
> errormsg.Caption = "Picture not found"
>
> errormsg.Visible = True
>
> End If
>
> Else
>
> hideImageFrame
>
> errormsg.Caption = "Click Add/Change to add picture"
>
> errormsg.Visible = True
>
> End If
>
>
>
> End Sub
>
>
>
> Sub getFileName()
>
> ' Displays the Office File Open dialog to choose a file name
>
> ' for the current employee record. If the user selects a file
>
> ' display it in the image control.
>
> Dim fileName As String
>
> Dim result As Integer
>
> With Application.FileDialog(msoFileDialogFilePicker)
>
> .Title = "Select Employee Picture"
>
> .Filters.Add "All Files", "*.*"
>
> .Filters.Add "JPEGs", "*.jpg"
>
> .Filters.Add "Bitmaps", "*.bmp"
>
> .FilterIndex = 3
>
> .AllowMultiSelect = False
>
> .InitialFileName = CurrentProject.path
>
> result = .Show
>
> If (result <> 0) Then
>
> fileName = Trim(.SelectedItems.Item(1))
>
> Me![ImagePath].Visible = True
>
> Me![ImagePath].SetFocus
>
> Me![ImagePath].Text = fileName
>
> Me![FirstName].SetFocus
>
> Me![ImagePath].Visible = False
>
> End If
>
> End With
>
> End Sub
>
>
>
> Sub showErrorMessage()
>
> ' Display the errormsg label if the image file is not available.
>
> If Not IsNull(Me!Photo) Then
>
> errormsg.Visible = False
>
> Else
>
> errormsg.Visible = True
>
> End If
>
> End Sub
>
>
>
> Function IsRelative(fName As String) As Boolean
>
> ' Return false if the file name contains a drive or UNC path
>
> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") = 0)
>
> End Function
>
>
>
> Sub hideImageFrame()
>
> ' Hide the image control
>
> Me![ImageFrame].Visible = False
>
> End Sub
>
>
>
> Sub showImageFrame()
>
> ' Display the image control
>
> Me![ImageFrame].Visible = True
>
> End Sub
>
>
|
|
Couple of things here:
Y're trying to pick up values from an Access form, ok?
No need to build up a seperate recordset to to populate "strReportsTo"
variable.
Try to see whether there's a field (control) in your mainform that contains
this information.
Then use the value of this control to populate (and maintain) the variable.
Next:
I presume there's a button on your mainform to wire the current/selected
record
(form and subform-data) to MS Word.
Correct?
So basically it comes down to the user "printing" the Access form (and
subform) data to a Word document by hitting that button.
Correct?
In doing this, the field "GrossPurchaseTotal" (present on one of your other
subforms) has to transfered to
Word as well.
Correct?
The last thing can be done by using something like (in the Button_Click
event code):
(whereby "MyOtherSubformName" is the name of the subform where
"GrossPurchaseTotal" is hosted)
doc.FormFields("GrossPurchaseTotal").result =
Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
--
Krgrds,
Perry
System:
Vista/Office Ultimate
VS2005/VSTO2005 SE
"Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] >I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
> Server). I have a series of Queries that manipulate the data and populate
> an ACCESS Form. This Form has the following features:
>
> 1. A Main Form, with several pages (Tabs) which display data from one
> (OverallData) Table
> a. One of these pages (Tabs) contains a Sub-Form which displays data
> from another (SpecificData) Table
>
> Here's my quandry ... I'm trying to display (in the WORD template) a field
> from the Sub-Form -- how do I modify the code-syntax to accomplish this?
> Example: .FormFields("fldDeliveryFee").result =
> Nz(Me!DeliveryFee)
>
> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which is
> populated from a seperate table) -- what do I need to modify to accomplish
> this?
>
>
>
> I have the following code which I use to display the data (via Text Form
> Fields) in my WORD document:
> ======================================
> Option Compare Database
> Option Explicit
>
> Dim path As String
>
> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>
> Const DOC_NAME1 As String = _
>
> "Products1.dot"
>
> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>
> Const DOC_NAME2 As String = _
>
> " Products2.dot "
>
> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>
> Const DOC_NAME3 As String = _
>
> " Products3.dot "
>
> Private Sub AddPicture_Click()
>
> ' Use the Office File Open dialog to get a file name to use
>
> ' as an employee picture.
>
> getFileName
>
> End Sub
>
> Private Sub cmdPrint Products1_Click()
>
> 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_NAME1)
>
> 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_PATH1 & DOC_NAME1, , True)
>
> Set rst = New ADODB.Recordset
>
>
>
> strSQL = "SELECT * FROM PRODUCTS"
>
> rst.Open strSQL, CurrentProject.Connection, _
>
> adOpenStatic, adLockReadOnly
>
> If Not rst.EOF Then
>
> strReportsTo = Nz(rst.Fields(0).Value)
>
> rst.Close
>
> End If
>
>
>
> With doc
>
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>
> .FormFields("fldAddress1").result = Nz(Me!Address1)
>
> .FormFields("fldAddress2").result = Nz(Me!Address2)
>
> .FormFields("fldCity").result = Nz(Me!City)
>
> .FormFields("fldRegion").result = Nz(Me!Region)
>
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>
> .FormFields("fldProductName").result = Nz(Me!ProductName)
>
> .FormFields("fldQty").result = Nz(Me!Qty)
>
> .FormFields("fldPrice").result = Nz(Me!Price)
>
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>
>
>
> 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
>
>
>
> Private Sub cmdPrintProducts2 _Click()
>
> 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_NAME2)
>
> 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_PATH2 & DOC_NAME2, , True)
>
> Set rst = New ADODB.Recordset
>
>
>
> strSQL = "SELECT * FROM PRODUCTS"
>
> rst.Open strSQL, CurrentProject.Connection, _
>
> adOpenStatic, adLockReadOnly
>
> If Not rst.EOF Then
>
> strReportsTo = Nz(rst.Fields(0).Value)
>
> rst.Close
>
> End If
>
>
>
> With doc
>
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>
> .FormFields("fldAddress1").result = Nz(Me!Address1)
>
> .FormFields("fldAddress2").result = Nz(Me!Address2)
>
> .FormFields("fldCity").result = Nz(Me!City)
>
> .FormFields("fldRegion").result = Nz(Me!Region)
>
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>
> .FormFields("fldProductName").result = Nz(Me!ProductName)
>
> .FormFields("fldQty").result = Nz(Me!Qty)
>
> .FormFields("fldPrice").result = Nz(Me!Price)
>
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>
> 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
>
>
>
>
>
> Private Sub cmdPrintProducts3_Click()
>
> 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_NAME3)
>
> 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_PATH3 & DOC_NAME3, , True)
>
> Set rst = New ADODB.Recordset
>
>
>
>
>
> strSQL = "SELECT * FROM PRODUCTS"
>
> rst.Open strSQL, CurrentProject.Connection, _
>
> adOpenStatic, adLockReadOnly
>
> If Not rst.EOF Then
>
> strReportsTo = Nz(rst.Fields(0).Value)
>
> rst.Close
>
> End If
>
>
>
> With doc
>
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>
> .FormFields("fldAddress1").result = Nz(Me!Address1)
>
> .FormFields("fldAddress2").result = Nz(Me!Address2)
>
> .FormFields("fldCity").result = Nz(Me!City)
>
> .FormFields("fldRegion").result = Nz(Me!Region)
>
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>
> .FormFields("fldProductName").result = Nz(Me!ProductName)
>
> .FormFields("fldQty").result = Nz(Me!Qty)
>
> .FormFields("fldPrice").result = Nz(Me!Price)
>
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>
> 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
>
> ================================================================
>
> The REST of the code is deleted for ease-of-viewing
>
> ================================================================
>
>
>
> Private Sub Form_RecordExit(Cancel As Integer)
>
> ' Hide the errormsg label to reduce flashing when navigating
>
> ' between records.
>
> errormsg.Visible = False
>
> End Sub
>
> Private Sub RemovePicture_Click()
>
> ' Clear the file name for the employee record and display the
>
> ' errormsg label.
>
> Me![ImagePath] = ""
>
> hideImageFrame
>
> errormsg.Visible = True
>
> End Sub
>
>
>
> Private Sub Form_AfterUpdate()
>
> ' Requery the ReportsTo combo box after a record has been changed.
>
> ' Then, either show the errormsg label if no file name exists for
>
> ' the employee record or display the image if there is a file name that
>
> ' exists.
>
> 'Me!ReportsTo.Requery
>
> On Error Resume Next
>
> showErrorMessage
>
> showImageFrame
>
> If (IsRelative(Me!ImagePath) = True) Then
>
> Me![ImageFrame].Picture = path & Me![ImagePath]
>
> Else
>
> Me![ImageFrame].Picture = Me![ImagePath]
>
> End If
>
> End Sub
>
>
>
> Private Sub ImagePath_AfterUpdate()
>
> ' After selecting an image for the employee, display it.
>
> On Error Resume Next
>
> showErrorMessage
>
> showImageFrame
>
> If (IsRelative(Me!ImagePath) = True) Then
>
> Me![ImageFrame].Picture = path & Me![ImagePath]
>
> Else
>
> Me![ImageFrame].Picture = Me![ImagePath]
>
> End If
>
> End Sub
>
> Private Sub Form_Current()
>
> ' Display the picture for the current employee record if the image
>
> ' exists. If the file name no longer exists or the file name was blank
>
> ' for the current employee, set the errormsg label caption to the
>
> ' appropriate message.
>
> Dim res As Boolean
>
> Dim fName As String
>
>
>
> path = CurrentProject.path
>
> On Error Resume Next
>
> errormsg.Visible = False
>
> If Not IsNull(Me!Photo) Then
>
> res = IsRelative(Me!Photo)
>
> fName = Me![ImagePath]
>
> If (res = True) Then
>
> fName = path & "\" & fName
>
> End If
>
>
>
> Me![ImageFrame].Picture = fName
>
> showImageFrame
>
> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>
> If (Me![ImageFrame].Picture <> fName) Then
>
> hideImageFrame
>
> errormsg.Caption = "Picture not found"
>
> errormsg.Visible = True
>
> End If
>
> Else
>
> hideImageFrame
>
> errormsg.Caption = "Click Add/Change to add picture"
>
> errormsg.Visible = True
>
> End If
>
>
>
> End Sub
>
>
>
> Sub getFileName()
>
> ' Displays the Office File Open dialog to choose a file name
>
> ' for the current employee record. If the user selects a file
>
> ' display it in the image control.
>
> Dim fileName As String
>
> Dim result As Integer
>
> With Application.FileDialog(msoFileDialogFilePicker)
>
> .Title = "Select Employee Picture"
>
> .Filters.Add "All Files", "*.*"
>
> .Filters.Add "JPEGs", "*.jpg"
>
> .Filters.Add "Bitmaps", "*.bmp"
>
> .FilterIndex = 3
>
> .AllowMultiSelect = False
>
> .InitialFileName = CurrentProject.path
>
> result = .Show
>
> If (result <> 0) Then
>
> fileName = Trim(.SelectedItems.Item(1))
>
> Me![ImagePath].Visible = True
>
> Me![ImagePath].SetFocus
>
> Me![ImagePath].Text = fileName
>
> Me![FirstName].SetFocus
>
> Me![ImagePath].Visible = False
>
> End If
>
> End With
>
> End Sub
>
>
>
> Sub showErrorMessage()
>
> ' Display the errormsg label if the image file is not available.
>
> If Not IsNull(Me!Photo) Then
>
> errormsg.Visible = False
>
> Else
>
> errormsg.Visible = True
>
> End If
>
> End Sub
>
>
>
> Function IsRelative(fName As String) As Boolean
>
> ' Return false if the file name contains a drive or UNC path
>
> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") = 0)
>
> End Function
>
>
>
> Sub hideImageFrame()
>
> ' Hide the image control
>
> Me![ImageFrame].Visible = False
>
> End Sub
>
>
>
> Sub showImageFrame()
>
> ' Display the image control
>
> Me![ImageFrame].Visible = True
>
> End Sub
>
>
|
|
Perry,
I feel that I've done a poor job in describing my project .
RE:
Couple of things here:
Y're trying to pick up values from an Access form, ok?
Yes: I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
Server). I have a series of Queries that manipulate the data and populate
an ACCESS Form. This Form has several pages/tabs on it, and one page/tab
incorporates a sub-form.
n Here's my quandry ... I'm trying to display (in a WORD template)
several fields from the Main Form (which is a Single Form), and all data
displayed in the sub-form (which is an Continuous Form linked to the
main-form by the ProductID field)
No need to build up a seperate recordset to to populate "strReportsTo"
variable.
This was some residual code left over from the Original Code that I'd
modified
Next:
I presume there's a button on your mainform to wire the current/selected
record (form and subform-data) to MS Word.
Correct?
Yes
So basically it comes down to the user "printing" the Access form (and
subform) data to a Word document by hitting that button.
Correct?
Yes
In doing this, the field "GrossPurchaseTotal" (present on one of your other
subforms) has to transfered to Word as well. Correct?
Yes
The last thing can be done by using something like (in the Button_Click
event code):
(whereby "MyOtherSubformName" is the name of the subform where
"GrossPurchaseTotal" is hosted)
doc.FormFields("GrossPurchaseTotal").result =
Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
I have the following code which I use to display the data (via Text Form
Fields) in my WORD document (**SEE EXPLANATORY NOTES**):
Option Compare Database
Option Explicit
Dim path As String
Const DOC_PATH1 As String = "\\Fileserver\Products\ "
Const DOC_NAME1 As String = _
"Products1.dot"
Const DOC_PATH2 As String = "\\Fileserver\Products\ "
Const DOC_NAME2 As String = _
" Products2.dot "
Const DOC_PATH3 As String = "\\Fileserver\Products\ "
Const DOC_NAME3 As String = _
" Products3.dot "
Private Sub AddPicture_Click()
' Use the Office File Open dialog to get a file name to use
' as an employee picture.
getFileName
End Sub
Private Sub cmdPrint Products1_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As ADODB.Recordset
Dim strSQL 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_NAME1)
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_PATH1 & DOC_NAME1, , True)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM PRODUCTS"
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
With doc
.FormFields("fldCompanyName").result = Nz(Me!CompanyName) (**THIS
FIELD POPULATES IN WORD**)
.FormFields("fldAddress1").result = Nz(Me!Address1) (**THIS FIELD
POPULATES IN WORD**)
.FormFields("fldAddress2").result = Nz(Me!Address2) (**THIS FIELD
POPULATES IN WORD**)
.FormFields("fldCity").result = Nz(Me!City) (**THIS FIELD POPULATES
IN WORD**)
.FormFields("fldRegion").result = Nz(Me!Region) (**THIS FIELD
POPULATES IN WORD**)
.FormFields("fldPostalCode").result = Nz(Me!PostalCode) (**THIS
FIELD POPULATES IN WORD**)
.FormFields("fldProductName").result = Nz(Me!ProductName) (**THIS
FIELD POPULATES IN WORD**)
.FormFields("fldQty").result = Nz(Me!Qty) (**THIS FIELD POPULATES IN
WORD**)
.FormFields("fldPrice").result = Nz(Me!Price) (**THIS FIELD
POPULATES IN WORD**)
.FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee) (**THIS
FIELD POPULATES IN WORD**)
(**This Last Line (below) - your suggested code - fails to populate, &
renders the following error message**)
.FormFields("fldGrossPurchaseTotal").result =
Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
(**2465Microsoft Office Access can't find the field 'PurchaseHistory'
referred to in your expression**)
**No Data shows in the "fldGrossPurchasTotal" Text Form Field in the WORD
document**
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
Private Sub cmdPrintProducts2 _Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As ADODB.Recordset
Dim strSQL 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_NAME2)
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_PATH2 & DOC_NAME2, , True)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM PRODUCTS"
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
With doc
.FormFields("fldCompanyName").result = Nz(Me!CompanyName)
.FormFields("fldAddress1").result = Nz(Me!Address1)
.FormFields("fldAddress2").result = Nz(Me!Address2)
.FormFields("fldCity").result = Nz(Me!City)
.FormFields("fldRegion").result = Nz(Me!Region)
.FormFields("fldPostalCode").result = Nz(Me!PostalCode)
.FormFields("fldProductName").result = Nz(Me!ProductName)
.FormFields("fldQty").result = Nz(Me!Qty)
.FormFields("fldPrice").result = Nz(Me!Price)
.FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
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
Private Sub cmdPrintProducts3_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As ADODB.Recordset
Dim strSQL 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_NAME3)
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_PATH3 & DOC_NAME3, , True)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM PRODUCTS"
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
With doc
.FormFields("fldCompanyName").result = Nz(Me!CompanyName)
.FormFields("fldAddress1").result = Nz(Me!Address1)
.FormFields("fldAddress2").result = Nz(Me!Address2)
.FormFields("fldCity").result = Nz(Me!City)
.FormFields("fldRegion").result = Nz(Me!Region)
.FormFields("fldPostalCode").result = Nz(Me!PostalCode)
.FormFields("fldProductName").result = Nz(Me!ProductName)
.FormFields("fldQty").result = Nz(Me!Qty)
.FormFields("fldPrice").result = Nz(Me!Price)
.FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
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
"Perry" <drumper[ at ]gmail.com> wrote in message
news:%233K5PKjkHHA.4112[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Couple of things here:
>
> Y're trying to pick up values from an Access form, ok?
> No need to build up a seperate recordset to to populate "strReportsTo"
> variable.
> Try to see whether there's a field (control) in your mainform that
> contains this information.
> Then use the value of this control to populate (and maintain) the
> variable.
>
> Next:
> I presume there's a button on your mainform to wire the current/selected
> record
> (form and subform-data) to MS Word.
> Correct?
> So basically it comes down to the user "printing" the Access form (and
> subform) data to a Word document by hitting that button.
> Correct?
>
> In doing this, the field "GrossPurchaseTotal" (present on one of your
> other subforms) has to transfered to
> Word as well.
> Correct?
> The last thing can be done by using something like (in the Button_Click
> event code):
> (whereby "MyOtherSubformName" is the name of the subform where
> "GrossPurchaseTotal" is hosted)
>
> doc.FormFields("GrossPurchaseTotal").result =
> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>
> --
> Krgrds,
> Perry
>
> System:
> Vista/Office Ultimate
> VS2005/VSTO2005 SE
>
>
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
> news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
>>I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
>> Server). I have a series of Queries that manipulate the data and
>> populate
>> an ACCESS Form. This Form has the following features:
>>
>> 1. A Main Form, with several pages (Tabs) which display data from one
>> (OverallData) Table
>> a. One of these pages (Tabs) contains a Sub-Form which displays data
>> from another (SpecificData) Table
>>
>> Here's my quandry ... I'm trying to display (in the WORD template) a
>> field
>> from the Sub-Form -- how do I modify the code-syntax to accomplish this?
>> Example: .FormFields("fldDeliveryFee").result =
>> Nz(Me!DeliveryFee)
>>
>> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which is
>> populated from a seperate table) -- what do I need to modify to
>> accomplish
>> this?
>>
>>
>>
>> I have the following code which I use to display the data (via Text Form
>> Fields) in my WORD document:
>> ======================================
>> Option Compare Database
>> Option Explicit
>>
>> Dim path As String
>>
>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>
>> Const DOC_NAME1 As String = _
>>
>> "Products1.dot"
>>
>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>
>> Const DOC_NAME2 As String = _
>>
>> " Products2.dot "
>>
>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>
>> Const DOC_NAME3 As String = _
>>
>> " Products3.dot "
>>
>> Private Sub AddPicture_Click()
>>
>> ' Use the Office File Open dialog to get a file name to use
>>
>> ' as an employee picture.
>>
>> getFileName
>>
>> End Sub
>>
>> Private Sub cmdPrint Products1_Click()
>>
>> 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_NAME1)
>>
>> 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_PATH1 & DOC_NAME1, , True)
>>
>> Set rst = New ADODB.Recordset
>>
>>
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>>
>> rst.Open strSQL, CurrentProject.Connection, _
>>
>> adOpenStatic, adLockReadOnly
>>
>> If Not rst.EOF Then
>>
>> strReportsTo = Nz(rst.Fields(0).Value)
>>
>> rst.Close
>>
>> End If
>>
>>
>>
>> With doc
>>
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>
>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>
>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>
>> .FormFields("fldCity").result = Nz(Me!City)
>>
>> .FormFields("fldRegion").result = Nz(Me!Region)
>>
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>
>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>
>> .FormFields("fldQty").result = Nz(Me!Qty)
>>
>> .FormFields("fldPrice").result = Nz(Me!Price)
>>
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>
>>
>>
>> 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
>>
>>
>>
>> Private Sub cmdPrintProducts2 _Click()
>>
>> 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_NAME2)
>>
>> 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_PATH2 & DOC_NAME2, , True)
>>
>> Set rst = New ADODB.Recordset
>>
>>
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>>
>> rst.Open strSQL, CurrentProject.Connection, _
>>
>> adOpenStatic, adLockReadOnly
>>
>> If Not rst.EOF Then
>>
>> strReportsTo = Nz(rst.Fields(0).Value)
>>
>> rst.Close
>>
>> End If
>>
>>
>>
>> With doc
>>
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>
>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>
>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>
>> .FormFields("fldCity").result = Nz(Me!City)
>>
>> .FormFields("fldRegion").result = Nz(Me!Region)
>>
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>
>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>
>> .FormFields("fldQty").result = Nz(Me!Qty)
>>
>> .FormFields("fldPrice").result = Nz(Me!Price)
>>
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>
>> 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
>>
>>
>>
>>
>>
>> Private Sub cmdPrintProducts3_Click()
>>
>> 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_NAME3)
>>
>> 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_PATH3 & DOC_NAME3, , True)
>>
>> Set rst = New ADODB.Recordset
>>
>>
>>
>>
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>>
>> rst.Open strSQL, CurrentProject.Connection, _
>>
>> adOpenStatic, adLockReadOnly
>>
>> If Not rst.EOF Then
>>
>> strReportsTo = Nz(rst.Fields(0).Value)
>>
>> rst.Close
>>
>> End If
>>
>>
>>
>> With doc
>>
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>
>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>
>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>
>> .FormFields("fldCity").result = Nz(Me!City)
>>
>> .FormFields("fldRegion").result = Nz(Me!Region)
>>
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>
>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>
>> .FormFields("fldQty").result = Nz(Me!Qty)
>>
>> .FormFields("fldPrice").result = Nz(Me!Price)
>>
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>
>> 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
>>
>> ================================================================
>>
>> The REST of the code is deleted for ease-of-viewing
>>
>> ================================================================
>>
>>
>>
>> Private Sub Form_RecordExit(Cancel As Integer)
>>
>> ' Hide the errormsg label to reduce flashing when navigating
>>
>> ' between records.
>>
>> errormsg.Visible = False
>>
>> End Sub
>>
>> Private Sub RemovePicture_Click()
>>
>> ' Clear the file name for the employee record and display the
>>
>> ' errormsg label.
>>
>> Me![ImagePath] = ""
>>
>> hideImageFrame
>>
>> errormsg.Visible = True
>>
>> End Sub
>>
>>
>>
>> Private Sub Form_AfterUpdate()
>>
>> ' Requery the ReportsTo combo box after a record has been changed.
>>
>> ' Then, either show the errormsg label if no file name exists for
>>
>> ' the employee record or display the image if there is a file name
>> that
>>
>> ' exists.
>>
>> 'Me!ReportsTo.Requery
>>
>> On Error Resume Next
>>
>> showErrorMessage
>>
>> showImageFrame
>>
>> If (IsRelative(Me!ImagePath) = True) Then
>>
>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>
>> Else
>>
>> Me![ImageFrame].Picture = Me![ImagePath]
>>
>> End If
>>
>> End Sub
>>
>>
>>
>> Private Sub ImagePath_AfterUpdate()
>>
>> ' After selecting an image for the employee, display it.
>>
>> On Error Resume Next
>>
>> showErrorMessage
>>
>> showImageFrame
>>
>> If (IsRelative(Me!ImagePath) = True) Then
>>
>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>
>> Else
>>
>> Me![ImageFrame].Picture = Me![ImagePath]
>>
>> End If
>>
>> End Sub
>>
>> Private Sub Form_Current()
>>
>> ' Display the picture for the current employee record if the image
>>
>> ' exists. If the file name no longer exists or the file name was
>> blank
>>
>> ' for the current employee, set the errormsg label caption to the
>>
>> ' appropriate message.
>>
>> Dim res As Boolean
>>
>> Dim fName As String
>>
>>
>>
>> path = CurrentProject.path
>>
>> On Error Resume Next
>>
>> errormsg.Visible = False
>>
>> If Not IsNull(Me!Photo) Then
>>
>> res = IsRelative(Me!Photo)
>>
>> fName = Me![ImagePath]
>>
>> If (res = True) Then
>>
>> fName = path & "\" & fName
>>
>> End If
>>
>>
>>
>> Me![ImageFrame].Picture = fName
>>
>> showImageFrame
>>
>> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>>
>> If (Me![ImageFrame].Picture <> fName) Then
>>
>> hideImageFrame
>>
>> errormsg.Caption = "Picture not found"
>>
>> errormsg.Visible = True
>>
>> End If
>>
>> Else
>>
>> hideImageFrame
>>
>> errormsg.Caption = "Click Add/Change to add picture"
>>
>> errormsg.Visible = True
>>
>> End If
>>
>>
>>
>> End Sub
>>
>>
>>
>> Sub getFileName()
>>
>> ' Displays the Office File Open dialog to choose a file name
>>
>> ' for the current employee record. If the user selects a file
>>
>> ' display it in the image control.
>>
>> Dim fileName As String
>>
>> Dim result As Integer
>>
>> With Application.FileDialog(msoFileDialogFilePicker)
>>
>> .Title = "Select Employee Picture"
>>
>> .Filters.Add "All Files", "*.*"
>>
>> .Filters.Add "JPEGs", "*.jpg"
>>
>> .Filters.Add "Bitmaps", "*.bmp"
>>
>> .FilterIndex = 3
>>
>> .AllowMultiSelect = False
>>
>> .InitialFileName = CurrentProject.path
>>
>> result = .Show
>>
>> If (result <> 0) Then
>>
>> fileName = Trim(.SelectedItems.Item(1))
>>
>> Me![ImagePath].Visible = True
>>
>> Me![ImagePath].SetFocus
>>
>> Me![ImagePath].Text = fileName
>>
>> Me![FirstName].SetFocus
>>
>> Me![ImagePath].Visible = False
>>
>> End If
>>
>> End With
>>
>> End Sub
>>
>>
>>
>> Sub showErrorMessage()
>>
>> ' Display the errormsg label if the image file is not available.
>>
>> If Not IsNull(Me!Photo) Then
>>
>> errormsg.Visible = False
>>
>> Else
>>
>> errormsg.Visible = True
>>
>> End If
>>
>> End Sub
>>
>>
>>
>> Function IsRelative(fName As String) As Boolean
>>
>> ' Return false if the file name contains a drive or UNC path
>>
>> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") =
>> 0)
>>
>> End Function
>>
>>
>>
>> Sub hideImageFrame()
>>
>> ' Hide the image control
>>
>> Me![ImageFrame].Visible = False
>>
>> End Sub
>>
>>
>>
>> Sub showImageFrame()
>>
>> ' Display the image control
>>
>> Me![ImageFrame].Visible = True
>>
>> End Sub
>>
>>
>
|
|
Replace
[Quoted Text] > .FormFields("fldGrossPurchaseTotal").result = _
> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
by
.FormFields("fldGrossPurchaseTotal").result = _
Nz(Me.Controls("PurchaseHistory").Form!GrossPurchaseTotal)
Kindly repost whether this adjustment gets you further (or not)
If it does get you further than this line, does the code fail elsewhere?
--
Krgrds,
Perry
System:
Vista/Office Ultimate
VS2005/VSTO2005 SE
"Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
news:u2j%23WCykHHA.4768[ at ]TK2MSFTNGP05.phx.gbl...
> Perry,
>
>
>
> I feel that I've done a poor job in describing my project .
>
>
>
> RE:
>
>
>
> Couple of things here:
>
> Y're trying to pick up values from an Access form, ok?
>
>
>
> Yes: I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
> Server). I have a series of Queries that manipulate the data and populate
> an ACCESS Form. This Form has several pages/tabs on it, and one page/tab
> incorporates a sub-form.
>
> n Here's my quandry ... I'm trying to display (in a WORD template)
> several fields from the Main Form (which is a Single Form), and all data
> displayed in the sub-form (which is an Continuous Form linked to the
> main-form by the ProductID field)
>
>
>
> No need to build up a seperate recordset to to populate "strReportsTo"
> variable.
>
>
>
> This was some residual code left over from the Original Code that I'd
> modified
>
>
> Next:
> I presume there's a button on your mainform to wire the current/selected
> record (form and subform-data) to MS Word.
> Correct?
>
> Yes
> So basically it comes down to the user "printing" the Access form (and
> subform) data to a Word document by hitting that button.
> Correct?
>
> Yes
>
> In doing this, the field "GrossPurchaseTotal" (present on one of your
> other
> subforms) has to transfered to Word as well. Correct?
>
> Yes
>
> The last thing can be done by using something like (in the Button_Click
> event code):
> (whereby "MyOtherSubformName" is the name of the subform where
> "GrossPurchaseTotal" is hosted)
>
> doc.FormFields("GrossPurchaseTotal").result =
> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>
>
>
> I have the following code which I use to display the data (via Text Form
> Fields) in my WORD document (**SEE EXPLANATORY NOTES**):
>
>
>
> Option Compare Database
> Option Explicit
> Dim path As String
>
> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
> Const DOC_NAME1 As String = _
> "Products1.dot"
> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
> Const DOC_NAME2 As String = _
> " Products2.dot "
> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
> Const DOC_NAME3 As String = _
> " Products3.dot "
>
>
> Private Sub AddPicture_Click()
> ' Use the Office File Open dialog to get a file name to use
> ' as an employee picture.
> getFileName
> End Sub
>
> Private Sub cmdPrint Products1_Click()
> Dim appWord As Word.Application
> Dim doc As Word.Document
> Dim rst As ADODB.Recordset
> Dim strSQL 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_NAME1)
> 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_PATH1 & DOC_NAME1, , True)
> Set rst = New ADODB.Recordset
>
> strSQL = "SELECT * FROM PRODUCTS"
> rst.Open strSQL, CurrentProject.Connection, _
> adOpenStatic, adLockReadOnly
>
> With doc
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName) (**THIS
> FIELD POPULATES IN WORD**)
> .FormFields("fldAddress1").result = Nz(Me!Address1) (**THIS FIELD
> POPULATES IN WORD**)
> .FormFields("fldAddress2").result = Nz(Me!Address2) (**THIS FIELD
> POPULATES IN WORD**)
> .FormFields("fldCity").result = Nz(Me!City) (**THIS FIELD POPULATES
> IN WORD**)
> .FormFields("fldRegion").result = Nz(Me!Region) (**THIS FIELD
> POPULATES IN WORD**)
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode) (**THIS
> FIELD POPULATES IN WORD**)
> .FormFields("fldProductName").result = Nz(Me!ProductName) (**THIS
> FIELD POPULATES IN WORD**)
> .FormFields("fldQty").result = Nz(Me!Qty) (**THIS FIELD POPULATES
> IN WORD**)
> .FormFields("fldPrice").result = Nz(Me!Price) (**THIS FIELD
> POPULATES IN WORD**)
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee) (**THIS
> FIELD POPULATES IN WORD**)
>
> (**This Last Line (below) - your suggested code - fails to populate, &
> renders the following error message**)
> .FormFields("fldGrossPurchaseTotal").result =
> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>
> (**2465Microsoft Office Access can't find the field 'PurchaseHistory'
> referred to in your expression**)
>
> **No Data shows in the "fldGrossPurchasTotal" Text Form Field in the WORD
> document**
>
>
>
> 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
>
> Private Sub cmdPrintProducts2 _Click()
> Dim appWord As Word.Application
> Dim doc As Word.Document
> Dim rst As ADODB.Recordset
> Dim strSQL 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_NAME2)
> 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_PATH2 & DOC_NAME2, , True)
> Set rst = New ADODB.Recordset
>
>
>
>
> strSQL = "SELECT * FROM PRODUCTS"
> rst.Open strSQL, CurrentProject.Connection, _
> adOpenStatic, adLockReadOnly
>
>
> With doc
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
> .FormFields("fldAddress1").result = Nz(Me!Address1)
> .FormFields("fldAddress2").result = Nz(Me!Address2)
> .FormFields("fldCity").result = Nz(Me!City)
> .FormFields("fldRegion").result = Nz(Me!Region)
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
> .FormFields("fldProductName").result = Nz(Me!ProductName)
> .FormFields("fldQty").result = Nz(Me!Qty)
> .FormFields("fldPrice").result = Nz(Me!Price)
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>
> 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
>
> Private Sub cmdPrintProducts3_Click()
> Dim appWord As Word.Application
> Dim doc As Word.Document
> Dim rst As ADODB.Recordset
> Dim strSQL 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_NAME3)
> 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_PATH3 & DOC_NAME3, , True)
> Set rst = New ADODB.Recordset
>
> strSQL = "SELECT * FROM PRODUCTS"
> rst.Open strSQL, CurrentProject.Connection, _
> adOpenStatic, adLockReadOnly
>
> With doc
> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
> .FormFields("fldAddress1").result = Nz(Me!Address1)
> .FormFields("fldAddress2").result = Nz(Me!Address2)
> .FormFields("fldCity").result = Nz(Me!City)
> .FormFields("fldRegion").result = Nz(Me!Region)
> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
> .FormFields("fldProductName").result = Nz(Me!ProductName)
> .FormFields("fldQty").result = Nz(Me!Qty)
> .FormFields("fldPrice").result = Nz(Me!Price)
> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>
> 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
>
> "Perry" <drumper[ at ]gmail.com> wrote in message
> news:%233K5PKjkHHA.4112[ at ]TK2MSFTNGP04.phx.gbl...
>> Couple of things here:
>>
>> Y're trying to pick up values from an Access form, ok?
>> No need to build up a seperate recordset to to populate "strReportsTo"
>> variable.
>> Try to see whether there's a field (control) in your mainform that
>> contains this information.
>> Then use the value of this control to populate (and maintain) the
>> variable.
>>
>> Next:
>> I presume there's a button on your mainform to wire the current/selected
>> record
>> (form and subform-data) to MS Word.
>> Correct?
>> So basically it comes down to the user "printing" the Access form (and
>> subform) data to a Word document by hitting that button.
>> Correct?
>>
>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>> other subforms) has to transfered to
>> Word as well.
>> Correct?
>> The last thing can be done by using something like (in the Button_Click
>> event code):
>> (whereby "MyOtherSubformName" is the name of the subform where
>> "GrossPurchaseTotal" is hosted)
>>
>> doc.FormFields("GrossPurchaseTotal").result =
>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>
>> --
>> Krgrds,
>> Perry
>>
>> System:
>> Vista/Office Ultimate
>> VS2005/VSTO2005 SE
>>
>>
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>> news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
>>>I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
>>> Server). I have a series of Queries that manipulate the data and
>>> populate
>>> an ACCESS Form. This Form has the following features:
>>>
>>> 1. A Main Form, with several pages (Tabs) which display data from one
>>> (OverallData) Table
>>> a. One of these pages (Tabs) contains a Sub-Form which displays data
>>> from another (SpecificData) Table
>>>
>>> Here's my quandry ... I'm trying to display (in the WORD template) a
>>> field
>>> from the Sub-Form -- how do I modify the code-syntax to accomplish this?
>>> Example: .FormFields("fldDeliveryFee").result =
>>> Nz(Me!DeliveryFee)
>>>
>>> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which is
>>> populated from a seperate table) -- what do I need to modify to
>>> accomplish
>>> this?
>>>
>>>
>>>
>>> I have the following code which I use to display the data (via Text Form
>>> Fields) in my WORD document:
>>> ======================================
>>> Option Compare Database
>>> Option Explicit
>>>
>>> Dim path As String
>>>
>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>
>>> Const DOC_NAME1 As String = _
>>>
>>> "Products1.dot"
>>>
>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>
>>> Const DOC_NAME2 As String = _
>>>
>>> " Products2.dot "
>>>
>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>
>>> Const DOC_NAME3 As String = _
>>>
>>> " Products3.dot "
>>>
>>> Private Sub AddPicture_Click()
>>>
>>> ' Use the Office File Open dialog to get a file name to use
>>>
>>> ' as an employee picture.
>>>
>>> getFileName
>>>
>>> End Sub
>>>
>>> Private Sub cmdPrint Products1_Click()
>>>
>>> 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_NAME1)
>>>
>>> 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_PATH1 & DOC_NAME1, , True)
>>>
>>> Set rst = New ADODB.Recordset
>>>
>>>
>>>
>>> strSQL = "SELECT * FROM PRODUCTS"
>>>
>>> rst.Open strSQL, CurrentProject.Connection, _
>>>
>>> adOpenStatic, adLockReadOnly
>>>
>>> If Not rst.EOF Then
>>>
>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>
>>> rst.Close
>>>
>>> End If
>>>
>>>
>>>
>>> With doc
>>>
>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>
>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>
>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>
>>> .FormFields("fldCity").result = Nz(Me!City)
>>>
>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>
>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>
>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>
>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>
>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>
>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>
>>>
>>>
>>> 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
>>>
>>>
>>>
>>> Private Sub cmdPrintProducts2 _Click()
>>>
>>> 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_NAME2)
>>>
>>> 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_PATH2 & DOC_NAME2, , True)
>>>
>>> Set rst = New ADODB.Recordset
>>>
>>>
>>>
>>> strSQL = "SELECT * FROM PRODUCTS"
>>>
>>> rst.Open strSQL, CurrentProject.Connection, _
>>>
>>> adOpenStatic, adLockReadOnly
>>>
>>> If Not rst.EOF Then
>>>
>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>
>>> rst.Close
>>>
>>> End If
>>>
>>>
>>>
>>> With doc
>>>
>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>
>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>
>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>
>>> .FormFields("fldCity").result = Nz(Me!City)
>>>
>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>
>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>
>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>
>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>
>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>
>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>
>>> 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
>>>
>>>
>>>
>>>
>>>
>>> Private Sub cmdPrintProducts3_Click()
>>>
>>> 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_NAME3)
>>>
>>> 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_PATH3 & DOC_NAME3, , True)
>>>
>>> Set rst = New ADODB.Recordset
>>>
>>>
>>>
>>>
>>>
>>> strSQL = "SELECT * FROM PRODUCTS"
>>>
>>> rst.Open strSQL, CurrentProject.Connection, _
>>>
>>> adOpenStatic, adLockReadOnly
>>>
>>> If Not rst.EOF Then
>>>
>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>
>>> rst.Close
>>>
>>> End If
>>>
>>>
>>>
>>> With doc
>>>
>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>
>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>
>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>
>>> .FormFields("fldCity").result = Nz(Me!City)
>>>
>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>
>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>
>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>
>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>
>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>
>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>
>>> 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
>>>
>>> ================================================================
>>>
>>> The REST of the code is deleted for ease-of-viewing
>>>
>>> ================================================================
>>>
>>>
>>>
>>> Private Sub Form_RecordExit(Cancel As Integer)
>>>
>>> ' Hide the errormsg label to reduce flashing when navigating
>>>
>>> ' between records.
>>>
>>> errormsg.Visible = False
>>>
>>> End Sub
>>>
>>> Private Sub RemovePicture_Click()
>>>
>>> ' Clear the file name for the employee record and display the
>>>
>>> ' errormsg label.
>>>
>>> Me![ImagePath] = ""
>>>
>>> hideImageFrame
>>>
>>> errormsg.Visible = True
>>>
>>> End Sub
>>>
>>>
>>>
>>> Private Sub Form_AfterUpdate()
>>>
>>> ' Requery the ReportsTo combo box after a record has been changed.
>>>
>>> ' Then, either show the errormsg label if no file name exists for
>>>
>>> ' the employee record or display the image if there is a file name
>>> that
>>>
>>> ' exists.
>>>
>>> 'Me!ReportsTo.Requery
>>>
>>> On Error Resume Next
>>>
>>> showErrorMessage
>>>
>>> showImageFrame
>>>
>>> If (IsRelative(Me!ImagePath) = True) Then
>>>
>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>
>>> Else
>>>
>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>
>>> End If
>>>
>>> End Sub
>>>
>>>
>>>
>>> Private Sub ImagePath_AfterUpdate()
>>>
>>> ' After selecting an image for the employee, display it.
>>>
>>> On Error Resume Next
>>>
>>> showErrorMessage
>>>
>>> showImageFrame
>>>
>>> If (IsRelative(Me!ImagePath) = True) Then
>>>
>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>
>>> Else
>>>
>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>
>>> End If
>>>
>>> End Sub
>>>
>>> Private Sub Form_Current()
>>>
>>> ' Display the picture for the current employee record if the image
>>>
>>> ' exists. If the file name no longer exists or the file name was
>>> blank
>>>
>>> ' for the current employee, set the errormsg label caption to the
>>>
>>> ' appropriate message.
>>>
>>> Dim res As Boolean
>>>
>>> Dim fName As String
>>>
>>>
>>>
>>> path = CurrentProject.path
>>>
>>> On Error Resume Next
>>>
>>> errormsg.Visible = False
>>>
>>> If Not IsNull(Me!Photo) Then
>>>
>>> res = IsRelative(Me!Photo)
>>>
>>> fName = Me![ImagePath]
>>>
>>> If (res = True) Then
>>>
>>> fName = path & "\" & fName
>>>
>>> End If
>>>
>>>
>>>
>>> Me![ImageFrame].Picture = fName
>>>
>>> showImageFrame
>>>
>>> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>>>
>>> If (Me![ImageFrame].Picture <> fName) Then
>>>
>>> hideImageFrame
>>>
>>> errormsg.Caption = "Picture not found"
>>>
>>> errormsg.Visible = True
>>>
>>> End If
>>>
>>> Else
>>>
>>> hideImageFrame
>>>
>>> errormsg.Caption = "Click Add/Change to add picture"
>>>
>>> errormsg.Visible = True
>>>
>>> End If
>>>
>>>
>>>
>>> End Sub
>>>
>>>
>>>
>>> Sub getFileName()
>>>
>>> ' Displays the Office File Open dialog to choose a file name
>>>
>>> ' for the current employee record. If the user selects a file
>>>
>>> ' display it in the image control.
>>>
>>> Dim fileName As String
>>>
>>> Dim result As Integer
>>>
>>> With Application.FileDialog(msoFileDialogFilePicker)
>>>
>>> .Title = "Select Employee Picture"
>>>
>>> .Filters.Add "All Files", "*.*"
>>>
>>> .Filters.Add "JPEGs", "*.jpg"
>>>
>>> .Filters.Add "Bitmaps", "*.bmp"
>>>
>>> .FilterIndex = 3
>>>
>>> .AllowMultiSelect = False
>>>
>>> .InitialFileName = CurrentProject.path
>>>
>>> result = .Show
>>>
>>> If (result <> 0) Then
>>>
>>> fileName = Trim(.SelectedItems.Item(1))
>>>
>>> Me![ImagePath].Visible = True
>>>
>>> Me![ImagePath].SetFocus
>>>
>>> Me![ImagePath].Text = fileName
>>>
>>> Me![FirstName].SetFocus
>>>
>>> Me![ImagePath].Visible = False
>>>
>>> End If
>>>
>>> End With
>>>
>>> End Sub
>>>
>>>
>>>
>>> Sub showErrorMessage()
>>>
>>> ' Display the errormsg label if the image file is not available.
>>>
>>> If Not IsNull(Me!Photo) Then
>>>
>>> errormsg.Visible = False
>>>
>>> Else
>>>
>>> errormsg.Visible = True
>>>
>>> End If
>>>
>>> End Sub
>>>
>>>
>>>
>>> Function IsRelative(fName As String) As Boolean
>>>
>>> ' Return false if the file name contains a drive or UNC path
>>>
>>> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") =
>>> 0)
>>>
>>> End Function
>>>
>>>
>>>
>>> Sub hideImageFrame()
>>>
>>> ' Hide the image control
>>>
>>> Me![ImageFrame].Visible = False
>>>
>>> End Sub
>>>
>>>
>>>
>>> Sub showImageFrame()
>>>
>>> ' Display the image control
>>>
>>> Me![ImageFrame].Visible = True
>>>
>>> End Sub
>>>
>>>
>>
>
>
|
|
Perry,
I replaced the snippet of code you referenced -- I got the same Error
Message at the same point.
Note: The WORD document opens without incident, but the data does not
populate. When I close the WORD document, I see the Error Message.
Shane
"Perry" <drumper[ at ]gmail.com> wrote in message
news:B11007B4-27AE-448F-A4AE-D56BE38752E7[ at ]microsoft.com...
[Quoted Text] > Replace
>> .FormFields("fldGrossPurchaseTotal").result = _
>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>
> by
> .FormFields("fldGrossPurchaseTotal").result = _
> Nz(Me.Controls("PurchaseHistory").Form!GrossPurchaseTotal)
>
> Kindly repost whether this adjustment gets you further (or not)
> If it does get you further than this line, does the code fail elsewhere?
>
> --
> Krgrds,
> Perry
>
> System:
> Vista/Office Ultimate
> VS2005/VSTO2005 SE
>
>
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
> news:u2j%23WCykHHA.4768[ at ]TK2MSFTNGP05.phx.gbl...
>> Perry,
>>
>>
>>
>> I feel that I've done a poor job in describing my project .
>>
>>
>>
>> RE:
>>
>>
>>
>> Couple of things here:
>>
>> Y're trying to pick up values from an Access form, ok?
>>
>>
>>
>> Yes: I have a Front-End ACCESS db (linked to (3) Tables on a Back-End
>> SQL
>> Server). I have a series of Queries that manipulate the data and
>> populate
>> an ACCESS Form. This Form has several pages/tabs on it, and one page/tab
>> incorporates a sub-form.
>>
>> n Here's my quandry ... I'm trying to display (in a WORD template)
>> several fields from the Main Form (which is a Single Form), and all data
>> displayed in the sub-form (which is an Continuous Form linked to the
>> main-form by the ProductID field)
>>
>>
>>
>> No need to build up a seperate recordset to to populate "strReportsTo"
>> variable.
>>
>>
>>
>> This was some residual code left over from the Original Code that I'd
>> modified
>>
>>
>> Next:
>> I presume there's a button on your mainform to wire the current/selected
>> record (form and subform-data) to MS Word.
>> Correct?
>>
>> Yes
>> So basically it comes down to the user "printing" the Access form (and
>> subform) data to a Word document by hitting that button.
>> Correct?
>>
>> Yes
>>
>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>> other
>> subforms) has to transfered to Word as well. Correct?
>>
>> Yes
>>
>> The last thing can be done by using something like (in the Button_Click
>> event code):
>> (whereby "MyOtherSubformName" is the name of the subform where
>> "GrossPurchaseTotal" is hosted)
>>
>> doc.FormFields("GrossPurchaseTotal").result =
>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>
>>
>>
>> I have the following code which I use to display the data (via Text Form
>> Fields) in my WORD document (**SEE EXPLANATORY NOTES**):
>>
>>
>>
>> Option Compare Database
>> Option Explicit
>> Dim path As String
>>
>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>> Const DOC_NAME1 As String = _
>> "Products1.dot"
>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>> Const DOC_NAME2 As String = _
>> " Products2.dot "
>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>> Const DOC_NAME3 As String = _
>> " Products3.dot "
>>
>>
>> Private Sub AddPicture_Click()
>> ' Use the Office File Open dialog to get a file name to use
>> ' as an employee picture.
>> getFileName
>> End Sub
>>
>> Private Sub cmdPrint Products1_Click()
>> Dim appWord As Word.Application
>> Dim doc As Word.Document
>> Dim rst As ADODB.Recordset
>> Dim strSQL 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_NAME1)
>> 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_PATH1 & DOC_NAME1, , True)
>> Set rst = New ADODB.Recordset
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>> rst.Open strSQL, CurrentProject.Connection, _
>> adOpenStatic, adLockReadOnly
>>
>> With doc
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName) (**THIS
>> FIELD POPULATES IN WORD**)
>> .FormFields("fldAddress1").result = Nz(Me!Address1) (**THIS FIELD
>> POPULATES IN WORD**)
>> .FormFields("fldAddress2").result = Nz(Me!Address2) (**THIS FIELD
>> POPULATES IN WORD**)
>> .FormFields("fldCity").result = Nz(Me!City) (**THIS FIELD
>> POPULATES IN WORD**)
>> .FormFields("fldRegion").result = Nz(Me!Region) (**THIS FIELD
>> POPULATES IN WORD**)
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode) (**THIS
>> FIELD POPULATES IN WORD**)
>> .FormFields("fldProductName").result = Nz(Me!ProductName) (**THIS
>> FIELD POPULATES IN WORD**)
>> .FormFields("fldQty").result = Nz(Me!Qty) (**THIS FIELD POPULATES
>> IN WORD**)
>> .FormFields("fldPrice").result = Nz(Me!Price) (**THIS FIELD
>> POPULATES IN WORD**)
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee) (**THIS
>> FIELD POPULATES IN WORD**)
>>
>> (**This Last Line (below) - your suggested code - fails to populate, &
>> renders the following error message**)
>> .FormFields("fldGrossPurchaseTotal").result =
>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>
>> (**2465Microsoft Office Access can't find the field 'PurchaseHistory'
>> referred to in your expression**)
>>
>> **No Data shows in the "fldGrossPurchasTotal" Text Form Field in the WORD
>> document**
>>
>>
>>
>> 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
>>
>> Private Sub cmdPrintProducts2 _Click()
>> Dim appWord As Word.Application
>> Dim doc As Word.Document
>> Dim rst As ADODB.Recordset
>> Dim strSQL 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_NAME2)
>> 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_PATH2 & DOC_NAME2, , True)
>> Set rst = New ADODB.Recordset
>>
>>
>>
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>> rst.Open strSQL, CurrentProject.Connection, _
>> adOpenStatic, adLockReadOnly
>>
>>
>> With doc
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>> .FormFields("fldCity").result = Nz(Me!City)
>> .FormFields("fldRegion").result = Nz(Me!Region)
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>> .FormFields("fldQty").result = Nz(Me!Qty)
>> .FormFields("fldPrice").result = Nz(Me!Price)
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>
>> 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
>>
>> Private Sub cmdPrintProducts3_Click()
>> Dim appWord As Word.Application
>> Dim doc As Word.Document
>> Dim rst As ADODB.Recordset
>> Dim strSQL 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_NAME3)
>> 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_PATH3 & DOC_NAME3, , True)
>> Set rst = New ADODB.Recordset
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>> rst.Open strSQL, CurrentProject.Connection, _
>> adOpenStatic, adLockReadOnly
>>
>> With doc
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>> .FormFields("fldCity").result = Nz(Me!City)
>> .FormFields("fldRegion").result = Nz(Me!Region)
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>> .FormFields("fldQty").result = Nz(Me!Qty)
>> .FormFields("fldPrice").result = Nz(Me!Price)
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>
>> 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
>>
>> "Perry" <drumper[ at ]gmail.com> wrote in message
>> news:%233K5PKjkHHA.4112[ at ]TK2MSFTNGP04.phx.gbl...
>>> Couple of things here:
>>>
>>> Y're trying to pick up values from an Access form, ok?
>>> No need to build up a seperate recordset to to populate "strReportsTo"
>>> variable.
>>> Try to see whether there's a field (control) in your mainform that
>>> contains this information.
>>> Then use the value of this control to populate (and maintain) the
>>> variable.
>>>
>>> Next:
>>> I presume there's a button on your mainform to wire the current/selected
>>> record
>>> (form and subform-data) to MS Word.
>>> Correct?
>>> So basically it comes down to the user "printing" the Access form (and
>>> subform) data to a Word document by hitting that button.
>>> Correct?
>>>
>>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>>> other subforms) has to transfered to
>>> Word as well.
>>> Correct?
>>> The last thing can be done by using something like (in the Button_Click
>>> event code):
>>> (whereby "MyOtherSubformName" is the name of the subform where
>>> "GrossPurchaseTotal" is hosted)
>>>
>>> doc.FormFields("GrossPurchaseTotal").result =
>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>
>>> --
>>> Krgrds,
>>> Perry
>>>
>>> System:
>>> Vista/Office Ultimate
>>> VS2005/VSTO2005 SE
>>>
>>>
>>>
>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>>> news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
>>>>I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
>>>> Server). I have a series of Queries that manipulate the data and
>>>> populate
>>>> an ACCESS Form. This Form has the following features:
>>>>
>>>> 1. A Main Form, with several pages (Tabs) which display data from one
>>>> (OverallData) Table
>>>> a. One of these pages (Tabs) contains a Sub-Form which displays data
>>>> from another (SpecificData) Table
>>>>
>>>> Here's my quandry ... I'm trying to display (in the WORD template) a
>>>> field
>>>> from the Sub-Form -- how do I modify the code-syntax to accomplish
>>>> this?
>>>> Example: .FormFields("fldDeliveryFee").result =
>>>> Nz(Me!DeliveryFee)
>>>>
>>>> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which
>>>> is
>>>> populated from a seperate table) -- what do I need to modify to
>>>> accomplish
>>>> this?
>>>>
>>>>
>>>>
>>>> I have the following code which I use to display the data (via Text
>>>> Form
>>>> Fields) in my WORD document:
>>>> ======================================
>>>> Option Compare Database
>>>> Option Explicit
>>>>
>>>> Dim path As String
>>>>
>>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>>
>>>> Const DOC_NAME1 As String = _
>>>>
>>>> "Products1.dot"
>>>>
>>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>>
>>>> Const DOC_NAME2 As String = _
>>>>
>>>> " Products2.dot "
>>>>
>>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>>
>>>> Const DOC_NAME3 As String = _
>>>>
>>>> " Products3.dot "
>>>>
>>>> Private Sub AddPicture_Click()
>>>>
>>>> ' Use the Office File Open dialog to get a file name to use
>>>>
>>>> ' as an employee picture.
>>>>
>>>> getFileName
>>>>
>>>> End Sub
>>>>
>>>> Private Sub cmdPrint Products1_Click()
>>>>
>>>> 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_NAME1)
>>>>
>>>> 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_PATH1 & DOC_NAME1, , True)
>>>>
>>>> Set rst = New ADODB.Recordset
>>>>
>>>>
>>>>
>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>
>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>
>>>> adOpenStatic, adLockReadOnly
>>>>
>>>> If Not rst.EOF Then
>>>>
>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>
>>>> rst.Close
>>>>
>>>> End If
>>>>
>>>>
>>>>
>>>> With doc
>>>>
>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>
>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>
>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>
>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>
>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>
>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>
>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>
>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>
>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>
>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>
>>>>
>>>>
>>>> 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
>>>>
>>>>
>>>>
>>>> Private Sub cmdPrintProducts2 _Click()
>>>>
>>>> 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_NAME2)
>>>>
>>>> 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_PATH2 & DOC_NAME2, , True)
>>>>
>>>> Set rst = New ADODB.Recordset
>>>>
>>>>
>>>>
>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>
>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>
>>>> adOpenStatic, adLockReadOnly
>>>>
>>>> If Not rst.EOF Then
>>>>
>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>
>>>> rst.Close
>>>>
>>>> End If
>>>>
>>>>
>>>>
>>>> With doc
>>>>
>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>
>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>
>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>
>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>
>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>
>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>
>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>
>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>
>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>
>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>
>>>> 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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Private Sub cmdPrintProducts3_Click()
>>>>
>>>> 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_NAME3)
>>>>
>>>> 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_PATH3 & DOC_NAME3, , True)
>>>>
>>>> Set rst = New ADODB.Recordset
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>
>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>
>>>> adOpenStatic, adLockReadOnly
>>>>
>>>> If Not rst.EOF Then
>>>>
>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>
>>>> rst.Close
>>>>
>>>> End If
>>>>
>>>>
>>>>
>>>> With doc
>>>>
>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>
>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>
>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>
>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>
>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>
>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>
>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>
>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>
>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>
>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>
>>>> 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
>>>>
>>>> ================================================================
>>>>
>>>> The REST of the code is deleted for ease-of-viewing
>>>>
>>>> ================================================================
>>>>
>>>>
>>>>
>>>> Private Sub Form_RecordExit(Cancel As Integer)
>>>>
>>>> ' Hide the errormsg label to reduce flashing when navigating
>>>>
>>>> ' between records.
>>>>
>>>> errormsg.Visible = False
>>>>
>>>> End Sub
>>>>
>>>> Private Sub RemovePicture_Click()
>>>>
>>>> ' Clear the file name for the employee record and display the
>>>>
>>>> ' errormsg label.
>>>>
>>>> Me![ImagePath] = ""
>>>>
>>>> hideImageFrame
>>>>
>>>> errormsg.Visible = True
>>>>
>>>> End Sub
>>>>
>>>>
>>>>
>>>> Private Sub Form_AfterUpdate()
>>>>
>>>> ' Requery the ReportsTo combo box after a record has been changed.
>>>>
>>>> ' Then, either show the errormsg label if no file name exists for
>>>>
>>>> ' the employee record or display the image if there is a file name
>>>> that
>>>>
>>>> ' exists.
>>>>
>>>> 'Me!ReportsTo.Requery
>>>>
>>>> On Error Resume Next
>>>>
>>>> showErrorMessage
>>>>
>>>> showImageFrame
>>>>
>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>
>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>
>>>> Else
>>>>
>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>
>>>> End If
>>>>
>>>> End Sub
>>>>
>>>>
>>>>
>>>> Private Sub ImagePath_AfterUpdate()
>>>>
>>>> ' After selecting an image for the employee, display it.
>>>>
>>>> On Error Resume Next
>>>>
>>>> showErrorMessage
>>>>
>>>> showImageFrame
>>>>
>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>
>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>
>>>> Else
>>>>
>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>
>>>> End If
>>>>
>>>> End Sub
>>>>
>>>> Private Sub Form_Current()
>>>>
>>>> ' Display the picture for the current employee record if the image
>>>>
>>>> ' exists. If the file name no longer exists or the file name was
>>>> blank
>>>>
>>>> ' for the current employee, set the errormsg label caption to the
>>>>
>>>> ' appropriate message.
>>>>
>>>> Dim res As Boolean
>>>>
>>>> Dim fName As String
>>>>
>>>>
>>>>
>>>> path = CurrentProject.path
>>>>
>>>> On Error Resume Next
>>>>
>>>> errormsg.Visible = False
>>>>
>>>> If Not IsNull(Me!Photo) Then
>>>>
>>>> res = IsRelative(Me!Photo)
>>>>
>>>> fName = Me![ImagePath]
>>>>
>>>> If (res = True) Then
>>>>
>>>> fName = path & "\" & fName
>>>>
>>>> End If
>>>>
>>>>
>>>>
>>>> Me![ImageFrame].Picture = fName
>>>>
>>>> showImageFrame
>>>>
>>>> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>>>>
>>>> If (Me![ImageFrame].Picture <> fName) Then
>>>>
>>>> hideImageFrame
>>>>
>>>> errormsg.Caption = "Picture not found"
>>>>
>>>> errormsg.Visible = True
>>>>
>>>> End If
>>>>
>>>> Else
>>>>
>>>> hideImageFrame
>>>>
>>>> errormsg.Caption = "Click Add/Change to add picture"
>>>>
>>>> errormsg.Visible = True
>>>>
>>>> End If
>>>>
>>>>
>>>>
>>>> End Sub
>>>>
>>>>
>>>>
>>>> Sub getFileName()
>>>>
>>>> ' Displays the Office File Open dialog to choose a file name
>>>>
>>>> ' for the current employee record. If the user selects a file
>>>>
>>>> ' display it in the image control.
>>>>
>>>> Dim fileName As String
>>>>
>>>> Dim result As Integer
>>>>
>>>> With Application.FileDialog(msoFileDialogFilePicker)
>>>>
>>>> .Title = "Select Employee Picture"
>>>>
>>>> .Filters.Add "All Files", "*.*"
>>>>
>>>> .Filters.Add "JPEGs", "*.jpg"
>>>>
>>>> .Filters.Add "Bitmaps", "*.bmp"
>>>>
>>>> .FilterIndex = 3
>>>>
>>>> .AllowMultiSelect = False
>>>>
>>>> .InitialFileName = CurrentProject.path
>>>>
>>>> result = .Show
>>>>
>>>> If (result <> 0) Then
>>>>
>>>> fileName = Trim(.SelectedItems.Item(1))
>>>>
>>>> Me![ImagePath].Visible = True
>>>>
>>>> Me![ImagePath].SetFocus
>>>>
>>>> Me![ImagePath].Text = fileName
>>>>
>>>> Me![FirstName].SetFocus
>>>>
>>>> Me![ImagePath].Visible = False
>>>>
>>>> End If
>>>>
>>>> End With
>>>>
>>>> End Sub
>>>>
>>>>
>>>>
>>>> Sub showErrorMessage()
>>>>
>>>> ' Display the errormsg label if the image file is not available.
>>>>
>>>> If Not IsNull(Me!Photo) Then
>>>>
>>>> errormsg.Visible = False
>>>>
>>>> Else
>>>>
>>>> errormsg.Visible = True
>>>>
>>>> End If
>>>>
>>>> End Sub
>>>>
>>>>
>>>>
>>>> Function IsRelative(fName As String) As Boolean
>>>>
>>>> ' Return false if the file name contains a drive or UNC path
>>>>
>>>> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") =
>>>> 0)
>>>>
>>>> End Function
>>>>
>>>>
>>>>
>>>> Sub hideImageFrame()
>>>>
>>>> ' Hide the image control
>>>>
>>>> Me![ImageFrame].Visible = False
>>>>
>>>> End Sub
>>>>
>>>>
>>>>
>>>> Sub showImageFrame()
>>>>
>>>> ' Display the image control
>>>>
>>>> Me![ImageFrame].Visible = True
>>>>
>>>> End Sub
>>>>
>>>>
>>>
>>
>>
>
|
|
"PurchaseHistory" this is the subform where "GrossPurchaseTotal" is hosted
in, isn't it?
If it isn't, kindly forward the correct name of the subform (!) where
"GrossPurchaseTotal" is hosted.
--
Krgrds,
Perry
System:
Vista/Office Ultimate
VS2005/VSTO2005 SE
"Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
news:OU$jivzkHHA.4848[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Perry,
>
> I replaced the snippet of code you referenced -- I got the same Error
> Message at the same point.
>
> Note: The WORD document opens without incident, but the data does not
> populate. When I close the WORD document, I see the Error Message.
>
> Shane
> "Perry" <drumper[ at ]gmail.com> wrote in message
> news:B11007B4-27AE-448F-A4AE-D56BE38752E7[ at ]microsoft.com...
>> Replace
>>> .FormFields("fldGrossPurchaseTotal").result = _
>>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>
>> by
>> .FormFields("fldGrossPurchaseTotal").result = _
>> Nz(Me.Controls("PurchaseHistory").Form!GrossPurchaseTotal)
>>
>> Kindly repost whether this adjustment gets you further (or not)
>> If it does get you further than this line, does the code fail elsewhere?
>>
>> --
>> Krgrds,
>> Perry
>>
>> System:
>> Vista/Office Ultimate
>> VS2005/VSTO2005 SE
>>
>>
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>> news:u2j%23WCykHHA.4768[ at ]TK2MSFTNGP05.phx.gbl...
>>> Perry,
>>>
>>>
>>>
>>> I feel that I've done a poor job in describing my project .
>>>
>>>
>>>
>>> RE:
>>>
>>>
>>>
>>> Couple of things here:
>>>
>>> Y're trying to pick up values from an Access form, ok?
>>>
>>>
>>>
>>> Yes: I have a Front-End ACCESS db (linked to (3) Tables on a Back-End
>>> SQL
>>> Server). I have a series of Queries that manipulate the data and
>>> populate
>>> an ACCESS Form. This Form has several pages/tabs on it, and one
>>> page/tab incorporates a sub-form.
>>>
>>> n Here's my quandry ... I'm trying to display (in a WORD template)
>>> several fields from the Main Form (which is a Single Form), and all data
>>> displayed in the sub-form (which is an Continuous Form linked to the
>>> main-form by the ProductID field)
>>>
>>>
>>>
>>> No need to build up a seperate recordset to to populate "strReportsTo"
>>> variable.
>>>
>>>
>>>
>>> This was some residual code left over from the Original Code that I'd
>>> modified
>>>
>>>
>>> Next:
>>> I presume there's a button on your mainform to wire the current/selected
>>> record (form and subform-data) to MS Word.
>>> Correct?
>>>
>>> Yes
>>> So basically it comes down to the user "printing" the Access form (and
>>> subform) data to a Word document by hitting that button.
>>> Correct?
>>>
>>> Yes
>>>
>>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>>> other
>>> subforms) has to transfered to Word as well. Correct?
>>>
>>> Yes
>>>
>>> The last thing can be done by using something like (in the Button_Click
>>> event code):
>>> (whereby "MyOtherSubformName" is the name of the subform where
>>> "GrossPurchaseTotal" is hosted)
>>>
>>> doc.FormFields("GrossPurchaseTotal").result =
>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>
>>>
>>>
>>> I have the following code which I use to display the data (via Text Form
>>> Fields) in my WORD document (**SEE EXPLANATORY NOTES**):
>>>
>>>
>>>
>>> Option Compare Database
>>> Option Explicit
>>> Dim path As String
>>>
>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>> Const DOC_NAME1 As String = _
>>> "Products1.dot"
>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>> Const DOC_NAME2 As String = _
>>> " Products2.dot "
>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>> Const DOC_NAME3 As String = _
>>> " Products3.dot "
>>>
>>>
>>> Private Sub AddPicture_Click()
>>> ' Use the Office File Open dialog to get a file name to use
>>> ' as an employee picture.
>>> getFileName
>>> End Sub
>>>
>>> Private Sub cmdPrint Products1_Click()
>>> Dim appWord As Word.Application
>>> Dim doc As Word.Document
>>> Dim rst As ADODB.Recordset
>>> Dim strSQL 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_NAME1)
>>> 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_PATH1 & DOC_NAME1, , True)
>>> Set rst = New ADODB.Recordset
>>>
>>> strSQL = "SELECT * FROM PRODUCTS"
>>> rst.Open strSQL, CurrentProject.Connection, _
>>> adOpenStatic, adLockReadOnly
>>>
>>> With doc
>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName) (**THIS
>>> FIELD POPULATES IN WORD**)
>>> .FormFields("fldAddress1").result = Nz(Me!Address1) (**THIS FIELD
>>> POPULATES IN WORD**)
>>> .FormFields("fldAddress2").result = Nz(Me!Address2) (**THIS FIELD
>>> POPULATES IN WORD**)
>>> .FormFields("fldCity").result = Nz(Me!City) (**THIS FIELD
>>> POPULATES IN WORD**)
>>> .FormFields("fldRegion").result = Nz(Me!Region) (**THIS FIELD
>>> POPULATES IN WORD**)
>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode) (**THIS
>>> FIELD POPULATES IN WORD**)
>>> .FormFields("fldProductName").result = Nz(Me!ProductName) (**THIS
>>> FIELD POPULATES IN WORD**)
>>> .FormFields("fldQty").result = Nz(Me!Qty) (**THIS FIELD POPULATES
>>> IN WORD**)
>>> .FormFields("fldPrice").result = Nz(Me!Price) (**THIS FIELD
>>> POPULATES IN WORD**)
>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee) (**THIS
>>> FIELD POPULATES IN WORD**)
>>>
>>> (**This Last Line (below) - your suggested code - fails to populate, &
>>> renders the following error message**)
>>> .FormFields("fldGrossPurchaseTotal").result =
>>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>>
>>> (**2465Microsoft Office Access can't find the field 'PurchaseHistory'
>>> referred to in your expression**)
>>>
>>> **No Data shows in the "fldGrossPurchasTotal" Text Form Field in the
>>> WORD document**
>>>
>>>
>>>
>>> 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
>>>
>>> Private Sub cmdPrintProducts2 _Click()
>>> Dim appWord As Word.Application
>>> Dim doc As Word.Document
>>> Dim rst As ADODB.Recordset
>>> Dim strSQL 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_NAME2)
>>> 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_PATH2 & DOC_NAME2, , True)
>>> Set rst = New ADODB.Recordset
>>>
>>>
>>>
>>>
>>> strSQL = "SELECT * FROM PRODUCTS"
>>> rst.Open strSQL, CurrentProject.Connection, _
>>> adOpenStatic, adLockReadOnly
>>>
>>>
>>> With doc
>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>> .FormFields("fldCity").result = Nz(Me!City)
>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>
>>> 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
>>>
>>> Private Sub cmdPrintProducts3_Click()
>>> Dim appWord As Word.Application
>>> Dim doc As Word.Document
>>> Dim rst As ADODB.Recordset
>>> Dim strSQL 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_NAME3)
>>> 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_PATH3 & DOC_NAME3, , True)
>>> Set rst = New ADODB.Recordset
>>>
>>> strSQL = "SELECT * FROM PRODUCTS"
>>> rst.Open strSQL, CurrentProject.Connection, _
>>> adOpenStatic, adLockReadOnly
>>>
>>> With doc
>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>> .FormFields("fldCity").result = Nz(Me!City)
>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>
>>> 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
>>>
>>> "Perry" <drumper[ at ]gmail.com> wrote in message
>>> news:%233K5PKjkHHA.4112[ at ]TK2MSFTNGP04.phx.gbl...
>>>> Couple of things here:
>>>>
>>>> Y're trying to pick up values from an Access form, ok?
>>>> No need to build up a seperate recordset to to populate "strReportsTo"
>>>> variable.
>>>> Try to see whether there's a field (control) in your mainform that
>>>> contains this information.
>>>> Then use the value of this control to populate (and maintain) the
>>>> variable.
>>>>
>>>> Next:
>>>> I presume there's a button on your mainform to wire the
>>>> current/selected record
>>>> (form and subform-data) to MS Word.
>>>> Correct?
>>>> So basically it comes down to the user "printing" the Access form (and
>>>> subform) data to a Word document by hitting that button.
>>>> Correct?
>>>>
>>>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>>>> other subforms) has to transfered to
>>>> Word as well.
>>>> Correct?
>>>> The last thing can be done by using something like (in the Button_Click
>>>> event code):
>>>> (whereby "MyOtherSubformName" is the name of the subform where
>>>> "GrossPurchaseTotal" is hosted)
>>>>
>>>> doc.FormFields("GrossPurchaseTotal").result =
>>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>>
>>>> --
>>>> Krgrds,
>>>> Perry
>>>>
>>>> System:
>>>> Vista/Office Ultimate
>>>> VS2005/VSTO2005 SE
>>>>
>>>>
>>>>
>>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>>>> news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
>>>>>I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
>>>>> Server). I have a series of Queries that manipulate the data and
>>>>> populate
>>>>> an ACCESS Form. This Form has the following features:
>>>>>
>>>>> 1. A Main Form, with several pages (Tabs) which display data from one
>>>>> (OverallData) Table
>>>>> a. One of these pages (Tabs) contains a Sub-Form which displays
>>>>> data
>>>>> from another (SpecificData) Table
>>>>>
>>>>> Here's my quandry ... I'm trying to display (in the WORD template) a
>>>>> field
>>>>> from the Sub-Form -- how do I modify the code-syntax to accomplish
>>>>> this?
>>>>> Example: .FormFields("fldDeliveryFee").result =
>>>>> Nz(Me!DeliveryFee)
>>>>>
>>>>> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which
>>>>> is
>>>>> populated from a seperate table) -- what do I need to modify to
>>>>> accomplish
>>>>> this?
>>>>>
>>>>>
>>>>>
>>>>> I have the following code which I use to display the data (via Text
>>>>> Form
>>>>> Fields) in my WORD document:
>>>>> ======================================
>>>>> Option Compare Database
>>>>> Option Explicit
>>>>>
>>>>> Dim path As String
>>>>>
>>>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>>>
>>>>> Const DOC_NAME1 As String = _
>>>>>
>>>>> "Products1.dot"
>>>>>
>>>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>>>
>>>>> Const DOC_NAME2 As String = _
>>>>>
>>>>> " Products2.dot "
>>>>>
>>>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>>>
>>>>> Const DOC_NAME3 As String = _
>>>>>
>>>>> " Products3.dot "
>>>>>
>>>>> Private Sub AddPicture_Click()
>>>>>
>>>>> ' Use the Office File Open dialog to get a file name to use
>>>>>
>>>>> ' as an employee picture.
>>>>>
>>>>> getFileName
>>>>>
>>>>> End Sub
>>>>>
>>>>> Private Sub cmdPrint Products1_Click()
>>>>>
>>>>> 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_NAME1)
>>>>>
>>>>> 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_PATH1 & DOC_NAME1, , True)
>>>>>
>>>>> Set rst = New ADODB.Recordset
>>>>>
>>>>>
>>>>>
>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>
>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>
>>>>> adOpenStatic, adLockReadOnly
>>>>>
>>>>> If Not rst.EOF Then
>>>>>
>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>
>>>>> rst.Close
>>>>>
>>>>> End If
>>>>>
>>>>>
>>>>>
>>>>> With doc
>>>>>
>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>
>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>
>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>
>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>
>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>
>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>
>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>
>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>
>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>
>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>
>>>>>
>>>>>
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>>> Private Sub cmdPrintProducts2 _Click()
>>>>>
>>>>> 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_NAME2)
>>>>>
>>>>> 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_PATH2 & DOC_NAME2, , True)
>>>>>
>>>>> Set rst = New ADODB.Recordset
>>>>>
>>>>>
>>>>>
>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>
>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>
>>>>> adOpenStatic, adLockReadOnly
>>>>>
>>>>> If Not rst.EOF Then
>>>>>
>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>
>>>>> rst.Close
>>>>>
>>>>> End If
>>>>>
>>>>>
>>>>>
>>>>> With doc
>>>>>
>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>
>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>
>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>
>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>
>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>
>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>
>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>
>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>
>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>
>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Private Sub cmdPrintProducts3_Click()
>>>>>
>>>>> 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_NAME3)
>>>>>
>>>>> 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_PATH3 & DOC_NAME3, , True)
>>>>>
>>>>> Set rst = New ADODB.Recordset
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>
>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>
>>>>> adOpenStatic, adLockReadOnly
>>>>>
>>>>> If Not rst.EOF Then
>>>>>
>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>
>>>>> rst.Close
>>>>>
>>>>> End If
>>>>>
>>>>>
>>>>>
>>>>> With doc
>>>>>
>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>
>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>
>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>
>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>
>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>
>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>
>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>
>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>
>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>
>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>
>>>>> 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
>>>>>
>>>>> ================================================================
>>>>>
>>>>> The REST of the code is deleted for ease-of-viewing
>>>>>
>>>>> ================================================================
>>>>>
>>>>>
>>>>>
>>>>> Private Sub Form_RecordExit(Cancel As Integer)
>>>>>
>>>>> ' Hide the errormsg label to reduce flashing when navigating
>>>>>
>>>>> ' between records.
>>>>>
>>>>> errormsg.Visible = False
>>>>>
>>>>> End Sub
>>>>>
>>>>> Private Sub RemovePicture_Click()
>>>>>
>>>>> ' Clear the file name for the employee record and display the
>>>>>
>>>>> ' errormsg label.
>>>>>
>>>>> Me![ImagePath] = ""
>>>>>
>>>>> hideImageFrame
>>>>>
>>>>> errormsg.Visible = True
>>>>>
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> Private Sub Form_AfterUpdate()
>>>>>
>>>>> ' Requery the ReportsTo combo box after a record has been changed.
>>>>>
>>>>> ' Then, either show the errormsg label if no file name exists for
>>>>>
>>>>> ' the employee record or display the image if there is a file name
>>>>> that
>>>>>
>>>>> ' exists.
>>>>>
>>>>> 'Me!ReportsTo.Requery
>>>>>
>>>>> On Error Resume Next
>>>>>
>>>>> showErrorMessage
>>>>>
>>>>> showImageFrame
>>>>>
>>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>>
>>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>>
>>>>> Else
>>>>>
>>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>>
>>>>> End If
>>>>>
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> Private Sub ImagePath_AfterUpdate()
>>>>>
>>>>> ' After selecting an image for the employee, display it.
>>>>>
>>>>> On Error Resume Next
>>>>>
>>>>> showErrorMessage
>>>>>
>>>>> showImageFrame
>>>>>
>>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>>
>>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>>
>>>>> Else
>>>>>
>>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>>
>>>>> End If
>>>>>
>>>>> End Sub
>>>>>
>>>>> Private Sub Form_Current()
>>>>>
>>>>> ' Display the picture for the current employee record if the image
>>>>>
>>>>> ' exists. If the file name no longer exists or the file name was
>>>>> blank
>>>>>
>>>>> ' for the current employee, set the errormsg label caption to the
>>>>>
>>>>> ' appropriate message.
>>>>>
>>>>> Dim res As Boolean
>>>>>
>>>>> Dim fName As String
>>>>>
>>>>>
>>>>>
>>>>> path = CurrentProject.path
>>>>>
>>>>> On Error Resume Next
>>>>>
>>>>> errormsg.Visible = False
>>>>>
>>>>> If Not IsNull(Me!Photo) Then
>>>>>
>>>>> res = IsRelative(Me!Photo)
>>>>>
>>>>> fName = Me![ImagePath]
>>>>>
>>>>> If (res = True) Then
>>>>>
>>>>> fName = path & "\" & fName
>>>>>
>>>>> End If
>>>>>
>>>>>
>>>>>
>>>>> Me![ImageFrame].Picture = fName
>>>>>
>>>>> showImageFrame
>>>>>
>>>>> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>>>>>
>>>>> If (Me![ImageFrame].Picture <> fName) Then
>>>>>
>>>>> hideImageFrame
>>>>>
>>>>> errormsg.Caption = "Picture not found"
>>>>>
>>>>> errormsg.Visible = True
>>>>>
>>>>> End If
>>>>>
>>>>> Else
>>>>>
>>>>> hideImageFrame
>>>>>
>>>>> errormsg.Caption = "Click Add/Change to add picture"
>>>>>
>>>>> errormsg.Visible = True
>>>>>
>>>>> End If
>>>>>
>>>>>
>>>>>
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> Sub getFileName()
>>>>>
>>>>> ' Displays the Office File Open dialog to choose a file name
>>>>>
>>>>> ' for the current employee record. If the user selects a file
>>>>>
>>>>> ' display it in the image control.
>>>>>
>>>>> Dim fileName As String
>>>>>
>>>>> Dim result As Integer
>>>>>
>>>>> With Application.FileDialog(msoFileDialogFilePicker)
>>>>>
>>>>> .Title = "Select Employee Picture"
>>>>>
>>>>> .Filters.Add "All Files", "*.*"
>>>>>
>>>>> .Filters.Add "JPEGs", "*.jpg"
>>>>>
>>>>> .Filters.Add "Bitmaps", "*.bmp"
>>>>>
>>>>> .FilterIndex = 3
>>>>>
>>>>> .AllowMultiSelect = False
>>>>>
>>>>> .InitialFileName = CurrentProject.path
>>>>>
>>>>> result = .Show
>>>>>
>>>>> If (result <> 0) Then
>>>>>
>>>>> fileName = Trim(.SelectedItems.Item(1))
>>>>>
>>>>> Me![ImagePath].Visible = True
>>>>>
>>>>> Me![ImagePath].SetFocus
>>>>>
>>>>> Me![ImagePath].Text = fileName
>>>>>
>>>>> Me![FirstName].SetFocus
>>>>>
>>>>> Me![ImagePath].Visible = False
>>>>>
>>>>> End If
>>>>>
>>>>> End With
>>>>>
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> Sub showErrorMessage()
>>>>>
>>>>> ' Display the errormsg label if the image file is not available.
>>>>>
>>>>> If Not IsNull(Me!Photo) Then
>>>>>
>>>>> errormsg.Visible = False
>>>>>
>>>>> Else
>>>>>
>>>>> errormsg.Visible = True
>>>>>
>>>>> End If
>>>>>
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> Function IsRelative(fName As String) As Boolean
>>>>>
>>>>> ' Return false if the file name contains a drive or UNC path
>>>>>
>>>>> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\")
>>>>> = 0)
>>>>>
>>>>> End Function
>>>>>
>>>>>
>>>>>
>>>>> Sub hideImageFrame()
>>>>>
>>>>> ' Hide the image control
>>>>>
>>>>> Me![ImageFrame].Visible = False
>>>>>
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> Sub showImageFrame()
>>>>>
>>>>> ' Display the image control
>>>>>
>>>>> Me![ImageFrame].Visible = True
>>>>>
>>>>> End Sub
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
|
|
Yes, "PurchaseHistory" is the name of the sub-form which contains the
data-field "GrossPurchaseTotal"
================================
"Perry" <drumper[ at ]gmail.com> wrote in message
news:08E71733-43C9-4EA8-8758-99ABA14E7A44[ at ]microsoft.com...
[Quoted Text] > "PurchaseHistory" this is the subform where "GrossPurchaseTotal" is hosted
> in, isn't it?
> If it isn't, kindly forward the correct name of the subform (!) where
> "GrossPurchaseTotal" is hosted.
>
> --
> Krgrds,
> Perry
>
> System:
> Vista/Office Ultimate
> VS2005/VSTO2005 SE
>
>
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
> news:OU$jivzkHHA.4848[ at ]TK2MSFTNGP05.phx.gbl...
>> Perry,
>>
>> I replaced the snippet of code you referenced -- I got the same Error
>> Message at the same point.
>>
>> Note: The WORD document opens without incident, but the data does not
>> populate. When I close the WORD document, I see the Error Message.
>>
>> Shane
>> "Perry" <drumper[ at ]gmail.com> wrote in message
>> news:B11007B4-27AE-448F-A4AE-D56BE38752E7[ at ]microsoft.com...
>>> Replace
>>>> .FormFields("fldGrossPurchaseTotal").result = _
>>>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>>
>>> by
>>> .FormFields("fldGrossPurchaseTotal").result = _
>>> Nz(Me.Controls("PurchaseHistory").Form!GrossPurchaseTotal)
>>>
>>> Kindly repost whether this adjustment gets you further (or not)
>>> If it does get you further than this line, does the code fail elsewhere?
>>>
>>> --
>>> Krgrds,
>>> Perry
>>>
>>> System:
>>> Vista/Office Ultimate
>>> VS2005/VSTO2005 SE
>>>
>>>
>>>
>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>>> news:u2j%23WCykHHA.4768[ at ]TK2MSFTNGP05.phx.gbl...
>>>> Perry,
>>>>
>>>>
>>>>
>>>> I feel that I've done a poor job in describing my project .
>>>>
>>>>
>>>>
>>>> RE:
>>>>
>>>>
>>>>
>>>> Couple of things here:
>>>>
>>>> Y're trying to pick up values from an Access form, ok?
>>>>
>>>>
>>>>
>>>> Yes: I have a Front-End ACCESS db (linked to (3) Tables on a Back-End
>>>> SQL
>>>> Server). I have a series of Queries that manipulate the data and
>>>> populate
>>>> an ACCESS Form. This Form has several pages/tabs on it, and one
>>>> page/tab incorporates a sub-form.
>>>>
>>>> n Here's my quandry ... I'm trying to display (in a WORD
>>>> template) several fields from the Main Form (which is a Single Form),
>>>> and all data displayed in the sub-form (which is an Continuous Form
>>>> linked to the main-form by the ProductID field)
>>>>
>>>>
>>>>
>>>> No need to build up a seperate recordset to to populate "strReportsTo"
>>>> variable.
>>>>
>>>>
>>>>
>>>> This was some residual code left over from the Original Code that I'd
>>>> modified
>>>>
>>>>
>>>> Next:
>>>> I presume there's a button on your mainform to wire the
>>>> current/selected
>>>> record (form and subform-data) to MS Word.
>>>> Correct?
>>>>
>>>> Yes
>>>> So basically it comes down to the user "printing" the Access form (and
>>>> subform) data to a Word document by hitting that button.
>>>> Correct?
>>>>
>>>> Yes
>>>>
>>>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>>>> other
>>>> subforms) has to transfered to Word as well. Correct?
>>>>
>>>> Yes
>>>>
>>>> The last thing can be done by using something like (in the Button_Click
>>>> event code):
>>>> (whereby "MyOtherSubformName" is the name of the subform where
>>>> "GrossPurchaseTotal" is hosted)
>>>>
>>>> doc.FormFields("GrossPurchaseTotal").result =
>>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>>
>>>>
>>>>
>>>> I have the following code which I use to display the data (via Text
>>>> Form
>>>> Fields) in my WORD document (**SEE EXPLANATORY NOTES**):
>>>>
>>>>
>>>>
>>>> Option Compare Database
>>>> Option Explicit
>>>> Dim path As String
>>>>
>>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>> Const DOC_NAME1 As String = _
>>>> "Products1.dot"
>>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>> Const DOC_NAME2 As String = _
>>>> " Products2.dot "
>>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>> Const DOC_NAME3 As String = _
>>>> " Products3.dot "
>>>>
>>>>
>>>> Private Sub AddPicture_Click()
>>>> ' Use the Office File Open dialog to get a file name to use
>>>> ' as an employee picture.
>>>> getFileName
>>>> End Sub
>>>>
>>>> Private Sub cmdPrint Products1_Click()
>>>> Dim appWord As Word.Application
>>>> Dim doc As Word.Document
>>>> Dim rst As ADODB.Recordset
>>>> Dim strSQL 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_NAME1)
>>>> 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_PATH1 & DOC_NAME1, , True)
>>>> Set rst = New ADODB.Recordset
>>>>
>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>> adOpenStatic, adLockReadOnly
>>>>
>>>> With doc
>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>> (**THIS FIELD POPULATES IN WORD**)
>>>> .FormFields("fldAddress1").result = Nz(Me!Address1) (**THIS
>>>> FIELD POPULATES IN WORD**)
>>>> .FormFields("fldAddress2").result = Nz(Me!Address2) (**THIS
>>>> FIELD POPULATES IN WORD**)
>>>> .FormFields("fldCity").result = Nz(Me!City) (**THIS FIELD
>>>> POPULATES IN WORD**)
>>>> .FormFields("fldRegion").result = Nz(Me!Region) (**THIS FIELD
>>>> POPULATES IN WORD**)
>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode) (**THIS
>>>> FIELD POPULATES IN WORD**)
>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>> (**THIS FIELD POPULATES IN WORD**)
>>>> .FormFields("fldQty").result = Nz(Me!Qty) (**THIS FIELD
>>>> POPULATES IN WORD**)
>>>> .FormFields("fldPrice").result = Nz(Me!Price) (**THIS FIELD
>>>> POPULATES IN WORD**)
>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>> (**THIS FIELD POPULATES IN WORD**)
>>>>
>>>> (**This Last Line (below) - your suggested code - fails to populate, &
>>>> renders the following error message**)
>>>> .FormFields("fldGrossPurchaseTotal").result =
>>>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>>>
>>>> (**2465Microsoft Office Access can't find the field 'PurchaseHistory'
>>>> referred to in your expression**)
>>>>
>>>> **No Data shows in the "fldGrossPurchasTotal" Text Form Field in the
>>>> WORD document**
>>>>
>>>>
>>>>
>>>> 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
>>>>
>>>> Private Sub cmdPrintProducts2 _Click()
>>>> Dim appWord As Word.Application
>>>> Dim doc As Word.Document
>>>> Dim rst As ADODB.Recordset
>>>> Dim strSQL 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_NAME2)
>>>> 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_PATH2 & DOC_NAME2, , True)
>>>> Set rst = New ADODB.Recordset
>>>>
>>>>
>>>>
>>>>
>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>> adOpenStatic, adLockReadOnly
>>>>
>>>>
>>>> With doc
>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>
>>>> 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
>>>>
>>>> Private Sub cmdPrintProducts3_Click()
>>>> Dim appWord As Word.Application
>>>> Dim doc As Word.Document
>>>> Dim rst As ADODB.Recordset
>>>> Dim strSQL 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_NAME3)
>>>> 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_PATH3 & DOC_NAME3, , True)
>>>> Set rst = New ADODB.Recordset
>>>>
>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>> adOpenStatic, adLockReadOnly
>>>>
>>>> With doc
>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>
>>>> 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
>>>>
>>>> "Perry" <drumper[ at ]gmail.com> wrote in message
>>>> news:%233K5PKjkHHA.4112[ at ]TK2MSFTNGP04.phx.gbl...
>>>>> Couple of things here:
>>>>>
>>>>> Y're trying to pick up values from an Access form, ok?
>>>>> No need to build up a seperate recordset to to populate "strReportsTo"
>>>>> variable.
>>>>> Try to see whether there's a field (control) in your mainform that
>>>>> contains this information.
>>>>> Then use the value of this control to populate (and maintain) the
>>>>> variable.
>>>>>
>>>>> Next:
>>>>> I presume there's a button on your mainform to wire the
>>>>> current/selected record
>>>>> (form and subform-data) to MS Word.
>>>>> Correct?
>>>>> So basically it comes down to the user "printing" the Access form (and
>>>>> subform) data to a Word document by hitting that button.
>>>>> Correct?
>>>>>
>>>>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>>>>> other subforms) has to transfered to
>>>>> Word as well.
>>>>> Correct?
>>>>> The last thing can be done by using something like (in the
>>>>> Button_Click event code):
>>>>> (whereby "MyOtherSubformName" is the name of the subform where
>>>>> "GrossPurchaseTotal" is hosted)
>>>>>
>>>>> doc.FormFields("GrossPurchaseTotal").result =
>>>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>>>
>>>>> --
>>>>> Krgrds,
>>>>> Perry
>>>>>
>>>>> System:
>>>>> Vista/Office Ultimate
>>>>> VS2005/VSTO2005 SE
>>>>>
>>>>>
>>>>>
>>>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in
>>>>> bericht news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
>>>>>>I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
>>>>>> Server). I have a series of Queries that manipulate the data and
>>>>>> populate
>>>>>> an ACCESS Form. This Form has the following features:
>>>>>>
>>>>>> 1. A Main Form, with several pages (Tabs) which display data from
>>>>>> one
>>>>>> (OverallData) Table
>>>>>> a. One of these pages (Tabs) contains a Sub-Form which displays
>>>>>> data
>>>>>> from another (SpecificData) Table
>>>>>>
>>>>>> Here's my quandry ... I'm trying to display (in the WORD template) a
>>>>>> field
>>>>>> from the Sub-Form -- how do I modify the code-syntax to accomplish
>>>>>> this?
>>>>>> Example: .FormFields("fldDeliveryFee").result =
>>>>>> Nz(Me!DeliveryFee)
>>>>>>
>>>>>> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which
>>>>>> is
>>>>>> populated from a seperate table) -- what do I need to modify to
>>>>>> accomplish
>>>>>> this?
>>>>>>
>>>>>>
>>>>>>
>>>>>> I have the following code which I use to display the data (via Text
>>>>>> Form
>>>>>> Fields) in my WORD document:
>>>>>> ======================================
>>>>>> Option Compare Database
>>>>>> Option Explicit
>>>>>>
>>>>>> Dim path As String
>>>>>>
>>>>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>>>>
>>>>>> Const DOC_NAME1 As String = _
>>>>>>
>>>>>> "Products1.dot"
>>>>>>
>>>>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>>>>
>>>>>> Const DOC_NAME2 As String = _
>>>>>>
>>>>>> " Products2.dot "
>>>>>>
>>>>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>>>>
>>>>>> Const DOC_NAME3 As String = _
>>>>>>
>>>>>> " Products3.dot "
>>>>>>
>>>>>> Private Sub AddPicture_Click()
>>>>>>
>>>>>> ' Use the Office File Open dialog to get a file name to use
>>>>>>
>>>>>> ' as an employee picture.
>>>>>>
>>>>>> getFileName
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>> Private Sub cmdPrint Products1_Click()
>>>>>>
>>>>>> 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_NAME1)
>>>>>>
>>>>>> 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_PATH1 & DOC_NAME1, , True)
>>>>>>
>>>>>> Set rst = New ADODB.Recordset
>>>>>>
>>>>>>
>>>>>>
>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>
>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>
>>>>>> adOpenStatic, adLockReadOnly
>>>>>>
>>>>>> If Not rst.EOF Then
>>>>>>
>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>
>>>>>> rst.Close
>>>>>>
>>>>>> End If
>>>>>>
>>>>>>
>>>>>>
>>>>>> With doc
>>>>>>
>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>
>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>
>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>
>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>
>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>
>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>
>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>
>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>
>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>
>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>
>>>>>>
>>>>>>
>>>>>> 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
>>>>>>
>>>>>>
>>>>>>
>>>>>> Private Sub cmdPrintProducts2 _Click()
>>>>>>
>>>>>> 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_NAME2)
>>>>>>
>>>>>> 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_PATH2 & DOC_NAME2, , True)
>>>>>>
>>>>>> Set rst = New ADODB.Recordset
>>>>>>
>>>>>>
>>>>>>
>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>
>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>
>>>>>> adOpenStatic, adLockReadOnly
>>>>>>
>>>>>> If Not rst.EOF Then
>>>>>>
>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>
>>>>>> rst.Close
>>>>>>
>>>>>> End If
>>>>>>
>>>>>>
>>>>>>
>>>>>> With doc
>>>>>>
>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>
>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>
>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>
>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>
>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>
>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>
>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>
>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>
>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>
>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>
>>>>>> 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
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Private Sub cmdPrintProducts3_Click()
>>>>>>
>>>>>> 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_NAME3)
>>>>>>
>>>>>> 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_PATH3 & DOC_NAME3, , True)
>>>>>>
>>>>>> Set rst = New ADODB.Recordset
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>
>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>
>>>>>> adOpenStatic, adLockReadOnly
>>>>>>
>>>>>> If Not rst.EOF Then
>>>>>>
>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>
>>>>>> rst.Close
>>>>>>
>>>>>> End If
>>>>>>
>>>>>>
>>>>>>
>>>>>> With doc
>>>>>>
>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>
>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>
>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>
>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>
>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>
>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>
>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>
>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>
>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>
>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>
>>>>>> 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
>>>>>>
>>>>>> ================================================================
>>>>>>
>>>>>> The REST of the code is deleted for ease-of-viewing
>>>>>>
>>>>>> ================================================================
>>>>>>
>>>>>>
>>>>>>
>>>>>> Private Sub Form_RecordExit(Cancel As Integer)
>>>>>>
>>>>>> ' Hide the errormsg label to reduce flashing when navigating
>>>>>>
>>>>>> ' between records.
>>>>>>
>>>>>> errormsg.Visible = False
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>> Private Sub RemovePicture_Click()
>>>>>>
>>>>>> ' Clear the file name for the employee record and display the
>>>>>>
>>>>>> ' errormsg label.
>>>>>>
>>>>>> Me![ImagePath] = ""
>>>>>>
>>>>>> hideImageFrame
>>>>>>
>>>>>> errormsg.Visible = True
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>>
>>>>>> Private Sub Form_AfterUpdate()
>>>>>>
>>>>>> ' Requery the ReportsTo combo box after a record has been changed.
>>>>>>
>>>>>> ' Then, either show the errormsg label if no file name exists for
>>>>>>
>>>>>> ' the employee record or display the image if there is a file name
>>>>>> that
>>>>>>
>>>>>> ' exists.
>>>>>>
>>>>>> 'Me!ReportsTo.Requery
>>>>>>
>>>>>> On Error Resume Next
>>>>>>
>>>>>> showErrorMessage
>>>>>>
>>>>>> showImageFrame
>>>>>>
>>>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>>>
>>>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>>>
>>>>>> Else
>>>>>>
>>>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>>>
>>>>>> End If
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>>
>>>>>> Private Sub ImagePath_AfterUpdate()
>>>>>>
>>>>>> ' After selecting an image for the employee, display it.
>>>>>>
>>>>>> On Error Resume Next
>>>>>>
>>>>>> showErrorMessage
>>>>>>
>>>>>> showImageFrame
>>>>>>
>>>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>>>
>>>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>>>
>>>>>> Else
>>>>>>
>>>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>>>
>>>>>> End If
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>> Private Sub Form_Current()
>>>>>>
>>>>>> ' Display the picture for the current employee record if the image
>>>>>>
>>>>>> ' exists. If the file name no longer exists or the file name was
>>>>>> blank
>>>>>>
>>>>>> ' for the current employee, set the errormsg label caption to the
>>>>>>
>>>>>> ' appropriate message.
>>>>>>
>>>>>> Dim res As Boolean
>>>>>>
>>>>>> Dim fName As String
>>>>>>
>>>>>>
>>>>>>
>>>>>> path = CurrentProject.path
>>>>>>
>>>>>> On Error Resume Next
>>>>>>
>>>>>> errormsg.Visible = False
>>>>>>
>>>>>> If Not IsNull(Me!Photo) Then
>>>>>>
>>>>>> res = IsRelative(Me!Photo)
>>>>>>
>>>>>> fName = Me![ImagePath]
>>>>>>
>>>>>> If (res = True) Then
>>>>>>
>>>>>> fName = path & "\" & fName
>>>>>>
>>>>>> End If
>>>>>>
>>>>>>
>>>>>>
>>>>>> Me![ImageFrame].Picture = fName
>>>>>>
>>>>>> showImageFrame
>>>>>>
>>>>>> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>>>>>>
>>>>>> If (Me![ImageFrame].Picture <> fName) Then
>>>>>>
>>>>>> hideImageFrame
>>>>>>
>>>>>> errormsg.Caption = "Picture not found"
>>>>>>
>>>>>> errormsg.Visible = True
>>>>>>
>>>>>> End If
>>>>>>
>>>>>> Else
>>>>>>
>>>>>> hideImageFrame
>>>>>>
>>>>>> errormsg.Caption = "Click Add/Change to add picture"
>>>>>>
>>>>>> errormsg.Visible = True
>>>>>>
>>>>>> End If
>>>>>>
>>>>>>
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>>
>>>>>> Sub getFileName()
>>>>>>
>>>>>> ' Displays the Office File Open dialog to choose a file name
>>>>>>
>>>>>> ' for the current employee record. If the user selects a file
>>>>>>
>>>>>> ' display it in the image control.
>>>>>>
>>>>>> Dim fileName As String
>>>>>>
>>>>>> Dim result As Integer
>>>>>>
>>>>>> With Application.FileDialog(msoFileDialogFilePicker)
>>>>>>
>>>>>> .Title = "Select Employee Picture"
>>>>>>
>>>>>> .Filters.Add "All Files", "*.*"
>>>>>>
>>>>>> .Filters.Add "JPEGs", "*.jpg"
>>>>>>
>>>>>> .Filters.Add "Bitmaps", "*.bmp"
>>>>>>
>>>>>> .FilterIndex = 3
>>>>>>
>>>>>> .AllowMultiSelect = False
>>>>>>
>>>>>> .InitialFileName = CurrentProject.path
>>>>>>
>>>>>> result = .Show
>>>>>>
>>>>>> If (result <> 0) Then
>>>>>>
>>>>>> fileName = Trim(.SelectedItems.Item(1))
>>>>>>
>>>>>> Me![ImagePath].Visible = True
>>>>>>
>>>>>> Me![ImagePath].SetFocus
>>>>>>
>>>>>> Me![ImagePath].Text = fileName
>>>>>>
>>>>>> Me![FirstName].SetFocus
>>>>>>
>>>>>> Me![ImagePath].Visible = False
>>>>>>
>>>>>> End If
>>>>>>
>>>>>> End With
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>>
>>>>>> Sub showErrorMessage()
>>>>>>
>>>>>> ' Display the errormsg label if the image file is not available.
>>>>>>
>>>>>> If Not IsNull(Me!Photo) Then
>>>>>>
>>>>>> errormsg.Visible = False
>>>>>>
>>>>>> Else
>>>>>>
>>>>>> errormsg.Visible = True
>>>>>>
>>>>>> End If
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>>
>>>>>> Function IsRelative(fName As String) As Boolean
>>>>>>
>>>>>> ' Return false if the file name contains a drive or UNC path
>>>>>>
>>>>>> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\")
>>>>>> = 0)
>>>>>>
>>>>>> End Function
>>>>>>
>>>>>>
>>>>>>
>>>>>> Sub hideImageFrame()
>>>>>>
>>>>>> ' Hide the image control
>>>>>>
>>>>>> Me![ImageFrame].Visible = False
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>>
>>>>>> Sub showImageFrame()
>>>>>>
>>>>>> ' Display the image control
>>>>>>
>>>>>> Me![ImageFrame].Visible = True
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>
|
|
Doug,
I apologize for the delay in replying to your post ...
In reponse to your (2) questions ...
[Quoted Text] > If you put a MsgBox Nz(Me!DeliveryFee) command in your code, what does it
> display?
The MsgBox Returns the "DeliveryFee" value
> Also, what does MsgBox Me!GrossPurchaseTotal display?
Since "GrossPurchaseTotal is a data field in a table linked to the sub-form,
I get the following Error Message:
"2465Microsoft Office Access can't find the field 'GrossPurchaseTotal'
referred to in your expression"
=====================================================
"Doug Robbins - Word MVP" <dkr[ at ]REMOVECAPSmvps.org> wrote in message
news:OfEeRinjHHA.460[ at ]TK2MSFTNGP05.phx.gbl...
> What happens when you run your code now?
>
> If you put a
>
> MsgBox Nz(Me!DeliveryFee)
>
> command in your code, what does it display?
>
> Also, what does MsgBox Me!GrossPurchaseTotal display?
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> wrote in message
> news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
>>I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
>> Server). I have a series of Queries that manipulate the data and
>> populate
>> an ACCESS Form. This Form has the following features:
>>
>> 1. A Main Form, with several pages (Tabs) which display data from one
>> (OverallData) Table
>> a. One of these pages (Tabs) contains a Sub-Form which displays data
>> from another (SpecificData) Table
>>
>> Here's my quandry ... I'm trying to display (in the WORD template) a
>> field
>> from the Sub-Form -- how do I modify the code-syntax to accomplish this?
>> Example: .FormFields("fldDeliveryFee").result =
>> Nz(Me!DeliveryFee)
>>
>> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form (which is
>> populated from a seperate table) -- what do I need to modify to
>> accomplish
>> this?
>>
>>
>>
>> I have the following code which I use to display the data (via Text Form
>> Fields) in my WORD document:
>> ======================================
>> Option Compare Database
>> Option Explicit
>>
>> Dim path As String
>>
>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>
>> Const DOC_NAME1 As String = _
>>
>> "Products1.dot"
>>
>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>
>> Const DOC_NAME2 As String = _
>>
>> " Products2.dot "
>>
>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>
>> Const DOC_NAME3 As String = _
>>
>> " Products3.dot "
>>
>> Private Sub AddPicture_Click()
>>
>> ' Use the Office File Open dialog to get a file name to use
>>
>> ' as an employee picture.
>>
>> getFileName
>>
>> End Sub
>>
>> Private Sub cmdPrint Products1_Click()
>>
>> 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_NAME1)
>>
>> 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_PATH1 & DOC_NAME1, , True)
>>
>> Set rst = New ADODB.Recordset
>>
>>
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>>
>> rst.Open strSQL, CurrentProject.Connection, _
>>
>> adOpenStatic, adLockReadOnly
>>
>> If Not rst.EOF Then
>>
>> strReportsTo = Nz(rst.Fields(0).Value)
>>
>> rst.Close
>>
>> End If
>>
>>
>>
>> With doc
>>
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>
>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>
>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>
>> .FormFields("fldCity").result = Nz(Me!City)
>>
>> .FormFields("fldRegion").result = Nz(Me!Region)
>>
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>
>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>
>> .FormFields("fldQty").result = Nz(Me!Qty)
>>
>> .FormFields("fldPrice").result = Nz(Me!Price)
>>
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>
>>
>>
>> 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
>>
>>
>>
>> Private Sub cmdPrintProducts2 _Click()
>>
>> 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_NAME2)
>>
>> 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_PATH2 & DOC_NAME2, , True)
>>
>> Set rst = New ADODB.Recordset
>>
>>
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>>
>> rst.Open strSQL, CurrentProject.Connection, _
>>
>> adOpenStatic, adLockReadOnly
>>
>> If Not rst.EOF Then
>>
>> strReportsTo = Nz(rst.Fields(0).Value)
>>
>> rst.Close
>>
>> End If
>>
>>
>>
>> With doc
>>
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>
>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>
>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>
>> .FormFields("fldCity").result = Nz(Me!City)
>>
>> .FormFields("fldRegion").result = Nz(Me!Region)
>>
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>
>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>
>> .FormFields("fldQty").result = Nz(Me!Qty)
>>
>> .FormFields("fldPrice").result = Nz(Me!Price)
>>
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>
>> 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
>>
>>
>>
>>
>>
>> Private Sub cmdPrintProducts3_Click()
>>
>> 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_NAME3)
>>
>> 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_PATH3 & DOC_NAME3, , True)
>>
>> Set rst = New ADODB.Recordset
>>
>>
>>
>>
>>
>> strSQL = "SELECT * FROM PRODUCTS"
>>
>> rst.Open strSQL, CurrentProject.Connection, _
>>
>> adOpenStatic, adLockReadOnly
>>
>> If Not rst.EOF Then
>>
>> strReportsTo = Nz(rst.Fields(0).Value)
>>
>> rst.Close
>>
>> End If
>>
>>
>>
>> With doc
>>
>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>
>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>
>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>
>> .FormFields("fldCity").result = Nz(Me!City)
>>
>> .FormFields("fldRegion").result = Nz(Me!Region)
>>
>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>
>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>
>> .FormFields("fldQty").result = Nz(Me!Qty)
>>
>> .FormFields("fldPrice").result = Nz(Me!Price)
>>
>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>
>> 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
>>
>> ================================================================
>>
>> The REST of the code is deleted for ease-of-viewing
>>
>> ================================================================
>>
>>
>>
>> Private Sub Form_RecordExit(Cancel As Integer)
>>
>> ' Hide the errormsg label to reduce flashing when navigating
>>
>> ' between records.
>>
>> errormsg.Visible = False
>>
>> End Sub
>>
>> Private Sub RemovePicture_Click()
>>
>> ' Clear the file name for the employee record and display the
>>
>> ' errormsg label.
>>
>> Me![ImagePath] = ""
>>
>> hideImageFrame
>>
>> errormsg.Visible = True
>>
>> End Sub
>>
>>
>>
>> Private Sub Form_AfterUpdate()
>>
>> ' Requery the ReportsTo combo box after a record has been changed.
>>
>> ' Then, either show the errormsg label if no file name exists for
>>
>> ' the employee record or display the image if there is a file name
>> that
>>
>> ' exists.
>>
>> 'Me!ReportsTo.Requery
>>
>> On Error Resume Next
>>
>> showErrorMessage
>>
>> showImageFrame
>>
>> If (IsRelative(Me!ImagePath) = True) Then
>>
>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>
>> Else
>>
>> Me![ImageFrame].Picture = Me![ImagePath]
>>
>> End If
>>
>> End Sub
>>
>>
>>
>> Private Sub ImagePath_AfterUpdate()
>>
>> ' After selecting an image for the employee, display it.
>>
>> On Error Resume Next
>>
>> showErrorMessage
>>
>> showImageFrame
>>
>> If (IsRelative(Me!ImagePath) = True) Then
>>
>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>
>> Else
>>
>> Me![ImageFrame].Picture = Me![ImagePath]
>>
>> End If
>>
>> End Sub
>>
>> Private Sub Form_Current()
>>
>> ' Display the picture for the current employee record if the image
>>
>> ' exists. If the file name no longer exists or the file name was
>> blank
>>
>> ' for the current employee, set the errormsg label caption to the
>>
>> ' appropriate message.
>>
>> Dim res As Boolean
>>
>> Dim fName As String
>>
>>
>>
>> path = CurrentProject.path
>>
>> On Error Resume Next
>>
>> errormsg.Visible = False
>>
>> If Not IsNull(Me!Photo) Then
>>
>> res = IsRelative(Me!Photo)
>>
>> fName = Me![ImagePath]
>>
>> If (res = True) Then
>>
>> fName = path & "\" & fName
>>
>> End If
>>
>>
>>
>> Me![ImageFrame].Picture = fName
>>
>> showImageFrame
>>
>> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>>
>> If (Me![ImageFrame].Picture <> fName) Then
>>
>> hideImageFrame
>>
>> errormsg.Caption = "Picture not found"
>>
>> errormsg.Visible = True
>>
>> End If
>>
>> Else
>>
>> hideImageFrame
>>
>> errormsg.Caption = "Click Add/Change to add picture"
>>
>> errormsg.Visible = True
>>
>> End If
>>
>>
>>
>> End Sub
>>
>>
>>
>> Sub getFileName()
>>
>> ' Displays the Office File Open dialog to choose a file name
>>
>> ' for the current employee record. If the user selects a file
>>
>> ' display it in the image control.
>>
>> Dim fileName As String
>>
>> Dim result As Integer
>>
>> With Application.FileDialog(msoFileDialogFilePicker)
>>
>> .Title = "Select Employee Picture"
>>
>> .Filters.Add "All Files", "*.*"
>>
>> .Filters.Add "JPEGs", "*.jpg"
>>
>> .Filters.Add "Bitmaps", "*.bmp"
>>
>> .FilterIndex = 3
>>
>> .AllowMultiSelect = False
>>
>> .InitialFileName = CurrentProject.path
>>
>> result = .Show
>>
>> If (result <> 0) Then
>>
>> fileName = Trim(.SelectedItems.Item(1))
>>
>> Me![ImagePath].Visible = True
>>
>> Me![ImagePath].SetFocus
>>
>> Me![ImagePath].Text = fileName
>>
>> Me![FirstName].SetFocus
>>
>> Me![ImagePath].Visible = False
>>
>> End If
>>
>> End With
>>
>> End Sub
>>
>>
>>
>> Sub showErrorMessage()
>>
>> ' Display the errormsg label if the image file is not available.
>>
>> If Not IsNull(Me!Photo) Then
>>
>> errormsg.Visible = False
>>
>> Else
>>
>> errormsg.Visible = True
>>
>> End If
>>
>> End Sub
>>
>>
>>
>> Function IsRelative(fName As String) As Boolean
>>
>> ' Return false if the file name contains a drive or UNC path
>>
>> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") =
>> 0)
>>
>> End Function
>>
>>
>>
>> Sub hideImageFrame()
>>
>> ' Hide the image control
>>
>> Me![ImageFrame].Visible = False
>>
>> End Sub
>>
>>
>>
>> Sub showImageFrame()
>>
>> ' Display the image control
>>
>> Me![ImageFrame].Visible = True
>>
>> End Sub
>>
>>
>
>
|
|
Are you sure that "GrossPurchaseTotal" is the name of the data control on yr
subform?
Sorry for spamming you, but this should work.
--
Krgrds,
Perry
System:
Vista/Office Ultimate
VS2005/VSTO2005 SE
"Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
news:u7XP6A0kHHA.4152[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Yes, "PurchaseHistory" is the name of the sub-form which contains the
> data-field "GrossPurchaseTotal"
> ================================
> "Perry" <drumper[ at ]gmail.com> wrote in message
> news:08E71733-43C9-4EA8-8758-99ABA14E7A44[ at ]microsoft.com...
>> "PurchaseHistory" this is the subform where "GrossPurchaseTotal" is
>> hosted in, isn't it?
>> If it isn't, kindly forward the correct name of the subform (!) where
>> "GrossPurchaseTotal" is hosted.
>>
>> --
>> Krgrds,
>> Perry
>>
>> System:
>> Vista/Office Ultimate
>> VS2005/VSTO2005 SE
>>
>>
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>> news:OU$jivzkHHA.4848[ at ]TK2MSFTNGP05.phx.gbl...
>>> Perry,
>>>
>>> I replaced the snippet of code you referenced -- I got the same Error
>>> Message at the same point.
>>>
>>> Note: The WORD document opens without incident, but the data does not
>>> populate. When I close the WORD document, I see the Error Message.
>>>
>>> Shane
>>> "Perry" <drumper[ at ]gmail.com> wrote in message
>>> news:B11007B4-27AE-448F-A4AE-D56BE38752E7[ at ]microsoft.com...
>>>> Replace
>>>>> .FormFields("fldGrossPurchaseTotal").result = _
>>>>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>>>
>>>> by
>>>> .FormFields("fldGrossPurchaseTotal").result = _
>>>> Nz(Me.Controls("PurchaseHistory").Form!GrossPurchaseTotal)
>>>>
>>>> Kindly repost whether this adjustment gets you further (or not)
>>>> If it does get you further than this line, does the code fail
>>>> elsewhere?
>>>>
>>>> --
>>>> Krgrds,
>>>> Perry
>>>>
>>>> System:
>>>> Vista/Office Ultimate
>>>> VS2005/VSTO2005 SE
>>>>
>>>>
>>>>
>>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>>>> news:u2j%23WCykHHA.4768[ at ]TK2MSFTNGP05.phx.gbl...
>>>>> Perry,
>>>>>
>>>>>
>>>>>
>>>>> I feel that I've done a poor job in describing my project .
>>>>>
>>>>>
>>>>>
>>>>> RE:
>>>>>
>>>>>
>>>>>
>>>>> Couple of things here:
>>>>>
>>>>> Y're trying to pick up values from an Access form, ok?
>>>>>
>>>>>
>>>>>
>>>>> Yes: I have a Front-End ACCESS db (linked to (3) Tables on a Back-End
>>>>> SQL
>>>>> Server). I have a series of Queries that manipulate the data and
>>>>> populate
>>>>> an ACCESS Form. This Form has several pages/tabs on it, and one
>>>>> page/tab incorporates a sub-form.
>>>>>
>>>>> n Here's my quandry ... I'm trying to display (in a WORD
>>>>> template) several fields from the Main Form (which is a Single Form),
>>>>> and all data displayed in the sub-form (which is an Continuous Form
>>>>> linked to the main-form by the ProductID field)
>>>>>
>>>>>
>>>>>
>>>>> No need to build up a seperate recordset to to populate "strReportsTo"
>>>>> variable.
>>>>>
>>>>>
>>>>>
>>>>> This was some residual code left over from the Original Code that I'd
>>>>> modified
>>>>>
>>>>>
>>>>> Next:
>>>>> I presume there's a button on your mainform to wire the
>>>>> current/selected
>>>>> record (form and subform-data) to MS Word.
>>>>> Correct?
>>>>>
>>>>> Yes
>>>>> So basically it comes down to the user "printing" the Access form (and
>>>>> subform) data to a Word document by hitting that button.
>>>>> Correct?
>>>>>
>>>>> Yes
>>>>>
>>>>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>>>>> other
>>>>> subforms) has to transfered to Word as well. Correct?
>>>>>
>>>>> Yes
>>>>>
>>>>> The last thing can be done by using something like (in the
>>>>> Button_Click event code):
>>>>> (whereby "MyOtherSubformName" is the name of the subform where
>>>>> "GrossPurchaseTotal" is hosted)
>>>>>
>>>>> doc.FormFields("GrossPurchaseTotal").result =
>>>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>>>
>>>>>
>>>>>
>>>>> I have the following code which I use to display the data (via Text
>>>>> Form
>>>>> Fields) in my WORD document (**SEE EXPLANATORY NOTES**):
>>>>>
>>>>>
>>>>>
>>>>> Option Compare Database
>>>>> Option Explicit
>>>>> Dim path As String
>>>>>
>>>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>>> Const DOC_NAME1 As String = _
>>>>> "Products1.dot"
>>>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>>> Const DOC_NAME2 As String = _
>>>>> " Products2.dot "
>>>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>>> Const DOC_NAME3 As String = _
>>>>> " Products3.dot "
>>>>>
>>>>>
>>>>> Private Sub AddPicture_Click()
>>>>> ' Use the Office File Open dialog to get a file name to use
>>>>> ' as an employee picture.
>>>>> getFileName
>>>>> End Sub
>>>>>
>>>>> Private Sub cmdPrint Products1_Click()
>>>>> Dim appWord As Word.Application
>>>>> Dim doc As Word.Document
>>>>> Dim rst As ADODB.Recordset
>>>>> Dim strSQL 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_NAME1)
>>>>> 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_PATH1 & DOC_NAME1, , True)
>>>>> Set rst = New ADODB.Recordset
>>>>>
>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>> adOpenStatic, adLockReadOnly
>>>>>
>>>>> With doc
>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>> (**THIS FIELD POPULATES IN WORD**)
>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1) (**THIS
>>>>> FIELD POPULATES IN WORD**)
>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2) (**THIS
>>>>> FIELD POPULATES IN WORD**)
>>>>> .FormFields("fldCity").result = Nz(Me!City) (**THIS FIELD
>>>>> POPULATES IN WORD**)
>>>>> .FormFields("fldRegion").result = Nz(Me!Region) (**THIS FIELD
>>>>> POPULATES IN WORD**)
>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode) (**THIS
>>>>> FIELD POPULATES IN WORD**)
>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>> (**THIS FIELD POPULATES IN WORD**)
>>>>> .FormFields("fldQty").result = Nz(Me!Qty) (**THIS FIELD
>>>>> POPULATES IN WORD**)
>>>>> .FormFields("fldPrice").result = Nz(Me!Price) (**THIS FIELD
>>>>> POPULATES IN WORD**)
>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>> (**THIS FIELD POPULATES IN WORD**)
>>>>>
>>>>> (**This Last Line (below) - your suggested code - fails to populate, &
>>>>> renders the following error message**)
>>>>> .FormFields("fldGrossPurchaseTotal").result =
>>>>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>>>>
>>>>> (**2465Microsoft Office Access can't find the field 'PurchaseHistory'
>>>>> referred to in your expression**)
>>>>>
>>>>> **No Data shows in the "fldGrossPurchasTotal" Text Form Field in the
>>>>> WORD document**
>>>>>
>>>>>
>>>>>
>>>>> 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
>>>>>
>>>>> Private Sub cmdPrintProducts2 _Click()
>>>>> Dim appWord As Word.Application
>>>>> Dim doc As Word.Document
>>>>> Dim rst As ADODB.Recordset
>>>>> Dim strSQL 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_NAME2)
>>>>> 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_PATH2 & DOC_NAME2, , True)
>>>>> Set rst = New ADODB.Recordset
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>> adOpenStatic, adLockReadOnly
>>>>>
>>>>>
>>>>> With doc
>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>
>>>>> 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
>>>>>
>>>>> Private Sub cmdPrintProducts3_Click()
>>>>> Dim appWord As Word.Application
>>>>> Dim doc As Word.Document
>>>>> Dim rst As ADODB.Recordset
>>>>> Dim strSQL 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_NAME3)
>>>>> 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_PATH3 & DOC_NAME3, , True)
>>>>> Set rst = New ADODB.Recordset
>>>>>
>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>> adOpenStatic, adLockReadOnly
>>>>>
>>>>> With doc
>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>
>>>>> 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
>>>>>
>>>>> "Perry" <drumper[ at ]gmail.com> wrote in message
>>>>> news:%233K5PKjkHHA.4112[ at ]TK2MSFTNGP04.phx.gbl...
>>>>>> Couple of things here:
>>>>>>
>>>>>> Y're trying to pick up values from an Access form, ok?
>>>>>> No need to build up a seperate recordset to to populate
>>>>>> "strReportsTo" variable.
>>>>>> Try to see whether there's a field (control) in your mainform that
>>>>>> contains this information.
>>>>>> Then use the value of this control to populate (and maintain) the
>>>>>> variable.
>>>>>>
>>>>>> Next:
>>>>>> I presume there's a button on your mainform to wire the
>>>>>> current/selected record
>>>>>> (form and subform-data) to MS Word.
>>>>>> Correct?
>>>>>> So basically it comes down to the user "printing" the Access form
>>>>>> (and subform) data to a Word document by hitting that button.
>>>>>> Correct?
>>>>>>
>>>>>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>>>>>> other subforms) has to transfered to
>>>>>> Word as well.
>>>>>> Correct?
>>>>>> The last thing can be done by using something like (in the
>>>>>> Button_Click event code):
>>>>>> (whereby "MyOtherSubformName" is the name of the subform where
>>>>>> "GrossPurchaseTotal" is hosted)
>>>>>>
>>>>>> doc.FormFields("GrossPurchaseTotal").result =
>>>>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>>>>
>>>>>> --
>>>>>> Krgrds,
>>>>>> Perry
>>>>>>
>>>>>> System:
>>>>>> Vista/Office Ultimate
>>>>>> VS2005/VSTO2005 SE
>>>>>>
>>>>>>
>>>>>>
>>>>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in
>>>>>> bericht news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
>>>>>>>I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
>>>>>>> Server). I have a series of Queries that manipulate the data and
>>>>>>> populate
>>>>>>> an ACCESS Form. This Form has the following features:
>>>>>>>
>>>>>>> 1. A Main Form, with several pages (Tabs) which display data from
>>>>>>> one
>>>>>>> (OverallData) Table
>>>>>>> a. One of these pages (Tabs) contains a Sub-Form which displays
>>>>>>> data
>>>>>>> from another (SpecificData) Table
>>>>>>>
>>>>>>> Here's my quandry ... I'm trying to display (in the WORD template) a
>>>>>>> field
>>>>>>> from the Sub-Form -- how do I modify the code-syntax to accomplish
>>>>>>> this?
>>>>>>> Example: .FormFields("fldDeliveryFee").result =
>>>>>>> Nz(Me!DeliveryFee)
>>>>>>>
>>>>>>> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form
>>>>>>> (which is
>>>>>>> populated from a seperate table) -- what do I need to modify to
>>>>>>> accomplish
>>>>>>> this?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> I have the following code which I use to display the data (via Text
>>>>>>> Form
>>>>>>> Fields) in my WORD document:
>>>>>>> ======================================
>>>>>>> Option Compare Database
>>>>>>> Option Explicit
>>>>>>>
>>>>>>> Dim path As String
>>>>>>>
>>>>>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>>>>>
>>>>>>> Const DOC_NAME1 As String = _
>>>>>>>
>>>>>>> "Products1.dot"
>>>>>>>
>>>>>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>>>>>
>>>>>>> Const DOC_NAME2 As String = _
>>>>>>>
>>>>>>> " Products2.dot "
>>>>>>>
>>>>>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>>>>>
>>>>>>> Const DOC_NAME3 As String = _
>>>>>>>
>>>>>>> " Products3.dot "
>>>>>>>
>>>>>>> Private Sub AddPicture_Click()
>>>>>>>
>>>>>>> ' Use the Office File Open dialog to get a file name to use
>>>>>>>
>>>>>>> ' as an employee picture.
>>>>>>>
>>>>>>> getFileName
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>> Private Sub cmdPrint Products1_Click()
>>>>>>>
>>>>>>> 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_NAME1)
>>>>>>>
>>>>>>> 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_PATH1 & DOC_NAME1, , True)
>>>>>>>
>>>>>>> Set rst = New ADODB.Recordset
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>>
>>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>>
>>>>>>> adOpenStatic, adLockReadOnly
>>>>>>>
>>>>>>> If Not rst.EOF Then
>>>>>>>
>>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>>
>>>>>>> rst.Close
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> With doc
>>>>>>>
>>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>>
>>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>>
>>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>>
>>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>>
>>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>>
>>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>>
>>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>>
>>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>>
>>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>>
>>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Private Sub cmdPrintProducts2 _Click()
>>>>>>>
>>>>>>> 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_NAME2)
>>>>>>>
>>>>>>> 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_PATH2 & DOC_NAME2, , True)
>>>>>>>
>>>>>>> Set rst = New ADODB.Recordset
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>>
>>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>>
>>>>>>> adOpenStatic, adLockReadOnly
>>>>>>>
>>>>>>> If Not rst.EOF Then
>>>>>>>
>>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>>
>>>>>>> rst.Close
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> With doc
>>>>>>>
>>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>>
>>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>>
>>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>>
>>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>>
>>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>>
>>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>>
>>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>>
>>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>>
>>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>>
>>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>>
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Private Sub cmdPrintProducts3_Click()
>>>>>>>
>>>>>>> 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_NAME3)
>>>>>>>
>>>>>>> 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_PATH3 & DOC_NAME3, , True)
>>>>>>>
>>>>>>> Set rst = New ADODB.Recordset
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>>
>>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>>
>>>>>>> adOpenStatic, adLockReadOnly
>>>>>>>
>>>>>>> If Not rst.EOF Then
>>>>>>>
>>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>>
>>>>>>> rst.Close
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> With doc
>>>>>>>
>>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>>
>>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>>
>>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>>
>>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>>
>>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>>
>>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>>
>>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>>
>>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>>
>>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>>
>>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>>
>>>>>>> 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
>>>>>>>
>>>>>>> ================================================================
>>>>>>>
>>>>>>> The REST of the code is deleted for ease-of-viewing
>>>>>>>
>>>>>>> ================================================================
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Private Sub Form_RecordExit(Cancel As Integer)
>>>>>>>
>>>>>>> ' Hide the errormsg label to reduce flashing when navigating
>>>>>>>
>>>>>>> ' between records.
>>>>>>>
>>>>>>> errormsg.Visible = False
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>> Private Sub RemovePicture_Click()
>>>>>>>
>>>>>>> ' Clear the file name for the employee record and display the
>>>>>>>
>>>>>>> ' errormsg label.
>>>>>>>
>>>>>>> Me![ImagePath] = ""
>>>>>>>
>>>>>>> hideImageFrame
>>>>>>>
>>>>>>> errormsg.Visible = True
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Private Sub Form_AfterUpdate()
>>>>>>>
>>>>>>> ' Requery the ReportsTo combo box after a record has been
>>>>>>> changed.
>>>>>>>
>>>>>>> ' Then, either show the errormsg label if no file name exists for
>>>>>>>
>>>>>>> ' the employee record or display the image if there is a file
>>>>>>> name that
>>>>>>>
>>>>>>> ' exists.
>>>>>>>
>>>>>>> 'Me!ReportsTo.Requery
>>>>>>>
>>>>>>> On Error Resume Next
>>>>>>>
>>>>>>> showErrorMessage
>>>>>>>
>>>>>>> showImageFrame
>>>>>>>
>>>>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>>>>
>>>>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>>>>
>>>>>>> Else
>>>>>>>
>>>>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Private Sub ImagePath_AfterUpdate()
>>>>>>>
>>>>>>> ' After selecting an image for the employee, display it.
>>>>>>>
>>>>>>> On Error Resume Next
>>>>>>>
>>>>>>> showErrorMessage
>>>>>>>
>>>>>>> showImageFrame
>>>>>>>
>>>>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>>>>
>>>>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>>>>
>>>>>>> Else
>>>>>>>
>>>>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>> Private Sub Form_Current()
>>>>>>>
>>>>>>> ' Display the picture for the current employee record if the
>>>>>>> image
>>>>>>>
>>>>>>> ' exists. If the file name no longer exists or the file name was
>>>>>>> blank
>>>>>>>
>>>>>>> ' for the current employee, set the errormsg label caption to the
>>>>>>>
>>>>>>> ' appropriate message.
>>>>>>>
>>>>>>> Dim res As Boolean
>>>>>>>
>>>>>>> Dim fName As String
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> path = CurrentProject.path
>>>>>>>
>>>>>>> On Error Resume Next
>>>>>>>
>>>>>>> errormsg.Visible = False
>>>>>>>
>>>>>>> If Not IsNull(Me!Photo) Then
>>>>>>>
>>>>>>> res = IsRelative(Me!Photo)
>>>>>>>
>>>>>>> fName = Me![ImagePath]
>>>>>>>
>>>>>>> If (res = True) Then
>>>>>>>
>>>>>>> fName = path & "\" & fName
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Me![ImageFrame].Picture = fName
>>>>>>>
>>>>>>> showImageFrame
>>>>>>>
>>>>>>> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>>>>>>>
>>>>>>> If (Me![ImageFrame].Picture <> fName) Then
>>>>>>>
>>>>>>> hideImageFrame
>>>>>>>
>>>>>>> errormsg.Caption = "Picture not found"
>>>>>>>
>>>>>>> errormsg.Visible = True
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>> Else
>>>>>>>
>>>>>>> hideImageFrame
>>>>>>>
>>>>>>> errormsg.Caption = "Click Add/Change to add picture"
>>>>>>>
>>>>>>> errormsg.Visible = True
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Sub getFileName()
>>>>>>>
>>>>>>> ' Displays the Office File Open dialog to choose a file name
>>>>>>>
>>>>>>> ' for the current employee record. If the user selects a file
>>>>>>>
>>>>>>> ' display it in the image control.
>>>>>>>
>>>>>>> Dim fileName As String
>>>>>>>
>>>>>>> Dim result As Integer
>>>>>>>
>>>>>>> With Application.FileDialog(msoFileDialogFilePicker)
>>>>>>>
>>>>>>> .Title = "Select Employee Picture"
>>>>>>>
>>>>>>> .Filters.Add "All Files", "*.*"
>>>>>>>
>>>>>>> .Filters.Add "JPEGs", "*.jpg"
>>>>>>>
>>>>>>> .Filters.Add "Bitmaps", "*.bmp"
>>>>>>>
>>>>>>> .FilterIndex = 3
>>>>>>>
>>>>>>> .AllowMultiSelect = False
>>>>>>>
>>>>>>> .InitialFileName = CurrentProject.path
>>>>>>>
>>>>>>> result = .Show
>>>>>>>
>>>>>>> If (result <> 0) Then
>>>>>>>
>>>>>>> fileName = Trim(.SelectedItems.Item(1))
>>>>>>>
>>>>>>> Me![ImagePath].Visible = True
>>>>>>>
>>>>>>> Me![ImagePath].SetFocus
>>>>>>>
>>>>>>> Me![ImagePath].Text = fileName
>>>>>>>
>>>>>>> Me![FirstName].SetFocus
>>>>>>>
>>>>>>> Me![ImagePath].Visible = False
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Sub showErrorMessage()
>>>>>>>
>>>>>>> ' Display the errormsg label if the image file is not available.
>>>>>>>
>>>>>>> If Not IsNull(Me!Photo) Then
>>>>>>>
>>>>>>> errormsg.Visible = False
>>>>>>>
>>>>>>> Else
>>>>>>>
>>>>>>> errormsg.Visible = True
>>>>>>>
>>>>>>> End If
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Function IsRelative(fName As String) As Boolean
>>>>>>>
>>>>>>> ' Return false if the file name contains a drive or UNC path
>>>>>>>
>>>>>>> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName,
>>>>>>> "\\") = 0)
>>>>>>>
>>>>>>> End Function
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Sub hideImageFrame()
>>>>>>>
>>>>>>> ' Hide the image control
>>>>>>>
>>>>>>> Me![ImageFrame].Visible = False
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Sub showImageFrame()
>>>>>>>
>>>>>>> ' Display the image control
>>>>>>>
>>>>>>> Me![ImageFrame].Visible = True
>>>>>>>
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
|
|
Perry,
No worries -- I didn't consider it spamming -- I welcome your assistance --
I will recheck my code. Perhaps our terminology is at odds. Let me specify
what I have:
MAIN FORM -- A Single Form (Data populates fine from the Main Form)
Form Properties:
Record Source: a query with [Enter ProductID] parameter
Text Box Properties:
Name: CompanyName
Control Source: Company Name
WORD Text Form Field:
Bookmark: fldCompanyName
SUB FORM/SUB REPORT: PurchaseHistory -- A Continuous Form
Name: Child72
Source Object: PurchaseHistory (which is a separate query)
Form
Record Source: ProductSpecific (Table)
Text Box Properties:
Name: GrossPurchaseTotal
Control Source: Gross Purchase Total
WORD Text Form Field:
Bookmark: fldGrossPurchaseTotal
Here's what I just changed and the data NOW POPULATES :)
doc.FormFields("fldGrossPurchaseTotal").result = _
Nz(Me.Controls("Child72").Form!GrossPurchaseTotal)
==========================
The problem I have now is -- On my Sub-Form, I have a Product Name field as
well -- I could have up to 10 products on the Sub-Form, but I'm not sure how
to populate each of these 10 Products on the WORD document.
Example:
My Sub-Form fields are:
ProductID CustomerID Product Name GrossPurchaseTotal
================================================
0001 102 Pillows $4,700
0005 201 Sheets $7,500
0007 202 Comforter $1,200
ONLY the 1st Product is being populate in the WORD document -- how do I
create a LOOP to capture all the entries on the Sub-Form?
Thank you for all you help with this Perry.
Shane
"Perry" <drumper[ at ]gmail.com> wrote in message
news:B878636D-29F2-44BA-9EA1-F207C19D40D3[ at ]microsoft.com...
[Quoted Text] > Are you sure that "GrossPurchaseTotal" is the name of the data control on
> yr subform?
>
> Sorry for spamming you, but this should work.
>
> --
> Krgrds,
> Perry
>
> System:
> Vista/Office Ultimate
> VS2005/VSTO2005 SE
>
>
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
> news:u7XP6A0kHHA.4152[ at ]TK2MSFTNGP04.phx.gbl...
>> Yes, "PurchaseHistory" is the name of the sub-form which contains the
>> data-field "GrossPurchaseTotal"
>> ================================
>> "Perry" <drumper[ at ]gmail.com> wrote in message
>> news:08E71733-43C9-4EA8-8758-99ABA14E7A44[ at ]microsoft.com...
>>> "PurchaseHistory" this is the subform where "GrossPurchaseTotal" is
>>> hosted in, isn't it?
>>> If it isn't, kindly forward the correct name of the subform (!) where
>>> "GrossPurchaseTotal" is hosted.
>>>
>>> --
>>> Krgrds,
>>> Perry
>>>
>>> System:
>>> Vista/Office Ultimate
>>> VS2005/VSTO2005 SE
>>>
>>>
>>>
>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>>> news:OU$jivzkHHA.4848[ at ]TK2MSFTNGP05.phx.gbl...
>>>> Perry,
>>>>
>>>> I replaced the snippet of code you referenced -- I got the same Error
>>>> Message at the same point.
>>>>
>>>> Note: The WORD document opens without incident, but the data does not
>>>> populate. When I close the WORD document, I see the Error Message.
>>>>
>>>> Shane
>>>> "Perry" <drumper[ at ]gmail.com> wrote in message
>>>> news:B11007B4-27AE-448F-A4AE-D56BE38752E7[ at ]microsoft.com...
>>>>> Replace
>>>>>> .FormFields("fldGrossPurchaseTotal").result = _
>>>>>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>>>>
>>>>> by
>>>>> .FormFields("fldGrossPurchaseTotal").result = _
>>>>> Nz(Me.Controls("PurchaseHistory").Form!GrossPurchaseTotal)
>>>>>
>>>>> Kindly repost whether this adjustment gets you further (or not)
>>>>> If it does get you further than this line, does the code fail
>>>>> elsewhere?
>>>>>
>>>>> --
>>>>> Krgrds,
>>>>> Perry
>>>>>
>>>>> System:
>>>>> Vista/Office Ultimate
>>>>> VS2005/VSTO2005 SE
>>>>>
>>>>>
>>>>>
>>>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in
>>>>> bericht news:u2j%23WCykHHA.4768[ at ]TK2MSFTNGP05.phx.gbl...
>>>>>> Perry,
>>>>>>
>>>>>>
>>>>>>
>>>>>> I feel that I've done a poor job in describing my project .
>>>>>>
>>>>>>
>>>>>>
>>>>>> RE:
>>>>>>
>>>>>>
>>>>>>
>>>>>> Couple of things here:
>>>>>>
>>>>>> Y're trying to pick up values from an Access form, ok?
>>>>>>
>>>>>>
>>>>>>
>>>>>> Yes: I have a Front-End ACCESS db (linked to (3) Tables on a
>>>>>> Back-End SQL
>>>>>> Server). I have a series of Queries that manipulate the data and
>>>>>> populate
>>>>>> an ACCESS Form. This Form has several pages/tabs on it, and one
>>>>>> page/tab incorporates a sub-form.
>>>>>>
>>>>>> n Here's my quandry ... I'm trying to display (in a WORD
>>>>>> template) several fields from the Main Form (which is a Single Form),
>>>>>> and all data displayed in the sub-form (which is an Continuous Form
>>>>>> linked to the main-form by the ProductID field)
>>>>>>
>>>>>>
>>>>>>
>>>>>> No need to build up a seperate recordset to to populate
>>>>>> "strReportsTo"
>>>>>> variable.
>>>>>>
>>>>>>
>>>>>>
>>>>>> This was some residual code left over from the Original Code that I'd
>>>>>> modified
>>>>>>
>>>>>>
>>>>>> Next:
>>>>>> I presume there's a button on your mainform to wire the
>>>>>> current/selected
>>>>>> record (form and subform-data) to MS Word.
>>>>>> Correct?
>>>>>>
>>>>>> Yes
>>>>>> So basically it comes down to the user "printing" the Access form
>>>>>> (and
>>>>>> subform) data to a Word document by hitting that button.
>>>>>> Correct?
>>>>>>
>>>>>> Yes
>>>>>>
>>>>>> In doing this, the field "GrossPurchaseTotal" (present on one of your
>>>>>> other
>>>>>> subforms) has to transfered to Word as well. Correct?
>>>>>>
>>>>>> Yes
>>>>>>
>>>>>> The last thing can be done by using something like (in the
>>>>>> Button_Click event code):
>>>>>> (whereby "MyOtherSubformName" is the name of the subform where
>>>>>> "GrossPurchaseTotal" is hosted)
>>>>>>
>>>>>> doc.FormFields("GrossPurchaseTotal").result =
>>>>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>>>>
>>>>>>
>>>>>>
>>>>>> I have the following code which I use to display the data (via Text
>>>>>> Form
>>>>>> Fields) in my WORD document (**SEE EXPLANATORY NOTES**):
>>>>>>
>>>>>>
>>>>>>
>>>>>> Option Compare Database
>>>>>> Option Explicit
>>>>>> Dim path As String
>>>>>>
>>>>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>>>> Const DOC_NAME1 As String = _
>>>>>> "Products1.dot"
>>>>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>>>> Const DOC_NAME2 As String = _
>>>>>> " Products2.dot "
>>>>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>>>> Const DOC_NAME3 As String = _
>>>>>> " Products3.dot "
>>>>>>
>>>>>>
>>>>>> Private Sub AddPicture_Click()
>>>>>> ' Use the Office File Open dialog to get a file name to use
>>>>>> ' as an employee picture.
>>>>>> getFileName
>>>>>> End Sub
>>>>>>
>>>>>> Private Sub cmdPrint Products1_Click()
>>>>>> Dim appWord As Word.Application
>>>>>> Dim doc As Word.Document
>>>>>> Dim rst As ADODB.Recordset
>>>>>> Dim strSQL 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_NAME1)
>>>>>> 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_PATH1 & DOC_NAME1, , True)
>>>>>> Set rst = New ADODB.Recordset
>>>>>>
>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>> adOpenStatic, adLockReadOnly
>>>>>>
>>>>>> With doc
>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>> (**THIS FIELD POPULATES IN WORD**)
>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1) (**THIS
>>>>>> FIELD POPULATES IN WORD**)
>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2) (**THIS
>>>>>> FIELD POPULATES IN WORD**)
>>>>>> .FormFields("fldCity").result = Nz(Me!City) (**THIS FIELD
>>>>>> POPULATES IN WORD**)
>>>>>> .FormFields("fldRegion").result = Nz(Me!Region) (**THIS FIELD
>>>>>> POPULATES IN WORD**)
>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>> (**THIS FIELD POPULATES IN WORD**)
>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>> (**THIS FIELD POPULATES IN WORD**)
>>>>>> .FormFields("fldQty").result = Nz(Me!Qty) (**THIS FIELD
>>>>>> POPULATES IN WORD**)
>>>>>> .FormFields("fldPrice").result = Nz(Me!Price) (**THIS FIELD
>>>>>> POPULATES IN WORD**)
>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>> (**THIS FIELD POPULATES IN WORD**)
>>>>>>
>>>>>> (**This Last Line (below) - your suggested code - fails to populate,
>>>>>> & renders the following error message**)
>>>>>> .FormFields("fldGrossPurchaseTotal").result =
>>>>>> Nz(Me!PurchaseHistory.Form!GrossPurchaseTotal)
>>>>>>
>>>>>> (**2465Microsoft Office Access can't find the field 'PurchaseHistory'
>>>>>> referred to in your expression**)
>>>>>>
>>>>>> **No Data shows in the "fldGrossPurchasTotal" Text Form Field in the
>>>>>> WORD document**
>>>>>>
>>>>>>
>>>>>>
>>>>>> 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
>>>>>>
>>>>>> Private Sub cmdPrintProducts2 _Click()
>>>>>> Dim appWord As Word.Application
>>>>>> Dim doc As Word.Document
>>>>>> Dim rst As ADODB.Recordset
>>>>>> Dim strSQL 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_NAME2)
>>>>>> 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_PATH2 & DOC_NAME2, , True)
>>>>>> Set rst = New ADODB.Recordset
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>> adOpenStatic, adLockReadOnly
>>>>>>
>>>>>>
>>>>>> With doc
>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>
>>>>>> 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
>>>>>>
>>>>>> Private Sub cmdPrintProducts3_Click()
>>>>>> Dim appWord As Word.Application
>>>>>> Dim doc As Word.Document
>>>>>> Dim rst As ADODB.Recordset
>>>>>> Dim strSQL 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_NAME3)
>>>>>> 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_PATH3 & DOC_NAME3, , True)
>>>>>> Set rst = New ADODB.Recordset
>>>>>>
>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>> adOpenStatic, adLockReadOnly
>>>>>>
>>>>>> With doc
>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>
>>>>>> 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
>>>>>>
>>>>>> "Perry" <drumper[ at ]gmail.com> wrote in message
>>>>>> news:%233K5PKjkHHA.4112[ at ]TK2MSFTNGP04.phx.gbl...
>>>>>>> Couple of things here:
>>>>>>>
>>>>>>> Y're trying to pick up values from an Access form, ok?
>>>>>>> No need to build up a seperate recordset to to populate
>>>>>>> "strReportsTo" variable.
>>>>>>> Try to see whether there's a field (control) in your mainform that
>>>>>>> contains this information.
>>>>>>> Then use the value of this control to populate (and maintain) the
>>>>>>> variable.
>>>>>>>
>>>>>>> Next:
>>>>>>> I presume there's a button on your mainform to wire the
>>>>>>> current/selected record
>>>>>>> (form and subform-data) to MS Word.
>>>>>>> Correct?
>>>>>>> So basically it comes down to the user "printing" the Access form
>>>>>>> (and subform) data to a Word document by hitting that button.
>>>>>>> Correct?
>>>>>>>
>>>>>>> In doing this, the field "GrossPurchaseTotal" (present on one of
>>>>>>> your other subforms) has to transfered to
>>>>>>> Word as well.
>>>>>>> Correct?
>>>>>>> The last thing can be done by using something like (in the
>>>>>>> Button_Click event code):
>>>>>>> (whereby "MyOtherSubformName" is the name of the subform where
>>>>>>> "GrossPurchaseTotal" is hosted)
>>>>>>>
>>>>>>> doc.FormFields("GrossPurchaseTotal").result =
>>>>>>> Nz(Me!MyOtherSubformName.Form!GrossPurchaseTotal)
>>>>>>>
>>>>>>> --
>>>>>>> Krgrds,
>>>>>>> Perry
>>>>>>>
>>>>>>> System:
>>>>>>> Vista/Office Ultimate
>>>>>>> VS2005/VSTO2005 SE
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in
>>>>>>> bericht news:%23FoxEbnjHHA.4188[ at ]TK2MSFTNGP02.phx.gbl...
>>>>>>>>I have a Front-End ACCESS db (linked to (3) Tables on a Back-End SQL
>>>>>>>> Server). I have a series of Queries that manipulate the data and
>>>>>>>> populate
>>>>>>>> an ACCESS Form. This Form has the following features:
>>>>>>>>
>>>>>>>> 1. A Main Form, with several pages (Tabs) which display data from
>>>>>>>> one
>>>>>>>> (OverallData) Table
>>>>>>>> a. One of these pages (Tabs) contains a Sub-Form which displays
>>>>>>>> data
>>>>>>>> from another (SpecificData) Table
>>>>>>>>
>>>>>>>> Here's my quandry ... I'm trying to display (in the WORD template)
>>>>>>>> a field
>>>>>>>> from the Sub-Form -- how do I modify the code-syntax to accomplish
>>>>>>>> this?
>>>>>>>> Example: .FormFields("fldDeliveryFee").result =
>>>>>>>> Nz(Me!DeliveryFee)
>>>>>>>>
>>>>>>>> NOW: I want to include "GrossPurchaseTotal" from the Sub-Form
>>>>>>>> (which is
>>>>>>>> populated from a seperate table) -- what do I need to modify to
>>>>>>>> accomplish
>>>>>>>> this?
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> I have the following code which I use to display the data (via Text
>>>>>>>> Form
>>>>>>>> Fields) in my WORD document:
>>>>>>>> ======================================
>>>>>>>> Option Compare Database
>>>>>>>> Option Explicit
>>>>>>>>
>>>>>>>> Dim path As String
>>>>>>>>
>>>>>>>> Const DOC_PATH1 As String = "\\Fileserver\Products\ "
>>>>>>>>
>>>>>>>> Const DOC_NAME1 As String = _
>>>>>>>>
>>>>>>>> "Products1.dot"
>>>>>>>>
>>>>>>>> Const DOC_PATH2 As String = "\\Fileserver\Products\ "
>>>>>>>>
>>>>>>>> Const DOC_NAME2 As String = _
>>>>>>>>
>>>>>>>> " Products2.dot "
>>>>>>>>
>>>>>>>> Const DOC_PATH3 As String = "\\Fileserver\Products\ "
>>>>>>>>
>>>>>>>> Const DOC_NAME3 As String = _
>>>>>>>>
>>>>>>>> " Products3.dot "
>>>>>>>>
>>>>>>>> Private Sub AddPicture_Click()
>>>>>>>>
>>>>>>>> ' Use the Office File Open dialog to get a file name to use
>>>>>>>>
>>>>>>>> ' as an employee picture.
>>>>>>>>
>>>>>>>> getFileName
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Private Sub cmdPrint Products1_Click()
>>>>>>>>
>>>>>>>> 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_NAME1)
>>>>>>>>
>>>>>>>> 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_PATH1 & DOC_NAME1, , True)
>>>>>>>>
>>>>>>>> Set rst = New ADODB.Recordset
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>>>
>>>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>>>
>>>>>>>> adOpenStatic, adLockReadOnly
>>>>>>>>
>>>>>>>> If Not rst.EOF Then
>>>>>>>>
>>>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>>>
>>>>>>>> rst.Close
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> With doc
>>>>>>>>
>>>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>>>
>>>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>>>
>>>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>>>
>>>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>>>
>>>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>>>
>>>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>>>
>>>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>>>
>>>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>>>
>>>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>>>
>>>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Private Sub cmdPrintProducts2 _Click()
>>>>>>>>
>>>>>>>> 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_NAME2)
>>>>>>>>
>>>>>>>> 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_PATH2 & DOC_NAME2, , True)
>>>>>>>>
>>>>>>>> Set rst = New ADODB.Recordset
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>>>
>>>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>>>
>>>>>>>> adOpenStatic, adLockReadOnly
>>>>>>>>
>>>>>>>> If Not rst.EOF Then
>>>>>>>>
>>>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>>>
>>>>>>>> rst.Close
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> With doc
>>>>>>>>
>>>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>>>
>>>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>>>
>>>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>>>
>>>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>>>
>>>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>>>
>>>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>>>
>>>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>>>
>>>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>>>
>>>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>>>
>>>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>>>
>>>>>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Private Sub cmdPrintProducts3_Click()
>>>>>>>>
>>>>>>>> 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_NAME3)
>>>>>>>>
>>>>>>>> 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_PATH3 & DOC_NAME3, , True)
>>>>>>>>
>>>>>>>> Set rst = New ADODB.Recordset
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> strSQL = "SELECT * FROM PRODUCTS"
>>>>>>>>
>>>>>>>> rst.Open strSQL, CurrentProject.Connection, _
>>>>>>>>
>>>>>>>> adOpenStatic, adLockReadOnly
>>>>>>>>
>>>>>>>> If Not rst.EOF Then
>>>>>>>>
>>>>>>>> strReportsTo = Nz(rst.Fields(0).Value)
>>>>>>>>
>>>>>>>> rst.Close
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> With doc
>>>>>>>>
>>>>>>>> .FormFields("fldCompanyName").result = Nz(Me!CompanyName)
>>>>>>>>
>>>>>>>> .FormFields("fldAddress1").result = Nz(Me!Address1)
>>>>>>>>
>>>>>>>> .FormFields("fldAddress2").result = Nz(Me!Address2)
>>>>>>>>
>>>>>>>> .FormFields("fldCity").result = Nz(Me!City)
>>>>>>>>
>>>>>>>> .FormFields("fldRegion").result = Nz(Me!Region)
>>>>>>>>
>>>>>>>> .FormFields("fldPostalCode").result = Nz(Me!PostalCode)
>>>>>>>>
>>>>>>>> .FormFields("fldProductName").result = Nz(Me!ProductName)
>>>>>>>>
>>>>>>>> .FormFields("fldQty").result = Nz(Me!Qty)
>>>>>>>>
>>>>>>>> .FormFields("fldPrice").result = Nz(Me!Price)
>>>>>>>>
>>>>>>>> .FormFields("fldDeliveryFee").result = Nz(Me!DeliveryFee)
>>>>>>>>
>>>>>>>> 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
>>>>>>>>
>>>>>>>> ================================================================
>>>>>>>>
>>>>>>>> The REST of the code is deleted for ease-of-viewing
>>>>>>>>
>>>>>>>> ================================================================
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Private Sub Form_RecordExit(Cancel As Integer)
>>>>>>>>
>>>>>>>> ' Hide the errormsg label to reduce flashing when navigating
>>>>>>>>
>>>>>>>> ' between records.
>>>>>>>>
>>>>>>>> errormsg.Visible = False
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Private Sub RemovePicture_Click()
>>>>>>>>
>>>>>>>> ' Clear the file name for the employee record and display the
>>>>>>>>
>>>>>>>> ' errormsg label.
>>>>>>>>
>>>>>>>> Me![ImagePath] = ""
>>>>>>>>
>>>>>>>> hideImageFrame
>>>>>>>>
>>>>>>>> errormsg.Visible = True
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Private Sub Form_AfterUpdate()
>>>>>>>>
>>>>>>>> ' Requery the ReportsTo combo box after a record has been
>>>>>>>> changed.
>>>>>>>>
>>>>>>>> ' Then, either show the errormsg label if no file name exists
>>>>>>>> for
>>>>>>>>
>>>>>>>> ' the employee record or display the image if there is a file
>>>>>>>> name that
>>>>>>>>
>>>>>>>> ' exists.
>>>>>>>>
>>>>>>>> 'Me!ReportsTo.Requery
>>>>>>>>
>>>>>>>> On Error Resume Next
>>>>>>>>
>>>>>>>> showErrorMessage
>>>>>>>>
>>>>>>>> showImageFrame
>>>>>>>>
>>>>>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>>>>>
>>>>>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>>>>>
>>>>>>>> Else
>>>>>>>>
>>>>>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Private Sub ImagePath_AfterUpdate()
>>>>>>>>
>>>>>>>> ' After selecting an image for the employee, display it.
>>>>>>>>
>>>>>>>> On Error Resume Next
>>>>>>>>
>>>>>>>> showErrorMessage
>>>>>>>>
>>>>>>>> showImageFrame
>>>>>>>>
>>>>>>>> If (IsRelative(Me!ImagePath) = True) Then
>>>>>>>>
>>>>>>>> Me![ImageFrame].Picture = path & Me![ImagePath]
>>>>>>>>
>>>>>>>> Else
>>>>>>>>
>>>>>>>> Me![ImageFrame].Picture = Me![ImagePath]
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Private Sub Form_Current()
>>>>>>>>
>>>>>>>> ' Display the picture for the current employee record if the
>>>>>>>> image
>>>>>>>>
>>>>>>>> ' exists. If the file name no longer exists or the file name
>>>>>>>> was blank
>>>>>>>>
>>>>>>>> ' for the current employee, set the errormsg label caption to
>>>>>>>> the
>>>>>>>>
>>>>>>>> ' appropriate message.
>>>>>>>>
>>>>>>>> Dim res As Boolean
>>>>>>>>
>>>>>>>> Dim fName As String
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> path = CurrentProject.path
>>>>>>>>
>>>>>>>> On Error Resume Next
>>>>>>>>
>>>>>>>> errormsg.Visible = False
>>>>>>>>
>>>>>>>> If Not IsNull(Me!Photo) Then
>>>>>>>>
>>>>>>>> res = IsRelative(Me!Photo)
>>>>>>>>
>>>>>>>> fName = Me![ImagePath]
>>>>>>>>
>>>>>>>> If (res = True) Then
>>>>>>>>
>>>>>>>> fName = path & "\" & fName
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Me![ImageFrame].Picture = fName
>>>>>>>>
>>>>>>>> showImageFrame
>>>>>>>>
>>>>>>>> Me.PaintPalette = Me![ImageFrame].ObjectPalette
>>>>>>>>
>>>>>>>> If (Me![ImageFrame].Picture <> fName) Then
>>>>>>>>
>>>>>>>> hideImageFrame
>>>>>>>>
>>>>>>>> errormsg.Caption = "Picture not found"
>>>>>>>>
>>>>>>>> errormsg.Visible = True
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>> Else
>>>>>>>>
>>>>>>>> hideImageFrame
>>>>>>>>
>>>>>>>> errormsg.Caption = "Click Add/Change to add picture"
>>>>>>>>
>>>>>>>> errormsg.Visible = True
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Sub getFileName()
>>>>>>>>
>>>>>>>> ' Displays the Office File Open dialog to choose a file name
>>>>>>>>
>>>>>>>> ' for the current employee record. If the user selects a file
>>>>>>>>
>>>>>>>> ' display it in the image control.
>>>>>>>>
>>>>>>>> Dim fileName As String
>>>>>>>>
>>>>>>>> Dim result As Integer
>>>>>>>>
>>>>>>>> With Application.FileDialog(msoFileDialogFilePicker)
>>>>>>>>
>>>>>>>> .Title = "Select Employee Picture"
>>>>>>>>
>>>>>>>> .Filters.Add "All Files", "*.*"
>>>>>>>>
>>>>>>>> .Filters.Add "JPEGs", "*.jpg"
>>>>>>>>
>>>>>>>> .Filters.Add "Bitmaps", "*.bmp"
>>>>>>>>
>>>>>>>> .FilterIndex = 3
>>>>>>>>
>>>>>>>> .AllowMultiSelect = False
>>>>>>>>
>>>>>>>> .InitialFileName = CurrentProject.path
>>>>>>>>
>>>>>>>> result = .Show
>>>>>>>>
>>>>>>>> If (result <> 0) Then
>>>>>>>>
>>>>>>>> fileName = Trim(.SelectedItems.Item(1))
>>>>>>>>
>>>>>>>> Me![ImagePath].Visible = True
>>>>>>>>
>>>>>>>> Me![ImagePath].SetFocus
>>>>>>>>
>>>>>>>> Me![ImagePath].Text = fileName
>>>>>>>>
>>>>>>>> Me![FirstName].SetFocus
>>>>>>>>
>>>>>>>> Me![ImagePath].Visible = False
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>> End With
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Sub showErrorMessage()
>>>>>>>>
>>>>>>>> ' Display the errormsg label if the image file is not available.
>>>>>>>>
>>>>>>>> If Not IsNull(Me!Photo) Then
>>>>>>>>
>>>>>>>> errormsg.Visible = False
>>>>>>>>
>>>>>>>> Else
>>>>>>>>
>>>>>>>> errormsg.Visible = True
>>>>>>>>
>>>>>>>> End If
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Function IsRelative(fName As String) As Boolean
>>>>>>>>
>>>>>>>> ' Return false if the file name contains a drive or UNC path
>>>>>>>>
>>>>>>>> IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName,
>>>>>>>> "\\") = 0)
>>>>>>>>
>>>>>>>> End Function
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Sub hideImageFrame()
>>>>>>>>
>>>>>>>> ' Hide the image control
>>>>>>>>
>>>>>>>> Me![ImageFrame].Visible = False
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Sub showImageFrame()
>>>>>>>>
>>>>>>>> ' Display the image control
>>>>>>>>
>>>>>>>> Me![ImageFrame].Visible = True
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>
|
|
Perry,
Here's what I have now ...
==================================================
Here's what I just changed and the data NOW POPULATES :)
doc.FormFields("fldGrossPurchaseTotal").result = _
Nz(Me.Controls("Child72").Form!GrossPurchaseTotal)
==========================
The problem I have now is -- On my Sub-Form, I have a Product Name field as
well -- I could have up to 10 products on the Sub-Form, but I'm not sure how
to populate each of these 10 Products on the WORD document.
Example:
My Sub-Form fields are:
ProductID CustomerID Product Name GrossPurchaseTotal
================================================
0001 102 Pillows $4,700
0005 201 Sheets $7,500
0007 202 Comforter $1,200
ONLY the 1st Product is being populated in the WORD document -- how do I
create a LOOP to capture all the entries on the Sub-Form?
I have the following piece of code (from Pat Hartman, MVP): Pat explains
that (in the case of a Sub-Form -- Child72 in my case) the
doc.FormFields("fldCompanyName").result = Nz(Me!CompanyName) syntax
references to the CURRENT record in the subform, and that if the subform
is continuous (so that it shows multiple records), I would need to be more
sophisticated in my approach. He explained that (in this case) he creates a
long
text string by looping through the recordset. He then separates the fields
with a vbTab and uses vbCr to separate rows. Finally, he inserts the text
at a
bookmark/formfield and convert the text to a table. You can use any of the
standard table formats or format your own specifically.
Example:
InsertTextAtBookMark bkmk, strTable
Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
objTable.AutoFormat Format:=wdTableFormatClassic1, applyshading:=True,
applyHeadingrows:=False, AutoFit:=True
Pat's code:
==============
OK, here is the section of code that creates the string that is passed to
the sub I posted earlier. The code opens a query that takes one parameter -
the variable data header ID. It then loops through the recordset,
concatenating the text string returned. When there are no more records, the
loop ends and the string is passed to the sub that makes it a table in word.
In the case of this code, only one field from the table is used and so only
the ending vbCr is needed. If you were using multiple fields, then the code
would look like:
sTableItems = sTableItems & iSeqNum & ". " & rsDAO!SubjectiveText & vbTab &
rsDAO!otherfield1 & rsDAO!otherfield2 & vbCr
'Open subjectivities recordset
Set qdDAO = db.QueryDefs!qMergeSubjectivities
qdDAO.Parameters![EnterVariableDataHeaderID] =
Me.txtVariableDataHeaderID
Set rsDAO = qdDAO.OpenRecordset
sTableItems = ""
If rsDAO.EOF Then
Else
iSeqNum = 0
Do While rsDAO.EOF = False
iSeqNum = iSeqNum + 1
sTableItems = sTableItems & iSeqNum & ". " &
rsDAO!SubjectiveText & vbCr <------alternate version above
rsDAO.MoveNext
Loop
End If
Set qdDAO = Nothing
If sTableItems <> "" Then
sTableItems = Left(sTableItems, Len(sTableItems) - 1) 'remove
final vbCr to eliminate extra line at end
Call Finish1Column("Subjectivities_Items", sTableItems)
end if
==============================
Perry -- I don't really understand the code though -- specifically, I don't
understand why I need to specify the database (when I've already established
the connection)
RE: Set qdDAO = db.QueryDefs!qMergeSubjectivities
qdDAO.Parameters![EnterVariableDataHeaderID] =
Me.txtVariableDataHeaderID
'This piece of code copies the Requery_Specifics sub-form data to a WORD
template
Set qdDAO = db.Demo!qPurchaseHistory 'where Demo is the name of the
ACCESS db, and qPurchaseHistory is the name of the Query
qdDAO.Parameters![Enter Product ID] = Me.txtProductID
Set rsDAO = qdDAO.OpenRecordset
sTableItems = ""
If rsDAO.EOF Then
Else
iSeqNum = 0
Do While rsDAO.EOF = False
iSeqNum = iSeqNum + 1
sTableItems = sTableItems & iSeqNum & ". " & rsDAO!ProductID &
vbTab & rsDAO![Product Name] & vbCr
rsDAO.MoveNext
Loop
End If
Set qdDAO = Nothing
If sTableItems <> "" Then
sTableItems = Left(sTableItems, Len(sTableItems) - 1) 'Removes final
vbCr to eliminate extra line at end
Call Finish1Column("Subjectivities_Items", sTableItems) 'I'm not
certain what this Call does
End If
InsertTextAtBookMark ProductName, strTableItems
Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
objTable.AutoFormat Format:=wdTableFormatClassic1, applyshading:=True,
applyHeadingrows:=False, AutoFit:=True
End Sub
When I run the sub, I get the following error message:
"Compile Error:
Method or data not found"
In the VBE, ".Demo" (SEE BELOW) is highlighted in my code:
Set qdDAO = db.Demo!qPurchaseHistory 'where Demo is the name of the
ACCESS db, and qPurchaseHistory is the name of the Query
qdDAO.Parameters![Enter Product ID] = Me.txtProductID
Any thoughts?
Thank you for all you help with this Perry.
|
|
|
[Quoted Text] > well -- I could have up to 10 products on the Sub-Form, but I'm not sure
> how
> to populate each of these 10 Products on the WORD document.
In such case:
Build up a string variable, based on a seperate query.
Use the ID (selected record) of the parentform of the products subform.
Read the resultset of the query to build up a string variable in which the
records
are delimited by a vbCrLf.
Now, you can wire this variable to ms Word.
What you can do as well:
Use the product subform's datasource, and read this as a DAO.Recordset, as
in
(pseudo code)
Dim rs As dao.recordset
set rs = Mysubform.RecordSetClone
Now you can loop through the records of your subform (recordset) to build up
a string variable, delimited by vbCrLf and wire this variable to ms Word.
--
Krgrds,
Perry
System:
Vista/Office Ultimate
VS2005/VSTO2005 SE
"Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
news:uhh0w8$kHHA.4936[ at ]TK2MSFTNGP03.phx.gbl...
> Perry,
>
> Here's what I have now ...
> ==================================================
> Here's what I just changed and the data NOW POPULATES :)
> doc.FormFields("fldGrossPurchaseTotal").result = _
> Nz(Me.Controls("Child72").Form!GrossPurchaseTotal)
> ==========================
> The problem I have now is -- On my Sub-Form, I have a Product Name field
> as
> well -- I could have up to 10 products on the Sub-Form, but I'm not sure
> how
> to populate each of these 10 Products on the WORD document.
>
> Example:
>
> My Sub-Form fields are:
>
> ProductID CustomerID Product Name GrossPurchaseTotal
> ================================================
> 0001 102 Pillows $4,700
> 0005 201 Sheets $7,500
> 0007 202 Comforter $1,200
>
> ONLY the 1st Product is being populated in the WORD document -- how do I
> create a LOOP to capture all the entries on the Sub-Form?
>
> I have the following piece of code (from Pat Hartman, MVP): Pat explains
> that (in the case of a Sub-Form -- Child72 in my case) the
> doc.FormFields("fldCompanyName").result = Nz(Me!CompanyName) syntax
> references to the CURRENT record in the subform, and that if the subform
> is continuous (so that it shows multiple records), I would need to be more
> sophisticated in my approach. He explained that (in this case) he creates
> a long
> text string by looping through the recordset. He then separates the
> fields with a vbTab and uses vbCr to separate rows. Finally, he inserts
> the text at a
> bookmark/formfield and convert the text to a table. You can use any of
> the
> standard table formats or format your own specifically.
>
> Example:
>
> InsertTextAtBookMark bkmk, strTable
> Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
> objTable.AutoFormat Format:=wdTableFormatClassic1, applyshading:=True,
> applyHeadingrows:=False, AutoFit:=True
>
>
> Pat's code:
> ==============
> OK, here is the section of code that creates the string that is passed to
> the sub I posted earlier. The code opens a query that takes one
> parameter -
> the variable data header ID. It then loops through the recordset,
> concatenating the text string returned. When there are no more records,
> the
> loop ends and the string is passed to the sub that makes it a table in
> word.
> In the case of this code, only one field from the table is used and so
> only
> the ending vbCr is needed. If you were using multiple fields, then the
> code
> would look like:
>
> sTableItems = sTableItems & iSeqNum & ". " & rsDAO!SubjectiveText & vbTab
> &
> rsDAO!otherfield1 & rsDAO!otherfield2 & vbCr
>
> 'Open subjectivities recordset
> Set qdDAO = db.QueryDefs!qMergeSubjectivities
> qdDAO.Parameters![EnterVariableDataHeaderID] =
> Me.txtVariableDataHeaderID
> Set rsDAO = qdDAO.OpenRecordset
> sTableItems = ""
> If rsDAO.EOF Then
> Else
> iSeqNum = 0
> Do While rsDAO.EOF = False
> iSeqNum = iSeqNum + 1
> sTableItems = sTableItems & iSeqNum & ". " &
> rsDAO!SubjectiveText & vbCr <------alternate version above
> rsDAO.MoveNext
> Loop
> End If
> Set qdDAO = Nothing
> If sTableItems <> "" Then
> sTableItems = Left(sTableItems, Len(sTableItems) - 1) 'remove
> final vbCr to eliminate extra line at end
> Call Finish1Column("Subjectivities_Items", sTableItems)
> end if
> ==============================
> Perry -- I don't really understand the code though -- specifically, I
> don't understand why I need to specify the database (when I've already
> established the connection)
> RE: Set qdDAO = db.QueryDefs!qMergeSubjectivities
> qdDAO.Parameters![EnterVariableDataHeaderID] =
> Me.txtVariableDataHeaderID
>
> 'This piece of code copies the Requery_Specifics sub-form data to a WORD
> template
> Set qdDAO = db.Demo!qPurchaseHistory 'where Demo is the name of the
> ACCESS db, and qPurchaseHistory is the name of the Query
> qdDAO.Parameters![Enter Product ID] = Me.txtProductID
> Set rsDAO = qdDAO.OpenRecordset
> sTableItems = ""
> If rsDAO.EOF Then
> Else
> iSeqNum = 0
> Do While rsDAO.EOF = False
> iSeqNum = iSeqNum + 1
> sTableItems = sTableItems & iSeqNum & ". " & rsDAO!ProductID &
> vbTab & rsDAO![Product Name] & vbCr
> rsDAO.MoveNext
> Loop
> End If
> Set qdDAO = Nothing
> If sTableItems <> "" Then
> sTableItems = Left(sTableItems, Len(sTableItems) - 1) 'Removes
> final vbCr to eliminate extra line at end
>
> Call Finish1Column("Subjectivities_Items", sTableItems) 'I'm not
> certain what this Call does
> End If
>
> InsertTextAtBookMark ProductName, strTableItems
> Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
> objTable.AutoFormat Format:=wdTableFormatClassic1, applyshading:=True,
> applyHeadingrows:=False, AutoFit:=True
>
> End Sub
>
> When I run the sub, I get the following error message:
> "Compile Error:
> Method or data not found"
>
> In the VBE, ".Demo" (SEE BELOW) is highlighted in my code:
> Set qdDAO = db.Demo!qPurchaseHistory 'where Demo is the name of the
> ACCESS db, and qPurchaseHistory is the name of the Query
> qdDAO.Parameters![Enter Product ID] = Me.txtProductID
>
> Any thoughts?
>
> Thank you for all you help with this Perry.
>
>
|
|
Perry,
I'm kinda "wrapped around the axle" on this one -- does the following code
mirror the concept you offered in your latest post:
-- code found here: http://www.tek-tips.com/faqs.cfm?fid=760
===================================
WORD TEMPLATE
Create your Word document with a template format. Save it as a template
file (.dot). Use bookmarks to mark the place you want the data to be pulled
in. You can have as many bookmarks as you want. If you require the data to
be pulled into tables, don't create tables in Word, but let Access VBA
create the tables for you.
ACCESS DATABASE
Set up queries showing the fields you want to transfer to the Word document.
You may need to set up more than one query. If you have to do that, then
you will need to set up each as a recordset in the code with its own SQL
string. The idea of the SQL string is to narrow down the records in the
recordset to the exact info you need.
Assuming only two queries were made, code as follows:
In a module, key in a Public variable to be shared in database
Option Compare Database
Option Explicit
' location of the documents and templates -
' Where will Access find the Word Template?
Public Const m_strDIR As String = "d:\database\"
Public Const m_strTEMPLATE As String = "submittalcd.dot"
' set up objects for use and Public variables to be shared in database
Private m_objWord As Word.Application
Private m_objDoc As Word.Document
Public strProdNum As String
In the Forms Button for starting the event.
Create SQL statements based on the values of the active record (i.e.,
prodnum)
Click event:
Dim db As DAO.Database
Dim recSubmittal As DAO.Recordset
Dim recSubmittal2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
' Capture the field whose value will narrow your recordset down
strProdNum = Me.PartsID
strSQL = "SELECT * FROM qrySubmittalBase WHERE ProdNum= '" & strProdnum
& "';"
Set db = CurrentDb()
Set recSubmittal = db.OpenRecordset(strSQL)
StrSQL2 = "SELECT * FROM qrySubmittalDetail WHERE ProdNum= '" &
strProdnum & "';"
Set db = CurrentDb()
Set recSubmittal2 = db.OpenRecordset(strSQL2)
' This CreateSubmittal sub is created in the module
CreateSubmittal recSubmittal, recSubmittal2
Back in the module, create the above sub (remember, this is referenced in
the Forms click procedure)
This can be a little confusing here. the recSubmit is capturing the
recSubmittal and the recSubmit2 is capturing the recSubmittal2 recordsets.
Public Sub CreateSubmittal(recSubmit As DAO.Recordset, recSubmit2 As
DAO.Recordset)
Set m_objWord = New Word.Application
Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)
m_objWord.Visible = True
InsertTextAtBookmark "basepart", recSubmit("base")
InsertTextAtBookmark "title", recSubmit("title-version")
InsertTextAtBookmark "bundledparts", recSubmit("bundledparts")
InsertTextAtBookmark "ReleaseDate", recSubmit("ReleaseDate")
InsertTextAtBookmark "version", recSubmit("Version")
' Generate the table data
InsertSummaryTable recSubmit2
Set m_objDoc = Nothing
Set m_objWord = Nothing
End Sub
Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
' This finds the bookmarks in the Word template to place the data.
m_objDoc.Bookmarks(strBkmk).Select
m_objWord.Selection.Text = varText & ""
End Sub
Private Sub InsertSummaryTable(recR As DAO.Recordset)
' This pulls in the data for a table then highlights the data
' and creates a table in the Word document at a bookmark location
' for each field you want in the column of the table, have tabs
' surround it. Items in quotes are field names from the query/recordset
' If you need to have a blank column, just place vbTab in twice
On Error GoTo No_Record_Err
Dim strTable As String
Dim objTable As Word.Table
recR.MoveFirst
strTable = ""
While Not recR.EOF
strTable = strTable & vbTab & recR("discontinuedpart") & vbTab &
vbTab & recR("5x5No") & vbCr
recR.MoveNext
Wend
InsertTextAtBookmark "DiscPart", strTable
Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)
objTable.Select
objTable.Columns(1).Width = InchesToPoints(1.51)
objTable.Columns(2).Width = InchesToPoints(2.56)
objTable.Columns(3).Width = InchesToPoints(1.44)
objTable.Columns(4).Width = InchesToPoints(2.14)
Set objTable = Nothing
No_Record_Err:
Exit Sub
End Sub
"Perry" <drumper[ at ]gmail.com> wrote in message
news:BEFFD9EA-DAF3-41F1-84CE-E030BA7EC15C[ at ]microsoft.com...
[Quoted Text] >> well -- I could have up to 10 products on the Sub-Form, but I'm not sure
>> how
>> to populate each of these 10 Products on the WORD document.
>
> In such case:
> Build up a string variable, based on a seperate query.
> Use the ID (selected record) of the parentform of the products subform.
> Read the resultset of the query to build up a string variable in which the
> records
> are delimited by a vbCrLf.
> Now, you can wire this variable to ms Word.
>
> What you can do as well:
> Use the product subform's datasource, and read this as a DAO.Recordset, as
> in
> (pseudo code)
> Dim rs As dao.recordset
> set rs = Mysubform.RecordSetClone
>
> Now you can loop through the records of your subform (recordset) to build
> up
> a string variable, delimited by vbCrLf and wire this variable to ms Word.
>
>
> --
> Krgrds,
> Perry
>
> System:
> Vista/Office Ultimate
> VS2005/VSTO2005 SE
>
>
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
> news:uhh0w8$kHHA.4936[ at ]TK2MSFTNGP03.phx.gbl...
>> Perry,
>>
>> Here's what I have now ...
>> ==================================================
>> Here's what I just changed and the data NOW POPULATES :)
>> doc.FormFields("fldGrossPurchaseTotal").result = _
>> Nz(Me.Controls("Child72").Form!GrossPurchaseTotal)
>> ==========================
>> The problem I have now is -- On my Sub-Form, I have a Product Name field
>> as
>> well -- I could have up to 10 products on the Sub-Form, but I'm not sure
>> how
>> to populate each of these 10 Products on the WORD document.
>>
>> Example:
>>
>> My Sub-Form fields are:
>>
>> ProductID CustomerID Product Name GrossPurchaseTotal
>> ================================================
>> 0001 102 Pillows $4,700
>> 0005 201 Sheets $7,500
>> 0007 202 Comforter $1,200
>>
>> ONLY the 1st Product is being populated in the WORD document -- how do I
>> create a LOOP to capture all the entries on the Sub-Form?
>>
>> I have the following piece of code (from Pat Hartman, MVP): Pat explains
>> that (in the case of a Sub-Form -- Child72 in my case) the
>> doc.FormFields("fldCompanyName").result = Nz(Me!CompanyName) syntax
>> references to the CURRENT record in the subform, and that if the subform
>> is continuous (so that it shows multiple records), I would need to be
>> more sophisticated in my approach. He explained that (in this case) he
>> creates a long
>> text string by looping through the recordset. He then separates the
>> fields with a vbTab and uses vbCr to separate rows. Finally, he inserts
>> the text at a
>> bookmark/formfield and convert the text to a table. You can use any of
>> the
>> standard table formats or format your own specifically.
>>
>> Example:
>>
>> InsertTextAtBookMark bkmk, strTable
>> Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
>> objTable.AutoFormat Format:=wdTableFormatClassic1, applyshading:=True,
>> applyHeadingrows:=False, AutoFit:=True
>>
>>
>> Pat's code:
>> ==============
>> OK, here is the section of code that creates the string that is passed to
>> the sub I posted earlier. The code opens a query that takes one
>> parameter -
>> the variable data header ID. It then loops through the recordset,
>> concatenating the text string returned. When there are no more records,
>> the
>> loop ends and the string is passed to the sub that makes it a table in
>> word.
>> In the case of this code, only one field from the table is used and so
>> only
>> the ending vbCr is needed. If you were using multiple fields, then the
>> code
>> would look like:
>>
>> sTableItems = sTableItems & iSeqNum & ". " & rsDAO!SubjectiveText & vbTab
>> &
>> rsDAO!otherfield1 & rsDAO!otherfield2 & vbCr
>>
>> 'Open subjectivities recordset
>> Set qdDAO = db.QueryDefs!qMergeSubjectivities
>> qdDAO.Parameters![EnterVariableDataHeaderID] =
>> Me.txtVariableDataHeaderID
>> Set rsDAO = qdDAO.OpenRecordset
>> sTableItems = ""
>> If rsDAO.EOF Then
>> Else
>> iSeqNum = 0
>> Do While rsDAO.EOF = False
>> iSeqNum = iSeqNum + 1
>> sTableItems = sTableItems & iSeqNum & ". " &
>> rsDAO!SubjectiveText & vbCr <------alternate version above
>> rsDAO.MoveNext
>> Loop
>> End If
>> Set qdDAO = Nothing
>> If sTableItems <> "" Then
>> sTableItems = Left(sTableItems, Len(sTableItems) - 1) 'remove
>> final vbCr to eliminate extra line at end
>> Call Finish1Column("Subjectivities_Items", sTableItems)
>> end if
>> ==============================
>> Perry -- I don't really understand the code though -- specifically, I
>> don't understand why I need to specify the database (when I've already
>> established the connection)
>> RE: Set qdDAO = db.QueryDefs!qMergeSubjectivities
>> qdDAO.Parameters![EnterVariableDataHeaderID] =
>> Me.txtVariableDataHeaderID
>>
>> 'This piece of code copies the Requery_Specifics sub-form data to a WORD
>> template
>> Set qdDAO = db.Demo!qPurchaseHistory 'where Demo is the name of the
>> ACCESS db, and qPurchaseHistory is the name of the Query
>> qdDAO.Parameters![Enter Product ID] = Me.txtProductID
>> Set rsDAO = qdDAO.OpenRecordset
>> sTableItems = ""
>> If rsDAO.EOF Then
>> Else
>> iSeqNum = 0
>> Do While rsDAO.EOF = False
>> iSeqNum = iSeqNum + 1
>> sTableItems = sTableItems & iSeqNum & ". " & rsDAO!ProductID &
>> vbTab & rsDAO![Product Name] & vbCr
>> rsDAO.MoveNext
>> Loop
>> End If
>> Set qdDAO = Nothing
>> If sTableItems <> "" Then
>> sTableItems = Left(sTableItems, Len(sTableItems) - 1) 'Removes
>> final vbCr to eliminate extra line at end
>>
>> Call Finish1Column("Subjectivities_Items", sTableItems) 'I'm not
>> certain what this Call does
>> End If
>>
>> InsertTextAtBookMark ProductName, strTableItems
>> Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
>> objTable.AutoFormat Format:=wdTableFormatClassic1, applyshading:=True,
>> applyHeadingrows:=False, AutoFit:=True
>>
>> End Sub
>>
>> When I run the sub, I get the following error message:
>> "Compile Error:
>> Method or data not found"
>>
>> In the VBE, ".Demo" (SEE BELOW) is highlighted in my code:
>> Set qdDAO = db.Demo!qPurchaseHistory 'where Demo is the name of the
>> ACCESS db, and qPurchaseHistory is the name of the Query
>> qdDAO.Parameters![Enter Product ID] = Me.txtProductID
>>
>> Any thoughts?
>>
>> Thank you for all you help with this Perry.
>>
>>
>
|
|
|
[Quoted Text] > does the following code mirror the concept you offered in your latest
> post:
Yes, your below routine covers what I meant in my previous posting.
>> Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
>> ' This finds the bookmarks in the Word template to place the data.
>> m_objDoc.Bookmarks(strBkmk).Select
>> m_objWord.Selection.Text = varText & ""
>> End Sub
However, I would add in a bit of defensive programming to read:
Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
With m_objDoc.Bookmarks
If .Exists(strBkmk) Then
.Item(strBkmk).Range = varText & ""
End if
End with
End Sub
The rest of the code looks oke.
Provided, it calls the correct tables, uses the right Form criteria
the transmitting part to Word looks oke.
Have you tried it? Does the code fail somewhere ?
--
Krgrds,
Perry
System:
Vista/Office Ultimate
VS2005/VSTO2005 SE
"Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
news:uPhv1rxlHHA.4872[ at ]TK2MSFTNGP03.phx.gbl...
> Perry,
>
> I'm kinda "wrapped around the axle" on this one -- does the following code
> mirror the concept you offered in your latest post:
> -- code found here: http://www.tek-tips.com/faqs.cfm?fid=760
> ===================================
> WORD TEMPLATE
> Create your Word document with a template format. Save it as a template
> file (.dot). Use bookmarks to mark the place you want the data to be
> pulled in. You can have as many bookmarks as you want. If you require
> the data to be pulled into tables, don't create tables in Word, but let
> Access VBA create the tables for you.
>
> ACCESS DATABASE
> Set up queries showing the fields you want to transfer to the Word
> document. You may need to set up more than one query. If you have to do
> that, then you will need to set up each as a recordset in the code with
> its own SQL string. The idea of the SQL string is to narrow down the
> records in the recordset to the exact info you need.
>
> Assuming only two queries were made, code as follows:
>
> In a module, key in a Public variable to be shared in database
>
>
> Option Compare Database
> Option Explicit
>
> ' location of the documents and templates -
> ' Where will Access find the Word Template?
> Public Const m_strDIR As String = "d:\database\"
> Public Const m_strTEMPLATE As String = "submittalcd.dot"
>
> ' set up objects for use and Public variables to be shared in database
> Private m_objWord As Word.Application
> Private m_objDoc As Word.Document
> Public strProdNum As String
>
> In the Forms Button for starting the event.
>
> Create SQL statements based on the values of the active record (i.e.,
> prodnum)
>
> Click event:
>
> Dim db As DAO.Database
> Dim recSubmittal As DAO.Recordset
> Dim recSubmittal2 As DAO.Recordset
> Dim strSQL As String
> Dim strSQL2 As String
>
>
> ' Capture the field whose value will narrow your recordset down
> strProdNum = Me.PartsID
>
> strSQL = "SELECT * FROM qrySubmittalBase WHERE ProdNum= '" &
> strProdnum & "';"
> Set db = CurrentDb()
> Set recSubmittal = db.OpenRecordset(strSQL)
>
> StrSQL2 = "SELECT * FROM qrySubmittalDetail WHERE ProdNum= '" &
> strProdnum & "';"
> Set db = CurrentDb()
> Set recSubmittal2 = db.OpenRecordset(strSQL2)
>
>
>
> ' This CreateSubmittal sub is created in the module
> CreateSubmittal recSubmittal, recSubmittal2
>
> Back in the module, create the above sub (remember, this is referenced in
> the Forms click procedure)
> This can be a little confusing here. the recSubmit is capturing the
> recSubmittal and the recSubmit2 is capturing the recSubmittal2 recordsets.
>
> Public Sub CreateSubmittal(recSubmit As DAO.Recordset, recSubmit2 As
> DAO.Recordset)
>
> Set m_objWord = New Word.Application
> Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)
>
> m_objWord.Visible = True
>
> InsertTextAtBookmark "basepart", recSubmit("base")
> InsertTextAtBookmark "title", recSubmit("title-version")
> InsertTextAtBookmark "bundledparts", recSubmit("bundledparts")
> InsertTextAtBookmark "ReleaseDate", recSubmit("ReleaseDate")
> InsertTextAtBookmark "version", recSubmit("Version")
>
>
> ' Generate the table data
> InsertSummaryTable recSubmit2
>
>
> Set m_objDoc = Nothing
> Set m_objWord = Nothing
>
>
> End Sub
>
> Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
> ' This finds the bookmarks in the Word template to place the data.
> m_objDoc.Bookmarks(strBkmk).Select
> m_objWord.Selection.Text = varText & ""
>
> End Sub
>
> Private Sub InsertSummaryTable(recR As DAO.Recordset)
> ' This pulls in the data for a table then highlights the data
> ' and creates a table in the Word document at a bookmark location
> ' for each field you want in the column of the table, have tabs
> ' surround it. Items in quotes are field names from the query/recordset
> ' If you need to have a blank column, just place vbTab in twice
> On Error GoTo No_Record_Err
> Dim strTable As String
> Dim objTable As Word.Table
>
> recR.MoveFirst
> strTable = ""
> While Not recR.EOF
> strTable = strTable & vbTab & recR("discontinuedpart") & vbTab &
> vbTab & recR("5x5No") & vbCr
> recR.MoveNext
> Wend
>
> InsertTextAtBookmark "DiscPart", strTable
> Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)
>
> objTable.Select
> objTable.Columns(1).Width = InchesToPoints(1.51)
> objTable.Columns(2).Width = InchesToPoints(2.56)
> objTable.Columns(3).Width = InchesToPoints(1.44)
> objTable.Columns(4).Width = InchesToPoints(2.14)
>
> Set objTable = Nothing
> No_Record_Err:
> Exit Sub
>
> End Sub
>
>
> "Perry" <drumper[ at ]gmail.com> wrote in message
> news:BEFFD9EA-DAF3-41F1-84CE-E030BA7EC15C[ at ]microsoft.com...
>>> well -- I could have up to 10 products on the Sub-Form, but I'm not sure
>>> how
>>> to populate each of these 10 Products on the WORD document.
>>
>> In such case:
>> Build up a string variable, based on a seperate query.
>> Use the ID (selected record) of the parentform of the products subform.
>> Read the resultset of the query to build up a string variable in which
>> the records
>> are delimited by a vbCrLf.
>> Now, you can wire this variable to ms Word.
>>
>> What you can do as well:
>> Use the product subform's datasource, and read this as a DAO.Recordset,
>> as in
>> (pseudo code)
>> Dim rs As dao.recordset
>> set rs = Mysubform.RecordSetClone
>>
>> Now you can loop through the records of your subform (recordset) to build
>> up
>> a string variable, delimited by vbCrLf and wire this variable to ms Word.
>>
>>
>> --
>> Krgrds,
>> Perry
>>
>> System:
>> Vista/Office Ultimate
>> VS2005/VSTO2005 SE
>>
>>
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> schreef in bericht
>> news:uhh0w8$kHHA.4936[ at ]TK2MSFTNGP03.phx.gbl...
>>> Perry,
>>>
>>> Here's what I have now ...
>>> ==================================================
>>> Here's what I just changed and the data NOW POPULATES :)
>>> doc.FormFields("fldGrossPurchaseTotal").result = _
>>> Nz(Me.Controls("Child72").Form!GrossPurchaseTotal)
>>> ==========================
>>> The problem I have now is -- On my Sub-Form, I have a Product Name field
>>> as
>>> well -- I could have up to 10 products on the Sub-Form, but I'm not sure
>>> how
>>> to populate each of these 10 Products on the WORD document.
>>>
>>> Example:
>>>
>>> My Sub-Form fields are:
>>>
>>> ProductID CustomerID Product Name GrossPurchaseTotal
>>> ================================================
>>> 0001 102 Pillows $4,700
>>> 0005 201 Sheets $7,500
>>> 0007 202 Comforter $1,200
>>>
>>> ONLY the 1st Product is being populated in the WORD document -- how do I
>>> create a LOOP to capture all the entries on the Sub-Form?
>>>
>>> I have the following piece of code (from Pat Hartman, MVP): Pat
>>> explains that (in the case of a Sub-Form -- Child72 in my case) the
>>> doc.FormFields("fldCompanyName").result = Nz(Me!CompanyName) syntax
>>> references to the CURRENT record in the subform, and that if the subform
>>> is continuous (so that it shows multiple records), I would need to be
>>> more sophisticated in my approach. He explained that (in this case) he
>>> creates a long
>>> text string by looping through the recordset. He then separates the
>>> fields with a vbTab and uses vbCr to separate rows. Finally, he inserts
>>> the text at a
>>> bookmark/formfield and convert the text to a table. You can use any of
>>> the
>>> standard table formats or format your own specifically.
>>>
>>> Example:
>>>
>>> InsertTextAtBookMark bkmk, strTable
>>> Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
>>> objTable.AutoFormat Format:=wdTableFormatClassic1,
>>> applyshading:=True,
>>> applyHeadingrows:=False, AutoFit:=True
>>>
>>>
>>> Pat's code:
>>> ==============
>>> OK, here is the section of code that creates the string that is passed
>>> to
>>> the sub I posted earlier. The code opens a query that takes one
>>> parameter -
>>> the variable data header ID. It then loops through the recordset,
>>> concatenating the text string returned. When there are no more records,
>>> the
>>> loop ends and the string is passed to the sub that makes it a table in
>>> word.
>>> In the case of this code, only one field from the table is used and so
>>> only
>>> the ending vbCr is needed. If you were using multiple fields, then the
>>> code
>>> would look like:
>>>
>>> sTableItems = sTableItems & iSeqNum & ". " & rsDAO!SubjectiveText &
>>> vbTab &
>>> rsDAO!otherfield1 & rsDAO!otherfield2 & vbCr
>>>
>>> 'Open subjectivities recordset
>>> Set qdDAO = db.QueryDefs!qMergeSubjectivities
>>> qdDAO.Parameters![EnterVariableDataHeaderID] =
>>> Me.txtVariableDataHeaderID
>>> Set rsDAO = qdDAO.OpenRecordset
>>> sTableItems = ""
>>> If rsDAO.EOF Then
>>> Else
>>> iSeqNum = 0
>>> Do While rsDAO.EOF = False
>>> iSeqNum = iSeqNum + 1
>>> sTableItems = sTableItems & iSeqNum & ". " &
>>> rsDAO!SubjectiveText & vbCr <------alternate version above
>>> rsDAO.MoveNext
>>> Loop
>>> End If
>>> Set qdDAO = Nothing
>>> If sTableItems <> "" Then
>>> sTableItems = Left(sTableItems, Len(sTableItems) - 1) 'remove
>>> final vbCr to eliminate extra line at end
>>> Call Finish1Column("Subjectivities_Items", sTableItems)
>>> end if
>>> ==============================
>>> Perry -- I don't really understand the code though -- specifically, I
>>> don't understand why I need to specify the database (when I've already
>>> established the connection)
>>> RE: Set qdDAO = db.QueryDefs!qMergeSubjectivities
>>> qdDAO.Parameters![EnterVariableDataHeaderID] =
>>> Me.txtVariableDataHeaderID
>>>
>>> 'This piece of code copies the Requery_Specifics sub-form data to a WORD
>>> template
>>> Set qdDAO = db.Demo!qPurchaseHistory 'where Demo is the name of the
>>> ACCESS db, and qPurchaseHistory is the name of the Query
>>> qdDAO.Parameters![Enter Product ID] = Me.txtProductID
>>> Set rsDAO = qdDAO.OpenRecordset
>>> sTableItems = ""
>>> If rsDAO.EOF Then
>>> Else
>>> iSeqNum = 0
>>> Do While rsDAO.EOF = False
>>> iSeqNum = iSeqNum + 1
>>> sTableItems = sTableItems & iSeqNum & ". " & rsDAO!ProductID
>>> & vbTab & rsDAO![Product Name] & vbCr
>>> rsDAO.MoveNext
>>> Loop
>>> End If
>>> Set qdDAO = Nothing
>>> If sTableItems <> "" Then
>>> sTableItems = Left(sTableItems, Len(sTableItems) - 1) 'Removes
>>> final vbCr to eliminate extra line at end
>>>
>>> Call Finish1Column("Subjectivities_Items", sTableItems) 'I'm not
>>> certain what this Call does
>>> End If
>>>
>>> InsertTextAtBookMark ProductName, strTableItems
>>> Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
>>> objTable.AutoFormat Format:=wdTableFormatClassic1,
>>> applyshading:=True, applyHeadingrows:=False, AutoFit:=True
>>>
>>> End Sub
>>>
>>> When I run the sub, I get the following error message:
>>> "Compile Error:
>>> Method or data not found"
>>>
>>> In the VBE, ".Demo" (SEE BELOW) is highlighted in my code:
>>> Set qdDAO = db.Demo!qPurchaseHistory 'where Demo is the name of the
>>> ACCESS db, and qPurchaseHistory is the name of the Query
>>> qdDAO.Parameters![Enter Product ID] = Me.txtProductID
>>>
>>> Any thoughts?
>>>
>>> Thank you for all you help with this Perry.
>>>
>>>
>>
>
>
|
|
|