Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Location
    Marlborough, MA, USA
    Posts
    3

    Unanswered: Word Mail Merge Parameter Based Query with Dropdown

    I have a Word mail-merge which generates thank-you letters for donations to the local food pantry. The mail-merge gets its data from an Access 2007 query. Each donation is linked to thank you letter form based upon the type of donation.

    There's a single query that all of the Word letters utilize for the merge data. This query currently prompts for the ID of the letter to process. I would much rather have a dropdown list of letters types and have the user select from that.

    Based upon what I've read in this forum and others, I don't see any way to do this. It appears that I need to create a form with a command box which runs the query which pulls the data from [form]![form_name]![combo_box_name]

    Unfortunately this order doesn't work for me. I've tried simply placing [form]![form_name]![combo_box_name] into the query and running that, but it then prompts for [form]![form_name]![combo_box_name]

    Any ideas how I can do this?

    Thanks, Geoff

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    another appraoch may be to put a flag on the data that identifies if a letter needs to be generated. you coud refine that flag to be say a numeric value, of its say 0 then no letter is required, if 1 type 1 is required, 2 type 2 letter and so on.

    so you then run a specific query in the type letter that requests all data with a type one letter request, and so on.

    remember to run another query on completion which clears the flag once you have successfully sent th email merge to the post box.

    if you run the update query some time after the mail merge then there is a risk that soemone may queue a letter but it won't get printed in th ecurrent mailmerge but may get de listed, so you need some date element that identifies when a request was made, and only delete records before the ime the mailmerge was run.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2009
    Location
    Marlborough, MA, USA
    Posts
    3
    I understand the process flow. I didn't want to have a separate query for each letter. Thus the desire to have the user select the name of the letter that they're generating from a dropdown. Having a dropdown text field which matches the name of the letter that they're merging is much better than having to know that ID 5 matches "2009 Thanksgiving Food Donation".

    I could solve this if there was some way to pass a parameter from Word to Access, but I know of no way of doing that.

    Thanks, Geoff

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nah
    you pass a parameter as part of the query
    so in your type 1 letter you request data that is of type 1. same query different parameter.

    Google
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2009
    Location
    Marlborough, MA, USA
    Posts
    3
    How do you pass a parameter from a Word mail merge to Access?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to totally understand what you're looking for.

    If you want to use a combo box in Word, you can create a UserForm in Word, add a ComboBox control to it and feed this combo with data from an Access database, here is an example of code to do it:
    Code:
    Private Sub UserForm_Initialize()
    
        Const DatabaseName As String = "C:\Documents and Settings\Sinndho\My documents\Access\CF_Sit.mdb"
        Const SQLQuery As String = "SELECT DISTINCT Cust_Code FROM Tbl_Customers ORDER BY Cust_Code"
    
        Dim wks As DAO.Workspace
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        
        Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
        Set dbs = wks.OpenDatabase(DatabaseName)
        Set rst = dbs.OpenRecordset(SQLQuery, dbOpenSnapshot)
        With rst
            Do Until .EOF
                Me.ComboBox1.AddItem !Cust_Code
                .MoveNext
            Loop
            .Close
        End With
        dbs.Close
        Set rst = Nothing
        Set dbs = Nothing
        Set wks = Nothing
        
    End Sub
    If you want to work from Access there are many possibilities, depending on how you organize your application. One possibility that won't need to many changes in your program would consist in modifying the query each time you need to use it.
    1. Create a form in the Access database.
    2. Add a ComboBox (name: Combo_Cust_Codes)and a CommandButton (name: Command_MakeQuery) to this form.
    3. Set the RowSourceType property of the combo to Table/Query.
    4. Set the RowSource property of the combo to "SELECT DISTINCT Cust_Code FROM Tbl_Customers ORDER BY Cust_Code.
    5. Add this code in the Click event code of the Command_MakeQuery button:
    Code:
    Private Sub Command_MakeQuery_Click()
    
        Const SQLQuery As String = "SELECT Cust_Group, Cust_Client, Cust_PlantCode, Cust_City, Cust_Country_Code, Cust_Code " & _
                                   "FROM Tbl_Customers WHERE Cust_Code = '<Cust_Code>'"
        Const QueryName As String = "Qry_MailMerge"
    
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        Dim bolFound As Boolean
        
        If Not IsNull(Me.Combo_Cust_Codes.Value) Then
            strSQL = Replace(SQLQuery, "<Cust_Code>", Me.Combo_Cust_Codes.Value)
            For Each qdf In CurrentDb.QueryDefs
                If qdf.Name = QueryName Then
                    qdf.SQL = strSQL
                    CurrentDb.QueryDefs.Refresh
                    bolFound = True
                    Exit For
                End If
            Next
            If bolFound = False Then
                CurrentDb.CreateQueryDef QueryName, strSQL
                CurrentDb.QueryDefs.Refresh
            End If
        Else
            MsgBox "You must select a customer in the combo.", vbInformation, "No customer."
        End If
        
    End Sub
    You can now use Qry_MailMerge in your mail merge process (of course you need to change the strings to work with your own data).

    This is only a quick solution among many others. You could even change the query in the Access database from the Word document.
    Have a nice day!

  7. #7
    Join Date
    Jul 2015
    Posts
    1

    Use Access makeTable query for mail-merge prompted solution

    Quote Originally Posted by geoffschultz View Post
    I have a Word mail-merge which generates thank-you letters for donations to the local food pantry. The mail-merge gets its data from an Access 2007 query. Each donation is linked to thank you letter form based upon the type of donation.

    There's a single query that all of the Word letters utilize for the merge data. This query currently prompts for the ID of the letter to process. I would much rather have a dropdown list of letters types and have the user select from that.

    Based upon what I've read in this forum and others, I don't see any way to do this. It appears that I need to create a form with a command box which runs the query which pulls the data from [form]![form_name]![combo_box_name]

    Unfortunately this order doesn't work for me. I've tried simply placing [form]![form_name]![combo_box_name] into the query and running that, but it then prompts for [form]![form_name]![combo_box_name]

    Any ideas how I can do this?

    Thanks, Geoff
    Hi Geoff,

    I found the easiest way forward, including using ODBC linked tables to MS SQLServer - was build your resultset using a make table query in Access involving a prompted parameter - or the dropdown in your case. Keep your mail-merge document to select the whole table and everything works fine. Trying to achieve this within the Word mail-merge solution isn't fool-proof and is confusing for some users.

    Here is a code snippet..

    ElseIf InStr(rs![Argument], "DavidPre") > 0 Then
    On Error Resume Next
    MyDatabase.TableDefs.Delete "DavidPreEngInv"
    MyDatabase.TableDefs.Refresh
    On Error GoTo HandleButtonClick_Err ' turn err handling back on
    ' pickup the command line to run word from our config table for this report
    stSql = "SELECT config_value FROM config WHERE config_name = " & Chr(39) & "DavidPreEngInv" & Chr(39)
    Set rs2 = MyDatabase.OpenRecordset(stSql, dbOpenSnapshot)
    If Not rs2.EOF Then sShellCommand = rs2.Fields(0)
    rs2.Close
    sReportParam = InputBox("Please enter the Job No (including leading zeros):", "Job/Invoice No")
    If Len(sReportParam) = 0 Then
    GoTo HandleButtonClick_Exit
    End If
    ' here is the make table query

    Set qdfReport = MyDatabase.QueryDefs("mkt_DavidPreEngInvByRpt")
    qdfReport.Parameters("Job Number").Value = sReportParam
    MyWorkspace.BeginTrans
    qdfReport.Execute
    MyWorkspace.CommitTrans ' ensure it is done
    qdfReport.Close
    Set qdfReport = Nothing
    ' get ready to fire up word if we are ready
    If (Len(sShellProgram) > 0) And (Len(sShellCommand) > 0) Then
    Shell Chr$(34) & sShellProgram & Chr$(34) & Chr$(32) & Chr$(34) & sShellCommand & Chr$(34), 1
    End If

    ' sShellProgram value = C:\Program Files (x86)\Microsoft Office\Office14\WinWord.exe
    ' sShellCommand value = /tC:\Templates\Pre-Engagment Timber Pest_David_RSA2012.dot

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •