Lightwave
Ad astra
- Local time
- Today, 18:58
- Joined
- Sep 27, 2004
- Messages
- 1,537
Issue with appropriate formating when creating Word Documents from an Access 2003 function that creates docs.
I have written a function within Access 2003 to try and create a word doc in word 2010.
I create a recordset from a parent table and then create a further recordset of the children of this parent table. I then create a word document for each parent and place the children records within the document.
The children vary in number for each parent and I place loop through the related child records placing 3 fields for each child into the parents word document. I would like to format these children fields appropriately. Code listed below
Unfortunately - the formatting is only registering against the final child record to be added to the document and not all of the other ones. I've been hacking about with it but can't seem to get it working. It would appear to be something to do with the syntax of my range property.
Has anyone got any ideas where I am going wrong?
I will try and add the mdb
I have written a function within Access 2003 to try and create a word doc in word 2010.
I create a recordset from a parent table and then create a further recordset of the children of this parent table. I then create a word document for each parent and place the children records within the document.
The children vary in number for each parent and I place loop through the related child records placing 3 fields for each child into the parents word document. I would like to format these children fields appropriately. Code listed below
Unfortunately - the formatting is only registering against the final child record to be added to the document and not all of the other ones. I've been hacking about with it but can't seem to get it working. It would appear to be something to do with the syntax of my range property.
Has anyone got any ideas where I am going wrong?
I will try and add the mdb
Code:
Function ParentandChildintoSeparateWordDocs()
'Make sure the name of the recordset is unambigous
'Good practice to reference the actual library
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim dbChild As DAO.Database
Dim rschild As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM T045ExpressionInterest where Extra = 555")
With rs
If Not (rs.EOF And rs.BOF) Then
'There are no records if EOF and BOF are both true you are at the end and at the beginning
rs.MoveFirst
Do Until rs.EOF = True
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add ' create a new document
wrdApp.Visible = True
'this line can be altered to open the document on the screen
With wrdDoc
.Content.InsertAfter ("Proposer:" & rs!Proposer)
.Content.InsertParagraphAfter
.Content.InsertAfter ("Summary of the Submission:" & rs!SummarySubmission)
.Content.InsertParagraphAfter
.Content.InsertAfter ("Synoptic Conclusion:" & rs!ConclusionSynopsis)
.Content.InsertParagraphAfter
Set dbChild = CurrentDb
Set rschild = db.OpenRecordset("SELECT * FROM T046EOIComments WHERE FKID = " & rs!PKID)
With rschild
If Not (rschild.EOF And rschild.BOF) Then
'There are no records if EOF and BOF are both true you are at the end and at the beginning
rschild.MoveFirst
Do Until rschild.EOF = True
With wrdDoc
.Styles(wdStyleHeading1).Font.Name = "Times New Roman"
.Styles(wdStyleHeading1).Font.Size = 16
.Styles(wdStyleHeading1).Font.Bold = True
.Styles(wdStyleHeading1).Font.Color = wdColorBlack
.Styles(wdStyleHeading2).Font.Name = "Stencil"
.Styles(wdStyleHeading2).Font.Size = 12
.Styles(wdStyleHeading2).Font.Bold = True
.Styles(wdStyleHeading2).Font.Color = wdColorRed
.Styles(wdStyleNormal).Font.Name = "Arial"
.Styles(wdStyleNormal).Font.Size = 10
.Styles(wdStyleNormal).Font.Color = wdColorBlue
.Content.ParagraphFormat.LineSpacingRule = wdLineSpaceExactly
.Content.ParagraphFormat.LineSpacing = 5
.Range(0).Style = .Styles(wdStyleHeading1)
.Content.InsertAfter ("Consulting Body:" & rschild!Body)
.Content.InsertParagraphAfter
.Range(.Characters.Count - 1).Style = .Styles(wdStyleHeading2)
.Content.InsertAfter ("Consultation response : " & rschild!Comment)
.Content.InsertParagraphAfter
.Range(.Characters.Count - 1).Style = .Styles(wdStyleNormal)
.Content.InsertAfter ("Date Updated: " & rschild!DateUpdated)
.Content.InsertAfter (" ")
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
End With
rschild.MoveNext
Loop
Else
Exit Function
End If
rschild.Close
End With
Set rschild = Nothing
Set dbChild = Nothing
.SaveAs ("\\directory\CreatedWordDoc" & rs!EOINumber & ".doc")
.Close ' close the document
End With ' With wrdDoc
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
rs.Edit
rs!Extra = 555
rs.Update
rs.MoveNext
Loop
Else
MsgBox "No Records Available for updating exit sub"
Exit Function
End If
MsgBox "Looped through the records and updated the value number field"
rs.Close
End With
Set rs = Nothing
Set db = Nothing
End Function
