VBA Mail Merge - How to define record?

[RP1America]

Hi, I am fairly new to VBA and even newer to dealing with Mail Merge in VBA.

The end result that I am looking for is to be able to have the user enter an input on a userform (PlanNumber) and from that, mail merge that record into the document from a .dbf file.

I am lost. I have scourred the web looking for something that I can digest, yet have come up empty. Any help would be greatly appreciated!

I recorded a macro while doing the merge manually and got the following, yet was hoping to see where it accepted my input to find the PlanNumber and that is not there.

Thank you in advance!

Sub Merge1()
'
' Merge1 Macro
'
'
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
            .LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
        End With
        .Execute Pause:=False
    End With
    Windows("Zero Dollar Contract ASA Terminations.dotm").Activate
End Sub

 

[SkipVought]

hi,

I pasted your code into my vb editor.

I STEPPED into the code.

I selected ActiveDocument.MailMerge and ADDED a Watch .

I discovered that the MailMerge obejct has a the following property: ActiveDocument.MailMerge.DataSource.ConnectString

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

I appreciate the info, Skip. Unfortunately, I still am not understanding.

How do I tell VBA which record to merge?

 

[SkipVought]

yet was hoping to see where it accepted my input to find the PlanNumber

I think it's the QueryString property that you want to look at.

Please copy BOTH the connection and querystring and post back.

What value are you inputting and where?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi RP1America,

Is there a particular reason for having a userform?

You can use fairly mundane field coding instead to solicit the record details in an ordinary mailmerge main document. For example, you could use a field coded like:
{SKIPIF{MERGEFIELD PlanNumber}<> {FILLIN "Plan Number to Process?" \o \d 0}}


Cheers
[MS MVP - Word]

 

[RP1America]

Skip - Connection? QueryString? I am still at a complete loss.

Macropod/Skip - Yes, I already am using a userform that determines letter content by user input. My intention is to add another textbox to where they can enter the Plan Number (e.g., A12345). This Plan Number would then be used to determine which .dbf record is used to merge.

Upon researching QueryString on the web, I found a piece of code that I thought might work for me. Though, it is blowing up on the first line.

    ActiveDocument.MailMerge.DataSource.QueryString = _
    "SELECT * FROM K:\CORPRPS\CORPDATA\Global.dbf WHERE ((CINM='" & txtPlan.Text & "'))" & ""
    
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=True
    End With
    Windows("Zero Dollar Contract ASA Terminations.dotm").Activate

 

[SkipVought]

What is the error message on the first statement?

Hit the debug button and select

    [highlight]ActiveDocument.MailMerge.DataSource[/highlight].QueryString = _
    "SELECT * FROM K:\CORPRPS\CORPDATA\Global.dbf WHERE ((CINM='" & txtPlan.Text & "'))" & ""

...and select View > Watch Window and report what results in the Watch Window.

Also, I assume that CINM is a TEXT field and that txtPlan.Text resolves to a valid text value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Okay, I need to start from scratch as I am not at all understanding the code that I posted for this issue.

Would someone please help me construct the code?

My word doc (which will contain the macro) is titled "Zero Dollar Contract ASA Terminations.dotm".

It is already linked to the source document for mail merge, however, if it is needed, that is titled "K:\CORPRPS\CORPDATA\Global.dbf".

The column title within the source document that I would like to search and match is titled "CINM".

The input on my userform that should match CINM is "txtPlan".

I am needing to merge the row data of txtPlan found in the CINM column of Global.dbf into my Word doc. This will be only one record.

Help please! Thank you!