Moving from macro to Code, is this right?!

[Franky G]

Hi,

I have a button on a form which starts a macro, which does the following.

Setwarnings off
OpenQuery "QueryMergeData"
RunApp "C:\Program Files\Microsoft Office\Office\WINWORD.EXE" & "J:\Reports\BC\Admin\Merge.doc"
Setwarnings on

The Word Document uses data from a table that query has just created as it's datasource. I would like to convert this sequence into code, but before running the mail merge check that there is actually data in the table! If there is no data, then I want Access to inform the user with a message box and stop the mail merge from running.

Would something like this do the trick? Any suggestions would be welcome.

Private Sub Command132_Click()
DoCmd.RunQuery "QueryMergeData", acViewNormal (Do I need to have to specify an acView parameter?)
If DCount("*", "[TableMergeData]") > 0 Then
DoCmd.RunMacro ("MacroMailMerge") I don't know how to start Word and open the mail merge template using code!
Else
msgbox "No data to report, closing"
End If
End Sub

I'm assuming I can perform a DCount on a table, is this correct?

Thanks for any help,
FrankyG

 

[The_Doc_Man]

I think your syntax is wrong on the DCount, but absolutely yes you can run a DCount on a table.

If DCount("*", "[TableMergeData]")

DCount("[{any field name}]", "TableMergeData")

The domain portion (second element) doesn't want brackets. It is the first element that wants brackets. If there is a prime key in the table, use that field name.

As to opening Word, there is a command button wizard that would activate Word and trigger a mailmerge. I think there is, anyway.

But you could do this from the other side of the coin much more easily. Set up a query to define the data you want, then go into Word to pre-define a mailmerge template based on said query. When you want the report, trigger the merge from Word.

I'm not suggesting that this is the most elegant way. It is just the easiest way most of the time, based on this principle: If you want an XXX output, open the XXX application to get it. If XXX needs data from YYY, let XXX do the opening of YYY behind the scenes. Doing it the other way around (opening XXX from YYY) is looking at the data flow backwards.