Can a table (think Word table) be put ON a report?

[weigoldk]

I want to draw (or insert) a table on top of (or in?) a report. Drawing lines is easy, but to create a whole table using lines seems a bit daunting.

What I need to end up with is a report based on a parameter query. I will be printing out certificates for a special license. I used to use Word for it, but I can't get the runapp fuction to work in Access 2000 or greater.

 

[dhookom]

To draw a table, add this code to the On Page event of your report:

Private Sub Report_Page()
    Me.Line (1000, 1000)-Step(8000, 200), , B
    Me.Line (1400, 1200)-Step(7200, 500), , B
    Me.Line (1800, 1700)-Step(400, 3000), , B
    Me.Line (7800, 1700)-Step(400, 3000), , B
End Sub

Sorry couldn't resist...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

Hi

but I can't get the runapp fuction to work in Access 2000

What have you tried? I often use Word with Access.

 

[weigoldk]

I posted this over on the forms forum--I probably should have mentoned that.

have an Access 97 database that is past due to be converted up to Access 2000. After the conversion however, I'm getting an error. I have a button that should do the following:

Run Word and open my document
My document is a mail merge main document
It's data source is a query back in the Access database
The query prompts for a date (so the user jumps back to Access and puts in the date and clicks OK)
Then the merge executes
I print and am happy

When I try to run it post the conversion I get the following error in Word:
Word experienced an error trying to open the file.
Try these suggestions
*Check the file permissions for the document or drive.
*Make sure there is sufficient free memory and disk space.
*Open the file with the Text Recovery converter.

My macro simply says:
winword.exe c:\temp\ccmerge5.doc

The document will end up being on a shared drive, but for the time being, I have it on my C drive, so it really isn't a permissions issue (I verified this by simply opening the file in Word).

My Access 97 database simply had an On Click event for the button that then did a RunApp command. In searching around for help on this forum, I found one piece of advice that says to use a Macro. I tried a Macro, but I end up with the same results.

If I have to re-think my way of getting to Word, I'll be happy to do that (Word is needed for the advanced formatting features), but please be gentle with me I've very new to scripting.

 

[Remou]

I tried this in a macro:
winword.exe "C:\TestDirName\Doc1.doc"
And it worked. Could it be as simple as adding quotes? If not, perhaps try building a sample document, and see if that works.

You might like to look at this:
Automate Word Mail Merge From Access
faq705-3237

 

[weigoldk]

Thanks Remou,

I also was able to get the macro to work if I opened a word document, except when the document was a mail merge main document that looped back to the Access db's query to get the data.

Thanks also for the link. I'll study that as soon as I get a chance.

 

[Remou]

I tested with a merge document that looped back to ask for a date. I am using Access 2000.

 

[weigoldk]

Remou,

How did you get it to connect to the query? When I try to set my datasource, all I see are tables.

 

[Remou]

The second tab on the Open Data Source form offered me a choice of queries, however, I seem to recall something about install. I will see if I can remember . I have some Access code that will do this too, if you want.

 

[weigoldk]

Remou, that would be great.

 

[Remou]

Well, this is a long and complicated way, but hopefully you will end up with what you want. First, remove the date bit from your query, then change MMerge below to the name of your query. After you have run the code, you can save your document, add the date bit back (though a form for the date might be better) and then use the Macro above to open. I have tried all this, so if it does not work, it will be back to trying to remember if there was an install thingy.

Public Function OpenWordDocs()
'References: Microsoft Word 9.0 Object Library
Dim strDBmergeSource
Dim objWord As Word.Application
Dim wrdMerge As Word.MailMerge

Set objWord = CreateObject("Word.Application")
objWord.Documents.Add

Set wrdMerge = objWord.ActiveDocument.MailMerge
strDBmergeSource = CurrentProject.FullName

objWord.Application.Visible = True
objWord.Application.DisplayAlerts = wdAlertsNone

With wrdMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=strDBmergeSource, LinkToSource:=True, _
        AddToRecentFiles:=False, Connection:="QUERY MMerge", SQLStatement:= _
        "SELECT * FROM [MMerge]", SQLStatement1:=""
End With

objWord.Application.DisplayAlerts = wdAlertsAll
Set objWord = Nothing
Set wrdMerge = Nothing
End Function

 

[weigoldk]

Remou, you are more than kind. I will try the code ASAP.

Yesterday, while I was out searching the web, I found this, but it did not help:

***check the references to make sure you have referenced the Word Object Library

***Open the module, and select tools, then references. Scroll down to the Word Object library and place a check box beside it.

 

[weigoldk]

Remou or any "script" writer.

Where do I insert the code. I thought it sould go within my "onclic" event for mu button, but I'm getting an error there (End Sub expected).

Here's what I did:

Private Sub Print_CCW_License_Click()


Public Function OpenWordDocs()
'References: Microsoft Word 9.0 Object Library
Dim strDBmergeSource
Dim objWord As Word.Application
Dim wrdMerge As Word.MailMerge

Set objWord = CreateObject("Word.Application")
objWord.Documents.Add

Set wrdMerge = objWord.ActiveDocument.MailMerge
strDBmergeSource = CurrentProject.FullName

objWord.Application.Visible = True
objWord.Application.DisplayAlerts = wdAlertsNone

With wrdMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=strDBmergeSource, LinkToSource:=True, _
AddToRecentFiles:=False, Connection:="QUERY Gun Board Meeting Date Query", SQLStatement:= _
"SELECT * FROM [Gun Board Meeting Date Query]", SQLStatement1:=""
End With

objWord.Application.DisplayAlerts = wdAlertsAll
Set objWord = Nothing
Set wrdMerge = Nothing
End Function
End Function

End Sub

 

[Remou]

The way you would do it if it was a finished function is to put the code snippet in a module or else put it underneath your click event. Then call it in the click event:
Private Sub Print_CCW_License_Click()
Call OpenWordDocs()
End Sub

However, it is not a finished piece of code, it is only a (hopeful) way round the fact that your mailmerge is not showing queries as an option. So what you need to do is to paste the snippet of code into a module, follow the steps above, and then choose run (!) from the menu on the code window. When you save, don't give the module the same name as the code (that is, don't call the module OpenWordDocs). Also, it may not work.

 

[weigoldk]

Remou,

Would you mind if I moved this to a new thread? It really doesn't fit under my original title and if I ever get this figured out, I'll want everyone to join the party!

 

[Remou]

Not at all, you are quite right. In the meantime, here is some code that will create a mailmerge from scratch:

Public Function OpenWordDocs()
'References: Microsoft Word 9.0 Object Library
Dim strDBmergeSource
Dim objWord As Word.Application
Dim wrdMerge As Word.MailMerge
Dim strSQL As String
Dim strDate

'Get the date for this run using an InputBox
strDate = InputBox("Please enter date. ", , Date)

'Build a suitable SQL string
strSQL = "SELECT * FROM [Mail Merge] Where Updated <=#" & Format(strDate, "mm/dd/yyyy") & "#"
'Debug.Print strSQL

'Select a template
strTemplate = "C:\Title.dot"

'Create an instance of Word
Set objWord = CreateObject("Word.Application")
'And show it
objWord.Application.Visible = True
objWord.Application.DisplayAlerts = wdAlertsNone

'Check if the template exists, if it does, use it
'If not, just open a new document
If Len(Dir(strTemplate)) <> 0 Then
    objWord.Documents.Add Template:=strTemplate
Else
    objWord.Documents.Add
End If

'Mailmerge
Set wrdMerge = objWord.ActiveDocument.MailMerge
'Current database name and path
strDBmergeSource = CurrentProject.FullName

'With mailmerge
With wrdMerge
    'If the template is not used, set up form letters
    If Len(Dir(strTemplate)) = 0 Then
        .MainDocumentType = wdFormLetters
    End If
    'Add all the linking stuff
    .OpenDataSource Name:=strDBmergeSource, LinkToSource:=True, _
        AddToRecentFiles:=False, Connection:="QUERY MailMerge", SQLStatement:= _
        strSQL, SQLStatement1:=""
End With

'Release everything leaving the Mailmerge to the user.
objWord.Application.DisplayAlerts = wdAlertsAll
Set objWord = Nothing
Set wrdMerge = Nothing
End Function

 

[weigoldk]

I worked on this just a bit over the weekend. I can't seem to get past this line and error.

Dim objWord As Word.Application

Error: Compile Error
User defined type not defined.

 

[weigoldk]

This thread has evolved itself into something that no longer fits the subject.

I summarized it starteda new thread.

Merging with Word Main Document from Access Form
thread703-1147862