code for looping

[Steven811]

Hi

I want to export data from an active form, linked to the active control to Word. The code I am using works fine and is enclosed for reference. The problem I'm trying to resolve is that the last form holds historical data linked to the active control through an ID that I wish to include.

They're visit and action records like this

8th Oct, called Tim, he's out, call again on 9th Oct
9th Oct, called Tim and agreed to meet on 11th at site.

The continuous form I am using allows you to scroll through the records, but I want to print them with the other recordset data.

The code someone has suggested I use is as follows:

[Do While Not rs.EOF
.ActiveDocument.Bookmarks("field1").Select
.Selection.Text = (CStr(Me!subform1.Form!subform2.Form _
!subform3.Form!subform4.Form!field1))
rs.MoveNext
Loop]

I've had a look through the archives and can't see anything.

Any suggestions would be gratefully received.

Steven811 - novice

The full code is as follows:

[Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err

Dim objWord As Word.Application


'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
.Visible = True

'Open the document.
.Documents.Open ("C:\MyMerge.doc")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("CompanyName").Select
.Selection.Text = (CStr(Forms!forCustomerDetails2!CompanyName))


'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("SiteName").Select
.Selection.Text = (CStr(Me!forSiteName.Form!SiteName))

.ActiveDocument.Bookmarks("cboDesignation").Select
.Selection.Text = (CStr(Me!forSiteName.Form!cboDesignation))

.ActiveDocument.Bookmarks("SiteAddress1").Select
.Selection.Text = (CStr(Me.forSiteName.Form!SiteAddress1))


.ActiveDocument.Bookmarks("cboManager").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!cboManager.Column(1)))

.ActiveDocument.Bookmarks("Contact11stName").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact11stName))

.ActiveDocument.Bookmarks("Contact12ndName").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact12ndName))

.ActiveDocument.Bookmarks("Contact1DDTelNoExt").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact1DDTelNoExt))

.ActiveDocument.Bookmarks("Contact1mb").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact1mb))

.ActiveDocument.Bookmarks("Contact1emailaddress").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact1emailaddress))

.ActiveDocument.Bookmarks("JobNo").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!JobNo))

.ActiveDocument.Bookmarks("Description").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!Description))


.ActiveDocument.Bookmarks("cboSupplier").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier.Column(1)))

.ActiveDocument.Bookmarks("cboSupplier1").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier1.Column(1)))

.ActiveDocument.Bookmarks("cboSupplier2").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier2.Column(1)))

.ActiveDocument.Bookmarks("cboSupplier3").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier3.Column(1)))

.ActiveDocument.Bookmarks("cboSupplier4").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier4.Column(1)))

.ActiveDocument.Bookmarks("cboCIS").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboCIS))

.ActiveDocument.Bookmarks("RMIssued").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!RMIssued))

.ActiveDocument.Bookmarks("RMCustApproved").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!RMCustApproved))

.ActiveDocument.Bookmarks("RMContractorApproved").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!RMContractorApproved))

.ActiveDocument.Bookmarks("DateofComment").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!DateofComment))

'By this:
Do While Not rs.EOF
.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!Comments))
rs.MoveNext
Loop


.ActiveDocument.Bookmarks("Action").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!Action))

.ActiveDocument.Bookmarks("ActionByDate").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!ActionByDate))

.ActiveDocument.Bookmarks("cboByWhom").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!cboByWhom))


'Move to each bookmark and insert text from the form.




End With

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
' objWord.ActiveDocument.PrintOut Background:=False

'Close the document without saving changes.
' objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'
' 'Quit Microsoft Word and release the object variable.
' objWord.Quit
' Set objWord = Nothing
' Exit Sub

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If

Exit Sub
End Sub]

 

[yeatmanj]

1. Do the tables have a relationship?
2. Why are you using word if you aren't saving? Why not just create a report?

 

[Steven811]

Loop

Hi

The tables have relationships.

With a parent and 4 subforms, a report dosn't work. Exporting it into Word does.

Regds

Steven811

 

[yeatmanj]

What are the results that you are getting with the current code?

 

[Steven811]

results

Hi

The results I am getting are that debug/compile does not produce an error.

When I run the merge the recordset comes across until I get to the comments bookmark. It works fine with the original code, when the following code is added it fails and stops at the "comments" bookmark.

[Do While Not rs.EOF
.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!Comments))
rs.MoveNext
Loop]

Someone I suggested I add the following:

[Do While Not rs.EOF
.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(re!Comments))
rs.MoveNext
Loop]

Any advice would be gratefully recieved.

Thanks

Steven811

 

[yeatmanj]

Hi again.

1. If the original code works fine, why are you changing it?

2. In the suggested addition is (CStr(re!Comments)) supposed to be (CStr(Me!Comments))

3. Have you tried to put all of the comments into a recordset or array before inserting to bookmark?

4. I noticed that you also have a Comment date. Are you trying to include that so that you get:
CommentDate Comment
CommentDate Comment
CommentDate Comment

 

[Steven811]

Hi

The original code works fine for the current record in the active form. The problem is that in one particular form I wish to print the other records (historical record of notes).

The code is meant to be as I've shown here, not saying it's right.

I thought that subform4 which holds all of the records was a record set.

The DateofComment Comment point you make is correct. In reality I will have Action, ByWho, ActionByDate as well.

Regds

Steven811 - novice

 

[yeatmanj]

Here is an example of a bookmark insert from one of my databases.

Public Function UtilityTransfer()

    Dim dbs As Database, rstMerge As Recordset
    Dim rstFiltered As Recordset
    Dim intPages As Integer
    Dim AppWord As Word.Application
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    
    ' Create dynaset-type Recordset object.
    Set rstMerge = dbs.OpenRecordset("qryMergeDocs", dbOpenDynaset)
    
    ' Set filter condition.
    rstMerge.Filter = "FacID = '" & strFacID & "'"

    ' Create filtered dynaset-type Recordset object.
    Set rstFiltered = rstMerge.OpenRecordset
    
    'Switch to Microsoft Word so it won't go away when you finish.
    On Error Resume Next
    AppActivate "Microsoft Word"

    'If Word isn't running, start and activate it.
    If Err Then
        Shell "WinWord /Automation", vbMaximizedFocus
        AppActivate "Microsoft Word"
    End If
   
    'Get an Application object so you can automate Word.
    Set AppWord = GetObject(, "Word.Application")
    
    'Move to each bookmark and insert data from filtered recordset.
    With AppWord
        .Documents.Add "T:\Team6Merge\" & strMergeDoc & ".dot"
        .ActiveDocument.ShowSpellingErrors = False
        .Selection.Goto wdGoToBookmark, Name:="FacID"
        .Selection.TypeText rstFiltered!FacID
        .Selection.Goto wdGoToBookmark, Name:="SiteName"
        .Selection.TypeText rstFiltered![Site Name]
        .Selection.Goto wdGoToBookmark, Name:="SiteAddress"
        .Selection.TypeText rstFiltered![Site Address]
        .Selection.Goto wdGoToBookmark, Name:="SiteCity"
        .Selection.TypeText rstFiltered![Site City]
        .Selection.Goto wdGoToBookmark, Name:="FacID1"
        .Selection.TypeText rstFiltered!FacID
        .Selection.Goto wdGoToBookmark, Name:="Extension"
        .Selection.TypeText rstFiltered!extension
        .Selection.Goto wdGoToBookmark, Name:="Signature"
        .Selection.TypeText rstFiltered!Signature
        .Selection.Goto wdGoToBookmark, Name:="JobTitle"
        .Selection.TypeText rstFiltered!JobTitle
        .Selection.Goto wdGoToBookmark, Name:="Email"
        .Selection.TypeText rstFiltered!email
        .Selection.Goto wdGoToBookmark, Name:="Initials"
        .Selection.TypeText rstFiltered!initials
        .Selection.Goto wdGoToBookmark, Name:="OwnerCC"
        .Selection.TypeText rstFiltered!OwnerCC
        .Selection.Goto wdGoToBookmark, Name:="ContractorCC"
        .Selection.TypeText rstFiltered!ContractorCC
    End With
    
    'Return to Access.
    AppActivate "Team 6 Tracking version 3.0"
    
    'Get the number of pages in the memo, ask the user whether to print it, and then tell Word to print it.
    intPages = AppWord.Selection.Information(wdNumberOfPagesInDocument)
    MsgBox "The document is complete, and has " & CStr(intPages) & " pages." & Chr(13) & "Please review and edit before printing.", vbOKOnly

rstMerge.Close
rstFiltered.Close
dbs.Close

End Function

I posted this to show you that instead of using a form to access my recordset I am using a query and filtering the results. I think that the problem you are encountering is because you are trying to access the data through the form. I could be wrong.

I think you are on the right track, but need to create a query to bring together all of your comments, Action, ByWho, ActionByDate, and DateOfComment records and filter them to the specific record group you want. From there you should be able to use the do while loop to insert each record from the new filtered recordset to the bookmark that you want.