none
Trying to send query results to a word form RRS feed

  • Question

  • Hi guys, I'm newer to <g class="gr_ gr_35 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="35" id="35">vba</g> and trying to make a button on a form (Access Examiner) to run a query (Notice Q) and then send the results of the query to a word form (I:\Document\OnsiteNotice.docx). I've debugged but nothing shows up as an error. Can anyone tell me what I'm doing wrong? THANKS!

    Private Sub Generate_Notice_Click()
    
    'Save form
    DoCmd.Save acForm, "Access Examiner"
    
    'Run query with current form's parameters
    
    DoCmd.OpenQuery "Notice Q", acViewNormal, acReadOnly
    DoCmd.Requery
    
    'Print to notice form.
    
    Dim appWord As Word.Application
    
    Dim doc As Word.Document
    
    'Avoid error 429, when Word isn't open.
    
    On Error Resume Next
    
    Err.Clear
    
    'Set appWord object variable to running instance of Word.
    
    Set appWord = GetObject(, "Word.Application")
    
    If Err.Number <> 0 Then
    
    'If Word isn't open, create a new instance of Word.
    
    Set appWord = New Word.Application
    
    End If
    
    Set doc = appWord.Documents.Open("I:\Document\OnsiteNotice.docx")
    
    With doc
    
    'Pull data from query results and put in form fields
    
    .FormFields("NoticeDate").Result = Me!Notice_Sent
    .FormFields("FirmName").Result = Me!Primary_Business_Name
    .FormFields("ContactName").Result = Me!Contact_Name
    .FormFields("ContactTitle").Result = Me!Contact_Title
    .FormFields("Contact Address1").Result = Me!Contact_Address
    .FormFields("CRD#").Result = Me!CRD_Number
    .FormFields("Exam#").Result = Me!Exam_Number
    .FormFields("OnsiteDate").Result = Me!OnSite
    .FormFields("ExaminerName").Result = Me!Name
    .FormFields("ExaminerTitle").Result = Me!Title
    .FormFields("ExaminerEmail").Result = Me!Email
    .FormFields("ExaminerPhone").Result = Me!Phone_Number
    .FormFields("Documents Due").Result = Me!Documents_Due_from_Registrant
    
    .Visible = True
    
    .Activate
    
    End With
    
    End Sub


    • Edited by Lyndsers Wednesday, August 23, 2017 3:48 PM
    Wednesday, August 23, 2017 3:46 PM

All replies

  • Why not use Word MailMerge to pull the data from the query?


    Build a little, test a little

    Wednesday, August 23, 2017 5:15 PM
  • Are you actually using Legacy Form Fields, or are you using the new (2007) Content Controls?  The code is different for the latter.

    What happens exactly when you run your code?


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, August 23, 2017 7:02 PM
  • Hello,

    Have your issue been resolved? We would appreciate if you could share the solution here.

    If your issue persists, you could firstly output field value like Me!Notice_Sent to check whether the values are expected. Then hard code the data which would be put in the formfield, for example: .FormFields("NoticeDate").Result = "value" to test if the method could be used to update value of formfield.

    You may try to update the value in Word and record a macro at the same time to see the correct method to set the value of formfield. You could also share us your document via OneDrive.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 1, 2017 8:15 AM
    Moderator
  • As you are passing values from the form into Word you don't need to open the query; simply bind the Access form to it.  The following is a function from an old demo file of mine:

    Sub FillForm(strTemplate As String)

        ' Opens a document in Word and inserts values from
        ' current record in form fields.
        ' Accepts: path to Word template file - String
        
        On Error GoTo Err_Handler
        
        Dim objWord As Object
        Dim objDoc As Object
        Dim frm As Form
        Dim strAddress As String
        
        ' return reference to form
        Set frm = Forms!frmAddresses
        
        ' if Word open return reference to it
        ' else establish reference to it
        On Error Resume Next
        Set objWord = GetObject(, "Word.Application")
        If Err.Number = 429 Then
            Set objWord = CreateObject("Word.Application")
        End If
        
        AppActivate "Microsoft Word"
        On Error GoTo Err_Handler
        
        ' open Word document in maximised window
        objWord.Visible = True
        Set objDoc = objWord.Documents.Add(strTemplate)
        objWord.WindowState = wdWindowStateMaximize
        
        ' insert first and lastname in form fields
        objDoc.FormFields("Text1").Result = frm!FirstName
        objDoc.FormFields("Text2").Result = frm!LastName
        
        Set objDoc = Nothing
        Set objWord = Nothing
        
    Exit_here:
        On Error GoTo 0
        Exit Sub
        
    Err_Handler:
        MsgBox Err.Description & " (" & Err.Number & ")"
        Resume Exit_here

    End Sub

    To pass the values from the Access form's current record into the Word form you merely need to call the function and pass the path to the Word template document into it


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, September 1, 2017 11:00 AM Clarified.
    Friday, September 1, 2017 10:57 AM