Transferring Access data into Word Table

Question

Hello Everyone

I need Help transferring MS Access Query data into various tables in a 3 page MS Word Template (using Mail Merge or Automation), Save and Print the output document.

The tables display the details of a Franchisee (Name, ID, DoB, Gender, Company Name, Company Reg No, Address, Tel, etc), the Shops (Shop Type, Community Type, Container Number, Shop Address, Latitude, Longitude, etc) operated by the Franchisee as well as the Phones (Phone Type, Phone No, IMEI, etc ) operated within each Shop.

The information should be displayed in the following format:

Franchisee Details

Tittle     Name                               ID            DoB                                  Gender    

Mr         Mantake Mahlare                690405 5678 09 0                1 January 1069                    Male

Company Name                   Company Reg No

Yebo Mantake Inc               2000 / 123 456 / 23

Physical Address

158 Lunnon Street, Hillcrest, 0128

Tel                           Fax

082 271 2400             082 271 6100

.................................................Page Break..........................

Shop Details

Shop Type                                  Community Type

6m Marine Container                     Rural Village

Container Number

NPD 231 456 211 050 02

Shop Address                                                Latitude              Longitude

267 Walnut Street, Mamelodi Ext 3, Pretotia       S25 / 15 / 27        E28 / 05 / 21.09

Phones Details

Phone Type                      Phone No            IMEI

IN SIGI                         082 201 1931         500 169 114 010 610

SIGI Pro                        082 203 1936         500 169 114 010 580

SIGI Dual Band               082 204 1941          500 169 114 010 850

Adondo 210                   082 205 1943         500 169 114 010 851

Adondo 240                   082 205 1947         500 169 114 010 880

Jembi                           082 207 1959         500 169 114 010 890

Shop Details

Shop Type                                       Community Type

Container Number

Shop Address                                             Latitude              Longitude

Phones Details

Phone Type                      Phone No            IMEI

..................................................................................Page Break................................................................................

CHARGE                                                  TIME OF DAY        TARIFF(VAT INCLUSIVE)

Standard calls to Telkom Network                All Times             R 0,90 per minute

Long Distance Calls to Telkom Network        All Times             R 0,90 per minute

Cellular to Cellular Calls                              All Times             R 0,90 per minute

For every Franchisee who has more than two shops, a new page (Page 2) is added and poulated with the shop and phone information as required.

Thanking you in advance for your assistance.

Answer 1

Hello Again

I’ve managed to transfer data from MS Access to MS Word Table. I now can transfer the Franchisees Info, Shop info and Phone (MSISDN) info. I recorded a macro that selects the MSISDN and converts it to a nested table and then copies the nested table and rePastes it and then merge to the existing table.

The trouble I have now is:

1.      Handling Null Values in my recordset so that ant Null values can be converted to zero Length string (“”). I tried the NZ() function but it does not seem to work. I’m probably doing it all wrong.

2.      Counting the Number of records transferred so that I can

a.      Select the correct number of records to convert into a nested table as above.

b.     Limit the records for the table to 10. On the Final CSTO Agreement Schedule Template, if a Franchisee operates more than 10 phones in a shop, the norm is to duplicate the shop’s details on another table and insert the excess phones there.

3.      How do I insert information for a second shop? Assuming a customer is operating two or more shops. Here I believe I need to change the cboSites Combo Box on frmFilterComboBox to a Multi-Select List Box allowing me to select more than one or All sites. I’m not sure how to do that though.

Answer 2

Hello Shane

Thanks for your response. I'm not sure if I understand how I'd be able to transfer Access info within word? Would I need to use Mail Merge or Bookmarks with automation?

I played around with Abert Kallal’s Super Easy Word Merge (http://www.members.shaw.ca/AlbertKallal/msaccess/WordMerge2008.zip) as well as Bryan Carbonnell’s Word Bookmarks (http://www.databaseadvisors.com/newsletters/newsletter072002/0207wordautomationlpt1_files/PartII.zip) with some limited success. My trouble arises with the Line Items. I could transfer the 1st shop and phone info but could not go to the next record, let alone conditionally add a table and merge to it in the event of more shops and phones

I am using Access 2007 and Word 2007.

Answer 3

I would recommend doing this from inside of Word, you are going to be there if you start in Access.  (by the way , you haven't told us what version of office you are using?)

I would post this to the Word Mail Merge forum:

http://social.answers.microsoft.com/Forums/en-US/wordshare/threads

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire