Exporting Multiple Records to a word file

[Stormrider83]

Hi!

This will have a simple solution (hopefully).

I have a word document with bookmarks and VBA code to export records and place them within the bookmarks:

Private Sub Labels_Click()

Dim MyWord As Word.Application
Dim PathDocu As String

Set MyWord = New Word.Application
PathDocu = "L:\Factory Database\"

With MyWord
    .Documents.Open (PathDocu & "L4731.doc")
    .ActiveDocument.Bookmarks("Description").Range.Text = Me.Description
    .ActiveDocument.Bookmarks("Part_No").Range.Text = Me.Part_No
    .ActiveDocument.Bookmarks("Qty").Range.Text = Me.Part_Qty
.Visible = True
End With
DoEvents
Set MyWord = Nothing

End Sub

The only problem is only the first record is placed in the document.

1) How do I place all records in the document?

2) How do I place all records in the document until page is full. ie if I have 10 records and 80 places how do I repeat the documents until the places are full.

To put it in context I am printing address labels but would like pages of them rather than 10 address labels and lots of blank stickers!

 

[MarkK]

I would use an Access report for address labels, which will automatically repeat the detail section of the report for every record in the recordsource. And you can set the width of the detail section in conjunction with column count, and width, and repeat the section horizontally.
I think you'll find this a far simpler solution.

 

[Stormrider83]

I would use an Access report for address labels, which will automatically repeat the detail section of the report for every record in the recordsource. And you can set the width of the detail section in conjunction with column count, and width, and repeat the section horizontally.
I think you'll find this a far simpler solution.

I did try it but the label I am using (Avery L4731) is not in Access only in word.

The custom selection doesn't quite work properly where as the Word template I am using does.

 

[Stormrider83]

Also I set the template to 7 across (in access) and it only prints one column admittedly to the end of the recordset.

 

[MarkK]

You can set the number of columns on the Menu->File->Page Setup dialog when your report is open in design view. Here you can also set the distance between columns.
You'll need to do a little math and a little experimentation to get the detail section of your report, and columns, and distance between columns, and heights all lined up and printing correctly, but I still expect this'll be easier and more reliable than using Word.

 

[Stormrider83]

The number of columns IS set to 7 and the maths is dead on.

Access still prints on line per page.

Even if the maths was slightly off I could live with it...

The problem isn't that the report is slightly off its simply printing one line per page...

 

[MarkK]

- Double check Menu->File->Page Setup->Columns tab. There are a number of settings here where you can explicitly set column width, height, etc... and there's a "Same as Detail" check box, which you'll probably need to clear.
- And I can't say, not being there, exactly what the problem may be. I do find this process a little finicky too. I wish you could set these properties in an Access property sheet for the report, but alas, this is not the case.

 

[Stormrider83]

- Double check Menu->File->Page Setup->Columns tab. There are a number of settings here where you can explicitly set column width, height, etc... and there's a "Same as Detail" check box, which you'll probably need to clear.
- And I can't say, not being there, exactly what the problem may be. I do find this process a little finicky too. I wish you could set these properties in an Access property sheet for the report, but alas, this is not the case.

Cheers will have a look and post how I get on!

Unforunately I am using Access 2007 lots of ribbons and no recognisable menus!