Access to Word

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

Answer 1

Hi,

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

Answer 2

Run-time error '-2147217904 (8004e10):

No value given for one or more required parameters.

Answer 3

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

Answer 4

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

Answer 5

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 [e-mail].

Answer 6

The string was built in an access query and successful ran prior to placing it into the procedure.

Answer 7

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

Answer 8

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 [e-mail].

Answer 9

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)

Answer 10

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?

Answer 11

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)

Answer 12

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 [e-mail].

Answer 13

It doesn't seem to be the issue here.  I take that is receive the same message.

Answer 14

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)

Answer 15

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 [e-mail].

Answer 16

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!