none
Access to Word RRS feed

  • Question

  • I am attempting the following, but I keep getting an error.  This is an adaption to a previous code that record that pulled a table. I am trying to pull a recordset set based off the ID in a form and then push to Word as a set of project notes. can anyone help?

    Private Sub cmdInitiationLetter_Click()
    
    Dim oApp        As Object 'Word.application
    Dim oDoc        As Object 'Word.Document
    Dim sFileName   As String
    
    
    On Error Resume Next
    
    Set oApp = GetObject(, "Word.Application")
    sFileName = "C:\Users\IODett\Documents\Cases-C Drive\Templates\Case Initiation ltr 2409.dotx"
    
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    
    On Error GoTo 0
    
    Set oDoc = oApp.Documents.Open(sFileName)
    oApp.Visible = True
    
    
    'Then For Text CC you'd do something like:
    
    'oDoc.SelectContentControlsByTitle("NameOfCC")(1).Range.Text = "Value"
    
    
    'For CC Check boxes you'd do something like:
    
    'oDoc.SelectContentControlsByTitle("NameOfCC")(1).Checked = True / False
    
    
    
    
    End Sub
    
    Private Sub Command200_Click()
    
    DoCmd.OpenReport "Case Notes", acViewPreview, , "Project = Forms![Project Details]![ID]"
    
    End Sub
    
    Private Sub Command201_Click()
         
       Dim appWord As Word.Application
       
        Dim strSQL As String
        Dim myRecordset As ADODB.Recordset
        Set myRecordset = New ADODB.Recordset
        myRecordset.ActiveConnection = CurrentProject.Connection
        
        strSQL = "SELECT TrackingDate, Notes " & _
                 "FROM [Tasker UNION] " & _
                 "WHERE (Project) = [Forms]![Project Details]![ID] " & _
                 "ORDER BY TrackingDate"
    
        myRecordset.Open strSQL
    
       
       Dim docWord As Word.Document
       Dim rngCurrent As Word.Range
       
       On Error GoTo Err_AccessToWordAutomation
       
       '-- Open the projects table
       'Set rsProjects = CurrentDb.OpenRecordset(qryTaskerUnionByProject)
       
       Set appWord = New Word.Application
       appWord.Visible = True
       
       '-- Create the document variable
       Set docWord = appWord.Documents.Add()
    
         
       '-- Set Range object to current document
       Set rngCurrent = docWord.Content
       
        
        With rngCurrent
            
            '-- Add a report header
            .ParagraphFormat.Alignment = wdAlignParagraphCenter
            .InsertAfter "Project Task Report"
            .InsertAfter vbCrLf
            .InsertAfter Date
            .InsertParagraphAfter
            .Collapse Direction:=wdCollapseEnd
            
                 
            '-- Loop through projects, breaking on each resource
            Do Until rsProjects.EOF
            
                '-- Write out the task, formatting it as a bullet.
                .InsertAfter rsProjects!TrackingDate
                .InsertAfter vbCrLf
                .ListFormat.ApplyBulletDefault
            
                rsProjects.MoveNext
                
            Loop
        
        End With
        
        Set docWord = Nothing
     
       Exit Sub
       
    Err_AccessToWordAutomation:
       
       Beep
       MsgBox "The Following Automation Error has occurred:" & _
                    vbCrLf & Err.Description, vbCritical, "Automation Error!"
       Set appWord = Nothing
       Exit Sub
    
    End Sub

    Thursday, March 9, 2017 3:36 PM

Answers

  • Your SQL statement is referencing the Forms value as a literal, which cannot be resolved. Should look like the following instead:

        strSQL = "SELECT TrackingDate, Notes " & _
                 "FROM [Tasker UNION] " & _
                 "WHERE (Project) = '" & [Forms]![Project Details]![ID] & "' " & _
                 "ORDER BY TrackingDate"

    If Project is a numeric value then simply remove the single quotes. I assumed the data type to be Text or String.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 16, 2017 12:32 PM

All replies

  • Hi,

    What error message were you getting and which line was causing it (highlighted)?

    Thursday, March 9, 2017 4:04 PM
  • Run-time error '-2147217904 (8004e10):

    No value given for one or more required parameters.

    Thursday, March 9, 2017 6:56 PM
  • Which line is highlighted by the debugger?

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

    Thursday, March 9, 2017 7:32 PM
  • the line

    myrecordset.open strSQL is highlifhted. 

    And I just realized I pasted more than needed. I am only concerned with the Command201_Click() procedure

    Thursday, March 9, 2017 8:44 PM
  • Hi NoviceVBAuser1775,

    if you see the value of strSQL then you will find query below.

    SELECT TrackingDate, Notes FROM [Tasker UNION] WHERE (Project) = [Forms]![Project Details]![ID] ORDER BY TrackingDate

    if you run the query in Access then you will find that the field mentioned in the query not in the table "Tasker UNION". in that case it will ask for parameters.

    so please first try to confirm that you spell the field name correctly.

    other thing try to store the value of [Forms]![Project Details]![ID] in variable and try to pass the variable in query.

    first try to run the query in Access , if it run successfully then try to use that in your code.

    Regards

    Deepak


    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, March 10, 2017 6:11 AM
    Moderator
  • The string was built in an access query and successful ran prior to placing it into the procedure.
    Wednesday, March 15, 2017 5:51 PM
  • I believe the issue here is that ADO doesn't know about Access forms, so the implicit parameter "[Forms]![Project Details]![ID]" can't be resolved.  The easiest way to handle this is to build the literal value of the ID into the query string, like this:

    strSQL = "SELECT TrackingDate, Notes " & _
             "FROM [Tasker UNION] " & _
             "WHERE Project = " & [Forms]![Project Details]![ID] & _
             " ORDER BY TrackingDate"
    
    

    That should work so long as Project is a numeric field.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 15, 2017 9:17 PM
  • Hi NoviceVBAuser1775,

    I suggest you to use debug.print and try to check your sqlstr variable.

    see the query and check the value of [Forms]![Project Details]![ID] is added to query or not.

    if value is not added then try to use a different way to get the value from form and try to pass in the sqlstr.

    Regards

    Deeepak


    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.

    Thursday, March 16, 2017 5:37 AM
    Moderator
  • Your SQL statement is referencing the Forms value as a literal, which cannot be resolved. Should look like the following instead:

        strSQL = "SELECT TrackingDate, Notes " & _
                 "FROM [Tasker UNION] " & _
                 "WHERE (Project) = '" & [Forms]![Project Details]![ID] & "' " & _
                 "ORDER BY TrackingDate"

    If Project is a numeric value then simply remove the single quotes. I assumed the data type to be Text or String.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 16, 2017 12:32 PM
  • Thank you for all help provided  so far.  I have now went back to my original script, which I provided below, and added some of the suggestions provided above.

    I now receive the following error message: 

    Item not found in this collection

    Private Sub Command201_Click()
           
       Dim appWord As Word.Application
       
       Dim strTheme As String
       Dim strCurrResource As String
       Dim rsProjects As Recordset
       Dim docWord As Word.Document
       Dim rngCurrent As Word.Range
       
       On Error GoTo Err_AccessToWordAutomation
       
       '-- Open the projects table
       Set rsProjects = CurrentDb.OpenRecordset( _
            "SELECT TrackingDate, Notes FROM [Tasker UNION] " & _
            "WHERE ([Tasker UNION].Project) = '" & [Forms]![Project Details]![ID] & "' " & _
            "ORDER BY [Tasker UNION].TrackingDate")
       
       Set appWord = New Word.Application
       appWord.Visible = True
       
       '-- Create the document variable
       Set docWord = appWord.Documents.Add()
    
         
       '-- Set Range object to current document
       Set rngCurrent = docWord.Content
       
        
        With rngCurrent
            
            '-- Add a report header
            .ParagraphFormat.Alignment = wdAlignParagraphCenter
            .InsertAfter "Project Task Report"
            .InsertAfter vbCrLf
            .InsertAfter Date
            .InsertParagraphAfter
            .Collapse Direction:=wdCollapseEnd
            
                 
            '-- Loop through projects, breaking on each resource
            Do Until rsProjects.EOF
            
                '-- If new resource, write out the resource information
                If strCurrResource <> rsProjects!irsProject Then
                    .ParagraphFormat.Alignment = wdAlignParagraphLeft
                    .InsertParagraphAfter
                    .Collapse Direction:=wdCollapseEnd
                    .InsertAfter "Tasks for Resource: " & rsProjects!irsProject
                    .InsertParagraphAfter
                    .Collapse Direction:=wdCollapseEnd
                    strCurrResource = rsProjects!irsProject
                End If
                
                '-- Write out the task, formatting it as a bullet.
                .InsertAfter rsProjects!chrTitle
                .InsertAfter vbCrLf
                .ListFormat.ApplyBulletDefault
            
                rsProjects.MoveNext
                
            Loop
        
        End With
        
        Set docWord = Nothing
     
       Exit Sub
       
    Err_AccessToWordAutomation:
       
       Beep
       MsgBox "The Following Automation Error has occurred:" & _
                    vbCrLf & Err.Description, vbCritical, "Automation Error!"
       Set appWord = Nothing
       Exit Sub
       
    End Sub

    Can anyone assist from here?

    Friday, March 17, 2017 2:56 AM
  • I'm going to guess that your error occurs when you reference rsProjects!irsProject since this column does not appear to be in your SELECT statement.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Friday, March 17, 2017 3:08 AM
  • Hi NoviceVBAuser1775,

    can you tell us on which line you get the error "Item not found in this collection"?

    also want to confirm from you that please try to post the value of strsql after assigning the value to it.

    so that we can take a solid decision about that line.

    if there is no error then we can move further to search for other possibilities for the issue.

    Regards

    Deepak


    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, March 17, 2017 9:22 AM
    Moderator
  • It doesn't seem to be the issue here.  I take that is receive the same message.
    Saturday, March 18, 2017 4:38 AM
  • It doesn't seem to be the issue here.  I take that is receive the same message.
    On which line of code does the error occur? Did you set a Debug breakpoint and step through code?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, March 18, 2017 11:31 AM
  • Hi NoviceVBAuser1775,

    your one line of reply is not enough for us to understand what you had tried and what you get as an output.

    it is better if you try to mentioned clearly what you remove, what you added, what you modified with the code sample.

    then put the picture of output below.

    it will helpful for us to understand your trial attempt to solve the issue.

    currently many community members trying to give you suggestion but we are unable to understand correctly which suggestion you implement to solve the issue.

    Regards

    Deepak


    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.

    Monday, March 20, 2017 8:57 AM
    Moderator
  • Thank you for the assistance.  Your answer went a long way to allowing me to get it to work.  I did what you suggested and there was field named wrong.  Once  I was able to see it, it worked well.  Thanks again!
    Tuesday, March 28, 2017 7:24 PM