 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / August 2007Copy Data From an ACCESS form to a Text From Field in a WORD Template
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?  SignatureHope 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 >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 [quoted text clipped - 599 lines] > > End Sub Doug, I apologize for the delay in replying to your post ... In reponse to your (2) questions ... > 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" ===================================================== > What happens when you run your code now? > [quoted text clipped - 615 lines] >> >> 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 >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 [quoted text clipped - 599 lines] > > 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 > Couple of things here: > [quoted text clipped - 644 lines] >> >> End Sub 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 > Perry, > [quoted text clipped - 914 lines] >>> >>> 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 > Replace >> .FormFields("fldGrossPurchaseTotal").result = _ [quoted text clipped - 938 lines] >>>> >>>> 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 > Perry, > [quoted text clipped - 948 lines] >>>>> >>>>> End Sub Yes, "PurchaseHistory" is the name of the sub-form which contains the data-field "GrossPurchaseTotal" ================================ > "PurchaseHistory" this is the subform where "GrossPurchaseTotal" is hosted > in, isn't it? [quoted text clipped - 964 lines] >>>>>> >>>>>> 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 > Yes, "PurchaseHistory" is the name of the sub-form which contains the > data-field "GrossPurchaseTotal" [quoted text clipped - 970 lines] >>>>>>> >>>>>>> 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 > Are you sure that "GrossPurchaseTotal" is the name of the data control on > yr subform? [quoted text clipped - 987 lines] >>>>>>>> >>>>>>>> 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. > 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 > Perry, > [quoted text clipped - 139 lines] > > 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 >> well -- I could have up to 10 products on the Sub-Form, but I'm not sure >> how [quoted text clipped - 170 lines] >> >> Thank you for all you help with this Perry. > 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 > Perry, > [quoted text clipped - 305 lines] >>> >>> Thank you for all you help with this Perry. "Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> дÈëÏûÏ¢ÐÂÎÅ:%23FoxEbnjHHA.4188@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 [quoted text clipped - 599 lines] > > End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.