Pulling Data from an Access Database to a Word Document (from all the records for a given field in a table)

Sub Button3_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String
    Dim strSQL As String
   
    Dim MyText As String
    Dim MyRange As Object
    Set MyRange = Selection.Range

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Folder\MyDataBase.mdb;User Id=admin;Password=;"
    cn.Open strConn
    strSQL = "SELECT ThirdField FROM Messages WHERE client_id = 222 order by [date]"
    rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
    While Not rs.EOF Then
        MyText = rs.Fields("ThirdField").Value
        Selection.Fields.Add Range:=Selection.Range, _
            Type:=wdFieldQuote, Text:=MyText
        rs.MoveNext
    Loop
    rs.Close
    cn.Close
End Sub

AW

Many thanks for your response to my question.
The code provided very kindly by you perhaps needs some editing.

When I pasted the code at it is the following line got highlighted as red:

While Not rs.EOF Then

Assuming this may be a typing error I changed this line to:

Do While Not rs.EOF

and then it did not come up as red. However, when I run the code after making the above change I get the following error message:

Run-time error ‘-2147217904(80040e10)’:
No value given for one or more required parameters

With the following line highlighted when I click the debug button

rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText

I was under the assumption that you were using the same database( and code) as was used in your previous question, which you referred to.  That was intended to only pull a single record.  If you simply change THAT code as I suggested, you should then be able to pull ALL of the records for client_id = 222.  

What are the values of the variables in the line which causes the error?

AW

I am using the code on exactly the same database and the table, the only difference is that I have changed the names of the table (t_history from Messages) and the name of the field I want pull data from to Bed02 from (ThirdField). I include the code I attaching the code as I am using it now.
I changed the names of the table, and field in the code provided by to correspond with the new names in my database and after checking everything very carefully, the code provided by you is actually working now, but with a few problems.

It is pulling data from different records from the field Bed02 (previous ThirdField) but it types them in the word document without any space between characters of a word and without any space between data from different records. It is also not able to pull any number data and replaces it with a strange character.

This is how it pulled and presented data from two records for the field (Bed02) from the table.  

ThiswastheFirstEntryforPatientÞThisistheLatestEntryforPatientNumberÞ

The actual data (text) in the first and last record for this patient were two sentences and I would have liked it to show in the word document as below:

This was the First Entry for Patient 222
This is the Latest Entry for Patient Number 222

Thanks again for your continued support.

Private Sub Button1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String
    Dim strSQL As String
    
    Dim MyText As String
    Dim MyRange As Object
    Set MyRange = Selection.Range

    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Documents\Writer\QuickDatabase.mdb;User Id=admin;Password=;"
    cn.Open strConn
    strSQL = "SELECT Bed02 FROM t_history WHERE patient_id = 222"
    rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
   
    
    Do While Not rs.EOF

       MyText = rs.Fields("Bed02").Value
       Selection.Fields.Add Range:=Selection.Range, _
           Type:=wdFieldQuote, Text:=MyText
       rs.MoveNext
   Loop
   rs.Close
   cn.Close


End Sub

Select allOpen in new window

Thank you for modified solution. Unfortunately it has not worked either.
The result from using the code was follows:

ThisisthefirstentryforPatientÞThiswastheFirstEntryforPatientÞThisistheLatestEntryforPatientNumberÞThisisthelastentryforPatientÞ

I think that perhaps I have not been able to explain the problem well in my previous comment.
What I meant to say was that in my database there is a table name t_history. This table has two records for Patient_id 222 at present.
The first of these two records has in has the following sentence the field named Bed02:
This is the first entry for Patient 222.

The second of these two records has the following sentence in the field named Bed02:
This is the 2nd entry for Patient 222.

I am hoping that the code for Button1 Click event will pull both these records for the Field Bed02 and get them typed on the word document such that first record gets typed first and the second record gets typed on the new line.
So the end result of pulling these records through Button1 Click will look like this:

This is the first entry for Patient 222.
This is the Latest entry for Patient 222.

I am attaching the database file that I am using for this code in case that helps.
I am a beginner and I asked posted this question only to understand a concept.
If I could write pseudo code for this problem:
I think it will
Pull the first record for the patient_id 222 from the Field named Bed02.
Move to the next record (2nd record) for  patient_id 222 from the Field named Bed02.
Then Moved to the next (third record) for patient_id 222 from the Field named Bed02.
Keep on doing this until you reach the last record for patient_id 222 in this table and stop after pulling it into the word.
QuickDatabase.mdb

Try this:

Missed the code snippet:

Private Sub Button1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String
    Dim strSQL As String
    
    Dim MyText As String
    Dim MyRange As Object
    Set MyRange = Selection.Range

    MyText = ""

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Documents\Writer\QuickDatabase.mdb;User Id=admin;Password=;"
    cn.Open strConn
    strSQL = "SELECT Bed02 FROM t_history WHERE patient_id = 222"
    rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
   
    
    Do While Not rs.EOF
      If MyText = "" Then
             MyText = rs.Fields("Bed02").Value
      Else
             MyText = MyText & vbCRLF & rs.Fields("Bed02").Value
      End if
      rs.MoveNext
   Loop
   rs.Close
   cn.Close
           Selection.Fields.Add Range:=Selection.Range, _
           Type:=wdFieldQuote, Text:=MyText


End Sub

Select allOpen in new window

Thank you for your solution. However, the output from this code was almost the same as from the previous solution. In the word document it looked like this:

ThiswastheFirstEntryforPatientÞThisistheSecondEntryforPatientNumberÞ

As you will note, the code has pulled the contents of the Field Bed02 from the table but without any space between the letters of the sentences stored in the relevant table field and without inserting any Line Break between the contents of Bed02 from the first and second record in the table.

The solution did not solve the problem completely, but because it got quite close to solving it I am accepting it as a solution.