Merge Object and not displaying Access Queries with VBA

jmo · #1 12-22-2015, 11:08 AM

I have a database where some queries utilize VBA code to output text for mail merge.

To make these queries appear during a mail merge I have to check the advanced option 'Confirm File Format on Open'. Then select DDE when doing the merge.

If I try to merge with VBA code within a word document:

ActiveDocument.MailMerge.OpenDataSource Name:=dbpath, ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Connection:="", Format:=wdOpenFormatAuto, SubType:=wdMergeSubType2000

It successfully opens the database but the queries that have the VBA code are absent as if the 'Confirm file format..' is unchecked. How can I make them appear when doing a merge with VBA code?

macropod · #2 12-22-2015, 08:02 PM

You could add the SQL Statement to the OpenDataSource method. The OpenDataSource method takes the following parameters:
Name, Format, ConfirmConversions, ReadOnly, LinkToSource, AddToRecentFiles, PasswordDocument, PasswordTemplate, Revert, WritePasswordDocument, WritePasswordTemplate, Connection, SQLStatement, SQLStatement1, OpenExclusive, SubType

You'll see two SQLStatement references - SQLStatement & SQLStatement1. SQLStatement accepts query strings of up to 255 characters. If the query string is longer than 255 characters, SQLStatement specifies the first portion of the string, and SQLStatement1 specifies the second portion.

You can export the current parameters to a new document, using code like:

Code:

Sub Test()
Dim StrMMData As String, DocTmp As Document
With ActiveDocument.MailMerge
  If .MainDocumentType <> wdNotAMergeDocument Then
    StrMMData = StrMMData & "Mail Merge Data Source Name:" & vbCr & .DataSource.Name & vbCr
    StrMMData = StrMMData & "Mail Merge Connect String:" & vbCr & .DataSource.ConnectString
    StrMMData = StrMMData & vbCr & "Mail Merge Query String:" & vbCr & .DataSource.QueryString
    Set DocTmp = Documents.Add
    DocTmp.Range.Text = StrMMData
  Else
    MsgBox "Not A Merge Document"
  End If
End With
End Sub

As for the necessity to use DDE, I suggest you verify that on a specialist Access forum (e.g. http://www.accessforums.net/)

jmo · #3 12-28-2015, 06:20 AM

Thank you, Macropod.

Do you know what SQL statements I would add to open up a window that displays all the queries/tables in a particular database? Leaving that statement blank seems to accomplish this, but does not seem to solve my problem.

I've tried toggling several settings to get the window that asks you to manually confirm the file connection so I can select DDE (which is what happens when the 'Confirm File Conversation..' advanced option is checked).

Essentially I'm wanting the window that opens that is done automatically when you follow the steps in this post: https://www.msofficeforums.com/mail-...ps-tricks.html (under Mail Merge Data Format Problems).

macropod · #4 12-28-2015, 09:54 PM

I previously suggested you confirm the need to use DDE; that is a most unusual requirement and I doubt it really is needed. Since you haven't posted any of the code you say you're already using, I can't comment further on that.

As for retrieving whatever the DDE connection uses, all you need do is run the macro I provided after doing that setup manually. If you do it before and after, you should be able to retrieve both the non-DDE SQL query string and the post-DDE connection string.

jmo · #5 12-29-2015, 06:36 AM

Sorry, I included this code in my first post but didn't tag it as such.

Code:

ActiveDocument.MailMerge.OpenDataSource Name:=dbpath, ConfirmConversions:=True, 
ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", 
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Connection:="", 
Format:=wdOpenFormatAuto, SubType:=wdMergeSubType2000

The database queries in the access document utilize access-basic for some calculations (such as turning numbers into words for contracting or calculating the number of months between two date fields). The only way I've been able to get them to appear as an option for a standard mail merge is by selecting DDE.

I tried the sample code you provided but when I copy the statement(s) it provides and then execute the merge the access basic functions, date, and number fields lose their formatting (as if the 'confirm file format' option box was not checked).

Is there a way to manually set a DDE connection or have it pop up the box to select the connection type as it does when you manually step through the merge with the tool-bar buttons?

macropod · #6 01-03-2016, 10:20 PM

Yes, I am aware of the code in you first post but, like the code in your last post, it contains no SQL statement! All the macro I provided does is to create a new document containing your:
• Mail Merge Data Source Name;
• Mail Merge Connect String; and
• Mail Merge Query String.
Nothing in that code executes a merge.

As for turning numbers into words and calculating the number of months between two date fields, both of those can be handled by field coding, without recourse to DDE or VBA.

jmo · #7 01-04-2016, 06:43 AM

I tried copying the sqlstatement provided by a manual mail-merge but it still will not look at the queries with the VBA code.

Macropod. I apologize. I'm very new to Office coding and so really all I'm wanting to do is part of the 'run the document' code I have is to have it automatically open up the mail-merge prompts including the connection type.

We have about 12 different queries (one for each program manager) that links to the same table. I wanted them to manually select their own query (or someone elses, if they're filling in) from a list of all the queries.

Really I just want when I have the code say 'run the mail merge' that all the boxes pop up as if I'd hit the button. If there's a specific SQLString that does that I'll give it a try the one I copied doesn't seem to do the DDE connection so the queries with VBA functions don't appear.

The code that will use what I'm trying to do is here:

Quote:

Private Sub RUN_Click()
Dim DBLoc As String
If FundSource.Value = "" Or FedFunds.Value = "" Or Match.Value = "" Or ServiceCharges.Value = "" Or Equipment.Value = "" Or DiscloseInfo.Value = "" Or FFATA.Value = "" Then
MsgBox ("ERROR: One or more option fields are blank. All fields must be set.")
Else
With ActiveDocument.CustomDocumentProperties
.Add Name:="FundSource", Type:=msoPropertyTypeString, Value:=FundSource.Value, LinkToContent:=False
.Add Name:="FedFunds", Type:=msoPropertyTypeString, Value:=FedFunds.Value, LinkToContent:=False
.Add Name:="Match", Type:=msoPropertyTypeString, Value:=Match.Value, LinkToContent:=False
.Add Name:="ServiceCharges", Type:=msoPropertyTypeString, Value:=ServiceCharges.Value, LinkToContent:=False
.Add Name:="Equipment", Type:=msoPropertyTypeString, Value:=Equipment.Value, LinkToContent:=False
.Add Name:="DiscloseInfo", Type:=msoPropertyTypeString, Value:=DiscloseInfo.Value, LinkToContent:=False
.Add Name:="FFATA", Type:=msoPropertyTypeString, Value:=FFATA.Value, LinkToContent:=False
.Add Name:="DBYear", Type:=msoPropertyTypeString, Value:=DBYear.Value, LinkToContent:=False
End With
ActiveDocument.Fields.Update
'MSGBOX will change text/move location/as more parts of the code work.
MsgBox ("Contract Created. Double Check Margins.")
‘MAIL MERGE CODE HERE
With Application.Dialogs(wdDialogFileSaveAs)
.Name = "h:\RDS\ASSESS\Program Files\#UNK AGENCY NAME Unsigned Contract.doc"
.Format = wdFormatDocument
.Show
End With
Unload Me
End If
End Sub

macropod · #8 01-04-2016, 04:12 PM

Until you post back with the output produced by the macro I posted, we're not going to make any progress with this. Besides which, the code you've now posted indicates you're also using a bunch of Custom Document Properties that aren't apparently related to the mailmerge. Perhaps you could explain what that's about.

jmo · #9 01-05-2016, 05:49 AM

The document is a contracting document with clauses that are either included or excluded depending upon certain factors. I put in custom document properties which are set via combo boxes. These clauses are stored in fields that are displayed IF a custom document property (via combobox) is set.

When the button to 'run' is pushed it updates all the fields to display the correct clauses. This piece works fine.

It's mail merging the queries which contain access VB that are the problem.

The output from your macro is as follows:

Quote:

Mail Merge Data Source Name:
H:\RDS\ASSESS\2016\Database\2016 OCJP Grants.mdb
Mail Merge Connect String:

Mail Merge Query String:
SELECT [2016 OCJP Grants TABLE].AUTH_AGENCY, [2016 OCJP Grants TABLE].FUND_SOURC, [2016 OCJP Grants TABLE].[2016 EDISON CONTRACT], [2016 OCJP Grants TABLE].BEGDATE, [2016 OCJP Grants TABLE].ENDDATE, [2016 OCJP Grants TABLE].[CFDA #], [2016 OCJP Grants TABLE].[VENDOR ID], [2016 OCJP Grants TABLE].[2016 SPEED CHART], [2016 OCJP Grants TABLE].[ACCOUNT CODE], [2016 OCJP Grants TABLE].FED_ID, [2016 OCJP Grants TABLE].PROJ_DIR, [2016 OCJP Grants TABLE].TITLE_DIR, [2016 OCJP Grants TABLE].ADDR1_DIR, [2016 OCJP Grants TABLE].ADDR2_DIR, [2016 OCJP Grants TABLE].CITY_DIR, [2016 OCJP Grants TABLE].PHONE_DIR, [2016 OCJP Grants TABLE].ZIP_DIR, [2016 OCJP Grants TABLE].EMAIL_DIR, [2016 OCJP Grants TABLE].FEDFUNDS16, [2016 OCJP Grants TABLE].MTCH_FUNDS16, [2016 OCJP Grants TABLE].FEDFUNDS17, [2016 OCJP Grants TABLE].MTCH_FUNDS17, [2016 OCJP Grants TABLE].ALL_FUNDS, [2016 OCJP Grants TABLE].MGR_NAM, [2016 OCJP Grants TABLE].MGR_GENDER, [2016 OCJP Grants TABLE].PHONE_MGR, [2016 OCJP Grants TABLE].MGR_EMAIL, [2016 OCJP Grants TABLE].[Agency FY End Date], English([ALL_FUNDS]) AS WORD_NUM, SpeedtoFain([2016 Speed Chart]) AS FAIN, SpeedtoDate([2016 Speed Chart]) AS AwardDate, SpeedtoAmount([2016 Speed Chart]) AS AwardAmount, SpeedtoAA([2016 Speed Chart]) AS AwardingAgency, SpeedtoContact([2016 Speed Chart]) AS FederalContact, SpeedtoPhone([2016 Speed Chart]) AS ContactPhone, SpeedtoAwardName([2016 Speed Chart]) AS GrantName, NumofMonths([BegDate],[EndDate]) AS NumMonths, ConvertNumberstoEnglish([NumMonths]) AS MonthWords
FROM [2016 OCJP Grants TABLE]
WHERE ((([2016 OCJP Grants TABLE].MGR_NAM)=""John Doe""));

If I copy this directly into the SQLString and run it then it skips all the Access VB sections but if I manually do it and connect as DDE it works fine.

macropod · #10 01-05-2016, 05:29 PM

OK, you've provided the Mail Merge Data Source Name and the Mail Merge Query String that the macro returns before you establish the DDE connection, but what Mail Merge Connect String and Mail Merge Query String does the macro return after you establish the DDE connection?

jmo · #11 01-07-2016, 06:35 AM

That is the display after connecting via DDE.

The process I did was:

Mailing -> Select Recipients ->
Navigate and Select to Database ->
Select 'DDE' from the popup 'Confirm Data Source' ->
Navigate to a query with Access Visual Basic -> Click OK

At this point I can merge or preview the document and all the fields appear correct (including those with the visual basic code). I then ran your macro and the output is what I posted previously.

When I take that output and insert it into the mail-merge object's SQLStrings it will not access the fields that have access visual basic. I'm assuming because when I do this there's no DDE connection.

Ultimately the functionality I'm seeking (from a user perspective) is:
User hits 'Run' -> 'Confirm Data Source' pops up (or DDE is automatically selected, which it's not doing. -> Query is selected.

There is no connect string which outputs with your macro. It just says 'Mail Merge Connect String' and a blank line.

macropod · #12 01-07-2016, 04:29 PM

OK, try:

Code:

Private Sub RUN_Click()
Dim DBLoc As String, StrSrc As String, StrFlNm As String
StrSrc = "H:\RDS\ASSESS\2016\Database\2016 OCJP Grants.mdb"
If FundSource.Value = "" Or FedFunds.Value = "" Or Match.Value = "" Or _
  ServiceCharges.Value = "" Or Equipment.Value = "" Or DiscloseInfo.Value = "" Or FFATA.Value = "" Then
  MsgBox ("ERROR: One or more option fields are blank. All fields must be set.")
Else
  With ActiveDocument
    With .CustomDocumentProperties
      .Add Name:="FundSource", Type:=msoPropertyTypeString, Value:=FundSource.Value, LinkToContent:=False
      .Add Name:="FedFunds", Type:=msoPropertyTypeString, Value:=FedFunds.Value, LinkToContent:=False
      .Add Name:="Match", Type:=msoPropertyTypeString, Value:=Match.Value, LinkToContent:=False
      .Add Name:="ServiceCharges", Type:=msoPropertyTypeString, Value:=ServiceCharges.Value, LinkToContent:=False
      .Add Name:="Equipment", Type:=msoPropertyTypeString, Value:=Equipment.Value, LinkToContent:=False
      .Add Name:="DiscloseInfo", Type:=msoPropertyTypeString, Value:=DiscloseInfo.Value, LinkToContent:=False
      .Add Name:="FFATA", Type:=msoPropertyTypeString, Value:=FFATA.Value, LinkToContent:=False
      .Add Name:="DBYear", Type:=msoPropertyTypeString, Value:=DBYear.Value, LinkToContent:=False
    End With
    .Fields.Update
    'MSGBOX will change text/move location/as more parts of the code work.
    MsgBox ("Contract Created. Double Check Margins.")
    'MAIL MERGE CODE HERE
    Application.DisplayAlerts = wdAlertsNone
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .OpenDataSource Name:=StrSrc, ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=False, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:="", _
        SQLStatement:="SELECT AUTH_AGENCY, FUND_SOURC, [2016 EDISON CONTRACT], BEGDATE, ENDDATE, " & _
        "[CFDA #], [VENDOR ID], [2016 SPEED CHART], [ACCOUNT CODE], FED_ID, TITLE_DIR, ADDR1_DIR, " & _
        "ADDR2_DIR, PROJ_DIR, CITY_DIR, PHONE_DIR, ZIP_DIR, EMAIL_DIR, FEDFUNDS16, MTCH_FUNDS16, ", _
        SQLStatement1:="FEDFUNDS17, MTCH_FUNDS17, ALL_FUNDS, MGR_NAM, MGR_GENDER, PHONE_MGR, MGR_EMAIL, " & _
        "[Agency FY End Date], English([ALL_FUNDS]) AS WORD_NUM, SpeedtoFain([2016 Speed Chart]) AS FAIN, " & _
        "SpeedtoDate([2016 Speed Chart]) AS AwardDate, SpeedtoAmount([2016 Speed Chart]) AS AwardAmount, " & _
        "SpeedtoAA([2016 Speed Chart]) AS AwardingAgency, SpeedtoContact([2016 Speed Chart]) AS FederalContact, " & _
        "SpeedtoPhone([2016 Speed Chart]) AS ContactPhone, SpeedtoAwardName([2016 Speed Chart]) AS GrantName, " & _
        "NumofMonths([BegDate],[EndDate]) AS NumMonths, ConvertNumberstoEnglish([NumMonths]) AS MonthWords " & _
        "FROM [2016 OCJP Grants TABLE] WHERE MGR_NAM=""John Doe""", SubType:=wdMergeSubType2000
        For i = 1 To .DataSource.RecordCount
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = True
          With .DataSource
            .FirstRecord = i
            .LastRecord = i
            .ActiveRecord = i
            StrFlNm = .DataFields("AUTH_AGENCY")
          End With
          .Execute Pause:=False
          With ActiveDocument
            .SaveAs FileName:="H:\RDS\ASSESS\Program Files\" & StrFlNm & " Unsigned Contract.doc", _
              FileFormat:=wdFormatDocument, AddToRecentFiles:=False
            .Close SaveChanges:=False
          End With
        Next
        .MainDocumentType = wdNotAMergeDocument
    End With
  End With
    Application.DisplayAlerts = wdAlertsAll
  Unload Me
End If
End Sub

I'm still not convinced you need DDE, though, especially given that the calculations you're doing and the number-to-text conversions can both be handled quite easily by field coding a field switches, respectively, in the mailmerge main document.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel worksheet as OBJECT in WORD not displaying	mohsin	Word	1	03-27-2012 10:43 PM
Mailmerge with Access queries	optiontips.in	Mail Merge	2	11-21-2011 03:12 AM
mail merge queries not displaying	donwalt	Mail Merge	0	09-05-2010 02:41 PM
flash shockwave object not displaying	webguync	PowerPoint	7	04-20-2010 07:23 AM
Access Object library 10	Gyto	Office	0	10-09-2008 09:04 AM