Ms Access Query to Ms Word Table Automation 2

[EnergyTed]

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

 

[EnergyTed]

Hi All

Please see latest current code -

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

Set appWord = GetObject(, "Word.Application")
Set docx = appWord.Documents.Add
strTemplateName = "C:\Users\username\All 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![Name]

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


Kind Regards


Ted

 

[Andrzejek]

"to automate the creation of a word document to enable the user to edit [Word?] once the data from the query has been updated."

Could you tell us why you do it? And if users insist on having the data, why Word?

It would make more sense to have it in Excel: you have rows and columns in Excel, which match nicely with records and fields in the data base. Plus you can just make MSQuery in Excel that runs every time you open the Excel file, no VBA needed.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[EnergyTed]

Hi Andy

They need/want to be able to edit the document after the data has been updated.


Ted

 

[Andrzejek]

Well, again: why Word? Why not simple MSQuery in Excel?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[EnergyTed]

Hi Andy

The word document is based on a predefined structure with logos, text, etc. Excel is not an option.


Ted

 

[Andrzejek]

it is hard to make sense from your "latest current code":

With appWord.Selection
    .GoTo what:=wdGoToBookmark, Name:="Name"

    Do While Not rs.EOF
        .TypeText "Name: " & rs![Name]

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

You have some With statement with no End With line, Do While without Loop, one set of double quotes in [tt]wdGoToBookmark[/tt] line, etc.

Please use TGML tags to show your code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[EnergyTed]

Hi Andy

So you ask the same question a few times, then criticize the code I need help with!

What is the point of you taking your time to respond if you cannot provide any help/advice?

Thanks for nothing.


Ted

 

[Andrzejek]

I read your post very carefully, and your responses.

Yes, I did ask the same question a few times, but that’s because I did not get the answer.
My initial question about Excel was merely an attempt to show other way of presenting the data to the user. I use Excel for this often and it is very easy in Excel, but Word can work as well. Not as easy, but doable.

So I looked at your code and have noticed that the code would / should not run. It will complain right away that the syntax is not correct. You have never stated “this is my non-working code and I only want to show my attempt to show overall logic” or something like that. (unless I missed that)

It would also be nice to include in your information right away that you already have some kind of template in Word with bookmarks and want to show your data in a table in Word. It would be also nice to know how many fields of data you want to show, is it always the same number or can it change, etc.

With that being said, I will let other to help you.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Hey EnergyTed, (Programmer),

Andy did just about what I would have done. Your scarcity of useful information is frustrating.

If it were me working under such unreasonable restrictions, I'd still do the data extraction in Excel, which could be accomplished in a few minutes of coding, COPY the resultset and PASTE it into your Word document. It could even be an object in Word.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EnergyTed]

Hi Skip

Copy and Paste, brilliant you deserve a star for that.

Stop wasting my time.

Ted

 

[Andrzejek]

I shouldn’t be really doing this, but here it is – one way of doing it in MSWord in a Word’s table.
Code requires to have a reference to Microsoft ActiveX Data Library (for the ADODB connection and recordset) and Microsoft Word Object Library.
You may skip connecting to the DB since you already are in Access.

Dim Cn As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim objWord As New Word.Application
Dim strSQL As String
Dim i As Integer

Cn.ConnectionString = connection string to your DB
Cn.CursorLocation = adUseClient
Cn.Open

strSQL = "SELECT some field(s) " & vbNewLine _
    & " FROM some table(s) " & vbNewLine _
    & " ORDER BY SomeField"

rec.Open strSQL, Cn

With objWord
    .Visible = True
    .Documents.Add

    .ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, _
        NumColumns:=rec.Fields.Count, DefaultTableBehavior:=wdWord9TableBehavior, _
        AutoFitBehavior:=wdAutoFitFixed

    With .Selection
        For i = 1 To rec.Fields.Count
            .TypeText Text:=rec.Fields(i - 1).Name
            .MoveRight Unit:=wdCell
        Next i
    
        Do While Not rec.EOF
            For i = 1 To rec.Fields.Count
                .TypeText Text:=rec.Fields(i - 1).Value
                .MoveRight Unit:=wdCell
            Next i
            rec.MoveNext
        Loop
    End With

End With

rec.Close
Set rec = Nothing

Cn.Close
Set Cn = Nothing

The outcome of strSQL (select statement) will be displayed in a table in Word.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.