Zameer as promised
To produce a report in Word from Access:
Determine the structure of the report ie fixed text and imported data
Start a new Word document, type in the fixed text. For the imported data parts I tend to use tables especially where I might need the Access CanGrow feature.
Consider an invoice:
Customer Name/Address
Invoice Number
Invoice Date
*Items being invoiced
Sub Total
VAT Rate
Total VAT
Grand Total Cost
*Iems being invoiced :-
Quantity
Description
Unit Cost
Total Cost
Customer Name/Address would be multiline
Items being invoiced would probably be multiline
VAT Rate, Total VAT, Sub Total and Grand Total Cost would be in "fixed" positions below the Items being invoiced.
The size of the paper would determine the maximum number of Items being invoiced on any particular invoice.
In Word I would have:
Customer Name/Address as a single cell table
Items being invoiced as a four column one row table
all the other items as single cell tables
Using Bookmarks:
Place your cursor in the Customer Name/Address cell, form the menu Insert | Bookmarks..., type a name for your Bookmark eg Customer
Repeat this for all the single cell fields.
With items being invoiced we have a choice, if we assume that the page can take 10 items, we could create 40 separate Bookmarks (Quantity1, Description1 etc. etc), however I would only create four - one in each column (Quantity, Description, UnitCost and TotalCost).
When the document design is complete use File | Save As.., and select Word Template as the file type.
In Access:
Depending on the type of report, you may be able to generate all the required data from one or more queries, or you may have to do some manipulation of the data.
Assuming that the Bookmarks have been give the same names as the Fields:
for each fld in rs.fields
goto bookmark fld.name
insert fld
next fld
Otherwise:
goto bookmark <specify name>
insert <variable>
goto bookmark <specify name>
insert <variable>
...
...
In the invoice example, the Customer detail would come from several fields, so I just concatenate them into a single value. The Items being invoiced I would produce as four separate variables using the same concatenation method.
Simple Concat:
Code:
Sub MyConcat(ByRef Target As String, ByVal Source As String)
If Source = "" Then
Exit Sub
Else
If Target <> "" Then Target = Target & vbCrLf
Target = Target & Source
End If
End Sub
Sub DoConcat()
Dim MasterString As String
MasterString = ""
MyConcat MasterString, ""
MyConcat MasterString, "This is the first line"
MyConcat MasterString, "This is the middle line"
MyConcat MasterString, "This is the last line"
Debug.Print MasterString
End Sub
The variables and Bookmarks should have the same names for simplicity so:
Dim Customer as string
Dim InvoiceNumber as string/Long whatever
etc.
etc.
InvoiceNumber can be assigned right away as the user requestedprinting this invoice
A few recordsets are needed (must be normalised
![[smile]](/data/assets/smilies/smile.gif "[smile] [smile]")
)
dim rsInvoice as Recordset
rsCustomer
rsInvoiceItems
and others as required
Then
Set rsInvoice = CurrentDb.OpenRecordset("SELECT etc. etc. FROM Invoices WHERE InvoiceNumber = " & InvoiceNumber)
InvoiceDate, VATRate, CustomerID can be assigned from here
Set rsCustmers = CurrentDb.OpenRecordset("SELECT etc. etc. FROM Customers WHERE CustmerID = " & CustomerID)
Use the MyConcat procedure to build the Customers variable
With InvoiceItems we can process each item:
Quantity * UnitPrice will give TotalCost for this item and SubTotal + TotalCost for the running total
additionally run each field against MyConcat
MyConcat Quantity, rsInvoiceItems!Quantity
...
...
Calculate VAT and GrandTotal
I (fortunately) always know what version of Word I'll be using so I set the appropriate reference and can use early binding, which helps with Intellisense, if you doon't know then you will need to use CreateObject/GetObject.
Code:
Private wrd As Word.Application
Private doc As Word.Document
Set wrd = New Word.Application
Set doc = wrd.Documents.Add("Invoice") 'This will create a new document based on the Invoice template
doc.Activate
With wrd.ActiveDocument
.Bookmarks(InvoiceNumber).Select
wrd.Selection.TypeText InvoiceNumber, "")
...
...
End With
wrd.PrintOut False
doc.Close wdDoNotSaveChanges
Set doc = Nothing
If wrd <> "" Then
Set wrd = Nothing
End If
If the user needs to interact with the Word document:
Code:
'Enter with TheDoc name as a parameter
'gWord2000Path is a global variable holding the path to Winword.exe
'PrintPath is the folder in which the document is saved (temporarily)
doc.SaveAs TheDoc
Shell gWord2000Path & " " & TheDoc, vbMaximizedFocus
'Wait for Word to open the document
Do While Dir(PrintPath & "~*.DOC", vbHidden) = ""
DoEvents
Loop
'Wait for the user to close Word
Do While Dir(PrintPath & "~*.DOC", vbHidden) <> ""
DoEvents
Loop
'The document was only saved temporarily so delete it
Kill TheDoc
I hope this makes sense and is of use to someone.
