Running a procedure from a Macro

[Todd K.]

I am trying to run a Word Merge from a macro in Access 2003. I have a module
with the following:

Private Sub MergeIt()

Dim oApp As Object
Set oApp = CreateObject("Word.Application")
oApp.Visible = True

Dim objWord As Object
Set objWord = GetObject("C:\Documents and Settings\dlg-todd.kirby\My
Documents\MOA-Practice.doc", "Word.Document")
objWord.Application.Visible = True

End Sub

In my macro, I have the command OpenModule where I have specified the module
name from the dropdown, but there is no dropdown for the procedure name. I
entered MergeIt() in the procedure name, but I am getting an error saying it
can't find the procedure. What am I doing wrong?

 

[Gina Whipp]

Todd,

Been a long time since I used a macro but try getting rid of the () after
MergeIt

 

[Douglas J. Steele]

Actually, since Todd's trying to call it from a Macro, MergeIt has to be a
function, not a sub. Macros can only call functions.

It doesn't matter that the function doesn't return a value, so all that's
necessary is to change the two uses of the keyword Sub to Function:

Private Function MergeIt()
Dim oApp As Object
Dim objWord As Object

Set oApp = CreateObject("Word.Application")
oApp.Visible = True
Set objWord = GetObject("C:\Documents and Settings\dlg-todd.kirby\My
Documents\MOA-Practice.doc", "Word.Document")
objWord.Application.Visible = True

End Function

I'm not sure what that's going to accomplish, though, since oApp and objWord
are local to the procedure, so can't be used anywhere else. If all that's
required is to open that particular word document, all you really need is

Application.FollowHyperlink "C:\Documents and Settings\dlg-todd.kirby\My
Documents\MOA-Practice.doc"

 

[Gina Whipp]

Douglas,

"Actually, since Todd's trying to call it from a Macro, MergeIt has to be a
function..." Thanks Douglas, I didn't know that, I have learned something
new!

 

[Todd K.]

Excellent Douglas, I created a button with the FollowHyperlink in the Event
Procedure and it works. Two more questions:

1) The reason I was doing it with a Macro was so that I could run a create
table query first, can I run a query in the same module?
2) The Word document I'm opening is a Merge document that is linked to the
created table in the database. When I run the FollowHyperlink, I get a box
popping up asking if I want to run the SQL. When I click OK, it opens but
does not update the data. Any ideas?

 

[Douglas J. Steele]

Yes, you can run queries in modules. If it's a saved query, you should be
able to use:

CurrentDb.QueryDefs("NameOfQuery").Execute dbFailOnError

If it's not a saved query, you have the choice of creating a temporary query
and running it:

Dim qdfTemp As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT ...."
Set qdfTemp = CurrentDb.CreateQueryDef("", strSQL)
qdfTemp.Execute dbFailOnError

or else using RunSQL to run your query:

Dim strSQL As String

strSQL = "SELECT ...."
DoCmd.RunSQL strSQL

If you want to avoid the message that'll pop up when using RunSQL, set
warnings off:

Dim strSQL As String

strSQL = "SELECT ...."
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Sorry, I've done nothing with Word merging, so I can't help you with your
second question.

 

[Todd K.]

Thanks Douglas, I actually cheated while I was waiting and converted the
macro to VB with the wizard, then copied the text into my module. Thanks for
the SQL hint below though, I had been wondering how to do that as well.

The merge letter is still not updating, I'll continue working on it and post
if I find a solution. Thanks again.

 

[Todd K.]

I've got it, I just had to open up the Word document and re-establish the
source table. It works now.