Use a Field from a sub form

[cjmitton]

I have a simple Form with a sub form linked in.

The main form has a business name visable and hidden the business ID (which is also the primary Key).

The sub form Lists the relevent contacts to that business. The form its self works fine, its a datasheet in datasheet view but edit / deletes and additions are all set to no. The sub form has the ContactID (primary Key) and business ID (shown for now as part of testing) along with first name and last name.

I now wish to run a mailmerge to the business and a contact selected from the list.

I have the basic's of the code I require but I'm having trouble selecting the contact from the sub form and adding the 'id' to the query that runs the merge.

Dim strBusID As String
Dim strContID As String
Dim wdApp As New Word.Application
Dim wdMMDoc As Word.Document
' Get the ID for the business ID
    strBusID = "[3rdPartyBusinessID] = " & [3rdPartyBusinessID] & ""
' Get Contact ID from Sub form
    strContID = "Me.[frm_subfrm_3rd_P_Contacts_basic].form.[3rdPartyContactID] = " & [3rdPartyContactID] & ""
 
' Open the merge document and give it a name!
Set wdMMDoc = wdApp.Documents.Add("H:\My Documents\Access\Merge Prop.dotx")
'    wdApp.Visible = False
    wdApp.Visible = True
' Connect to datasource and run sqlstatement to pick the info from the form
    wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=H:\My Documents\Access\DFA v2.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet" _
        , SQLStatement:="SELECT * FROM `qry_3rd_P_address_merge` Where " & strBusID & " And " & strContID

My code fails when it tries to pick up the contact ID from the sub form.
I also need it to be only

"[3rdPartyContactID] = " & [3rdPartyContactID] & ""

In the where statement, I'm getting very confused!

 

[cjmitton]

Realised where I was going wrong! All sorted now.

In case anyone wonders my code is below and if you wish I'll happily accept any comments on improving it!

Dim strContID1 As String
Dim strBusID As String
Dim strContID As String
Dim wdApp As New Word.Application
Dim wdMMDoc As Word.Document
' Get the Info for the where statement on the SQLStatement
    strBusID = "[3rdPartyBusinessID] = " & [3rdPartyBusinessID] & ""
'   Get the Contact ID from the sub form
    strContID1 = [frm_subfrm_3rd_P_Contacts_basic].[Form]![3rdPartyContactID]
'   Create the correct string to go in to the Where statement using the ID from the sub form above.
    strContID = "[3rdPartyContactID] = " & [strContID1] & ""
    
' Open the merge document and give it a name!
Set wdMMDoc = wdApp.Documents.Add("H:\My Documents\Access\Merge Prop.dotx")
'    wdApp.Visible = False
    wdApp.Visible = True
' Connect to datasource and run sqlstatement to pick the info from the form
    wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=H:\My Documents\Access\DFA v2.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet" _
        , SQLStatement:="SELECT * FROM `qry_3rd_P_address_merge` Where " & strBusID & " And " & strContID
' Run the merge
    wdMMDoc.MailMerge.Execute
' Close the Merge Document
    wdMMDoc.Close False
    wdApp.Visible = True
' Bring the merged letter to the front
    wdApp.Activate
' tidy up
    Set wdApp = Nothing
    Set wdMMDoc = Nothing