Hi All
I have been asked to automate the creation of a word document to enable the user to edit once the data from the query has been updated. I have successfully created a button on a form that updates the word document for the current record. However, when I try to enhance the code to update direct from a query the code goes into a silent loop and does not execute properly?
The working code for the single record from a form -
Private Sub Command5_Click()
Dim appWord As Word.Application
Dim docx As Word.Document
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set docx = appWord.Documents.Open("C:\Users\username\Desktop\Single Word Template.docx", , True)
With docx
.FormFields("fldName").Result = Me!Name
.FormFields("fldDate").Result = Me!Date
.Visible = True
.Activate
End With
Set docx = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub
I have tried several alternatives and here is the non working code for All records from a query to a word table -
Private Sub Command5_Click()
Dim appWord As Word.Application
Dim docx As Word.Document
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strTemplateName As String
errHandler:
MsgBox Err.Number & ": " & Err.Description
Set appWord = GetObject(, "Word.Application")
Set docx = appWord.Documents.Add
strTemplateName = ""C:\Users\username\Desktop\Single Word Template.docx"
appWord.Visible = True
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("qry_MC/CMA_Members/License/Dates")
Set docx = appWord.Documents.Add(strTemplateName)
With appWord.Selection.GoTo what:=wdGoToBookmark, Name:= Name"
Do while Not rs.EOF.TypeText "Name: " & rs![empName]
With appWord.Selection.GoTo what:=wdGoToBookmark, Name:=Table_Start"
Do While Not rs.EOF
With appWord.Selection
.TypeText rs!Name
.MoveRight unit:=wdCell, Count:=1
.TypeText rs!Date
.MoveRight unit:=wdCell, Count:=1
End With
rs.MoveNext
Loop
appWord.Selection.Rows.Delete
End With
End Sub
Any help, tips, best practice advice would be greatly appreciated.
Kind Regards
Ted
I have been asked to automate the creation of a word document to enable the user to edit once the data from the query has been updated. I have successfully created a button on a form that updates the word document for the current record. However, when I try to enhance the code to update direct from a query the code goes into a silent loop and does not execute properly?
The working code for the single record from a form -
Private Sub Command5_Click()
Dim appWord As Word.Application
Dim docx As Word.Document
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set docx = appWord.Documents.Open("C:\Users\username\Desktop\Single Word Template.docx", , True)
With docx
.FormFields("fldName").Result = Me!Name
.FormFields("fldDate").Result = Me!Date
.Visible = True
.Activate
End With
Set docx = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub
I have tried several alternatives and here is the non working code for All records from a query to a word table -
Private Sub Command5_Click()
Dim appWord As Word.Application
Dim docx As Word.Document
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strTemplateName As String
errHandler:
MsgBox Err.Number & ": " & Err.Description
Set appWord = GetObject(, "Word.Application")
Set docx = appWord.Documents.Add
strTemplateName = ""C:\Users\username\Desktop\Single Word Template.docx"
appWord.Visible = True
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("qry_MC/CMA_Members/License/Dates")
Set docx = appWord.Documents.Add(strTemplateName)
With appWord.Selection.GoTo what:=wdGoToBookmark, Name:= Name"
Do while Not rs.EOF.TypeText "Name: " & rs![empName]
With appWord.Selection.GoTo what:=wdGoToBookmark, Name:=Table_Start"
Do While Not rs.EOF
With appWord.Selection
.TypeText rs!Name
.MoveRight unit:=wdCell, Count:=1
.TypeText rs!Date
.MoveRight unit:=wdCell, Count:=1
End With
rs.MoveNext
Loop
appWord.Selection.Rows.Delete
End With
End Sub
Any help, tips, best practice advice would be greatly appreciated.
Kind Regards
Ted