How to create word file template and use data from ms access

[DwinTheBeginner]

I am new in MS ACCESS, Is there someone who could help me with this problem.
I have a ms access database consist of 2 tables 1 is client information and the other is one is
the bank information (checking account). I wanted to create a button that when pressed.
It would create a template a word template that includes a permanent text on it, display some of the
details of the client, including the bank information (information based on a query) and the check they issued for example 5 checks (table format). Thanks in advance.


This is to inform mr.<- permanent (Alfred Chua, Address here) <-from database
with Bank Account number (12467899) <-from database issued 5 checks see table below.

Bank name Branch Check # amount <-from database
Sterling Los Angeles 123456 $1,000.00 <-from database
-----------------------------------------------------------------------------------------

This is to inform mr.Alfred Chua, Address here with Bank Account number (12467899) issued 5 checks see table below.

Bank name / Branch / Check # / amount
Sterling / Los Angeles / 123456 / $1,000.00

 

[lameid]

Generally I see word and think Mail merge from word (even if automating from Access) but in your case with multiple rows I would see how well making a report with sub report for the checks and then saving/outputting that to word.

One weird thing I saw way back in Access 97 is that if a control was only wide enough for a fraction of a charcter it bombed saving to... (wow may have been Excel not Word). In that case it was a status code and the control was not quite wide enough for the last few pixels of the "M". This oddity likely does not exist anymore but is something to look at if the report runs prints and fails to convert.

Beyond that you would have to basically insert text into the word document directly which means you would have to loop through each record in a recordset from Access. In this type of scenario, I would look at a pre-existing word template with bookmarks and updating these (saw someone do it once). A variation of this would be to build all the text into one column and then still mail merger the data - I think there is a FAQ about listing multiple values in a column using VBA.

I think that about covers the topics for possibilities... I'd start with a report and see if that does not work out as it is by far the simplest solution.

 

[dhookom]

Lameid's suggestion is fairly comprehensive. When I need to merge to a document with a table, I use the faq701-4233 in a query that I export to a Word Merge file. I use Chr(9)/tab and Chr(13) & Chr(10)/CrLf to build the "table" of transactions. At that point, Access is no longer needed as I use a Word template with the export file.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Duane, Thanks for the FAQ.

The only other thought I have to add is I like to use the vbCRLF constant in VBA instead of combinations of chr(10) and chr(13).

As for which method to use... I would go to Duane's more abundant real world experience than my guess as to what is easiest.

 

[dhookom]

The issue with vbCRLF is that it doesn't work in a query. It is limited to VBA. Chr(13) & Chr(10) works in either a query or in code so I tend to use the longer expression.

Duane
Hook'D on Access
MS Access MVP

 

[DwinTheBeginner]

To duane and dhookom thanks for the idea ill try both and see how it works. Then ill give you some feedback on it. Thanks a lot guys.

 

[DwinTheBeginner]

To lameid thanks....for the idea.